Inserting values to a table returned by a Stored Procedure in Oracle 11g

Question

I need to insert values returned by a stored procedure into a table. My stored procedure consist of multiple select statements and unions, something like this:

create or replace
PROCEDURE          "my_str_proc" (
  emp_code     IN     VARCHAR2,
  hired_year   IN     VARCHAR2,
  q_bio        OUT SYS_REFCURSOR)
AS
BEGIN
  OPEN q_bio FOR
    select column list from table_1 where....and...
    UNION
    select column list from table_2 where....and...
    UNION
    select column list from table_3 where....and...
    UNION
    select column list from table_4 where....and...

 ORDER BY hired_year;

 RETURN;

 /* I plan to add the following statement to the existing proedure script:*/
 /* How can I get the return values after the procedure is done running 
    and inserted into a table ? */
    INSERT INTO Report_table (col1,col2,col3,col4,col5,col6,col7,col8,col9)
    VALUES (?????)

END;

How can I write the script so the values returned by the procedure at the end got inserted into a table that I have created. I can't find the script example and I'm new with Oracle. Our Oracle is 11g

Do you mean you want to insert from this procedure, or from whatever calls this procedure? — Alex Poole
What do you mean, "return value?" Return value of what? This is a procedure, not a function. There is no value to return. Please be more specific. — OldProgrammer
I called this procedure from my ColdFusion. It returns a record set and I display the record set as a PDF report using ColdFusion so user can see the result. It's been functioning for several years but somehow this time there are more than 5000 records in the record set and my coldfusion server is complaining and showing out of memory error message. When I ran this procedure in SQL developer, I got the record set within 6 seconds. So the problem is obivously in Coldfusion server (?). So if I can insert the record set into a table then I can select from this table with ColdFusion. — user2127860

krokodilko krokodilko · Accepted Answer · 2013-08-16T22:16:48

Assuming that the structure of your temporary table will be the same as a row returned by the ref-cursor (the same number of columns and the same types of corresponding columns), then the below code should work:

declare
   my_row temp_table_name%rowtype;
   q_bio SYS_REFCURSOR;
begin
  my_str_proc( 'x', 'y', q_bio );
  LOOP
    FETCH q_bio INTO my_row;
    exit when q_bio%NOTFOUND;
    INSERT INTO temp_table_name VALUES my_row ;
  END LOOP;
  CLOSE q_bio;
end;
/

Inserting values to a table returned by a Stored Procedure in Oracle 11g

1 Answers