Home » SQL & PL/SQL » SQL & PL/SQL » How to pass the result in refcursor to a table without using LOOP (oracle 10g)
How to pass the result in refcursor to a table without using LOOP [message #413642] Thu, 16 July 2009 22:31 Go to next message
bond007
Messages: 58
Registered: March 2009
Member
How can i pass the resultset in c (cursor variable) to table sbu without using LOOP.
CREATE OR REPLACE PROCEDURE test1 is
 TYPE cur_typ IS REF CURSOR;
 c           cur_typ;
 query_str   VARCHAR2(1000);
 
    v_sbu_code    VARCHAR2(20);
    v_sbu_number  VARCHAR2(20);
BEGIN
    query_str := 'SELECT sbu_code,sbu_number from sbu';
   
    OPEN c FOR query_str;

  ---------------
  --  insted of writing the following loop can we pass the result set directly to the tbale sbu ?
  ---------------
   
    LOOP
        FETCH c INTO v_sbu_code ,v_sbu_number;
        EXIT WHEN c%NOTFOUND;
        -- process row here
         inset into sbu(v_sbu_code ||v_sbu_number);
    END LOOP;
    CLOSE c;
END;
/
SHOW ERRORS;
/
set serveroutput onSHOW ERRORS;
/
Re: How to pass the result in refcursor to a table without using LOOP [message #413647 is a reply to message #413642] Thu, 16 July 2009 22:47 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>How can i pass the resultset in c (cursor variable) to table sbu without using LOOP.
CURSOR does not equal result set.
A cursor is a handle or name for a private SQL area in which a parsed statement and other information for processing the statement are kept. (Oracle Call Interface, OCI, refers to these as statement handles.) Although most Oracle users rely on automatic cursor handling of Oracle utilities, the programmatic interfaces offer application designers more control over cursors.

>SELECT sbu_code,sbu_number from sbu;
>inset into sbu(v_sbu_code ||v_sbu_number);
What does line above supposed to do?
Why do you want to SELECT rows from SBU so they can be INSERTed into SBU?

INSERT INTO bonuses
   SELECT employee_id, salary*1.1 
   FROM employees
   WHERE commission_pct > 0.25; 

[Updated on: Thu, 16 July 2009 22:59]

Report message to a moderator

Re: How to pass the result in refcursor to a table without using LOOP [message #413651 is a reply to message #413647] Thu, 16 July 2009 23:11 Go to previous messageGo to next message
bond007
Messages: 58
Registered: March 2009
Member
Thanks BlackSwan for your quick reply.
It is my fault ..it ll be
inset into temp_sbu(v_sbu_code ||v_sbu_number);
Re: How to pass the result in refcursor to a table without using LOOP [message #413653 is a reply to message #413642] Thu, 16 July 2009 23:14 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>inset into temp_sbu(v_sbu_code ||v_sbu_number);
I don't think so.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

using sqlplus along with CUT & PASTE post functioning SQL.
Re: How to pass the result in refcursor to a table without using LOOP [message #413668 is a reply to message #413642] Fri, 17 July 2009 00:17 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Quote:
inset into sbu(v_sbu_code ||v_sbu_number);



This is not Oracle syntex for INSERT statement.

regards,
Delna
Re: How to pass the result in refcursor to a table without using LOOP [message #413671 is a reply to message #413642] Fri, 17 July 2009 00:22 Go to previous message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>This is not Oracle syntex for INSERT statement.
I previously stated the obvious; twice in this thread.

You can lead some folks to knowledge, but you can not make them think.
Previous Topic: dynamic sql not working
Next Topic: How to get values of previous record
Goto Forum:
  


Current Time: Tue Dec 06 12:31:33 CST 2016

Total time taken to generate the page: 0.14162 seconds