Oracle ORA-06548 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-06548: No More Rows Needed — What It Means and How to Fix It

ORA-06548 is raised in Oracle when a caller signals that it no longer needs any more rows from a pipelined table function. This typically happens when a query applies a row-limiting clause (like ROWNUM or FETCH FIRST) to the output of a pipelined function, causing Oracle to terminate the function early. While this is not always a critical error, failing to handle it properly can cause cursor leaks, unexpected query failures, and cascading errors in production environments.

Top 3 Causes

1. Unhandled NO_DATA_NEEDED Exception in a Pipelined Function

When a pipelined function keeps attempting to PIPE ROW after the caller has already received all the rows it needs, Oracle throws ORA-06548 internally. If the function has no EXCEPTION block to catch it, the error propagates outward and terminates the query abnormally.

-- PROBLEMATIC: No exception handler for NO_DATA_NEEDED
CREATE OR REPLACE FUNCTION bad_pipe_func
RETURN emp_table_type PIPELINED
AS
    CURSOR c IS SELECT employee_id, first_name, salary FROM employees;
    r employees%ROWTYPE;
BEGIN
    OPEN c;
    LOOP
        FETCH c INTO r;
        EXIT WHEN c%NOTFOUND;
        PIPE ROW(emp_rec_type(r.employee_id, r.first_name, r.salary));
    END LOOP;
    CLOSE c;
    RETURN;
    -- Missing: EXCEPTION WHEN NO_DATA_NEEDED => causes ORA-06548 to propagate
END;
/

2. Using ROWNUM or FETCH FIRST with a Pipelined Function

Applying row-limiting clauses to a pipelined function result set is the most common trigger for ORA-06548. Oracle sends a "stop" signal to the function as soon as the row limit is reached, and if the function is still mid-loop trying to pipe more rows, the exception fires.

-- This query will trigger ORA-06548 if the function lacks NO_DATA_NEEDED handling
SELECT *
FROM TABLE(bad_pipe_func())
WHERE ROWNUM <= 10;

-- SAFE version: Always include NO_DATA_NEEDED in pipelined functions
CREATE OR REPLACE FUNCTION safe_pipe_func
RETURN emp_table_type PIPELINED
AS
    CURSOR c IS SELECT employee_id, first_name, salary FROM employees;
    r employees%ROWTYPE;
BEGIN
    OPEN c;
    LOOP
        FETCH c INTO r;
        EXIT WHEN c%NOTFOUND;
        PIPE ROW(emp_rec_type(r.employee_id, r.first_name, r.salary));
    END LOOP;
    CLOSE c;
    RETURN;
EXCEPTION
    WHEN NO_DATA_NEEDED THEN
        IF c%ISOPEN THEN CLOSE c; END IF;
        RETURN; -- Graceful exit
    WHEN OTHERS THEN
        IF c%ISOPEN THEN CLOSE c; END IF;
        RAISE;
END;
/

-- Now safe to use with row-limiting clauses
SELECT * FROM TABLE(safe_pipe_func()) FETCH FIRST 10 ROWS ONLY;

3. Nested Cursors Inside Pipelined Functions Without Cleanup

When a pipelined function opens multiple or nested cursors internally, and the caller triggers an early exit, all open cursors must be closed in the NO_DATA_NEEDED handler. Failing to do so leads to cursor leaks and can quickly cause ORA-01000: maximum open cursors exceeded.

CREATE OR REPLACE FUNCTION nested_pipe_func
RETURN emp_table_type PIPELINED
AS
    CURSOR dept_c IS SELECT department_id FROM departments;
    CURSOR emp_c(p_id NUMBER) IS
        SELECT employee_id, first_name, salary
        FROM employees WHERE department_id = p_id;
    v_dept NUMBER;
    v_id NUMBER; v_name VARCHAR2(100); v_sal NUMBER;
BEGIN
    OPEN dept_c;
    LOOP
        FETCH dept_c INTO v_dept;
        EXIT WHEN dept_c%NOTFOUND;
        OPEN emp_c(v_dept);
        LOOP
            FETCH emp_c INTO v_id, v_name, v_sal;
            EXIT WHEN emp_c%NOTFOUND;
            PIPE ROW(emp_rec_type(v_id, v_name, v_sal));
        END LOOP;
        CLOSE emp_c;
    END LOOP;
    CLOSE dept_c;
    RETURN;
EXCEPTION
    WHEN NO_DATA_NEEDED THEN
        -- Close ALL open cursors to prevent cursor leak
        IF emp_c%ISOPEN THEN CLOSE emp_c; END IF;
        IF dept_c%ISOPEN THEN CLOSE dept_c; END IF;
        RETURN;
    WHEN OTHERS THEN
        IF emp_c%ISOPEN THEN CLOSE emp_c; END IF;
        IF dept_c%ISOPEN THEN CLOSE dept_c; END IF;
        RAISE;
END;
/

Quick Fix Solutions

Always add EXCEPTION WHEN NO_DATA_NEEDED THEN ... RETURN; to every pipelined function — this is the single most effective fix.
Close all open cursors inside the NO_DATA_NEEDED block to avoid ORA-01000.
Never suppress the exception with a bare NULL; — always call RETURN so Oracle knows the function terminated cleanly.

Prevention Tips

Establish a coding standard: Every pipelined table function in your codebase must include a NO_DATA_NEEDED exception handler. Add this as a mandatory item in your PL/SQL code review checklist.
Test with row-limiting queries: Always include unit test cases that use ROWNUM, FETCH FIRST, or restrictive WHERE clauses against your pipelined functions. Catching ORA-06548 in development is far cheaper than diagnosing cursor leaks in production.

Related Errors

Error Code	Description
`ORA-01000`	Maximum open cursors exceeded — often caused by unhandled ORA-06548 leaking cursors
`ORA-06503`	PL/SQL function returned without value — can co-occur with pipelined function issues
`ORA-06512`	Appears in the error stack trace pointing to the exact line of the ORA-06548 trigger

📖 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