1

In the question "Is Oracle's syntax diagram for PL/SQL blocks wrong?" René Nyffenegger showed how Oracle allows cursor definitions to preceed variable declarations in the declare section of a block in spite of Oracle's documenation indicated that this was not allowed. And asked if he was missing anything.

Paxdiablo's answer concurred with René's reading of the documentation that a cursor definition can not come prior to a variable declaration, since variable declarations are only allowed in item_list_1, cursor definitions are only allowed in item_list_2 and item_list_1 comes before item_list_2.

René commented, "I wonder if there is any distinction between item_1 elements and item_2 elements at all?" In my words, "Is there any ordering required between different types of entries in the declare section of a block?"

Community
  • 1
  • 1
Shannon Severance
  • 18,025
  • 3
  • 46
  • 67

2 Answers2

3

As of Oracle 10g R2 the answer is yes, there is a required ordering between items in item_list_1 and item_list_2, even if cursor definitions are allowed out of place.

For example a procedure definition is not allowed before a variable declaration:

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
Shannon Severance
  • 18,025
  • 3
  • 46
  • 67
1

I think (based on very limited empric tests) that after the first procedure definition or function definItion nothing but further procedure definitions or function definitions are allowed.

So, variable declarations (or more generally an item declarations), cursor declarations, type definitions and so on should be item 1 elems and (probably 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) permitted in both the item 1 elems and item 2 elems.

René Nyffenegger
  • 39,402
  • 33
  • 158
  • 293
  • Was that on 11gR2? I don't have 11gR2 handy, it does appear that what you describe is 10gR2 behavior, matches the error message in my answer. Also it is the behavior described in the 10gR2 documentation: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/block_declaration.htm#sthref2482 – Shannon Severance Aug 25 '11 at 22:51
  • Yes, that was on a 10gR2. I won't be able to touch an 11gR2 until Monday. – René Nyffenegger Aug 25 '11 at 23:45