Loading...

Tuesday, May 19, 2009

ORA-06553: PLS-221: 'name' is not a procedure or is undefined

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

This type of parsing error is quickly explained:
select dbms_db_version.version from dual;

SQL Error: ORA-06553: PLS-221: 'VERSION' is not a procedure or is undefined
In the example above dbms_db_version.version is not a packaged function, but a packaged constant.
Packaged constants can be used inside PL/SQL procedures but not inside ordinary SQL statements, however you can specify such constants in SQL DML statements inside programs:
declare
v number;
begin
select dbms_db_version.version into v
from dual;
dbms_output.put_line(v);
end;
/

If you need to specify constants inside DML statements such as SELECT/DELETE/INSERT/UPDATE statements, then you can create wrapping functions as follows:

create or replace function db_version
return integer deterministic
is
begin
return dbms_db_version.version;
end;

select db_version from dual;

DB_VERSION
-------------
10

See message translations for PLS-00221 and search additional resources.

2 comments:

Ryan Snead said...

The same error occurs with DBMS_RANDOM.STRING in Oracle 11gR2.
Using the same form you showed with DBMS_DB_VERSION.VERSION works.

SELECT into var
FROM DUAL;

I do find it strange that DBMS_RANDOM.STRING behaves this way since it is most certainly not a constant.

Byte64 said...

Ryan,
I tried both on 10gR2 and 11gR2 and the following works in either case:

-- pure SQL
select DBMS_RANDOM.STRING('a',3) from dual;

Indeed DBMS_RANDOM.STRING is a packaged function, not a constant.

If you omit the parameters, then you get an error, but obviously a different one, not PLS-221.

And if you try this on 11gR2 it's still returning PLS-221:

select DBMS_DB_VERSION.VERSION from dual;

Regards
Flavio

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles