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

2 comments:

  1. UnknownFriday, 21 March, 2014

    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.

    ReplyDelete
  2. Byte64Monday, 24 March, 2014

    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

    ReplyDelete

I appreciate your comment however bear in mind that I might not have the time to reply soon.
Normally I do not reply to all comments but I am glad if you found something useful or if you learned something new, in that case I strongly encourage you to promote the article with the +1 google button.
Flavio