Oracle ошибка pls 00103 encountered the symbol

What is the problem with this package as it is giving an error?

CREATE OR REPLACE PACKAGE PKG_SHOW_CUST_DETAILS 
AS
    PROCEDURE SHOW_CUST_DETAILS( myArg VARCHAR2);
END PKG_SHOW_CUST_DETAILS;

CREATE OR REPLACE PACKAGE BODY PKG_SHOW_CUST_DETAILS 
AS
    PROCEDURE SHOW_CUST_DETAILS(myArg VARCHAR2)
    IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE(myArg);        
    END SHOW_CUST_DETAILS;

END PKG_SHOW_CUST_DETAILS;
/

On compilation of the above script, I am getting the following errors:

SQL> show errors
Errors for PACKAGE PKG_SHOW_CUST_DETAILS:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/1      PLS-00103: Encountered the symbol "CREATE"

The package is very simple and I am not able to compile it. I searched earlier answers on this error message and none of them did solve my problem.
I am consistently getting this error for 2 more packages and I am stuck on this error message no matter what I do. I even tried to strip everything to the barest minimum as shown above, but the error message does not seem to go away.
BTW I am executing this on command line SQL plus session after logging into my Oracle 11G database.
YES- SET SERVEROUTPUT ON — is executed and the error message has nothing to do with this command.

What am I missing?

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

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  /

Let’s see the error.

SQL> show errors;
Errors for PROCEDURE P1:

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  /

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  /

Let’s see the error.

SQL> show errors
Errors for PROCEDURE P1:

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  /

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  /

Let’s see the error.

SQL> show errors;
Errors for PROCEDURE P1:

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  /

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  /

What is the problem with this package as it is giving an error?

CREATE OR REPLACE PACKAGE PKG_SHOW_CUST_DETAILS 
AS
    PROCEDURE SHOW_CUST_DETAILS( myArg VARCHAR2);
END PKG_SHOW_CUST_DETAILS;

CREATE OR REPLACE PACKAGE BODY PKG_SHOW_CUST_DETAILS 
AS
    PROCEDURE SHOW_CUST_DETAILS(myArg VARCHAR2)
    IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE(myArg);        
    END SHOW_CUST_DETAILS;

END PKG_SHOW_CUST_DETAILS;
/

On compilation of the above script, I am getting the following errors:

SQL> show errors
Errors for PACKAGE PKG_SHOW_CUST_DETAILS:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/1      PLS-00103: Encountered the symbol "CREATE"

The package is very simple and I am not able to compile it. I searched earlier answers on this error message and none of them did solve my problem.
I am consistently getting this error for 2 more packages and I am stuck on this error message no matter what I do. I even tried to strip everything to the barest minimum as shown above, but the error message does not seem to go away.
BTW I am executing this on command line SQL plus session after logging into my Oracle 11G database.
YES- SET SERVEROUTPUT ON — is executed and the error message has nothing to do with this command.

What am I missing?

The PLS-00103: Encountered the symbol “END” when expecting one of the following: error occurs when the keyword “END” is encountered when the PL/SQL code is expected to have a valid literal value. When oracle scans the PL/SQL code while compiling, it detects the “END” symbol instead of the literal one. In most cases, the error occurs just before the “END” statement line. If you fix the error where the line number is displayed, the error PLS-00103: Encountered the symbol “END” when expecting one of the following: will be resolved.

The PLS-00103: Encountered the symbol “END” when expecting one of the following: error appears right before the “END” keyword. Oracle was expecting a literal in the code, but instead found the symbol “END.” The problem isn’t caused by the keyword “END”. The error is caused by a programming error just before the keyword “END”. This error can be found in procedures, triggers, functions, packages, and PL/SQL blocks

Exception

The exception would show the exact error as well as the line number where it happened. The keyword “END” appears in the error message. The error occurs right before the word “END”. In the exception stack trace, the expected keywords or literals will be mentioned.

