Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2006-11-20
6
Medium Priority
?
732 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
Technology Partners: 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!

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
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.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

604 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