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.

ORA-06553: PLS-382: expression is of wrong type

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

If you are a skilled developer, feel free to skip reading this entry aimed to Oracle newbies ;-)

SELECT apex_util.is_username_unique('BOB')
FROM dual;

SQL Error: ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type

Packaged function APEX_UTIL.IS_USERNAME_UNIQUE returns a BOOLEAN type value.
Booleans are not allowed inside SQL expressions, they are only allowed within PL/SQL expressions.

If you need to call a function returning a boolean value inside a SQL query, you'll need to create a wrapper function returning either a number or a character value.
The following sample function wraps APEX_UTIL.IS_USERNAME_UNIQUE returning a single byte character value (either Y or N, corresponding to TRUE and FALSE respectively):

CREATE OR REPLACE
FUNCTION IS_USERNAME_UNIQUE_YN(
p_username IN VARCHAR2
) RETURN CHAR
AS
BEGIN
RETURN CASE WHEN APEX_UTIL.IS_USERNAME_UNIQUE(p_username)
THEN 'Y'
ELSE 'N' END;
END;
/

SELECT is_username_unique_yn('BOB') as "UNIQUE"
FROM dual;

UNIQUE
------
N

Note also that booleans parameter passing is forbidden even in nested functions inside SQL queries, so, imagine to have two functions, one returning a boolean (boolean_fn) value and another one accepting a boolean value (boolean2yn):

create or replace
function boolean_fn
return boolean
is
begin
 return true;
end;
/

create or replace
function boolean2yn(p in boolean)
return char
is
begin
 return case when p then 'Y' else 'N' end;
end;
/

select boolean2yn(boolean_fn) from dual;

SQL Error: ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type

Oracle Application Express allows the developer to choose from SQL expressions or PL/SQL expressions when writing validation rules or computations. This flexibility is a time saver because there is no need to create such wrapper functions in most cases.
You must use SQL expressions when you reference functions like NVL2 and DECODE. PL/SQL expressions are mandatory when boolean return values or boolean input parameters are involved.

See message translations for ORA-06553 and PLS-00382 and search additional resources.