PL-SQL Tables -- undocumented restrictions?

From: Jeffery Cann <jcann_at_fairway.com>
Date: 1998/08/20
Message-ID: <35DC6AEC.8C10ABCB@fairway.com>#1/1

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

• start jtest.pkg =================
  -- jtest.pkg
  --
  

create or replace package jtest as

• Declare PL-SQL table type pt_criteria_tab is table of number index by binary_integer;

function func(

	indata		in	number,
	outdata 	in out  pt_criteria_tab	
	)

return number;
pragma restrict_references ( func, wnds );

function test_func
return number;

end jtest;
/
show errors package jtest;

create or replace package body jtest as

---

function func(

	indata		in	number,
	outdata		in out	pt_criteria_tab

)
return number
is

        i number := 0;
begin

        return i;
end func;

---

function test_func
return number
is

	error		number;
	lindata		number;
	loutdata	pt_criteria_tab;
begin
	error	:=	jtest.func(lindata,loutdata);
	return error;

end test_func;

---

end jtest;
/
show errors package body jtest;

• end package jtest.pkg ===========
  • start test.sql ===================
    -- test.sql
    

declare

• Declare PL-SQL table type pt_criteria_tab is table of number index by binary_integer;
• standard test variables error number; in_data number := 0; out_data 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; /
  • end test.sql =====================

--

Jeffery Cann
Fairway Systems, Inc.
Senior Software Engineer Received on Thu Aug 20 1998 - 00:00:00 CDT