ORA-14551: Cannot Perform DML Inside Query - Fix Functions

ORA-14551: Cannot Perform DML Operation Inside a Query

Error Overview

Error Text: ORA-14551: cannot perform a DML operation inside a query

ORA-14551 occurs when a PL/SQL function called from a SQL statement attempts to execute a DML operation (INSERT, UPDATE, DELETE, or MERGE) on a table. Oracle’s SQL-to-PL/SQL calling convention prohibits side effects from functions invoked within SQL context — a SELECT statement (or any other SQL query) must not modify database state as a side effect of reading data.

This error most commonly surfaces when a function is designed to both return a value and log that value to a table, or when a function updates a sequence or audit table as part of its logic. The fix involves either restructuring the code to avoid DML in SQL-callable functions or using an autonomous transaction to isolate the DML.

Common Causes

1. Logging or Audit Inside a SQL-Callable Function

• Function logs usage to an audit or log table via INSERT within its body
• Every call from a SELECT statement triggers ORA-14551
• The function works fine when called from PL/SQL but fails when used in SQL

2. Sequence Update or Counter Increment in a Function

• Function increments a counter in a tracking table as a side effect
• UPDATE stats_table SET call_count = call_count + 1 inside a function called from SELECT
• Application relies on this side effect for analytics

3. Function Populates a Work Table Before Returning

• Function inserts intermediate results into a global temporary table (GTT)
• Intended to pre-cache data, but this violates the no-DML-in-query rule
• Developer confused about when DML is permitted

4. Complex Business Logic With Embedded Commits

• Function contains a COMMIT inside it — COMMIT is also disallowed in SQL-called functions
• Implicit commits from DDL inside the function body
• Savepoint management inside the function

5. Pipelined or Table Function With Side Effects

• A pipelined table function performs INSERT as part of its data generation logic
• PARALLEL_ENABLE function attempts DML that is forbidden in parallel execution context

Diagnostic Queries

Find Functions Called in SQL That Perform DML

-- Find PL/SQL functions with DML in the source code:
SELECT DISTINCT
  owner,
  name,
  type
FROM dba_source
WHERE type IN ('FUNCTION', 'PACKAGE BODY')
  AND REGEXP_LIKE(text, '\b(INSERT|UPDATE|DELETE|MERGE)\b', 'i')
  AND name IN (
    -- Functions that are also referenced in SQL (dependency check):
    SELECT referenced_name
    FROM dba_dependencies
    WHERE referenced_type = 'FUNCTION'
      AND type IN ('VIEW', 'TRIGGER', 'MATERIALIZED VIEW')
  )
ORDER BY owner, type, name;

-- Check a specific function for DML statements:
SELECT line, text
FROM dba_source
WHERE owner = UPPER('&schema')
  AND name = UPPER('&function_name')
  AND type = 'FUNCTION'
  AND REGEXP_LIKE(text, '\b(INSERT|UPDATE|DELETE|MERGE|COMMIT|ROLLBACK)\b', 'i')
ORDER BY line;

Trace the Error to the Calling SQL

-- Find recent ORA-14551 errors in the alert log:
SELECT originating_timestamp, message_text
FROM x$dbgalertext
WHERE message_text LIKE '%ORA-14551%'
ORDER BY originating_timestamp DESC
FETCH FIRST 20 ROWS ONLY;

-- Find currently executing SQL that might be causing this:
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.sql_id,
  q.sql_text,
  s.last_call_et  AS seconds_running
FROM v$session s
JOIN v$sql q ON s.sql_id = q.sql_id
WHERE s.status = 'ACTIVE'
  AND s.username IS NOT NULL
ORDER BY s.last_call_et DESC
FETCH FIRST 10 ROWS ONLY;

-- Look up the SQL by sql_id:
SELECT sql_text, parsing_schema_name, executions, last_active_time
FROM v$sql
WHERE sql_id = '&sql_id';

Check for PRAGMA AUTONOMOUS_TRANSACTION Usage

-- Which functions already use autonomous transactions?
SELECT
  s.owner,
  s.name,
  s.type
FROM dba_source s
WHERE UPPER(s.text) LIKE '%PRAGMA AUTONOMOUS_TRANSACTION%'
  AND s.type IN ('FUNCTION', 'PROCEDURE', 'PACKAGE BODY')
ORDER BY s.owner, s.type, s.name;

-- Function header and pragma declaration:
SELECT line, text
FROM dba_source
WHERE owner = UPPER('&schema')
  AND name = UPPER('&function_name')
  AND type = 'FUNCTION'
  AND line <= 20
ORDER BY line;

Verify Function Purity for SQL Usage

-- Check RNDS/WNDS/RNPS/WNPS assertions on packaged functions:
SELECT
  owner,
  package_name,
  object_name,
  overload,
  purity
FROM dba_arguments
WHERE object_name = UPPER('&function_name')
  AND package_name IS NOT NULL
ORDER BY overload;
-- WNDS = writes no database state (no DML). If absent, DML is possible.

Step-by-Step Resolution

1. Understand the Pattern Causing the Error

The error always follows this pattern:

-- This SELECT calls a function that does an INSERT inside it:
SELECT my_logging_function(id) FROM some_table;
-- ORA-14551 fires because my_logging_function does:
--   INSERT INTO audit_log VALUES (...);

2. Solution A — Use PRAGMA AUTONOMOUS_TRANSACTION (Most Common Fix)

Autonomous transactions execute in a separate transaction context, allowing DML from within a SQL-called function:

CREATE OR REPLACE FUNCTION log_and_return(p_id IN NUMBER) RETURN VARCHAR2 IS
  PRAGMA AUTONOMOUS_TRANSACTION;  -- This line isolates the DML
  v_result VARCHAR2(100);
