Error Assoc Arrays Data Type as OUT Formal Parameter [message #333880] |
Mon, 14 July 2008 13:51 |
sbryantma
Messages: 16 Registered: September 2006
|
Junior Member |
|
|
Hi,
This is my first time to use the associated arrays collection in a PL/SQL procedure. Please see the error message of wrong type of collection argument under the package body below:
-----------------------------------------------------------
CREATE OR REPLACE package test_pkg AS
TYPE my_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
PROCEDURE main (errbuff OUT VARCHAR2
,retcode OUT NUMBER);
PROCEDURE tab_test (l_id IN NUMBER, l_tab OUT my_tab);
END test_pkg;
CREATE OR REPLACE package body test_pkg AS
PROCEDURE main ( errbuff OUT VARCHAR2
,retcode OUT NUMBER ) IS
l_id NUMBER;
TYPE my_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
l_tab my_tab;
BEGIN
l_id :=10;
tab_test(l_id,l_tab);
-- ERROR MESSAGE: "PLS-00306: wrong number or types of arguments in call to 'test_pkg'"
END main;
PROCEDURE tab_test (p_id IN NUMBER, p_tab OUT my_tab) IS
i NUMBER;
BEGIN
FOR i IN 1..5 LOOP
p_tab(i) := 'Line '||i;
END LOOP;
END tab_test;
END test_pkg;
/
------------------------------------------------------
Can you be able to identify what I did wrong with the using of collection parameter as wrong data type in the PL/SQL procedures?
Thanks,
Steve
[Mod-Edit: Frank added code-tags to improve readability]
[Updated on: Tue, 15 July 2008 00:16] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
Re: Error Assoc Arrays Data Type as OUT Formal Parameter [message #333894 is a reply to message #333891] |
Mon, 14 July 2008 14:45 |
sbryantma
Messages: 16 Registered: September 2006
|
Junior Member |
|
|
I am still missing something. The below query is used the only paramter and still show the same error message. Can you give me a little hint? Can you test the query below. Thanks, Steve
CREATE OR REPLACE package test_pkg AS
TYPE my_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
PROCEDURE main (errbuff OUT VARCHAR2
,retcode OUT NUMBER);
PROCEDURE tab_test (p_tab OUT my_tab);
END test_pkg;
CREATE OR REPLACE package body test_pkg AS
PROCEDURE main ( errbuff OUT VARCHAR2
,retcode OUT NUMBER ) IS
TYPE my_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
l_tab my_tab;
BEGIN
tab_test(l_tab);
-- ERROR MESSAGE: "PLS-00306: wrong number or types of arguments in call to 'test_pkg'"
END main;
PROCEDURE tab_test(p_tab OUT my_tab) IS
i NUMBER;
BEGIN
FOR i IN 1..5 LOOP
p_tab(i) := 'Line '||i;
END LOOP;
END tab_test;
END test_pkg;
/
|
|
|
Re: Error Assoc Arrays Data Type as OUT Formal Parameter [message #333896 is a reply to message #333894] |
Mon, 14 July 2008 14:59 |
ehegagoka
Messages: 493 Registered: July 2005
|
Senior Member |
|
|
hi, (hope i don't get scolded by this, also please correct me if i'm wrong)
in your package spec, you declared "my_tab" as an array, then you declared a procedure:
PROCEDURE tab_test (p_tab OUT my_tab);
the "my_tab" here refers to the package level "my_tab" array you declared.
now on your package body - procedure (i.e. main), you declared again a "my_tab" array, see it? so your l_tab variable now takes that procedure level "my_tab" array as it's datatype. but in your package spec, the declaration there of tab_test refers to the package level my_tab array. so pl/sql is expecting that you declare your l_tab variable to refer to the package level my_tab array type you declared in your package spec. possible solutions i think is to remove your declaration of "my_tab" in the procedure main, or refer to the absolute level of your my_tab datatype in your declaration of l_tab variable e.g. l_tab test_pkg.my_tab;
|
|
|
|
|
|