Re: Oracle Stored Procedures

From: Gunjeet Singh <gunjeet_at_sunsparkle.ebay.sun.com>
Date: 19 Aug 1998 17:40:22 GMT
Message-ID: <6rf2m6$qgm$1@ebaynews1.Ebay.Sun.COM>

Hi,

Sometime back Thomas Kyte had posted an article on using REF CURSORs in PL/SQL procedures. I had used it then to call a stored procedure from a Java program to get back a result set.

I had used cursors with Oracle 8.0.3 and JDK 1.1.5. I'm not sure if the jdbc drivers support it with Oracle 7.3.

In any case, here's how I have used them :

Part of Java Code --------------------- ResultSet l_cursor;

CallableStatement cs;

cs = conn.prepareCall("{call sp_ListEmp(?)}");

cs.registerOutParameter(1,OracleTypes.CURSOR);

cs.execute();

l_cursor = ((OracleCallableStatement)cs).getCursor(1);

String str1 = "";
int num=0;

while (l_cursor.next ())
{

        str1 = l_cursor.getString (1);
        System.out.println ("Name : " + str1);
        num = l_cursor.getInt (2);
        System.out.println ("Id : " + num);

}

Code for the stored procedure ------------- create or replace package types as type cursorType is ref cursor; end; /

create or replace function fn_ListEmp return types.cursortype as

l_cursor types.cursorType;
begin

open l_cursor for select ename, empno from emp order by ename;

return l_cursor;
end;
/

create or replace procedure sp_ListEmp(l_cursor IN OUT types.cursortype) as

begin

open l_cursor for select ename, empno from emp order by ename;

end;
/

Hope this helps.

Gunjeet

In article mv_at_newsops.execpc.com, Deva Seetharam <psdspss_at_execpc.com> writes:
> I have oracle 7.3 server.
> I am developing a client server app. using Sun's jdk 1.1.6 and jdbc
> drivers.
>
> I would like to have, Oracle stored procs that return, multiple rows.
> For example
> select emp, empId from employee
> where age > 18
> I want this to be part of stored proc. The result of this query will be
> more than
> 1 row. How to make stored procs return multiple rows ?
>
> If I could, how I process that within java ?
>
> Any help would be greatly appreciated.
>
> Best Regards,
> Deva
>
>
Received on Wed Aug 19 1998 - 12:40:22 CDT

Re: Oracle Stored Procedures | JDBC