BEGIN
  -- The DML runs in its own transaction:
  INSERT INTO audit_log (event_id, event_time, event_user)
  VALUES (p_id, SYSTIMESTAMP, SYS_CONTEXT('USERENV', 'SESSION_USER'));
  COMMIT;  -- Must commit (or rollback) within the autonomous transaction

  -- Return the intended value:
  SELECT description INTO v_result FROM main_table WHERE id = p_id;
  RETURN v_result;

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;  -- Roll back the autonomous transaction on error
    RAISE;
END;
/

Important: PRAGMA AUTONOMOUS_TRANSACTION commits independently of the calling transaction. Do not use it when the DML and the calling transaction must share the same ACID boundary.

3. Solution B — Separate the DML From the Function Call

-- BAD: Function does logging inside SELECT
SELECT my_function(col1) FROM my_table;

-- GOOD: Separate the SELECT from the logging
DECLARE
  v_results SYS.ODCINUMBERLIST;
  v_result  NUMBER;
BEGIN
  -- First: select the data
  SELECT col1 BULK COLLECT INTO v_results FROM my_table;

  -- Then: process and log
  FOR i IN 1..v_results.COUNT LOOP
    v_result := process_value(v_results(i));
    INSERT INTO audit_log VALUES (v_results(i), v_result, SYSDATE);
  END LOOP;
  COMMIT;
END;
/

4. Solution C — Move the DML to a Post-Query Step

-- If the function is called to enrich query results, collect first, DML second:

-- Step 1: Run the SELECT without the DML-triggering function
SELECT id, col1 FROM source_table WHERE status = 'ACTIVE';

-- Step 2: In the application layer (or a subsequent PL/SQL block), perform logging:
INSERT INTO audit_log (id, accessed_time) SELECT id, SYSDATE FROM source_table WHERE status = 'ACTIVE';
COMMIT;

5. Solution D — Refactor to a Stored Procedure

-- Instead of calling a DML-performing function in SQL,
-- wrap the entire operation in a procedure:

CREATE OR REPLACE PROCEDURE process_with_logging(p_cursor OUT SYS_REFCURSOR) AS
BEGIN
  -- Do the logging DML first
  INSERT INTO audit_log (event_type, event_time)
  VALUES ('PROCESS_RUN', SYSTIMESTAMP);

  -- Then open the cursor for the calling application
  OPEN p_cursor FOR
    SELECT id, col1, complex_computation(id) AS result
    FROM source_table
    WHERE status = 'ACTIVE';

  COMMIT;
END;
/

6. Fix a Pipelined Function With Side Effects

-- Pipelined functions must not perform DML.
-- Move any side-effect logic to a wrapper procedure:

-- Original bad pattern:
CREATE OR REPLACE FUNCTION gen_rows RETURN t_row_type PIPELINED IS
BEGIN
  FOR r IN (SELECT * FROM source) LOOP
    INSERT INTO log_table VALUES (r.id, SYSDATE);  -- ORA-14551
    PIPE ROW(r.id);
  END LOOP;
END;
/

-- Fixed: Separate the pipeline from the logging
-- Use the pipelined function for data, a procedure for side effects.

Prevention Strategies

1. Design SQL-Callable Functions as Pure Functions

-- A SQL-callable function should have no side effects.
-- Use WNDS assertion to enforce this at compile time:
CREATE OR REPLACE PACKAGE my_pkg AS
  FUNCTION pure_compute(p_val IN NUMBER) RETURN NUMBER;
  PRAGMA RESTRICT_REFERENCES(pure_compute, WNDS, WNPS, RNPS);
END;
/
-- WNDS = Writes No Database State: compiler will reject DML inside this function.

2. Code Review Checklist for Functions Used in SQL

• Does the function contain INSERT, UPDATE, DELETE, MERGE?
• Does the function call any procedure that might perform DML?
• Does the function include COMMIT or ROLLBACK?
• If any of the above: add PRAGMA AUTONOMOUS_TRANSACTION or move DML out.

3. Use a Code Quality Check Query

-- Run in CI/CD pipeline to catch new DML-in-function issues:
SELECT owner, name, type, line, text
FROM dba_source
WHERE type = 'FUNCTION'
  AND REGEXP_LIKE(text, '^\s*(INSERT|UPDATE|DELETE|MERGE|COMMIT|ROLLBACK)\b', 'i')
  AND name NOT IN (
    -- Exclude functions already known to use autonomous transactions:
    SELECT name FROM dba_source
    WHERE UPPER(text) LIKE '%PRAGMA AUTONOMOUS_TRANSACTION%'
  )
ORDER BY owner, name, line;

Related Errors

• ORA-04091 - Table is mutating (related trigger restriction)
• ORA-06512 - At line (PL/SQL stack trace)
• ORA-04088 - Error during execution of trigger

Emergency Response

Immediate Fix — Add PRAGMA AUTONOMOUS_TRANSACTION

-- Find the function:
SELECT owner, name FROM dba_source
WHERE UPPER(text) LIKE '%INSERT%' AND type = 'FUNCTION'
  AND name = UPPER('&failing_function');

-- Edit the function to add the pragma after the IS/AS keyword:
CREATE OR REPLACE FUNCTION &schema..&failing_function (...) RETURN ... IS
  PRAGMA AUTONOMOUS_TRANSACTION;  -- Add this line
BEGIN
  -- existing body...
  COMMIT;  -- Must add COMMIT inside autonomous transaction
  RETURN ...;
END;
/

Post-Fix Test

-- Confirm the function now works in SQL context:
SELECT &failing_function(sample_col) FROM dual;
-- Should return a value without ORA-14551.