Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Posts
    69

    Unanswered: INSERT into table from REF CURSOR

    In SQL server it is possible to have execute an INSERT statement as given below :

    INSERT INTO table_name EXECUTE proc_name(arg)

    Here, the Stored Procedure proc_name(arg) would return a Cursor which is used to populate data in the table.

    Is there a direct way of doing the same in Oracle ?

    One solution is to declare a REF CURSOR output variable, send it to proc_name as a parameter and use the same to populate the table once the procedure is executed. Now once the Ref Cursor has the data, what is the simplest way of inserting the data into the table. Iterate through each and every RECORD in the REF CURSOR and then perform inserts one row at a time ?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You certainly need to process the cursor. However, it doesn't have to by 1 row at a time; you will get a lot more efficiency by using array processing via collections and the BULK COLLECT clause:

    FETCH v_refcursor BULK COLLECT INTO array;

    You'll need to read up on this here.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •