Re: stored procedure as a table.

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 2 Oct 2001 08:01:37 -0700
Message-ID: <9pcksh012bn@drn.newsguy.com>

In article <9pchu4$70$1_at_diana.bcn.ttd.net>, "Antonio" says...
>
>
> Hi!
>
> I am migrating from Interbase Database to Oracle 8i (8.1.7), and I have
>some stored procedures in Interbase that acts like a table.
> I mean:
> I have one stored procedure like this ( it has been in oracle
>syntax):
>-----------------------
>CREATE OR REPLACE PROCEDURE MYPROC (
> A IN VARCHAR,
> B OUT VARCHAR)
>AS
> cursor c1 is
> SELECT field1 FROM table WHERE something;
> cur_values c1%ROWTYPE;
>begin
> open c1;
> loop
> fetch c1 into cur_values;
> exit when c1%NOTFOUND;
> b:= cur_values.field1;
>
> if(b=1) then b:=1000; end if;
> /* SUSPEND */ <-- What to put here in Oracle?
> end loop;
> close c1;
>end;
>/
>---------------------------
>
> And I want to make a select like: select * from MYPROC(a,b);
> and returning each value of b when SUSPEND is called. (SUSPEND is a call
>from Interbase that returns the current value of "b" for each row fetched in
>select).
>
> My question is:
> How can I make this in ORACLE?
>
> Thanx a lot in advance!!!!
>
>
>
>

You need to use Oracle9i or up to do that:

ops$tkyte_at_ORA9I.WORLD> create or replace function virtual_table( p_num_rows in number )
  2 return virtual_table_type
  3 PIPELINED -- NOTE the pipelined keyword   4 is
  5 begin

  6      for i in 1 .. p_num_rows
  7      loop
  8          pipe row( i );
  9      end loop;
 10  
 11      return; -- note return with no value!

 12 end;
 13 /

Function created.

ops$tkyte_at_ORA9I.WORLD> 
ops$tkyte_at_ORA9I.WORLD> 
ops$tkyte_at_ORA9I.WORLD> set echo off

Enter to continue

---

now we'll just see this in action

ops$tkyte_at_ORA9I.WORLD>
ops$tkyte_at_ORA9I.WORLD> select * from TABLE( virtual_table(5) );

COLUMN_VALUE

---

           1
           2
           3
           4
           5

ops$tkyte_at_ORA9I.WORLD> select * from TABLE( virtual_table(10) );

COLUMN_VALUE

---

           1
           2
           3
           4
           5
           6
           7
           8
           9
          10

10 rows selected.

We use "pipe" instead of suspend.

Prior to 9i, there is no pipe, it would look like this:

ops$tkyte_at_ORA9I.WORLD> create or replace type virtual_table_type as table of number
  2 /

Type created.

ops$tkyte_at_ORA9I.WORLD>
ops$tkyte_at_ORA9I.WORLD> create or replace function virtual_table( p_num_rows in number )
  2 return virtual_table_type
  3 is
  4 l_data virtual_table_type := virtual_table_type();   5 begin

  6      for i in 1 .. p_num_rows
  7      loop
  8                  l_data.extend;
  9                  l_data(l_data.count) := i;
 10      end loop;
 11  
 12      return l_data;

 13 end;
 14 /

Function created.

ops$tkyte_at_ORA9I.WORLD>
ops$tkyte_at_ORA9I.WORLD> select * from TABLE( virtual_table(5) );

COLUMN_VALUE

---

           1
           2
           3
           4
           5

ops$tkyte_at_ORA9I.WORLD> select * from TABLE( virtual_table(10) );

COLUMN_VALUE

---

           1
           2
           3
           4
           5
           6
           7
           8
           9
          10

10 rows selected.

you would fill up the collect and then return it (no "pipe", no suspend functionality)

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Tue Oct 02 2001 - 10:01:37 CDT