Oracle ORA-06561 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-06561: Given Statement Is Not Supported by Package DBMS_SQL

ORA-06561 is thrown when you attempt to execute a SQL statement through Oracle's DBMS_SQL package that the package simply does not support. While DBMS_SQL handles most standard DML and DDL, certain SQL constructs, compound statements, or specific cursor operations fall outside its supported scope. Understanding exactly what DBMS_SQL can and cannot handle is key to avoiding this error entirely.

Top 3 Causes

1. Executing Unsupported SQL Constructs via DBMS_SQL

Some SQL statements such as EXPLAIN PLAN, anonymous PL/SQL blocks (BEGIN...END), or compound multi-statement strings are not supported by DBMS_SQL. The parse step may succeed, but execution triggers ORA-06561.

-- BAD: Trying to run EXPLAIN PLAN through DBMS_SQL
DECLARE
  v_cur INTEGER;
  v_ret INTEGER;
BEGIN
  v_cur := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cur,
                 'EXPLAIN PLAN FOR SELECT * FROM EMP',
                 DBMS_SQL.NATIVE);
  v_ret := DBMS_SQL.EXECUTE(v_cur); -- ORA-06561 raised here
  DBMS_SQL.CLOSE_CURSOR(v_cur);
END;
/

-- GOOD: Use EXECUTE IMMEDIATE instead
BEGIN
  EXECUTE IMMEDIATE 'EXPLAIN PLAN FOR SELECT * FROM EMP';
  FOR r IN (SELECT PLAN_TABLE_OUTPUT
              FROM TABLE(DBMS_XPLAN.DISPLAY())) LOOP
    DBMS_OUTPUT.PUT_LINE(r.PLAN_TABLE_OUTPUT);
  END LOOP;
END;
/

2. Converting a DML Cursor Using DBMS_SQL.TO_REFCURSOR

DBMS_SQL.TO_REFCURSOR is only valid for SELECT statement cursors that have already been executed. Attempting to convert a DML (INSERT/UPDATE/DELETE) cursor raises ORA-06561.

-- BAD: Converting a DML cursor to REF CURSOR
DECLARE
  v_cur    INTEGER;
  v_ret    INTEGER;
  v_ref    SYS_REFCURSOR;
BEGIN
  v_cur := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cur,
                 'UPDATE EMP SET SAL = SAL * 1.1 WHERE DEPTNO = 20',
                 DBMS_SQL.NATIVE);
  v_ret := DBMS_SQL.EXECUTE(v_cur);
  v_ref := DBMS_SQL.TO_REFCURSOR(v_cur); -- ORA-06561 here
END;
/

-- GOOD: Only convert SELECT cursors after EXECUTE
DECLARE
  v_cur    INTEGER;
  v_ret    INTEGER;
  v_ref    SYS_REFCURSOR;
  v_empno  NUMBER;
  v_ename  VARCHAR2(30);
BEGIN
  v_cur := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cur,
                 'SELECT EMPNO, ENAME FROM EMP WHERE DEPTNO = 20',
                 DBMS_SQL.NATIVE);
  DBMS_SQL.DEFINE_COLUMN(v_cur, 1, v_empno);
  DBMS_SQL.DEFINE_COLUMN(v_cur, 2, v_ename, 30);
  v_ret := DBMS_SQL.EXECUTE(v_cur);
  v_ref := DBMS_SQL.TO_REFCURSOR(v_cur); -- Safe: SELECT + EXECUTED
  LOOP
    FETCH v_ref INTO v_empno, v_ename;
    EXIT WHEN v_ref%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_empno || ' - ' || v_ename);
  END LOOP;
  CLOSE v_ref;
END;
/

3. Passing Multiple Semicolon-Delimited Statements as One String

DBMS_SQL processes exactly one SQL statement per cursor. Combining multiple statements with semicolons into a single PARSE call is unsupported and causes ORA-06561.

-- BAD: Multiple statements in one PARSE call
DECLARE
  v_cur INTEGER;
  v_ret INTEGER;
BEGIN
  v_cur := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cur,
    'INSERT INTO LOG_TBL VALUES(1); INSERT INTO LOG_TBL VALUES(2);',
    DBMS_SQL.NATIVE); -- ORA-06561 on execute
  v_ret := DBMS_SQL.EXECUTE(v_cur);
  DBMS_SQL.CLOSE_CURSOR(v_cur);
END;
/

-- GOOD: Execute each statement separately
DECLARE
  PROCEDURE run_sql(p_sql VARCHAR2) IS
    l_cur INTEGER;
    l_ret INTEGER;
  BEGIN
    l_cur := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(l_cur, p_sql, DBMS_SQL.NATIVE);
    l_ret := DBMS_SQL.EXECUTE(l_cur);
    DBMS_SQL.CLOSE_CURSOR(l_cur);
  EXCEPTION
    WHEN OTHERS THEN
      IF DBMS_SQL.IS_OPEN(l_cur) THEN
        DBMS_SQL.CLOSE_CURSOR(l_cur);
      END IF;
      RAISE;
  END;
BEGIN
  run_sql('INSERT INTO LOG_TBL VALUES(1)');
  run_sql('INSERT INTO LOG_TBL VALUES(2)');
  COMMIT;
END;
/

Quick Fix Solutions

Prefer EXECUTE IMMEDIATE for simple dynamic SQL — it supports a broader range of statements with less boilerplate.
Only use TO_REFCURSOR on cursors opened with a SELECT statement that has been successfully executed.
Never concatenate multiple SQL statements into a single DBMS_SQL.PARSE call.
Always close cursors in an EXCEPTION block using DBMS_SQL.IS_OPEN to prevent cursor leaks.

Prevention Tips

Establish clear guidelines for DBMS_SQL vs EXECUTE IMMEDIATE. Reserve DBMS_SQL only for cases requiring runtime-variable bind parameters or bulk operations. For everything else, use EXECUTE IMMEDIATE. Document this decision in your team's coding standards.
Always wrap DBMS_SQL code in robust exception handlers. Validate cursor state with DBMS_SQL.IS_OPEN before closing, and log SQLCODE/SQLERRM to a dedicated error table for rapid diagnosis in production environments.

-- Safe DBMS_SQL wrapper pattern
DECLARE
  v_cur INTEGER := NULL;
  v_ret INTEGER;
BEGIN
  v_cur := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cur, :p_sql, DBMS_SQL.NATIVE);
  v_ret := DBMS_SQL.EXECUTE(v_cur);
  DBMS_SQL.CLOSE_CURSOR(v_cur);
EXCEPTION
  WHEN OTHERS THEN
    IF v_cur IS NOT NULL AND DBMS_SQL.IS_OPEN(v_cur) THEN
      DBMS_SQL.CLOSE_CURSOR(v_cur);
    END IF;
    RAISE;
END;
/

Related errors: ORA-06550 (PL/SQL compilation error), ORA-01000 (maximum open cursors exceeded), ORA-06512 (PL/SQL stack trace), ORA-00900 (invalid SQL statement).

📖 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