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.

About checking oracle database version: rediscovery of hot water?

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

Little problem of the day:
how to check for the oracle database version currently running from inside a PL/SQL program or query?

I knew there were various built-in dictionary views for checking the version of installed components, but i had another requirement, i wanted to run this query without special privileges, so, running the code as SYS was out of question.
I made a first attempt by memory:

select * from V$VERSION;

BANNER                                                      
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production                      
CORE 10.2.0.1.0 Production                                  
TNS for Linux: Version 10.2.0.1.0 - Production              
NLSRTL Version 10.2.0.1.0 - Production                      

5 rows selected

Now, this kind query is good for a human being but i find it of little use inside a program.

I mean, doesn't Oracle really provide something better to check for?

The answer is yes.

There is a view called PRODUCT_COMPONENT_VERSION that any user can query and it returns a formatted VERSION column.

select * from PRODUCT_COMPONENT_VERSION;

PRODUCT                             VERSION    STATUS
----------------------------------- ---------- ----------
Oracle Database 10g Express Edition 10.2.0.1.0 Product
NLSRTL                              10.2.0.1.0 Production
PL/SQL                              10.2.0.1.0 Production
TNS for Linux:                      10.2.0.1.0 Production

Apparently all we need to do is to filter out the results:

select * from PRODUCT_COMPONENT_VERSION
where product like 'Oracle Database%';

PRODUCT                             VERSION    STATUS
----------------------------------- ---------- ----------
Oracle Database 10g Express Edition 10.2.0.1.0 Product

A further look at web results for PRODUCT_COMPONENT_VERSION turned up some interesting OTN forum comments reporting that the query above would fail on an Oracle Personal Edition Database because column PRODUCT contains the word Personal before Oracle.
In view of these bizarre inconsistencies, i modified the query to minimize the possibilities of further surprises:

select version from PRODUCT_COMPONENT_VERSION
where lower(product) like '%oracle%database%';

Finally note that as of Oracle 10gR1 there is a package called DBMS_DB_VERSION containing two functions and several constants enabling simple version checking from PL/SQL.
Oddly enough the documentation of 10gR1 does not mention this package in the PL/SQL Packages and Types Reference, but the package is apparently there, although i must say i executed the code on a 10.1.0.5 (the results displayed below are taken from Oracle XE though).

begin
 dbms_output.put_line(dbms_db_version.version);
 dbms_output.put_line(dbms_db_version.release);
end;

10
2

In conclusion, the packaged function approach works fine as long as you are sure that you are running on version 10g or above and you don't need to check for the patch level, so i can't say if the package has been added later with a patch.