Mega Code Archive

 

Categories / Delphi / ADO Database

 

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

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