Home » SQL & PL/SQL » SQL & PL/SQL » Error Assoc Arrays Data Type as OUT Formal Parameter (PL/SQL)
Error Assoc Arrays Data Type as OUT Formal Parameter [message #333880] Mon, 14 July 2008 13:51 Go to next message
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 #333882 is a reply to message #333880] Mon, 14 July 2008 13:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: Error Assoc Arrays Data Type as OUT Formal Parameter [message #333888 is a reply to message #333880] Mon, 14 July 2008 14:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter). Copy and paste your session.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

There are my_tab and my_tab, my_tab is not the same as my_tab.

Regards
Michel
Re: Error Assoc Arrays Data Type as OUT Formal Parameter [message #333889 is a reply to message #333888] Mon, 14 July 2008 14:19 Go to previous messageGo to next message
sbryantma
Messages: 16
Registered: September 2006
Junior Member
Hi,

I am confused of my_tab is not the same as my_tab. Can you elaborate or give me a simple lines of code?

Thanks,
Steve
Re: Error Assoc Arrays Data Type as OUT Formal Parameter [message #333890 is a reply to message #333880] Mon, 14 July 2008 14:21 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi,
check the procedure "tab_test", i think you got the names of the parameters wrong i.e. l_tab -> p_tab, l_id -> p_id.

regards,
rhani
Re: Error Assoc Arrays Data Type as OUT Formal Parameter [message #333891 is a reply to message #333889] Mon, 14 July 2008 14:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
or give me a simple lines of code?

This is in your code, read it.
If you posted as requested, I could give the line numbers.

Regards
Michel
Re: Error Assoc Arrays Data Type as OUT Formal Parameter [message #333894 is a reply to message #333891] Mon, 14 July 2008 14:45 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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;
Re: Error Assoc Arrays Data Type as OUT Formal Parameter [message #333945 is a reply to message #333896] Tue, 15 July 2008 00:18 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Correct. Even though the two type-declarations look to be the same, Oracle does not recognize that. It treats them as two different types.
Re: Error Assoc Arrays Data Type as OUT Formal Parameter [message #334078 is a reply to message #333880] Tue, 15 July 2008 06:57 Go to previous messageGo to next message
sbryantma
Messages: 16
Registered: September 2006
Junior Member
That is interesting and makes sense. I will work on it later today and let you know. Thanks for so much of your time to help me out. Steve
Re: Error Assoc Arrays Data Type as OUT Formal Parameter [message #334114 is a reply to message #333880] Tue, 15 July 2008 08:15 Go to previous message
sbryantma
Messages: 16
Registered: September 2006
Junior Member
It works I truly appreciate from ehegagoka's message of changing the declaration line from l_tab my_tab to l_tab test_pkg.my_tab.
That is interesting and new to me of collection data type with a formal parameter. Wow! Thanks so much. Steve
Previous Topic: How to access specification's or body's same name constants.
Next Topic: ora-14452
Goto Forum:
  


Current Time: Fri Dec 06 22:57:03 CST 2024