CODEWITHSUNDEEP
oracle-databaseplsql
DavidG
DavidG

Reputation: 313

syntax error when declaring variables in a pl/sql procedure

This is sending me a bit mad. I'm trying to add in a variable to a procedure, but it wasn't working - I just got this error message:

[Error] Syntax check (25: 7): ERROR line 25, col 7, ending_line 25, ending_col 12, Found 'number', Expecting: ; -or- .. := DEFAULT NOT NULL -or- % -or- ( . @

I knocked up a really basic procedure below to isolate the problem and now I'm completely stuck, as every basic syntax guide I've looked as says to do what I've done. Why can't i declare variables as shown below? I normally code in SQL Server if that's any clue as to my problem. Many thanks if anyone can help!

CREATE OR REPLACE PROCEDURE MRCS.pro_xxx_test1 (cats out sys_refcursor)
IS

declare

spoon number;

balls varchar2(3);

BEGIN

 open cats for select * from dual;

   end;

/

Upvotes: 23

Views: 80524

Answers (4)

Groot_codes
Groot_codes

Reputation: 1

"Declare" keyword is not required for creating variables in procedure. Declare local variable between IS and BEGIN block for procedure and function

CREATE OR REPLACE PROCEDURE MRCS.pro_xxx_test1 (cats out sys_refcursor) IS spoon number; balls varchar2(3); BEGIN

open cats for select * from dual;

end;

Upvotes: 0

sandeep gupta
sandeep gupta

Reputation: 59

Declare local variable between IS and BEGIN block for procedure and function

CREATE OR REPLACE PROCEDURE MRCS.pro_xxx_test1 (cats out sys_refcursor)
IS
    spoon number;
    balls varchar2(3);
BEGIN

    open cats for select * from dual;

end;

/

Upvotes: 1

WBAR
WBAR

Reputation: 4984

CREATE OR REPLACE PROCEDURE MRCS.pro_xxx_test1 (cats out sys_refcursor)
IS
spoon number;
balls varchar2(3);
BEGIN
 open cats for select * from dual;
end;
/

Upvotes: 13

cagcowboy
cagcowboy

Reputation: 30898

Remove the "DECLARE". Not needed in a function / procedure declaration

Upvotes: 60

Related Questions