oracle PLS-00201 - 必须声明标识符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23526870/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PLS-00201 - identifier must be declared
提问by Mushy
I executed a PL/SQL script that created the following table
我执行了创建下表的 PL/SQL 脚本
TABLE_NAME VARCHAR2(30) := 'B2BOWNER.SSC_Page_Map';
I made an insert function for this table using arguments
我使用参数为此表创建了一个插入函数
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)
I was notified I had to declare B2BOWNER.SSC_Page_Mapprior to it appearing as an argument to my function. Why am I getting this error?
我被告知我必须B2BOWNER.SSC_Page_Map在它作为我的函数的参数出现之前声明。为什么我收到这个错误？
EDIT: Actual 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
EDIT:Complete PL/SQL Function
编辑：完整的 PL/SQL 函数
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
EDIT: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);
The error
错误
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
表已在正确的架构中进行了验证，并且具有正确的属性名称和类型
EDIT: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;
The output I received is
我收到的输出是
1
PL/SQL procedure successfully completed.
I have access to the table.
我可以访问桌子。
EDIT:
编辑：
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.
因此，我最终使用 PL/SQL 通过模式对表进行了插入，并且运行良好。看来我根本没有创建功能的权限，但这是一个假设。
EDIT:
编辑：
Actual table DDL statement
实表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';
采纳答案by Mushy
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.
在 下创建 TABLE 时B2BOWNER，一定要在 PL/SQL 函数前面加上 Schema 名称；即B2BOWNER.F_SSC_Page_Map_Insert。
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.
直到 DBA 指出这一点，我才意识到这一点。我本可以在我的根 USER/SCHEMA 下创建表，并且 PL/SQL 函数可以正常工作。
回答by udhay
The procedure name should be in caps while creating procedure in database. You may use small letters for your procedure name while calling from Java class like:
在数据库中创建过程时，过程名称应大写。从 Java 类调用时，您可以使用小写字母作为过程名称，例如：
String getDBUSERByUserIdSql = "{call getDBUSERByUserId(?,?,?,?)}";
In database the name of procedure should be:
在数据库中，过程的名称应该是：
GETDBUSERBYUSERID -- (all letters in caps only)
This serves as one of the solutions for this problem.
这是该问题的解决方案之一。
回答by elfekz
you should give permission on your db
你应该允许你的数据库
grant execute on (packageName or tableName) to user;
