Oracle ORA-06571 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-06571: Function Does Not Guarantee Not to Update Database

ORA-06571 is raised by Oracle when a function called within a SQL statement cannot guarantee that it will not modify the database. Oracle enforces this restriction to protect read consistency during query execution — if a function performs DML (INSERT, UPDATE, DELETE) while a SELECT is running, data integrity cannot be guaranteed. This error typically appears when a PL/SQL function lacks the proper purity declaration or actually contains DML operations.

Top 3 Causes

1. DML Statements Inside a Function Called from SQL

The most common cause is embedding DML logic (e.g., audit logging) inside a function that is then called from a SQL query.

-- PROBLEMATIC: This function inserts a log record, causing ORA-06571
CREATE OR REPLACE FUNCTION get_price(p_item_id IN NUMBER)
RETURN NUMBER IS
  v_price NUMBER;
BEGIN
  SELECT price INTO v_price FROM products WHERE item_id = p_item_id;

  -- DML inside function = ORA-06571 when called from SQL
  INSERT INTO access_log (item_id, log_time) VALUES (p_item_id, SYSDATE);
  COMMIT;

  RETURN v_price;
END;
/

-- This SELECT will trigger ORA-06571
SELECT item_id, get_price(item_id) FROM products;

2. Missing or Incorrect PRAGMA RESTRICT_REFERENCES

For package functions, Oracle requires an explicit purity declaration in the package specification. Without it, Oracle cannot verify the function is safe to call from SQL.

-- MISSING PRAGMA: Will cause ORA-06571 at runtime
CREATE OR REPLACE PACKAGE pricing_pkg IS
  FUNCTION get_price(p_item_id IN NUMBER) RETURN NUMBER;
  -- No PRAGMA = Oracle cannot confirm purity
END pricing_pkg;
/

-- CORRECT: Add PRAGMA RESTRICT_REFERENCES
CREATE OR REPLACE PACKAGE pricing_pkg IS
  FUNCTION get_price(p_item_id IN NUMBER) RETURN NUMBER;
  -- WNDS = Write No Database State (no DML allowed)
  PRAGMA RESTRICT_REFERENCES(get_price, WNDS, WNPS);
END pricing_pkg;
/

3. Indirect DML via Called Subprograms

Even if the function itself has no DML, calling another procedure or function that performs DML will still trigger ORA-06571. Oracle traces the entire call chain.

-- Sub-procedure that performs DML
CREATE OR REPLACE PROCEDURE write_log(p_id IN NUMBER) IS
BEGIN
  INSERT INTO audit_log VALUES (p_id, SYSDATE);
  COMMIT;
END;
/

-- Function calls the DML procedure — still causes ORA-06571 in SQL
CREATE OR REPLACE FUNCTION get_data(p_id IN NUMBER)
RETURN VARCHAR2 IS
  v_result VARCHAR2(100);
BEGIN
  SELECT description INTO v_result FROM items WHERE id = p_id;
  write_log(p_id); -- Indirect DML — Oracle detects this
  RETURN v_result;
END;
/

Quick Fix Solutions

Fix 1 — Remove DML from the function:

-- Clean, SQL-safe function
CREATE OR REPLACE FUNCTION get_price(p_item_id IN NUMBER)
RETURN NUMBER IS
  v_price NUMBER;
BEGIN
  SELECT price INTO v_price FROM products WHERE item_id = p_item_id;
  RETURN v_price; -- No DML, safe to call from SQL
END;
/

Fix 2 — Use AUTONOMOUS_TRANSACTION for mandatory logging:

-- Use AUTONOMOUS_TRANSACTION to isolate DML from the main transaction
CREATE OR REPLACE FUNCTION get_price_logged(p_item_id IN NUMBER)
RETURN NUMBER IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  v_price NUMBER;
BEGIN
  INSERT INTO access_log (item_id, log_time) VALUES (p_item_id, SYSDATE);
  COMMIT; -- Required inside autonomous transaction

  SELECT price INTO v_price FROM products WHERE item_id = p_item_id;
  RETURN v_price;
END;
/

-- Now safe to call from SQL
SELECT item_id, get_price_logged(item_id) AS price FROM products;

Fix 3 — Declare purity with DETERMINISTIC (Oracle 8i+):

-- Modern approach: use DETERMINISTIC for pure, read-only functions
CREATE OR REPLACE FUNCTION get_price(p_item_id IN NUMBER)
RETURN NUMBER DETERMINISTIC IS
  v_price NUMBER;
BEGIN
  SELECT price INTO v_price FROM products WHERE item_id = p_item_id;
  RETURN v_price;
END;
/

Prevention Tips

Design functions as pure by default. Any function intended to be called from SQL should never contain DML. Enforce this as a team coding standard and include it in code review checklists.
Audit existing functions regularly using the query below to detect hidden DML in functions before they cause runtime errors in production.

-- Detect functions containing DML keywords
SELECT DISTINCT name, type
FROM user_source
WHERE type = 'FUNCTION'
  AND (UPPER(text) LIKE '%INSERT%'
    OR UPPER(text) LIKE '%UPDATE%'
    OR UPPER(text) LIKE '%DELETE%')
ORDER BY name;

Related Errors

ORA-06572 — Purity level conflict between PRAGMA RESTRICT_REFERENCES declaration and actual function behavior.
ORA-14551 — Cannot perform DML inside a query; closely related to ORA-06571.
ORA-04091 — Mutating table error in triggers; shares the same DML-restriction context.

📖 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