Improve company productivity with a Business Account.Sign Up

x
?
Solved

insert from stored procedure's returned rowset (conversion from MSSQL server 2000)

Posted on 2006-11-20
6
Medium Priority
?
739 Views
Last Modified: 2008-01-09
Hey,

I'm converting some overnight jobs from sql server 2000 to oracle 9i.  What I want to do is emulate the following in oracle (paying mind to the efficiency of the code):

insert into mytable (col1, col2, col2)
exec usp_my_procedure start_date, end_date

I have already implemented this as follows:

mycursor                 SYS_REFCURSOR;

usp_my_procedure(start_date, end_date, mycursor);
        BEGIN
          --OPEN mycursor; (cursor is already open from within the procedure)
          FETCH mycusor                                                          
          INTO v_col1, v_col2, v_col3;
          WHILE mycursor%FOUND LOOP                                                
            BEGIN    
             insert into mytable values (v_col1, v_col2, v_col3);
            END;
          END LOOP;
         
          CLOSE mycursor;
        END;

Is there a better or more efficient way to do this?
0
Comment
Question by:Hillwaaa
6 Comments
 
LVL 18

Assisted Solution

by:rbrooker
rbrooker earned 600 total points
ID: 17983974
Hi, use bulkcollect.  i cant remember the syntax, but this wioll batch the inserts, rather than row by row...
0
 
LVL 36

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 200 total points
ID: 17984574
Can you move the insert into the same procedure as the cursor?  If yes, do you need a cursor loop, or can you simply do:
insert into [table2]
select [col1], [col2], [col3]
from [table1]
where ...
0
 
LVL 16

Author Comment

by:Hillwaaa
ID: 17984617
Hey markgeer - unfortunately no - the stored procedure is returning a result set that is used in various other procedures, and being inserted into a temporary table which is different each time.

While I could pass in the name of the temp table - I'd rather steer clear of this as it would cause numerous flow-on changes to the structure of the application, and I've been instructed by the client that they want both versions (sql server and oracle) to remain as similar as possible to simplify maintenance.

rbrooker, I'm looking into bulk collect, and how I can use this...

thanks both!
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
LVL 27

Accepted Solution

by:
sujith80 earned 1200 total points
ID: 17985281
The following example will give you an idea of how to use BULK operations with cursors.
The idea is: you BULK fetch the cursor into an array, and BULK insert the array into the table. YOu have to declare as many arrays as there are columns selected in the cursor.

--------------

create or replace procedure test_proc(p_arg out sys_refcursor)
is
begin
 open p_arg for select table_name from user_tables where rownum < 11;
end;
/

declare
 type t1 is table of varchar2(40) index by binary_integer;
 l_t1 t1;
 l_cur sys_refcursor;
begin
 test_proc(l_cur);
 loop
  fetch l_cur bulk collect into l_t1 LIMIT 1000;
  exit when l_t1.count = 0;
 
  forall i in l_t1.first..l_t1.last
   insert into c values(l_t1(i));

  commit;
 end loop;
 
 if l_cur%isopen then
  close l_cur;
 end if;
end;
/
0
 
LVL 36

Expert Comment

by:Mark Geerlings
ID: 17986990
If the client wants "both versions (sql server and oracle) to remain as similar as possible" then the client doesn't care about performance!  It is *NOT* possible to have an application run on both SQL Server and Oracle with the same SQL code and perform well in both systems!
0
 
LVL 16

Author Comment

by:Hillwaaa
ID: 17991801
lol - true, but then when did clients ever want anything that is possible (or non-contradictory with their other requirements :)

Thanks for your help all - I'll write up a second version using bulk collect and get them to choose their preference.

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

605 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question