stored procedure as a table.
Antonio
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!!!!
Thomas Kyte
You need to use Oracle9i or up to do that:
ops$tk...@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$tk...@ORA9I.WORLD>
ops$tk...@ORA9I.WORLD>
ops$tk...@ORA9I.WORLD> set echo off
Enter to continue
=================================================================
now we'll just see this in action
ops$tk...@ORA9I.WORLD>
ops$tk...@ORA9I.WORLD> select * from TABLE( virtual_table(5) );
COLUMN_VALUE
------------
1
2
3
4
5
ops$tk...@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$tk...@ORA9I.WORLD> create or replace type virtual_table_type as table of
number
2 /
Type created.
ops$tk...@ORA9I.WORLD>
ops$tk...@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$tk...@ORA9I.WORLD>
ops$tk...@ORA9I.WORLD> select * from TABLE( virtual_table(5) );
COLUMN_VALUE
------------
1
2
3
4
5
ops$tk...@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 (tk...@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