syntax error when declaring variables in a pl/sql procedure

Question

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;

/

Cuthbertson

The Oracle documentation is comprehensive, online and free. Please learn how to use it, as it will answer an awful lot of such trivial syntax questions. For instance, here is the syntax for stored procedures in Oracle: docs.oracle.com/cd/E11882_01/appdev.112/e25519/… – Lezlielg

I think APC was a little harsh. This question follows the stackoverlow rules and sending someone to look at the full specs does not answer the question. At least indicate what section, paragraph or page to look at. – Detrusion

Answer 1

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

Flavio

Anyone know why? This seems so arbitrary. – Krause

Answer 2

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;
/

Pagoda

Answer 3

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;

/

Forceps

Answer 4

"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;

Harleigh