CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
Manoj Wadhwani
Manoj Wadhwani

Reputation: 1547

creating a stored procedure in Oracle complain about invalid variable declaration

When I try to create a stored procedure like this:

create or replace
procedure USR_Trial
( auth out usrr.DEPARTMENT 
)
AS
BEGIN
  select authority_id 
  into auth
  from usrr where user_id='G68EF610';
END USR_Trial;

I get the following error:

Error(2,1): PLS-00488: invalid variable declaration:  object 'USRR.DEPARTMENT' must be a type or subtype

How can I fix this?

Upvotes: 1

Views: 1916

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132710

If usrr.DEPARTMENT is a column in a table and you want the OUT parameter to be of the same datatype as that column then the syntax is:

create or replace
procedure USR_Trial
( auth out usrr.DEPARTMENT%type 
)
...

Upvotes: 6

Related Questions