PLS-00103: Encountered the symbol "CREATE"

Learn pls-00103: encountered the symbol "create" with practical examples, diagrams, and best practices. Covers oracle-database, pls-00103 development techniques with visual explanations.
Tags:
Categories:

Understanding and Resolving PLS-00103: Encountered the symbol "CREATE"

Hero image for PLS-00103: Encountered the symbol "CREATE"

This article explains the common Oracle PLS-00103 error when encountering the "CREATE" symbol, its causes, and how to resolve it with practical examples.

The PLS-00103: Encountered the symbol "CREATE" error is a common compilation error in Oracle PL/SQL. It typically indicates that the CREATE statement, or a similar Data Definition Language (DDL) command, is being used in an inappropriate context within a PL/SQL block. PL/SQL is designed for procedural logic, while DDL statements are for defining or modifying database objects. This article will delve into why this error occurs and provide effective solutions.

Why PLS-00103 Occurs with CREATE

PL/SQL blocks (anonymous blocks, procedures, functions, packages, triggers) are primarily used for Data Manipulation Language (DML) operations (INSERT, UPDATE, DELETE, SELECT) and procedural logic. DDL statements like CREATE, ALTER, DROP, TRUNCATE, and GRANT are transactional and implicitly commit any pending transactions. Directly embedding DDL statements within a PL/SQL block's executable section is generally not allowed because it violates the procedural nature and transaction management of PL/SQL.

flowchart TD
    A[PL/SQL Block Start] --> B{Executable Section}
    B --> C{DML Statements (Allowed)}
    B --> D{Procedural Logic (Allowed)}
    B --> E{DDL Statements (CREATE, ALTER, DROP)}
    E -- X --> F[PLS-00103 Error]
    C --> G[PL/SQL Block End]
    D --> G

Illustration of DDL statements causing PLS-00103 within a PL/SQL block

Common Scenarios and Solutions

The PLS-00103 error with CREATE usually arises in a few specific situations. Understanding these scenarios is key to applying the correct solution.

Scenario 1: Attempting to Create an Object Directly in a PL/SQL Block

DECLARE
  v_table_name VARCHAR2(30) := 'MY_TEMP_TABLE';
BEGIN
  CREATE TABLE v_table_name (id NUMBER, name VARCHAR2(100));
END;
/

Incorrect attempt to create a table directly in PL/SQL

Solution: Use Dynamic SQL with EXECUTE IMMEDIATE

To execute DDL statements from within a PL/SQL block, you must use dynamic SQL. This involves constructing the DDL statement as a string and then executing it using the EXECUTE IMMEDIATE statement.

DECLARE
  v_table_name VARCHAR2(30) := 'MY_TEMP_TABLE';
  v_sql_stmt   VARCHAR2(200);
BEGIN
  v_sql_stmt := 'CREATE TABLE ' || v_table_name || ' (id NUMBER, name VARCHAR2(100))';
  EXECUTE IMMEDIATE v_sql_stmt;
  DBMS_OUTPUT.PUT_LINE('Table ' || v_table_name || ' created successfully.');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error creating table: ' || SQLERRM);
END;
/

Correct way to create a table using dynamic SQL

Scenario 2: Missing BEGIN...END Block for Anonymous PL/SQL

Sometimes, the error can occur if you're trying to execute a CREATE statement as part of a script that mixes DDL and PL/SQL, and the PL/SQL part isn't properly delimited.

CREATE OR REPLACE PROCEDURE my_proc
IS
BEGIN
  NULL;
END;
/

CREATE TABLE another_table (col1 NUMBER); -- This line might cause issues if not handled correctly in a script

DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM another_table;
END;
/

Example of mixed DDL and PL/SQL in a script

If you're running a script in SQL*Plus or SQL Developer, the CREATE TABLE statement is a standalone DDL command and should be fine. However, if you're trying to embed it within a larger PL/SQL context (e.g., a package body that's not just DDL), or if the tool expects a single PL/SQL block, it can lead to this error.

Solution: Ensure Proper Delimitation and Context

When writing scripts, ensure that DDL statements are executed as separate commands outside of PL/SQL blocks, or use EXECUTE IMMEDIATE if they must be inside. For creating stored procedures, functions, packages, etc., the CREATE OR REPLACE statement itself is DDL, but its body is PL/SQL.

-- This is correct: CREATE OR REPLACE is DDL, the body is PL/SQL
CREATE OR REPLACE FUNCTION get_sysdate RETURN DATE
IS
BEGIN
  RETURN SYSDATE;
END;
/

-- This is also correct: standalone DDL
CREATE TABLE my_data (id NUMBER, value VARCHAR2(50));

-- This is correct: anonymous PL/SQL block
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello from PL/SQL');
END;
/

Correctly structured DDL and PL/SQL in a script

Best Practices for DDL in PL/SQL

While EXECUTE IMMEDIATE allows DDL within PL/SQL, it should be used judiciously. Frequent DDL operations can lead to performance overhead due to recompilation and locking. Consider these best practices:

1. Minimize DDL in PL/SQL

If possible, perform DDL operations outside of PL/SQL blocks, especially for production environments. DDL is typically part of deployment scripts, not application logic.

2. Use EXECUTE IMMEDIATE for Dynamic DDL

When dynamic DDL is absolutely necessary (e.g., creating temporary tables with dynamic names, altering columns based on metadata), use EXECUTE IMMEDIATE.

3. Implement Robust Error Handling

Always wrap EXECUTE IMMEDIATE calls in an exception block to catch and handle potential runtime errors gracefully.

4. Grant Necessary Privileges

The user executing the PL/SQL block must have the necessary DDL privileges (e.g., CREATE TABLE, ALTER TABLE) directly, not just through a role, for EXECUTE IMMEDIATE to succeed.

5. Avoid Hardcoding Object Names

When using dynamic SQL, parameterize object names to prevent SQL injection vulnerabilities. While less critical for CREATE TABLE, it's a good habit.