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;
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.
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.
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.
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.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With