Passing an associative array as a parameter between packages

I've got two separate Oracle (v9.2) PL/SQL packages and I'm trying to pass an associative array (ie, index-by table) from a procedure in package1, as a parameter to a procedure in package2. Is this possible? I keep getting PLS-00306: wrong number or types of arguments in call 开发者_运维知识库to 'ROLLUP_TO_15' when I compile package1.

The array is defined as:

type list_tab is table of number(10)
  index by binary_integer;

in both package's spec. In the procedure in package1, I'm calling the second package as package2.rollup_to_15(chanList); That's the line I get the compile error on (chanList is a variable of type list_tab).

In package2, the procedure is defined as:

procedure rollup_to_15(channels in list_tab) is

I'm guessing that my problem is that the type is defined separately in each package, because I can pass the `chanList' variable to other procedures within the first package without any problems.

So, is it possible to pass an associative array between packages? And if so, how?

Dave

Yes, it's possible for sure.

It's hard to explain why do you receive error without package specs samples, but in general to pass a user-defined type as a parameter you should either with define type DDL, or defining the type in package spec.

I suppose you want the latter variant :)

So here're an example:

create or replace package TestPackage_1
as

type TTestType is table of varchar2(1) index by varchar2(1);

end TestPackage_1;
/

create or replace package TestPackage_2
as

procedure Dummy(aParam TestPackage_1.TTestType);

end TestPackage_2;
/

You can use TTestType type in any PL/SQL block, but not in SQL.

"The array is defined as: ... in both package's spec."

This is the source of your problem. PL/SQL regards two separate declarations as two different objects, even though both types have an identical signature. Consequently the engine hurls when you call this:

package2.rollup_to_15(chanList)

Your code has defined the chanList variable as package1.list_tab but the procedure is expecting a variable of type package2.list_tab.

The simplest solution is to declare LIST_TAB just in PACKAGE2, and chnage PACKAGE1 so that chanList is declared appropriately.