This type of parsing error is quickly explained:
select dbms_db_version.version from dual;In the example above dbms_db_version.version is not a packaged function, but a packaged constant.
SQL Error: ORA-06553: PLS-221: 'VERSION' is not a procedure or is undefined
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:
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.
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
Post a Comment