A script-enabled browser is required for this page to function properly.

%ROWTYPE or %TYPE Required in Variable Declarations

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.

Variable declarations Examples

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.

Example 1:

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
...


Example 2:

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
...


Example 3:

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 V1 conversion utility