Home » SQL & PL/SQL » SQL & PL/SQL » How to pass table/collection/array to function.
How to pass table/collection/array to function. [message #349793] Tue, 23 September 2008 02:42 Go to next message
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 Go to previous message
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
Previous Topic: Update table set value => ORA-00904
Next Topic: Merge statement
Goto Forum:
  


Current Time: Tue Feb 11 09:48:52 CST 2025