Home » SQL & PL/SQL » SQL & PL/SQL » Return Associative Arrays in a Stored Proc (Oracle)
Return Associative Arrays in a Stored Proc (Oracle) [message #348676] Wed, 17 September 2008 10:11 Go to next message
welshgaz
Messages: 4
Registered: September 2008
Junior Member
Howdy folks,

This is my first time writing these SPs and I can't really see whats wrong with this at the minute.

Current code...

CREATE OR REPLACE TYPE num_array AS TABLE OF NUMBER;
/

CREATE OR REPLACE PROCEDURE return_array_test (array_in IN num_array, array_out OUT num_array) AS

	temp NUMBER;

BEGIN
	FOR i IN 1 .. array_in.count
	LOOP
		array_out(i) := array_in(i)*2;
	END LOOP;
END return_array_test;
/


Then to run it....

set serveroutput on

DECLARE 

  array_in   num_array := num_array(1,2,3,4,5);
  array_out  num_array := num_array(1,2,3,4,5);
  
BEGIN

  return_array_test (array_in, array_out);

  dbms_output.put_line('Result(s)...');
  For i IN 1 .. array_out.count
  Loop
    dbms_output.put_line(array_out(i));
  END LOOP;

END;

/


I keep getting an error...

DECLARE
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at "ZORA03_DBO.RETURN_ARRAY_TEST", line 8
ORA-06512: at line 8


But no idea why ! Its driving me insane... can anyone help please ????

This is all test/prototype code to see if I can do this stuff but for my end result I want to return a array of numbers from an SP, this array incidentally I don't know how big it will be (its basically item keys that have failed update for whatever reason).

Thanks
- G
Re: Return Associative Arrays in a Stored Proc (Oracle) [message #348678 is a reply to message #348676] Wed, 17 September 2008 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
array_out is an OUT parameter (not IN OUT) and so input is not taken into account.
Two ways:
SQL> CREATE OR REPLACE TYPE num_array AS TABLE OF NUMBER;
  2  /

Type created.

SQL> CREATE OR REPLACE PROCEDURE return_array_test (array_in IN num_array, array_out IN OUT num_array) AS
  2  
  3   temp NUMBER;
  4  
  5  BEGIN
  6   FOR i IN 1 .. array_in.count
  7   LOOP
  8    array_out(i) := array_in(i)*2;
  9   END LOOP;
 10  END return_array_test;
 11  /

Procedure created.

SQL> DECLARE 
  2  
  3    array_in   num_array := num_array(1,2,3,4,5);
  4    array_out  num_array := num_array(1,2,3,4,5);
  5    
  6  BEGIN
  7  
  8    return_array_test (array_in, array_out);
  9  
 10    dbms_output.put_line('Result(s)...');
 11    For i IN 1 .. array_out.count
 12    Loop
 13      dbms_output.put_line(array_out(i));
 14    END LOOP;
 15  
 16  END;
 17  /
Result(s)...
2
4
6
8
10

PL/SQL procedure successfully completed.

or
SQL> CREATE OR REPLACE PROCEDURE return_array_test (array_in IN num_array, array_out OUT num_array) AS
  2  
  3   temp NUMBER;
  4  
  5  BEGIN
  6          array_out := num_array();
  7   FOR i IN 1 .. array_in.count
  8   LOOP
  9    array_out.extend;
 10    array_out(i) := array_in(i)*2;
 11   END LOOP;
 12  END return_array_test;
 13  /

Procedure created.

SQL> DECLARE 
  2  
  3    array_in   num_array := num_array(1,2,3,4,5);
  4    array_out  num_array := num_array(1,2,3,4,5);
  5    
  6  BEGIN
  7  
  8    return_array_test (array_in, array_out);
  9  
 10    dbms_output.put_line('Result(s)...');
 11    For i IN 1 .. array_out.count
 12    Loop
 13      dbms_output.put_line(array_out(i));
 14    END LOOP;
 15  
 16  END;
 17  /
Result(s)...
2
4
6
8
10

PL/SQL procedure successfully completed.

Regards
Michel
Re: Return Associative Arrays in a Stored Proc (Oracle) [message #348683 is a reply to message #348676] Wed, 17 September 2008 10:31 Go to previous messageGo to next message
welshgaz
Messages: 4
Registered: September 2008
Junior Member
Michel your are a diamond !

That has been driving me insane for the last hour... Worked a treat

This has brought up a new problem though ! (always one)
If I don't know what size of array I'm returning from the SP, how do I go about initialising the array to pass into the SP ? or using the combination of IN/OUT...

Re: Return Associative Arrays in a Stored Proc (Oracle) [message #348685 is a reply to message #348683] Wed, 17 September 2008 10:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You posted a working test case (I thank you for that) so it is easy to work on it and then be able to answer.

If you use the second way, you haven't to initialize the returned array (this is done by the called procedure) and so know the (max) size.
SQL> DECLARE 
  2  
  3    array_in   num_array := num_array(1,2,3,4,5);
  4    array_out  num_array;
  5    
  6  BEGIN
  7  
  8    return_array_test (array_in, array_out);
  9  
 10    dbms_output.put_line('Result(s)...');
 11    For i IN 1 .. array_out.count
 12    Loop
 13      dbms_output.put_line(array_out(i));
 14    END LOOP;
 15  
 16  END;
 17  /
Result(s)...
2
4
6
8
10

PL/SQL procedure successfully completed.

Regards
Michel

[Updated on: Wed, 17 September 2008 10:37]

Report message to a moderator

Re: Return Associative Arrays in a Stored Proc (Oracle) [message #348687 is a reply to message #348676] Wed, 17 September 2008 10:40 Go to previous message
welshgaz
Messages: 4
Registered: September 2008
Junior Member
Thank you !
I looked again (much more carefully) and I was missing the array_out.extend;
Previous Topic: running update on 200000 records
Next Topic: ORA-00600 durring merge on IOT
Goto Forum:
  


Current Time: Mon Dec 05 04:38:10 CST 2016

Total time taken to generate the page: 0.06427 seconds