The PL/SQL V8 compiler will issue an error on the following illegal syntax (which the PL/SQL V2 compiler incorrectly allows):
return variable-expression
procedure (parameter IN <table_name>)
procedure (parameter IN <table_name>.<column_name>)
The correct syntax should be:
return variable-type procedure (parameter IN <table_name>%ROWTYPE)
procedure (parameter IN <table_name>.<column_name>%TYPE)
To avoid an error, use the datatype of the expression, append %ROWTYPE
to the table name, or append %TYPE
to the column name.
These examples first present PL/SQL V2 code that will generate a compiler error, then show how you might edit the code to avoid the error.
Change this code:
FUNCTION return_column RETURN dept.dname IS
. . .
. . .to this:
FUNCTION return_column RETURN dept.dname%TYPE IS
. . .
Change this code:
PROCEDURE foo (name IN dept.dname) IS
. . .
. . .to this:
PROCEDURE foo (name IN dept.dname%TYPE) IS
. . .
Change this code:
FUNCTION return_table RETURN dept IS
. . .
. . .to this:
FUNCTION return_table RETURN dept.%ROWTYPE IS
. . .
Change this code:
PROCEDURE foo (name IN dept) IS
. . .
. . .to this:
PROCEDURE foo (name IN dept%ROWTYPE) IS
. . .
This example shows an exception where you cannot use %TYPE
.
When declaring parameters, change this code:
FUNCTION return_parm (x IN NUMBER)
RETURN x IS
. . .
. . .to this:
FUNCTION return_parm (x IN NUMBER)
RETURN number IS
. . .
About the PL/SQL conversion utility
Copyright © 1984, 2005, Oracle. All rights reserved.