In an anonymous PL/SQL block, if you do not follow the order of the item list in the declaration part, you might just end up with PLS-00103 error. Per Oracle documentation on Anonymous Block following is the order of the item list in the declaration part :
- Item list of variables, cursors, types, subtypes, constants etc.
- Functions.
- Procedures.
Let’s see the test cases :
- While declaring, I keep the local variable after the function :
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Automatic Storage Management options
SQL> set serveroutput on;
SQL> DECLARE
2
3 FUNCTION F_get_dt
4 RETURN DATE
5 IS
6 var_dt DATE;
7 BEGIN
8 var_dt := SYSDATE;
9
10 RETURN var_dt;
11 END f_get_dt;
12
13 var_curr_dt DATE DEFAULT SYSDATE;
14
15 BEGIN
16 var_curr_dt:= f_get_dt;
17
18 IF var_curr_dt = SYSDATE THEN
19 Dbms_output.put_line('Current Date');
20 ELSE
21 Dbms_output.put_line('Not Current Date');
22 END IF;
23 END;
24 /
var_curr_dt DATE DEFAULT SYSDATE;
*
ERROR at line 13:
ORA-06550: line 13, column 5:
PLS-00103: Encountered the symbol "VAR_CURR_DT" when expecting one of the
following:
begin function pragma procedure
ORA-06550: line 23, 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
So, Oracle doesn’t allow to do that. The local variable must be declared before the function. As, immediately after the END statement of function, it expects the BEGIN statement.
- I keep the list in correct order :
SQL> set serveroutput on;
SQL> DECLARE
2
3 var_curr_dt DATE DEFAULT SYSDATE;
4
5 FUNCTION F_get_dt
6 RETURN DATE
7 IS
8 var_dt DATE;
9 BEGIN
10 var_dt := SYSDATE;
11
12 RETURN var_dt;
13 END f_get_dt;
14
15 BEGIN
16 var_curr_dt:= f_get_dt;
17
18 IF var_curr_dt = SYSDATE THEN
19 Dbms_output.put_line('Current Date');
20 ELSE
21 Dbms_output.put_line('Not Current Date');
22 END IF;
23 END;
24 /
Current Date
PL/SQL procedure successfully completed.
I tried to investigate further about the reason behind such illogical implementation of an order for the item list. I posted a question in OraFAQ forum, Order of item list in declaration part of PL/SQL anonymous block Seems like the reason is unknown, or an internal Oracle mechanism. Whatever it is, as a pl/sql developer you must keep this in mind and not panic at a situation when you encounter this ridiculous issue.
Final verdict : PL/SQL parser state can accept only begin function pragma procedure.
Hope it helps!