Re: PL-SQL Tables -- undocumented restrictions?

Hi Jeffery,

Do you have a reason to declare a type in your SQL Test script similar to the one you've put in your package? I don't think so. You should refer to the type declared in your package specification and all goes well !

• test.sql declare
• standard test variables error number; in_data number := 0; out_data jtest.pt_criteria_tab; begin
• this first call obviously works error := jtest.test_func;
• this call does not error := jtest.func(in_data,out_data); end; /

Regards,
Jan Vissers

Jeffery Cann wrote in message <35DC6AEC.8C10ABCB_at_fairway.com>...
>Hello.
>
>I am attempting to use a PL-SQL table as a parameter in a function.
>Although my "PL/SQL User's Guide and Reference" (Release 2.3, page 4-4)
>does not state any restrictions in using PL-SQL tables as function or
>procedure parameters. I am running PL-SQL release 2.3.3.4.0 against
>Oracle7 Server Release 7.3.3.4.0.
>
>I have included the test package and test script. Have I made an error
>in my declarations, or are the run-time errors observed when running the
>test script an undocumented restriction of PL-SQL tables?
>
>My goal is to pass a pl-sql table directly to function 'func' (in
>jtest.pkg below).
>If I call this function directly (as in test.sql below), the
>PL-SQL compiler shows the following message:
>
>ERROR at line 1:
>ORA-06550: line 11, column 11:
>PLS-00306: wrong number or types of arguments in call to 'FUNC
>ORA-06550: line 11, column 2:
>PL/SQL: Statement ignored
>
>If I call test_func from test.sql, test_func can call func correctly
>because test_func and func are declared in the same package. Thus,
>passing a PL-SQL table does not cause an error.
>
>I suspect that you can only use a PL-SQL table as a parameter to a
>function or procedure if the calling block is part of the package where
>the PL-SQL table was declared. Thus, attempting to call a function with
>a PL-SQL table as a parameter from an anonymous block (as in test.sql)
>will cause the above error.
>
>Thanks for any help on this one.
>Jeffery Cann
Received on Thu Aug 20 1998 - 00:00:00 CDT