Re: User-defined type in user-defined function

From: The Gooch <uhcnam_at_hotmail.com>
Date: 1 Oct 2004 08:23:48 -0700
Message-ID: <19b033c1.0410010723.79df3cb1@posting.google.com>

Sybrand Bakker <sybrandb_at_hccnet.nl> wrote in message news:<f40pl0942ccnt6ot720g9v9a1op1mrb4m3_at_4ax.com>...
> On 30 Sep 2004 06:19:48 -0700, uhcnam_at_hotmail.com (The Gooch) wrote:
>
> >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?
>
>
> Your actual parameter is varchar2, not of the UDT. Which is exactly
> what the error message is saying. Define the type in the package spec,
> and make sure the actual parameter is of this type.

Sorry, I don't know how that typo occured. Anyway, calling the procedure CORRECTLY, as follows:

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_NewCoordOrder) INTO var_out FROM DUAL;
END;
/

results in the same error:

    SELECT MYTEST.FUNC_UPDATECOORD('ab', var_NewCoordOrder) INTO

                                                  *

ERROR at line 8:

ORA-06550: line 8, column 51:
PLS-00382: expression is of wrong type
ORA-06550: line 8, column 28:
PLS-00306: wrong number or types of arguments in call to

'FUNC_UPDATECOORD'
ORA-06550: line 8, column 21:
PL/SQL: ORA-00904: "MYTEST"."FUNC_UPDATECOORD": invalid identifier ORA-06550: line 8, column 5:
PL/SQL: SQL Statement ignored Received on Fri Oct 01 2004 - 10:23:48 CDT