?
Solved

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

Posted on 2006-11-20
6
Medium Priority
?
731 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 35

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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

752 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