Oracle user functions, ORA-06553 expression of of wrong type

by Mar 31, 1999

 Question and Answer Database

     FAQ: FAQ4561B - Oracle user functions, ORA-06553 expression of of wrong type
Category: Database (Oracle)
Platform: All-32Bit
 Product: All-CBuilder,   C++Builder1.0,   C++Builder3.x,   C++Builder4.x,   Delphi2.x,   Delphi3.x,   Delphi4.x,   VdBase7.x,   

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


7/13/99 10:50:43 AM
 

Article originally contributed by Bill Curtis