How to pass table/collection/array to function. [message #349793] |
Tue, 23 September 2008 02:42  |
gmoth
Messages: 17 Registered: September 2008 Location: South Africa
|
Junior Member |
|
|
I am not 100% sure about all the differences regarding collections, tables etc all I know is I have a cursor that selects data from more than one table and i need to create some collection that will store that data. Once all data is populated in that collection i need to send it to a function.
1. How do i send to a function in this case?
2. How do i create the function from within the package bearing in mind it will receive a collection and return one too.
3. Is creating a table the best way or would you use arrays, etc?
CREATE OR REPLACE PROCEDURE fc_gm_test_04
IS
CURSOR service_csr
IS
SELECT d.description_code, d.description_text, p.element_id
FROM descriptions d, product p
WHERE d.description_code = p.element_id
AND p.parent_subscr_no = 1154376;
TYPE service_rec IS TABLE OF service_csr%ROWTYPE;
services service_rec;
BEGIN
OPEN service_csr;
FETCH service_csr
BULK COLLECT INTO services;
CLOSE service_csr;
--pass_table_to_function(services);
END fc_gm_test_04;
/
|
|
|
Re: How to pass table/collection/array to function. [message #349824 is a reply to message #349793] |
Tue, 23 September 2008 04:17  |
 |
Alessandro Rossi
Messages: 166 Registered: September 2008 Location: Rome
|
Senior Member |
|
|
You can use sys_refcursor on 9i and newer oracle RDBMS.
SQL> declare
2 ret_val sys_refcursor;
3 x dual.dummy%type;
4 function pass
5 return sys_refcursor
6 is
7 c sys_refcursor;
8 begin
9 open c for
10 select dummy
11 from dual;
12 return c;
13 end;
14 begin
15 ret_val := pass;
16 fetch ret_val into x;
17 dbms_output.put_line(x);
18 end;
19 /
X
PL/SQL procedure successfully completed.
SQL>
Bye Alessandro
|
|
|