PL-SQL Tables -- undocumented restrictions?
Date: 1998/08/20
Message-ID: <35DC6AEC.8C10ABCB_at_fairway.com>#1/1
Hello.
[Quoted] 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
- start 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 CEST