PLS-00103
I cannot cover all error patterns of PL-00103 in this post, here are some cases that encounter PLS-00103.
Missing IS
You may miss some keywords from the programming unit and got PLS-00103: Encountered the symbol "BEGIN".
SQL> set serveroutput on;
SQL> create or replace procedure p1
2 begin
3 dbms_output.put_line('Procedure 1');
4 end;
5 /
Warning: Procedure created with compilation errors.
Let's see the error.
SQL> show errors;
Errors for PROCEDURE P1:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/1 PLS-00103: Encountered the symbol "BEGIN" when expecting one of
the following:
( ; is with default authid as cluster compress order using
compiled wrapped external deterministic parallel_enable
pipelined result_cache accessible rewrite
The symbol "is" was substituted for "BEGIN" to continue.
Solution
The keyword IS is expected before BEGIN as explained above.
SQL> create or replace procedure p1
2 is
3 begin
4 dbms_output.put_line('Procedure 1');
5 end;
6 /
Procedure created.
SQL> show errors
No errors.
Missing END
You may miss some keywords from the programming unit and got PLS-00103: Encountered the symbol "end-of-file".
SQL> create or replace procedure p1
2 is
3 begin
4 dbms_output.put_line('Procedure 1');
5 /
Warning: Procedure created with compilation errors.<
Let's see the error.
SQL> show errors
Errors for PROCEDURE P1:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/38 PLS-00103: Encountered the symbol "end-of-file" when expecting
one of the following:
( begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << continue close current delete fetch lock
insert open rollback savepoint set sql execute commit forall
merge pipe purge json_exists json_value json_query
json_object json_array
Solution
In this case, the keyword END is expected before the symbol / (slash).
SQL> create or replace procedure p1
2 is
3 begin
4 dbms_output.put_line('Procedure 1');
5 end;
6 /
Procedure created.
SQL> show errors
No errors.
Missing / (Slash)
When we tried to compile two procedures in a session, we got PLS-00103: Encountered the symbol "CREATE".
SQL> create or replace procedure p1
2 is
3 begin
4 dbms_output.put_line('Procedure 1');
5 end;
6
7 create or replace procedure p2
8 is
9 begin
10 dbms_output.put_line('Procedure 2');
11 end;
12 /
Warning: Procedure created with compilation errors.
Let's see the error.
SQL> show errors;
Errors for PROCEDURE P1:
LINE/COL ERROR
-------- -----------------------------------------------------------------
7/1 PLS-00103: Encountered the symbol "CREATE"
This is because every programming unit is an independent one, we should use symbol / (slash) in SQL*Plus to compile them separately.
SQL> create or replace procedure p1
2 is
3 begin
4 dbms_output.put_line('Procedure 1');
5 end;
6 /
Procedure created.
SQL> create or replace procedure p2
2 is
3 begin
4 dbms_output.put_line('Procedure 2');
5 end;
6 /
Procedure created.
EXECUTE IMMEDIATE
In an anonymous PL/SQL block, we use EXECUTE IMMEDIATE.
SQL> begin
2 execute immediate 'select nvl(first_name, 'NO_VALUE') from employees';
3 end;
4 /
execute immediate 'select nvl(first_name, 'NO_VALUE') from employees';
*
ERROR at line 2:
ORA-06550: line 2, column 46:
PLS-00103: Encountered the symbol "NO_VALUE" when expecting one of the following:
* & = - + ; < / > at in is mod remainder not rem return
returning <an exponent (**)> <> or != or ~= >= <= <> and or
like like2 like4 likec between into using || multiset bulk
member submultiset
The symbol "* was inserted before "NO_VALUE" to continue.
Solution
For EXECUTE IMMEDIATE statement, you should use extra single quotes to escape original single quotes in the statement like this.
SQL> begin
2 execute immediate 'select nvl(first_name, ''NO_VALUE'') from employees';
3 end;
4 /
PL/SQL procedure successfully completed.
Further reading: How to Use Bind Variable in Oracle