Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

syntax error when declaring variables in a pl/sql procedure

Tags:

oracle

plsql

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;

/
like image 632
DavidG Avatar asked Oct 08 '12 17:10

DavidG


People also ask

How do you declare a variable in a procedure in PL SQL?

After the declaration, PL/SQL allocates memory for the variable's value and the storage location is identified by the variable name. Syntax for declaring variable: Following is the syntax for declaring variable: variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]

What is syntax error in Plsql?

Syntax Error. 1. This is a type of error that is encountered while the program is running. This is an error encountered in the syntax of a sequence of characters or the tokens intended to be written in a particular language.

Can we declare variables inside record in PL SQL?

You can create a record variable in any of these ways: Define a record type and then declare a variable of that type. Use %ROWTYPE to declare a record variable that represents either a full or partial row of a database table or view.


3 Answers

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

like image 140
cagcowboy Avatar answered Oct 17 '22 17:10

cagcowboy


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;
/
like image 21
WBAR Avatar answered Oct 17 '22 19:10

WBAR


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;

/
like image 1
sandeep gupta Avatar answered Oct 17 '22 17:10

sandeep gupta