Re: About Return more tables from a PL/SQL procedure

From: Jan <janik_at_pobox.sk>
Date: 8 Jul 2004 07:28:30 -0700
Message-ID: <81511301.0407080628.798e668c@posting.google.com>

try a procedure with OUT parameters instead of a function, they (OUT parameters) can be many:

I define cursors types in a package:

---

CREATE OR REPLACE PACKAGE Cd AS

TYPE c1           IS REF CURSOR;
TYPE c2           IS REF CURSOR;

END Cd;

---

Then I create a procedure with 2 OUT parameters which are cursors:

---

CREATE OR REPLACE PROCEDURE P (c1_out OUT Cd.c1, c2_out OUT Cd.c2)   IS

BEGIN   OPEN c1_out FOR 'SELECT object_id, object_name from all_objects';   OPEN c2_out FOR 'SELECT table_name from all_tables';

END P;

---

And then I run the procedure p from e.g. a block:

---

DECLARE

   c1         Cd.c1;
   c2         Cd.c2;

BEGIN
   P(c1,c2);
END;

---

Jan

michaelwah_at_tom.com.hk (Michael) wrote in message news:<57aa0935.0407080245.3cbd3d8d_at_posting.google.com>...
> I am converting some Stored Procedure from T-SQL to PL/SQL.
>
> The T-SQL procedure return many table from database,
> and it is called from JDBC.
> how can PL/SQL do something like this ??
>
> Most of examples on web using a PL/SQL function which
> return a REF CURSOR, but this can only return one table.
>
> Thanks for your help.
Received on Thu Jul 08 2004 - 09:28:30 CDT