Home » SQL & PL/SQL » SQL & PL/SQL » passing an array as argument to a function
passing an array as argument to a function [message #196009] Tue, 03 October 2006 11:57 Go to next message
broker007
Messages: 3
Registered: October 2006
Junior Member
Hello!! I have a function that retrieves an array of values and I want to pass the whole array into another function so that I can process it in bulk.
Can you please give me an example, or at least an alternative way to do this?
Many thanks
Re: passing an array as argument to a function [message #196036 is a reply to message #196009] Tue, 03 October 2006 15:07 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
If you define the pl/sql table or varray in the package spec and make it visible, you can populate it with one function and then reference it from another piece of code witout passing the whole data set. If it's a big set, you may want to manually release the memory afterwards.


http://asktom.oracle.com/pls/ask/f?p=4950:8:431696::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1648092989461,
http://www.oracledba.co.uk/tips/plsql_table_memory.htm
Re: passing an array as argument to a function [message #196122 is a reply to message #196036] Wed, 04 October 2006 03:56 Go to previous messageGo to next message
broker007
Messages: 3
Registered: October 2006
Junior Member
This is useful. Thanks very much..
However, asktom's example is referring to pass a result to a procedure, whereas my problem is how can I call a procedure using a result set as an argument ..
is it for example:
proc(x in resultset); ???
and resultset is defined in the spec file?
Could you please clarify that for me?
Re: passing an array as argument to a function [message #196534 is a reply to message #196122] Thu, 05 October 2006 17:50 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
In 9i and above you can use SYS_REFCURSOR rather than define your own
CREATE TABLE ABC (A  NUMBER);

insert into abc values(1);
insert into abc values(9);
insert into abc values(13);
insert into abc values(999);

CREATE OR REPLACE PACKAGE ref_cur_tst
IS
-- dummy cursor to get %rowtype below
   CURSOR c1 IS SELECT a FROM abc;
   TYPE t_cur IS REF CURSOR RETURN c1%ROWTYPE;
-- or just use abc%rowtype to match whole table structure.
--  TYPE t_cur IS REF CURSOR RETURN abc%ROWTYPE;

   PROCEDURE get_abc (cv_cur IN OUT t_cur);
END ref_cur_tst;
/

CREATE OR REPLACE PACKAGE BODY ref_cur_tst
AS
   PROCEDURE get_abc (cv_cur IN OUT t_cur)
   IS
   BEGIN
      OPEN cv_cur FOR SELECT a FROM abc;
   END get_abc;
END ref_cur_tst;
/

set serveroutput on;

DECLARE
   a       abc.a%TYPE;
   cv_c1   ref_cur_tst.t_cur;
BEGIN
   ref_cur_tst.get_abc (cv_c1);

   LOOP
      FETCH cv_c1 INTO a;
      EXIT WHEN cv_c1%NOTFOUND;
      DBMS_OUTPUT.put_line (a);
   END LOOP;
   CLOSE cv_c1;
END;
/
Re: passing an array as argument to a function [message #196535 is a reply to message #196534] Thu, 05 October 2006 18:10 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Search for "lot of CPU consuming" in this doc:
http://www.akadia.com/services/ora_important_2000.html#How%20to%20avoid%20performance%20disaster%20with%20PL/SQL%20tables
Re: passing an array as argument to a function [message #197496 is a reply to message #196535] Wed, 11 October 2006 08:24 Go to previous message
broker007
Messages: 3
Registered: October 2006
Junior Member
Hi again..
I am still having problems with that.
After I manage to pass the whole array into another function as an argument, when I try to INSERT its values to a table I always have problem. Any suggestions?
Previous Topic: Select set of records from a table
Next Topic: Is commit & rollback is DCL command
Goto Forum:
  


Current Time: Sat Apr 27 08:19:05 CDT 2024