Oracle ORA-06535 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-06535: Statement String in EXECUTE IMMEDIATE is NULL or 0 Length

ORA-06535 is a PL/SQL runtime error that occurs when the SQL string passed to EXECUTE IMMEDIATE is either NULL or an empty string of zero length. Since Oracle treats an empty string ('') as NULL, both cases trigger this error. It is one of the most common issues encountered when working with dynamic SQL in PL/SQL stored procedures and packages.

Top 3 Causes

1. Uninitialized SQL String Variable (NULL by Default)

In PL/SQL, declared variables that are never assigned a value default to NULL. If your logic fails to populate the dynamic SQL string before calling EXECUTE IMMEDIATE, this error fires immediately.

-- ERROR EXAMPLE: v_sql is never assigned
DECLARE
  v_sql VARCHAR2(1000); -- defaults to NULL
BEGIN
  EXECUTE IMMEDIATE v_sql; -- ORA-06535 raised here
END;
/

-- FIXED: Always initialize or validate before execution
DECLARE
  v_sql VARCHAR2(1000);
BEGIN
  v_sql := 'SELECT COUNT(*) FROM dual';

  IF v_sql IS NOT NULL AND LENGTH(TRIM(v_sql)) > 0 THEN
    EXECUTE IMMEDIATE v_sql;
  ELSE
    DBMS_OUTPUT.PUT_LINE('SQL string is empty. Skipping execution.');
  END IF;
END;
/

2. NULL Input Parameters Breaking String Concatenation Logic

When building dynamic SQL from input parameters, a NULL parameter combined with faulty conditional logic can result in the entire SQL string being NULL. This is especially common when table names or WHERE clause conditions are passed from application layers.

-- ERROR EXAMPLE: p_table_name passed as NULL
DECLARE
  v_table VARCHAR2(100) := NULL;
  v_sql   VARCHAR2(1000);
  v_count NUMBER;
BEGIN
  -- If v_table is NULL and no guard exists,
  -- entire logic may produce a NULL v_sql
  IF v_table IS NOT NULL THEN
    v_sql := 'SELECT COUNT(*) FROM ' || v_table;
  END IF;
  -- v_sql is still NULL here if v_table was NULL
  EXECUTE IMMEDIATE v_sql INTO v_count; -- ORA-06535
END;
/

-- FIXED: Use NVL and provide a safe default
DECLARE
  v_table VARCHAR2(100) := NULL;
  v_sql   VARCHAR2(1000);
  v_count NUMBER;
BEGIN
  v_table := NVL(v_table, 'EMP'); -- fallback to default table
  v_sql   := 'SELECT COUNT(*) FROM ' || v_table;
  EXECUTE IMMEDIATE v_sql INTO v_count;
  DBMS_OUTPUT.PUT_LINE('Row count: ' || v_count);
END;
/

3. Empty String Assigned Due to Conditional Branch Logic

Oracle treats '' (empty string) identically to NULL. If a CASE expression or function call returns an empty string and that result is passed directly to EXECUTE IMMEDIATE, ORA-06535 will be raised.

-- ERROR EXAMPLE: CASE returns empty string (treated as NULL)
DECLARE
  v_mode VARCHAR2(10) := 'UNKNOWN';
  v_sql  VARCHAR2(1000);
BEGIN
  v_sql := CASE v_mode
             WHEN 'INSERT' THEN 'INSERT INTO log_table VALUES (SYSDATE)'
             WHEN 'DELETE' THEN 'DELETE FROM log_table'
             ELSE '' -- Oracle treats '' as NULL
           END;
  EXECUTE IMMEDIATE v_sql; -- ORA-06535 when mode is 'UNKNOWN'
END;
/

-- FIXED: Handle ELSE with a meaningful default or raise an error
DECLARE
  v_mode VARCHAR2(10) := 'UNKNOWN';
  v_sql  VARCHAR2(1000);
BEGIN
  v_sql := CASE v_mode
             WHEN 'INSERT' THEN 'INSERT INTO log_table VALUES (SYSDATE)'
             WHEN 'DELETE' THEN 'DELETE FROM log_table'
             ELSE NULL
           END;

  IF v_sql IS NULL THEN
    RAISE_APPLICATION_ERROR(-20001, 'Unsupported mode: ' || v_mode);
  END IF;

  EXECUTE IMMEDIATE v_sql;
END;
/

Quick Fix Solutions

Add a reusable validation wrapper around all your EXECUTE IMMEDIATE calls:

CREATE OR REPLACE PROCEDURE safe_exec(
  p_sql     IN VARCHAR2,
  p_context IN VARCHAR2 DEFAULT 'N/A'
) IS
BEGIN
  IF p_sql IS NULL OR LENGTH(TRIM(p_sql)) = 0 THEN
    RAISE_APPLICATION_ERROR(-20100,
      'Dynamic SQL is NULL or empty in context: ' || p_context);
  END IF;
  DBMS_OUTPUT.PUT_LINE('Executing [' || p_context || ']: ' 
                        || SUBSTR(p_sql, 1, 100));
  EXECUTE IMMEDIATE p_sql;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error in [' || p_context || ']: ' || SQLERRM);
    RAISE;
END safe_exec;
/

Prevention Tips

Always validate before executing: Use IF v_sql IS NOT NULL AND LENGTH(TRIM(v_sql)) > 0 as a standard guard before every EXECUTE IMMEDIATE call across your codebase.
Write unit tests for NULL and empty inputs: Include boundary-value test cases (NULL, '', whitespace-only strings) in your PL/SQL test suite using frameworks like utPLSQL to catch ORA-06535 before it reaches production.

Related Errors

ORA-06512 – Stack trace companion error showing line number of the failure.
ORA-00900 – Raised when a non-NULL but syntactically invalid SQL string is passed to EXECUTE IMMEDIATE.
ORA-01403 – NO_DATA_FOUND, commonly encountered alongside dynamic SQL SELECT INTO statements.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community