Error report -
ORA-06550: line 3, column 1:
PLS-00103: Encountered the symbol "END" when expecting one of the following:

   ( begin case declare 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
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Cause

Oracle was anticipating any of the literals in the code, but only saw the symbol “END”. The error is very often not in the keyword “END.” The error will appear right before the END keyword on the line. In the error stack trace, the expected literal will be identified. To fix this error, the expected literal should be added or corrected in the code.

Solution 1

The keywords “BEGIN” and “END” are used to start and end a block in Oracle. If there isn’t a statement in the BEGIN and END blocks, the code would throw an error. Between begin and end, at least one statement should be available. Delete the begin and end statements or add at least one statement in between if no statement is available.

declare
begin
end;

Exception

Error report -
ORA-06550: line 3, column 1:
PLS-00103: Encountered the symbol "END" when expecting one of the following:

   ( begin case declare 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
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Solution

declare
begin
  dbms_output.put_line('yawin');
end;

Output

yawin
PL/SQL procedure successfully completed.

Solution 2

In most of cases, the error occurs in the line just before the END statement. Double check the list just before the END statement. If any error exist, correct it. The error stack trace will display some possible changes in the error line. Before the “END” keyword, Oracle expects one of the specified keywords or literals to be added.

declare
begin
 dbms_output.put_line('yawin')
end;

Exception

Error report -
ORA-06550: line 4, column 1:
PLS-00103: Encountered the symbol "END" when expecting one of the following:

   := . ( % ;
The symbol ";" was substituted for "END" to continue.
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Solution

declare
begin
 dbms_output.put_line('yawin');
end;

Output

yawin
PL/SQL procedure successfully completed.

Solution 3

Oracle will throw an error if the keyword “END” is used in an unlikely location. Oracle encountered the symbol “END” as it was expecting some other keyword or literals in this instance. The “END” keyword should be added to close the block of statements begun with keyword “START”. The BEGIN and END keywords should be the same and in the same order.

declare
empid numeric(1);
begin
 dbms_output.put_line('yawin');
 begin
 IF empid > 0 THEN  
    declare
    empid1 numeric(1);
    
     dbms_output.put_line('yawin');
     end;
 end if;
end;

Exception

Error report -
ORA-06550: line 10, column 17:
PLS-00103: Encountered the symbol "." when expecting one of the following:

   constant exception <an identifier>
   <a double-quoted delimited-identifier> table long double ref
   char time timestamp interval date binary national character
   nchar
The symbol "<an identifier>" was substituted for "." to continue.
ORA-06550: line 11, column 6:
PLS-00103: Encountered the symbol "END" when expecting one of the following:

   begin function pragma procedure subtype type <an identifier>
   <a double-quoted delimited-identifier> current cursor delete
   exists prior
06550. 00000 -  "line %s, column %s:\n%s"

Solution

declare
empid numeric(1);
begin
 dbms_output.put_line('yawin');
 
 IF empid > 0 THEN  
    declare
    empid1 numeric(1);
    begin
     dbms_output.put_line('yawin');
     end;
 end if;
end;

Output

yawin
PL/SQL procedure successfully completed.

Solution 4

The error “PLS-00103: Encountered the symbol “END” when expecting one of the following:” would be thrown in the code if the BEGIN and END statements are missing. Before beginning a block of sentences, Oracle expects the BEGIN keyword. The END keyword is supposed to appear at the end of the statement block. The error would be thrown if the BEGIN and END statements are not present in the blocks.

declare
empid numeric(1);
begin
 dbms_output.put_line('yawin');
 begin
 IF empid > 0 THEN  
    declare
    empid1 numeric(1);
     dbms_output.put_line('yawin');
 end if;
end;

Exception

Error report -
ORA-06550: line 9, column 17:
PLS-00103: Encountered the symbol "." when expecting one of the following:

   constant exception <an identifier>
   <a double-quoted delimited-identifier> table long double ref
   char time timestamp interval date binary national character
   nchar
The symbol "<an identifier>" was substituted for "." to continue.
ORA-06550: line 10, column 2:
PLS-00103: Encountered the symbol "END" when expecting one of the following:

   begin function pragma procedure subtype type <an identifier>
   <a double-quoted delimited-identifier> current cursor delete
   exists prior
06550. 00000 -  "line %s, column %s:\n%s"

Solution

declare
empid numeric(1);
begin
 dbms_output.put_line('yawin');
 begin
 IF empid > 0 THEN  
    declare
    empid1 numeric(1);
    begin
       dbms_output.put_line('yawin');
    end
 end if;
end;

Output

yawin
PL/SQL procedure successfully completed.

If you want to use DDL statement in PL/SQL Blocks then you can do not write directly DDL Statement.
You have to use the Dynamic SQL Syntax for executing the DDL Statement.

Error:

SQL> begin
2 create table test_bkp1 (id number);
3 end
4 /
create table test_bkp1 (id number);
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
( begin case declare 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


Solution:
Donot use DDL directly in PL/SQL blocks. Use the DDL Statements with Dynamic SQL as follows.

BEGIN
EXECUTE IMMEDIATE 'create table TEST_BKP1 (id number, column_value varchar2(100))';
END;
/