Re: How can I use the result of a stored procedure in a select

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/04/28
Message-ID: <8eci3r$ain$1@nnrp1.deja.com>#1/1

In article <3909A119.34136FFD_at_cryo-interactive.fr>,   "Miguel Gonçalves" <m.goncalves_at_cryo-interactive.fr> wrote:
> I wrote a stored procedure which basicaly does :
>
> proc_stor_1 @user_choice as
> select id from table where param = @user_choice
>
> What I want to do is to use the result of proc_stor_1 in a select
> statement :
>
> select * from table2 where id in ( proc_stor_1)
>
> How can I do it ?
>
> Thanks
>
>

Well, you definitely would *not* use Transact SQL ;)

Using plsql, in Oracle8.0 and up this would be:

ops$tkyte_at_8i> create or replace type myTableType as table of number;   2 /

Type created.

ops$tkyte_at_8i>
ops$tkyte_at_8i>
ops$tkyte_at_8i> create or replace function get_data( p_user_choice in

varchar2 ) return myTableType
  2 as
  3 l_data myTableType := myTableType();   4 begin
  5 for x in ( select * from all_users where username like '%' || upper(p_user_choice) || '%' )

  6          loop
  7                  l_data.extend;
  8                  l_data(l_data.count) :=  x.user_id;
  9          end loop;
 10
 11          return l_data;

 12 end;
 13 /

Function created.

ops$tkyte_at_8i>
ops$tkyte_at_8i>
ops$tkyte_at_8i>
ops$tkyte_at_8i> select *

  2 from THE ( select cast( get_data('kyte') as mytableType ) from dual ) a
  3 /

COLUMN_VALUE

---

       32485
       30520
       29898

ops$tkyte_at_8i>

ops$tkyte_at_8i>
ops$tkyte_at_8i> select * from all_users where user_id in (

  2      select *
  3        from TABLE ( cast( get_data('kyte') as mytableType ) ) a

  4 )
  5 /

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
WEB$TKYTE_US                        29898 08-FEB-00
WEB$TKYTE                           30520 22-FEB-00
OPS$TKYTE                           32485 06-APR-00



--
Thomas Kyte                              tkyte_at_us.oracle.com

Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html

--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Fri Apr 28 2000 - 00:00:00 CDT