RE: ** pass table in a script to execute PL/SQL (procedure)

From: Daniel Wittry <daniel.wittry_at_quest.com>
Date: Fri, 15 Oct 2004 12:57:12 -0700
Message-ID: <B5C5F99D765BB744B54FFDF35F602621089797BF@irvmbxw02>

When you say pass a "table", if you mean a PL/SQL table, then yeah, no problem. That functionality exists way back, even 7.3.4, maybe even before.

  TYPE table_parm IS TABLE OF VARCHAR2(2000)

     INDEX BY BINARY_INTEGER;

  FUNCTION myFunction (in_t_data       IN table_parm,
                       in_num_in_table IN PLS_INTEGER)

    RETURN BOOLEAN
  BEGIN
    dbms_output.put_line( in_t_data(1) );   END myFunction;

DECLARE
  t_stuff table_parm;
BEGIN
  t_stuff(1) :=3D 'hello';
  IF myFunction (t_stuff, 5) THEN
    NULL;
  END IF;
END; The only gotcha is the datatype of the calling and called variable must be the same "thing." In the example above, you cannot define "table_parm" in two different places (like two different private package bodies), even if they are the same structure. If the calling and called procedures are in different packages, simply move the pl/sql table definition to a package header and reference pkg.var like any public variable.

hope that helps

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Oct 15 2004 - 14:53:25 CDT