execute stored procedure in sql*plus [message #40168] |
Thu, 12 September 2002 21:50 |
Jenny
Messages: 7 Registered: November 2001
|
Junior Member |
|
|
Hi,
how can I execute this pl/sql stored procedure in sql*plus?
create or replace package sp_pkg as
type refCursorxx is ref cursor;
procedure getdata(a1 out refCursorxx, a2 out refCursorxx);
end;
/
create or replace package body sp_pkg as
procedure getdata(a1 out refCursorxx, a2 out refCursorxx) is
begin
open a1 for select * from emp;
open a2 for select * from dept;
end getdata;
end;
/
end;
Thanks,
Jenny
|
|
|
Re: execute stored procedure in sql*plus [message #40170 is a reply to message #40168] |
Fri, 13 September 2002 01:42 |
Epe
Messages: 99 Registered: March 2002
|
Member |
|
|
Write the code in a text file and save it as "whatever_name.sql".
Open a sql*plus session and run the file by typing the following line :
@c:...whatever_name.sql
(if you saved the file on the c:... drive and directory...)
|
|
|
|
Re: execute stored procedure in sql*plus [message #40181 is a reply to message #40168] |
Fri, 13 September 2002 09:27 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
sql>var rc1 refcursor
sql>var rc2 refcursor
sql>exec sp_pkg.getdata(:rc1, :rc2)
PL/SQL procedure successfully completed.
sql>print rc1
EMPNO ENAME JOB MGR HIREDATE SAL
--------- ---------- --------- --------- --------------------- ---------
COMM DEPTNO
--------- ---------
7369 SMITH CLERK 7902 12/17/1980 12:00:00am 800
20
...rest of output snipped...
14 rows selected.
sql>print rc2
DEPTNO DNAME LOC
--------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
4 rows selected.
|
|
|