Inserting a RefCursor into a table

oracle, plsql

Solution

You can't insert into a table from a `refcursor`. You could write a procedure that fetches from the cursor and inserts into the table. If `schema.package.sproc` returns a ref cursor of `temp_table%rowtype`, you could do something like

DECLARE
  cur sys_refcursor;
  rec schema.temp_table%rowtype;
BEGIN
  schema.package.sproc( cur );
  LOOP
    FETCH cur INTO rec;
    EXIT WHEN cur%NOTFOUND;

    INSERT INTO schema.temp_table
      VALUES rec;
  END LOOP;
END;

Problem

I have a simple function where I run a stored procedure that returns a RefCursor and I try to use that RefCursor to insert data to a temporary table. I get the following error when trying to do so: `SQL Error: ORA-00947: not enough values` I know for a fact that the refcursor returns exactly the same number of values as the temporary table has, correct column names, their order and their type. I ran `print RefCursor` and I can see all of the data. Here's the code: ``` var r refcursor; EXEC SCHEMA.PACKAGE.SPROC(:r); insert into SCHEMA.TEMP_TABLE values (r); ``` I have to add that the stored procedure has a refcursor defined as a OUT parameter so it returns a correct type. Using `print r;` prints the correct data. What am I doing wrong? EDIT: Based on a suggestion I tried to use a fetch to a rowtype variable, but getting Invalid Number exception whenever I attempt to fetch a row: ``` DECLARE cur SYS_refcursor; rec SCHEMA.TEMP_TABLE%rowtype; begin SCHEMA.PACKAGE.SPROC( cur ); LOOP FETCH cur INTO rec; EXIT WHEN cur%NOTFOUND; INSERT INTO SCHEMA.TEMP_TABLE VALUES rec; END LOOP; EXCEPTION WHEN INVALID_NUMBER THEN DBMS_output.put_line(rec.move_id); end; ``` I added the exception block to see which row is failing and needless to say it is the first one. The stored procedure I run returns a refcursor of a select query from multiple tables. The temporary table defined as the exact copy of the refcursor columns and their types. Not sure what could be causing the exception.

Original source