Home » Developer & Programmer » JDeveloper, Java & XML » Executing Stored Procedures
Executing Stored Procedures [message #91236] Mon, 10 June 2002 21:04 Go to next message
Pradeep
Messages: 55
Registered: July 2000
Member
Hi,

How can I execute a stored procedure in Oracle that return a resultSet (not 1 value). I have heard that it is done using REF CURSOR, but don't know how exactly it can be achieved.

Please help me..

Regards
Pradeep
Re: Executing Stored Procedures [message #91246 is a reply to message #91236] Fri, 14 June 2002 16:19 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10706
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
is this helping you?
SQL> CREATE OR REPLACE package pack1 is 
  2  TYPE ref1 is ref cursor; 
  3  end; 
  4  / 

Package created.

SQL> CREATE OR REPLACE procedure proc1 (a IN number, b IN OUT pack1.ref1) is 
  2  begin 
  3  open b for select * from emp where empno = a; 
  4  end; 
  5  /

Procedure created.

SQL>  var r refcursor ;
SQL> exec proc1(7788,:r);

PL/SQL procedure successfully completed.

SQL> print r;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

1 row selected.

Re: Executing Stored Procedures [message #91253 is a reply to message #91236] Sun, 16 June 2002 12:16 Go to previous message
Mahesh Rajendran
Messages: 10706
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
this link may be more helpful

http://orafaq.net/scripts/plsql/refcurs.txt
Previous Topic: Re: i want to know the addresss of ram he was in bangalore in sangeetha aprts ,malleswaram8thcross.,
Next Topic: Re: Exception in thread "main" Java.lang.NoClassDefFoundError:dbas.
Goto Forum:
  


Current Time: Thu Dec 08 07:02:07 CST 2022