| cursor and function [message #374227] |
Thu, 31 May 2001 07:43  |
Ben
Messages: 48
Registered: January 2000
|
Member |
|
|
Hi All,
I have a stored function I need to use. However, my app cannot call function directly so I need to write a stored procedure to wrap the function since my app can only call stored proc. To make it complicated, my app can only accept cursor as return from the stored proc. The stored func will return a number and it will update a set of tables when it is called. I like to ask how to return a cursor for a function in a stored proc.
Thanks!
|
|
|
|
| Re: cursor and function [message #374232 is a reply to message #374227] |
Thu, 31 May 2001 12:54  |
Bala
Messages: 205
Registered: November 1999
|
Senior Member |
|
|
Hi Ben,
I guess you have posted similler question some time back.
You mean an array when you mension cursor...
you need a function which can return an array?
The following can be use ful to you... i got it from ASKTOM site(asktom.oracle.com)
-----------------------------------------------------------------
for details. It will be the most efficient way to return
800-1000 rows from PLSQL. If you happen to have the data in a
SQL table type already you don't have to return a ref cursor or
an array, the client can just "select" from the plsql variable.
For example, below I have a procedure that fills the array up
and bulk inserts it (much much faster then inserting a row at a
time). The client can then select * from the array -- this may
or may not apply in your case:
ops$tkyte@ORA8I.WORLD> create type myRecordType as object
( a int,
b varchar2(10),
c date
)
/
Type created.
ops$tkyte@ORA8I.WORLD> create table t
( x int, y varchar2(10), z date );
Table created.
ops$tkyte@ORA8I.WORLD> create or replace type myTableType
as table of myRecordType
/
Type created.
ops$tkyte@ORA8I.WORLD> create or replace package my_pkg
as
procedure my_procedure;
function my_function return myTableType;
end;
/
Package created.
ops$tkyte@ORA8I.WORLD> create or replace package body my_pkg
as
g_data myTableType;
procedure my_procedure
is
begin
g_data := myTableType();
for i in 1..50
loop
g_data.extend;
g_data(i) := myRecordType( i, 'row ' || i,
sysdate+i );
end loop;
insert into t
select *
from TABLE ( cast( g_data as mytableType ) );
end;
function my_function return myTableType
is
begin
return g_data;
end;
end;
/
Package body created.
ops$tkyte@ORA8I.WORLD> exec my_pkg.my_procedure;
PL/SQL procedure successfully completed.
ops$tkyte@ORA8I.WORLD> select * from TABLE ( cast(
my_pkg.my_function() as mytableType ) )
/
A B C
---------- ---------- ---------
1 row 1 19-FEB-01
2 row 2 20-FEB-01
3 row 3 21-FEB-01
.....
49 row 49 08-APR-01
50 row 50 09-APR-01
50 rows selected.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
