Does Oracle PS / SQL require specific ordering of records in the `declare` section of a block

Question

In the question, is the Oracle syntax diagram for PL / SQL blocks incorrect? “ René Nyffenegger showed how Oracle allows defining cursors to precede variable declarations in the block declaration section, despite Oracle documenation indicating that this was not allowed. And asked if it had something.

Paxdiablo's response agreed with reading the René documentation that cursor definitions cannot precede variable declarations, since variable declarations are allowed only in item_list_1, cursor definitions are only allowed in item_list_2 and item_list_1 before item_list_2.

Renee commented: “I wonder if there is any difference between item_1 elements and item_2 elements?” In my opinion: “Are there any ordering order between different types of entries in a block section declaration?”

Answer 1

As with Oracle 10g R2, yes, there is a required order between the item_list_1 and item_list_2 elements, even if cursor definitions are allowed inappropriate.

For example, specifying a specification is not allowed before declaring a variable:

SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for Solaris: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production SQL> declare 2 variable_declaration number; 3 procedure procedure_definition is begin 4 null; 5 end procedure_definition; 6 begin 7 null; 8 end; 9 / PL/SQL procedure successfully completed. SQL> declare 2 procedure procedure_definition is begin 3 null; 4 end procedure_definition; 5 variable_declaration number; 6 begin 7 null; 8 end; 9 / variable_declaration number; * ERROR at line 5: ORA-06550: line 5, column 5: PLS-00103: Encountered the symbol "VARIABLE_DECLARATION" when expecting one of the following: begin function package pragma procedure form ORA-06550: line 8, column 4: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: end not pragma final instantiable order overriding static member constructor map 

Answer 2

I think (based on very limited empirical tests) that after the first procedure definition or function definItion only procedure definitions or function definitions allowed.

So, variable declarations (or, more generally, item declarations ), cursor declarations , type definitions , etc. there should be item 1 elems and (possibly only) function definitions and procedure definitions should be item 2 elems (and only item 2 elems ).

(Only?) function declarations and procedure declarations are (or seem to be) valid in both item 1 elems and item 2 elems .