本文介绍了PLS-00201-标识符必须声明的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我执行了创建下表的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-标识符必须声明的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-18 20:14