Type validation within stored procedures

How does oracle stored procedures handle two different types with the same exact type declaration only that they are stored in two different packages? Example: package1 has type,

Type myType Is Table Of VARCHAR2(40) Index By Binary_Integer;

package2 has type,

Type myType Is Table Of VARCHAR2(40) Index By Binary_Integer;

Now if I use a stored procedure (parameter => myType) stored in package2 and make the call in package1 (input => myType), this throws me an error

PLS-00306: wrong number or types of arguments in call to

Does oracle handle these as different with no internal check for whether both types are 'table of varchar(40)'?

Also what's the best way I can make package1 myType be referencing package2 myType. Subtypes? or is there a way to do it directly?

Thanks

CodePudding user response：

It does not matter if the type signatures are identical, package1.mytype will never be the same type as package2.mytype as they are declared in two different places so they are not identical.

If you want to pass types between packages then only declare the type in one place and use it in all the different packages.

For example:

CREATE PACKAGE package1
AS
  Type myType Is Table Of VARCHAR2(40) Index By Binary_Integer;
  
  PROCEDURE proc(
    value IN package1.mytype
  );
END;
/

CREATE PACKAGE BODY package1
AS
  PROCEDURE proc(
    value IN package1.mytype
  )
  IS
    i PLS_INTEGER;
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Package 1:');
    i := value.FIRST;
    WHILE i IS NOT NULL LOOP
      DBMS_OUTPUT.PUT_LINE( i || ': ' || value(i) );
      i := value.NEXT(i);
    END LOOP;
  END;
END;
/

Do not declare the type in package2, use the type from package1:

CREATE PACKAGE package2
AS
  PROCEDURE proc(
    value IN package1.mytype
  );
END;
/

CREATE PACKAGE BODY package2
AS
  PROCEDURE proc(
    value IN package1.mytype
  )
  IS
    i PLS_INTEGER;
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Package 2:');
    i := value.FIRST;
    WHILE i IS NOT NULL LOOP
      DBMS_OUTPUT.PUT_LINE( i || ': ' || value(i) );
      i := value.NEXT(i);
    END LOOP;
  END;
END;
/

Then:

DECLARE
  value PACKAGE1.MYTYPE;
BEGIN
  value(2) := 'ABC';
  value(4) := 'DEF';
  value(5) := 'GHI';
  value(9) := 'JKL';
  
  PACKAGE1.PROC(value);
  PACKAGE2.PROC(value);
END;
/

Outputs:

Package 1:
2: ABC
4: DEF
5: GHI
9: JKL
Package 2:
2: ABC
4: DEF
5: GHI
9: JKL

db<>fiddle here

Does oracle handle these as different with no internal check for whether both types are 'table of varchar(40)'?

Yes, package1 != package2 therefore package1.mytype != package2.mytype regardless of whether the signature of package1.mytype is identical to the signature of package2.mytype.

what's the best way I can make package1.myType be referencing package2.myType. Subtypes? or is there a way to do it directly?

It will not work. Just create a single type and use that throughout all the packages.