| executing batch sql statements in oracle [message #372788] |
Fri, 09 March 2001 10:25  |
Anand Ranganathan
Messages: 2
Registered: March 2001
|
Junior Member |
|
|
Hi
I can execute batch sql statements in SQL server by separating multiple statements with a semicolon. However I cannot do this in oracle. Does oracle allow multiple sql statements to be executed and the results retrieved all in one roundtrip? how can i do this? thanks for any help
|
|
|
|
|
|
|
|
| Re: executing batch sql statements in oracle [message #372797 is a reply to message #372794] |
Fri, 09 March 2001 12:34  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
You are correct.
some options are to
- use dbms_output to output the results, but then you need to spool it to a file.
- use utl_file to write directly to a file (on the server)
- use pl/sql tables and pass them back as paramters
- use use Ref Cursors in 8i.
You can extend the example below to retrieve the results from different queries. Not for the faint hearted...
CREATE TABLE ABC (A NUMBER);
insert into abc values(1);
insert into abc values(9);
insert into abc values(13);
insert into abc values(999);
CREATE OR REPLACE PACKAGE ref_cur_tst
IS
CURSOR c1 IS SELECT a FROM abc;
TYPE t_cur IS REF CURSOR RETURN c1%ROWTYPE;
PROCEDURE get_abc (cv_cur IN OUT t_cur);
END ref_cur_tst;
/
CREATE OR REPLACE PACKAGE BODY ref_cur_tst
AS
PROCEDURE get_abc (cv_cur IN OUT t_cur)
IS
BEGIN
OPEN cv_cur FOR SELECT a FROM abc;
END get_abc;
END ref_cur_tst;
/
DECLARE
a abc.a%TYPE;
cv_c1 ref_cur_tst.t_cur;
BEGIN
ref_cur_tst.get_abc (cv_c1);
LOOP
FETCH cv_c1 INTO a;
EXIT WHEN cv_c1%NOTFOUND;
DBMS_OUTPUT.put_line (a);
END LOOP;
CLOSE cv_v1;
END;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
