Solved

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

Posted on 2006-11-20
6
725 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 150 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 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 50 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 27

Accepted Solution

by:
sujith80 earned 300 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 35

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

816 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now