PLS-00201-标识符必须声明 [英] PLS-00201 - identifier must be declared
问题描述
我执行了创建下表的PL/SQL脚本
I executed a PL/SQL script that created the following table
TABLE_NAME VARCHAR2(30) := 'B2BOWNER.SSC_Page_Map';
我使用参数
CREATE OR REPLACE FUNCTION F_SSC_Page_Map_Insert(
p_page_id IN B2BOWNER.SSC_Page_Map.Page_ID_NBR%TYPE,
p_page_type IN B2BOWNER.SSC_Page_Map.Page_Type%TYPE,
p_page_dcpn IN B2BOWNER.SSC_Page_Map.Page_Dcpn%TYPE)
我收到通知，我必须先声明B2BOWNER.SSC_Page_Map，然后它才能显示为函数的参数.为什么会出现此错误?
I was notified I had to declare B2BOWNER.SSC_Page_Map prior to it appearing as an argument to my function. Why am I getting this error?
编辑:实际错误
Warning: compiled but with compilation errors
Errors for FUNCTION F_SSC_PAGE_MAP_INSERT
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/48 PLS-00201: identifier 'SSC_PAGE_MAP.PAGE_ID_NBR' must be declared
0/0 PL/SQL: Compilation unit analysis terminated
编辑:完整的PL/SQL功能
Complete PL/SQL Function
RETURN INTEGER
IS
TABLE_DOES_NOT_EXIST exception;
PRAGMA EXCEPTION_INIT(TABLE_DOES_NOT_EXIST, -942); -- ORA-00942
BEGIN
INSERT INTO
B2BOWNER.SSC_Page_Map VALUES(
p_page_id,
p_page_type,
p_page_dcpn);
RETURN 0;
EXCEPTION
WHEN TABLE_DOES_NOT_EXIST THEN
RETURN -1;
WHEN DUP_VAL_ON_INDEX THEN
RETURN -2;
WHEN INVALID_NUMBER THEN
RETURN -3;
WHEN OTHERS THEN
RETURN -4;
END;
SHOW ERRORS PROCEDURE F_SSC_Page_Map_Insert;
GRANT EXECUTE ON F_SSC_Page_Map_Insert TO B2B_USER_DBROLE;
RETURN INTEGER
编辑:我更改了参数并收到了与插入命令有关的新错误
I change the arguments and received a new error related to the insert command
CREATE OR REPLACE FUNCTION F_SSC_Page_Map_Insert(
p_page_id IN INTEGER,
p_page_type IN VARCHAR2,
p_page_dcpn IN VARCHAR2)
RETURN INTEGER
IS
TABLE_DOES_NOT_EXIST exception;
PRAGMA EXCEPTION_INIT(TABLE_DOES_NOT_EXIST, -942); -- ORA-00942
BEGIN
INSERT INTO
B2BOWNER.SSC_Page_Map VALUES(
p_page_id,
p_page_type,
p_page_dcpn);
错误
Errors for FUNCTION F_SSC_PAGE_MAP_INSERT
LINE/COL ERROR
-------- -----------------------------------------------------------------
17/18 PL/SQL: ORA-00942: table or view does not exist
16/5 PL/SQL: SQL Statement ignored
已在正确的架构内以及具有正确的属性名称和类型的情况下验证了表
The tables has been verified within the correct schema and with the correct attribute names and types
编辑:我执行了以下命令，以检查我是否可以访问
I executed the following command to check if I have access
DECLARE
count_this INTEGER;
BEGIN
select count(*) into count_this
from all_tables
where owner = 'B2BOWNER'
and table_name = 'SSC_PAGE_MAP';
DBMS_OUTPUT.PUT_LINE(count_this);
END;
我收到的输出是
1
PL/SQL procedure successfully completed.
我可以访问表格.
因此，我最终使用PL/SQL通过模式在表中进行了插入，并且工作正常.看来我根本没有创建功能的权限，但这只是一个假设.
So I finally conducted an insert into the table via the schema using PL/SQL and it worked fine. It appears I simply do not have authority to create functions but that is an assumption.
实际表DDL语句
v_create := 'CREATE TABLE ' || TABLE_NAME || ' (
PAGE_ID_NBR NUMERIC(10) NOT NULL Check(Page_ID_NBR > 0),
PAGE_TYPE VARCHAR2(50) NOT NULL,
PAGE_DCPN VARCHAR2(100) NOT NULL,
PRIMARY KEY(Page_ID_NBR, Page_Type))';
EXECUTE IMMEDIATE v_create;
COMMIT WORK;
COMMIT COMMENT 'Create Table';
推荐答案
在B2BOWNER下创建TABLE时，请确保以Schema名称为PL/SQL函数添加前缀.即B2BOWNER.F_SSC_Page_Map_Insert.
When creating the TABLE under B2BOWNER, be sure to prefix the PL/SQL function with the Schema name; i.e. B2BOWNER.F_SSC_Page_Map_Insert.
直到DBA指出这一点，我才意识到这一点.我可以在根USER/SCHEMA下创建该表，并且PL/SQL函数可以正常工作.
I did not realize this until the DBAs pointed it out. I could have created the table under my root USER/SCHEMA and the PL/SQL function would have worked fine.
这篇关于PLS-00201-标识符必须声明的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
