Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Order of declaration in an anonymous pl/sql block

Tags:

oracle

plsql

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;
like image 857
aw crud Avatar asked Jun 09 '10 14:06

aw crud


People also ask

How does basic anonymous PL SQL block looks like?

PL/SQL anonymous block overviewA PL/SQL block consists of three sections: declaration, executable, and exception-handling sections. In a block, the executable section is mandatory while the declaration and exception-handling sections are optional. A PL/SQL block has a name.

When a PL SQL anonymous block is executed?

The PL/SQL anonymous block statement is an executable statement that can contain PL/SQL control statements and SQL statements. It can be used to implement procedural logic in a scripting language. In PL/SQL contexts, this statement can be compiled and executed by the data server.

How do you declare a block in PL SQL?

The basic program unit in PL/SQL is the block. A PL/SQL block is defined by the keywords DECLARE , BEGIN , EXCEPTION , and END . These keywords partition the block into a declarative part, an executable part, and an exception-handling part. Only the executable part is required.

How do you execute an anonymous block procedure?

And this is anonymous block call: SET serveroutput on DECLARE in_id number; my_cursor sys_refcursor; current_record my_test_table%ROWTYPE; BEGIN in_id := 1; test_package. test_procedure(in_id, my_cursor); open my_cursor; LOOP FETCH my_cursor INTO current_record; EXIT WHEN my_cursor%NOTFOUND; dbms_output.


1 Answers

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;
like image 51
Peter Lang Avatar answered Oct 07 '22 22:10

Peter Lang