Skip to content

How to Resolve PLS-00103: Encountered the symbol

  • by

PLS-00103

I cannot cover all error patterns of PL-00103 in this post, here are some cases that encounter PLS-00103.

  1. Missing IS
  2. Missing END
  3. Missing Slash
  4. EXECUTE IMMEDIATE

1. 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.

2. 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.

3. 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.

4. 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

Leave a Reply

Your email address will not be published.