Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2008
    Posts
    2

    Unanswered: Return Associative Arrays in a Stored Proc

    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...

    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

    HTML Code:
    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...

    Code:
    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

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •