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;

/

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: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/create_procedure.htm#LNPLS01373 — APC, Oct 08 '12 at 21:20
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. — Salvador Valencia, Mar 27 '18 at 18:05

score 60 · Accepted Answer · answered Oct 08 '12 at 17:12

60

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

answered Oct 08 '12 at 17:12

cagcowboy

30,012
11
69
93

2

Anyone know why? This seems so arbitrary. – Renel Chesak Jul 30 '18 at 11:16

score 13 · Answer 2 · answered Oct 08 '12 at 17:13

13

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

answered Oct 08 '12 at 17:13

WBAR

4,924
7
47
81

score 1 · Answer 3 · edited Dec 30 '20 at 04:04

1

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;

/

edited Dec 30 '20 at 04:04

Bùi Đức Khánh

3,975
6
27
43

answered Jun 30 '19 at 15:07

sandeep gupta

49
3

score 0 · Answer 4 · answered Aug 18 '23 at 13:50

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

syntax error when declaring variables in a pl/sql procedure

4 Answers4

Linked