Question:
When using my user functions I receive the error:
ORA-06553 expression of of wrong type.
What might be causing this error?
Answer:
Oracle SQL Plus example:
SQL> SELECT MYFUNCTION('ASTRING') FROM MYTABLE
2 /
SELECT MYFUNCTION('ASTRING') FROM MYTABLE
*
ERROR at line 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type
In this example the function has been defined with a return
type of boolean which is not a valid field type.
In other words the return type cannot be returned as part
of a sql result (see below).
USER FUNCTIONS FROM THE ORACLE 8 SQL REFERENCE:
You can write your own user functions in PL/SQL to
provide functionality that is not available in SQL or
SQL functions. User functions are used in a SQL statement
anywhere SQL functions can be used; that is, wherever
expression can occur.
For example, user functions can be used in the following:
the select list of a SELECT command
the condition of a WHERE clause
CONNECT BY, START WITH, ORDER BY, and GROUP BY clauses
the VALUES clause of an INSERT command
the SET clause of an UPDATE command
For a complete description on the creation and use of
user functions, see Oracle8 Application Developer's Guide.
Oracle user functions, ORA-06553 expression of of wrong type