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.
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.