oracle-databaseplsqlpreprocessororacle19c

Anonymous PL/SQL block error in Pro*C but not in SQL Developer: CSF-S-00201, identifier 'RAISE_APPLICATION_ERROR' must be declared


In a Pro*C file, I have a very straightforward anonymous PL/SQL block like this:

EXEC SQL BEGIN DECLARE SECTION;
     unsigned long long stuff = 0;
EXEC SQL END DECLARE SECTION;

EXEC SQL EXECUTE
DECLARE
    nrows pls_integer := 0;
BEGIN
    select count(*) into nrows from tbl;

    if nrows != 1 then
        RAISE_APPLICATION_ERROR(-20000,
                    'The table must contain exactly one row');
    else
        select stuff into :stuff FROM tbl;
    end if;
END; 
END-EXEC;

Pro*C protests with:

CSF-S-00201, identifier 'RAISE_APPLICATION_ERROR' must be declared

However, moving the anonymous PL/SQL block to the SQL developer (and adding a stuff variable):

VARIABLE stuff NUMBER;

DECLARE
    nrows pls_integer := 0;
BEGIN
    select count(*) into nrows from tbl;

    if nrows != 1 then
        RAISE_APPLICATION_ERROR(-20000,
                    'The table must contain exactly one row');
    else
        select stuff into :stuff FROM tbl;
    end if;
END;
/

works fine. What could be going on? The schema I'm using to compile the Pro*C file and to execute the block in SQL developer is the same.

I have set the following options:

sqlcheck=semantics
parse=partial
code=cpp
common_parser=yes

sqlcheck=semantics is mandatory if the file contains PL/SQL blocks, I have tried to remove common_parser=yes but the error is the same, and I can't remove parse=partial and code=cpp, but I don't think it would affect it whatsoever.


Solution

  • Not entirely sure why it's necessary, but you can avoid the error by explicitly referring to the DBMS_STANDARD package:

        if nrows != 1 then
            DBMS_STANDARD.RAISE_APPLICATION_ERROR(-20000,
                        'The table must contain exactly one row');
    

    The PL/SQL reference mentions that

    You can reference identifiers declared in the packages STANDARD and DBMS_STANDARD without qualifying them with the package names, unless you have declared a local identifier with the same name

    but that isn't the case from Pro*C.