tags:

views:

56

answers:

2

I have an anonymous pl/sql block with a procedure declared inside of it as well as a cursor. If I declare the procedure before the cursor it fails. Is there a requirement that cursors be declared prior to procedures?

What other rules are there for order of declaration in a pl/sql block?

This works:

DECLARE
 cursor cur is select 1 from dual;
 procedure foo as begin null; end foo;
BEGIN
 null;
END;

This fails with error PLS-00103: Encountered the symbol "CURSOR" when expecting one of the following: begin function package pragma procedure form

DECLARE
 procedure foo as begin null; end foo;
 cursor cur is select 1 from dual;
BEGIN
 null;
END;
+9  A: 

Cursors, variables, constants and types need to be declared before packages/functions.

This one would fail too:

DECLARE
 procedure foo as begin null; end foo;
 x VARCHAR2(10);
BEGIN
 null;
END;
Peter Lang
The documentation reference is herehttp://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/block.htm#i32791It isn't very clear, but "item declarations" (eg variables) are in list 1 and have to come before "procedure/function definitions" which are in list 2.
Gary
@Gary: Excellent, thanks!
Peter Lang
A: 

If you want to declare a cursor that is available to the sub procedure as well, just add another anonymous block:

DECLARE
 cursor cur is select 1 from dual;
BEGIN
 DECLARE
  procedure foo as begin null; end foo;
 BEGIN
  null;
 END;
END;
Jeffrey Kemp