📋

Description

The PLS-00103 error in Oracle Database indicates a syntax error during PL/SQL compilation. The parser encountered an unexpected token, signifying a violation of PL/SQL language rules.

💬

Error Message

PLS-00103: Encountered symbol ... when expecting ...

🔍

Known Causes

4 known causes

⚠️

Missing Semicolon

A required semicolon (;) is missing at the end of a PL/SQL statement, causing the parser to misinterpret the subsequent code.

⚠️

Incorrect Keyword Usage

A PL/SQL keyword is used in an invalid context or misspelled, leading to a syntax error.

⚠️

Unbalanced Parentheses

Parentheses, brackets, or curly braces are not properly matched, disrupting the expected code structure.

⚠️

Invalid Variable Declaration

A variable is declared with an incorrect data type or syntax, resulting in a parser error.

🛠️

Solutions

4 solutions available

1. Identify and Correct Typos in SQL Statements easy

The most common cause of PLS-00103 is a simple typo in your SQL or PL/SQL code.

1

Carefully review the line of code indicated by the PLS-00103 error message. The error message will often tell you what symbol it encountered and what it was expecting. For example, 'Encountered the symbol 'END' when expecting ')''.

Example error: PLS-00103: Encountered the symbol 'END' when expecting ')'

2

Compare the encountered symbol with the expected symbol. Look for common typos such as missing commas, misplaced semicolons, incorrect keywords (e.g., 'SELCET' instead of 'SELECT'), or unmatched parentheses/quotes.

Incorrect: SELECT column1, column2 FRM table_name;
Correct: SELECT column1, column2 FROM table_name;

3

Recompile the PL/SQL object (procedure, function, package, trigger) or re-execute the SQL statement after correcting the typo.

For PL/SQL objects, use: ALTER [PROCEDURE|FUNCTION|PACKAGE|PACKAGE BODY|TRIGGER] object_name COMPILE;

2. Verify Correct Use of PL/SQL Keywords and Syntax medium

Ensure that PL/SQL keywords are used in the correct context and that the overall syntax adheres to Oracle's PL/SQL grammar.

1

Examine the PL/SQL block structure. For instance, ensure that `BEGIN` is followed by `END;`, `LOOP` is followed by `END LOOP;`, and `IF` statements have corresponding `END IF;` clauses.

Incorrect: 
BEGIN
  -- some code
END -- missing semicolon

Correct:
BEGIN
  -- some code
END;

2

Check for correct placement of semicolons. Semicolons are typically used to terminate statements in PL/SQL. Be aware of exceptions, such as within `CASE` statements or certain control structures where they might not be immediately obvious.

Incorrect: 
IF condition THEN
  statement1
  statement2 -- missing semicolon here
END IF;

Correct:
IF condition THEN
  statement1;
  statement2;
END IF;

3

Consult the Oracle PL/SQL documentation for the specific syntax of constructs you are using (e.g., `FOR` loops, `WHILE` loops, cursor declarations, exception handling).

Refer to: Oracle PL/SQL Language Reference for your specific Oracle Database version.

4

Recompile the PL/SQL object after rectifying any syntax errors.

ALTER PROCEDURE procedure_name COMPILE;

3. Resolve Issues with Reserved Words and Identifiers medium

Avoid using Oracle reserved words as identifiers for tables, columns, or variables.

1

Review the list of Oracle reserved words. Keywords like `DATE`, `LEVEL`, `ROWNUM`, `ORDER`, `GROUP`, `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `CREATE`, `ALTER`, `DROP`, `TABLE`, `COLUMN`, `INDEX`, `CONSTRAINT`, `PROCEDURE`, `FUNCTION`, `PACKAGE`, `TRIGGER`, `EXCEPTION` are common examples. A comprehensive list can be found in the Oracle documentation.

Example: If you have a column named 'DATE', it can cause issues.

2

If a reserved word is being used as an identifier, either rename the object or enclose the identifier in double quotes. Using double quotes makes the identifier case-sensitive and can lead to other issues if not managed carefully. Renaming is generally preferred.

Option 1 (Preferred - Rename): 
CREATE TABLE my_table (my_date_column DATE); 

Option 2 (Using quotes - Use with caution):
CREATE TABLE "DATE" ( "DATE" DATE );
-- Accessing it:
SELECT "DATE" FROM "DATE";

3

After making changes, recompile any affected PL/SQL objects or re-execute the SQL statements.

ALTER TABLE my_table COMPILE BODY;

4. Ensure Correct Delimiter Usage in SQL*Plus or SQL Developer easy

In tools like SQL*Plus or SQL Developer, incorrect delimiter usage can lead to syntax errors when defining PL/SQL blocks.

1

When defining PL/SQL blocks (procedures, functions, anonymous blocks) in tools like SQL*Plus, you need to use a delimiter to indicate the end of the PL/SQL block itself, distinct from the semicolon that terminates individual SQL statements within the block.

Incorrect:
CREATE OR REPLACE PROCEDURE my_proc AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello');
END;
/

Correct:
CREATE OR REPLACE PROCEDURE my_proc AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello');
END;
/
-- The '/' is the delimiter here, signaling the end of the PL/SQL block definition.

2

If you need to use the forward slash `/` as part of your PL/SQL code (e.g., in a string literal), you must first change the delimiter to something else. A common practice is to use a different character like a semicolon `;` or a pipe `|`.

SET DEFINE OFF;
SET SERVEROUTPUT ON;
SET SQLTERMINATOR ON;
SET ESCAPE OFF;

-- Changing delimiter to semicolon
SET SQLTERMINATOR ';';

CREATE OR REPLACE PROCEDURE my_proc_with_slash AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('This is a string with a slash /');
END;
/

-- Resetting to default (forward slash)
SET SQLTERMINATOR '/';

3

Ensure that your tool's settings for command terminators (delimiters) are correctly configured for the type of script you are executing.

In SQL Developer, you can find this under Tools -> Preferences -> SQL Developer -> Worksheet -> Scripting -> 'SQL terminator'.

Oracle PLS-00103: Syntax Error