User-defined type in user-defined function

From: The Gooch <uhcnam_at_hotmail.com>
Date: 30 Sep 2004 06:19:48 -0700
Message-ID: <19b033c1.0409300519.395c15eb@posting.google.com>

I thought this worked for me before ...

I am trying to create a function MYTEST.FUNC_UPDATECOORD which has, as one of its parameters, a variable of a type that's defined in the package MYTEST. This results in the error:

ERROR at line 7:
ORA-06550: line 7, column 32:
PLS-00306: wrong number or types of arguments in call to 'FUNC_UPDATECOORD'
ORA-06550: line 7, column 21:
PL/SQL: ORA-00904: "MYTEST"."FUNC_UPDATECOORD": invalid identifier ORA-06550: line 7, column 5:
PL/SQL: SQL Statement ignored

which you can see if you cut and paste the following code in SQL Plus. Is it possible to have such a parameter for a user-defined function?

Machine specs are:

SQL> SELECT * FROM V$VERSION; BANNER

---

Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production PL/SQL Release 9.2.0.5.0 - Production
CORE 9.2.0.6.0 Production
TNS for Solaris: Version 9.2.0.5.0 - Production NLSRTL Version 9.2.0.5.0 - Production

Code snippet:

---

CREATE OR REPLACE PACKAGE MYTEST IS TYPE Order_Type IS TABLE OF NUMBER INDEX BY NATURAL;

FUNCTION FUNC_UPDATECOORD (

        Coord_in IN VARCHAR2, 
        NewCoordOrder_in IN Order_Type) 

RETURN VARCHAR2; PRAGMA RESTRICT_REFERENCES (FUNC_UPDATECOORD, WNDS, WNPS); FUNCTION FUNC_TEST (

        Coord_in IN VARCHAR2, 
        NewCoordOrder_in IN VARCHAR2) 

RETURN VARCHAR2; PRAGMA RESTRICT_REFERENCES (FUNC_TEST, WNDS, WNPS); END MYTEST;
/

CREATE OR REPLACE PACKAGE BODY MYTEST IS FUNCTION FUNC_TEST (

        Coord_in IN VARCHAR2, 
        NewCoordOrder_in IN VARCHAR2) 

RETURN VARCHAR2 IS BEGIN
    RETURN 'FUNC_TEST';
END FUNC_TEST; FUNCTION FUNC_UPDATECOORD (

        Coord_in IN VARCHAR2, 
        NewCoordOrder_in IN Order_Type) 

RETURN VARCHAR2 IS
BEGIN
    RETURN 'FUNC_UPDATECOORD';
END FUNC_UPDATECOORD;
END MYTEST;
/

DECLARE

var_NewCoordOrder MYTEST.Order_Type; 
var_mychar VARCHAR2(20); 
var_out VARCHAR2(20); 
BEGIN 
    SELECT          MYTEST.FUNC_TEST('ab', var_mychar) INTO var_out
FROM DUAL;
    SELECT          MYTEST.FUNC_UPDATECOORD('ab', var_mychar) INTO

var_out FROM DUAL;
END;
/ Received on Thu Sep 30 2004 - 08:19:48 CDT