Passing an associative array as a parameter between packages

Question

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

andr · Accepted Answer · 2011-04-25 13:28:55Z

5

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.

answered Apr 25, 2011 at 13:28

andr

1,6468 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

APC · Accepted Answer · 2011-04-25 13:49:46Z

1

"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.

answered Apr 25, 2011 at 13:49

APC

147k20 gold badges180 silver badges289 bronze badges

Collectives™ on Stack Overflow

Passing an associative array as a parameter between packages

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related