How to pass the result in refcursor to a table without using LOOP [message #413642] |
Thu, 16 July 2009 22:31  |
bond007
Messages: 64 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   |
 |
BlackSwan
Messages: 26766 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
|
|
|
|
|
|
|