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
Solved

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

Posted on 2006-11-20
6
726 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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ODBC in excel 2016 in Windows 10 via VBA 16 141
Oracle Query - Return results based on minimum value 8 35
SQL2016 to ORACLE11G linked-server 6 28
oracle forms question 22 42
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

829 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