Casting a Nested Table in a Cursor

Recently I did some work where we had a big selection of data that was returned by a function into a collection. The data needed to be manipulated in a few different ways. One of the great things in PL/SQL is you can cast the collection or nested table as normal database table.

This means you can use the processing power on SQL to do all sorts of things that would require a lot of work if you were just using standard array logic.

Below is an example of creating a simple nested table and then using SQL to sort it. This is a very simple example to show you how to use it. You can treat this table like any other table. Join it to normal database tables or even join it to another version of itself.

CREATE OR REPLACE
TYPE
pet_details AS OBJECT
(
pet_id NUMBER(3),
pet_name VARCHAR2(200),
pet_age NUMBER(3)
)
/

CREATE OR REPLACE
TYPE
list_of_pets AS TABLE OF pet_details
/

DECLARE 
     v_list_of_pets list_of_pets := list_of_pets();
    v_pet_details pet_details;
    -- pass the nested table in as a parameter to the cursor
    CURSOR c_test (cp_input_list list_of_pets) IS
    SELECT *
    FROM TABLE(cp_input_list)
    ORDER BY pet_age;

BEGIN
    -- put some sample data into a nested table
    v_pet_details := pet_details(1, 'Tom', 14);
    v_list_of_pets.EXTEND;
    v_list_of_pets(1) := v_pet_details;
    v_pet_details := pet_details(2, 'Jenny', 2);
    v_list_of_pets.EXTEND;
    v_list_of_pets(2) := v_pet_details;
    v_pet_details := pet_details(3, 'Rambo', 5);
    v_list_of_pets.EXTEND;
    v_list_of_pets(3) := v_pet_details;
    -- Now pass the nested table into the cursor and loop
    FOR v_test IN c_test(v_list_of_pets) LOOP
         dbms_output.put_line('Show me some pets ' ||
             to_char(v_test.pet_id) ||','||
             v_test.pet_name ||','||
             to_char(v_test.pet_age));
    END LOOP;
END;

ORA-01001: invalid cursor

Recently came across some code that was causing an ORA-010001: invalid cursor error but it seemed like it should work even though it is not really required. Here is an example of the offending code.

FUNCTION test_loop
         RETURN BOOLEAN IS
         CURSOR c_au IS
                     SELECT user_id FROM all_users;
BEGIN
          FOR v_au_rec IN c_au LOOP
                     IF c_au%ISOPEN THEN
                                CLOSE c_au;
                     END IF;
                     RETURN TRUE;
          END LOOP;
          RETURN FALSE;
EXCEPTION
          WHEN OTHERS THEN
                     RAISE;
END;

The example above doesn't do anything sensible but shows the issue. Inside the cursor loop it check if the cursor is open, if it is then close it and the return from the function which will exit the cursor. While it might seem like a good idea to close the cursor if you are going to exit the loop with a return from the function it is not needed. Oracle is smart enough to close the cursor on the way out anyway so there is no need to do it explicitly.

But I thought this should still work without an error but it doesn't.

Running this gives the ORA-010001: invalid cursor error but now where I expected.

It is actually raised as part of the RETURN TRUE;

I assume it is because the cursor is already closed by the explicit CLOSE statement so when it does the implicit close as part of the RETURN FALSE from the loop it fails as the cursor is already gone.

The simple solution is to let Oracle handle the close itself and remove the code to check and close the cursor.