Passing in a Java Array into a stored procedure

Question

I am attempting to pass a java array into my stored procedure and updating records with the values in the array. Currently when I attempt to execute and test the stored procedure I am running into

Error:ORA-06531: Reference to uninitialized collection.

Can anybody push me in the right direction or help me clean up my code. Below is the package spec followed the body.

CREATE OR REPLACE package AOMS.test_array1 as

type t1 is record (
s1 varchar2(1),
i_part_no varchar2(20),
i_itc varchar2(20),
s2 varchar2(1),
l_part_no varchar2(20));

type tab1 is table of t1 ;

tab2 tab1;

Here is the body.

CREATE OR REPLACE PACKAGE BODY AOMS.TEST_ARRAY1 AS 

I_ARRAY varchar2(1000);

PROCEDURE test_array2(i_array IN tab2%TYPE) AS

  l_s1 VARCHAR2(50);
  l_part_no1 VARCHAR2(50);
  l_itc varchar2(50);
  l_s2 varchar2(50);
  l_part_no2  varchar2(50);

BEGIN
   FOR x IN i_array.first .. i_array.last
   LOOP
  l_s1 := i_array(x).s1;
  l_part_no1 := i_array(x).i_part_no;
  l_itc := i_array(x).i_itc;
  l_s2  := i_array(x).s2;
  l_part_no2  := i_array(x).l_part_no;

  UPDATE replacement_parts
   SET frst_src = l_s1,
   frst_part_no = l_part_no1,
   ITC = l_itc,
   last_src = l_s2,
   last_part_no = l_part_no2


   WHERE    
     frst_src = 'P'
  AND frst_part_no = '96424447            ';
 COMMIT;
 END LOOP;
 END test_array2;
 END test_array1;
 /

I'm using Toad so when I call the procedure I just right click and execute and enter in my params. Here is the anonymous block code that gets generated when I attempt to execute.

DECLARE 
  I_ARRAY AOMS.TEST_ARRAY1.tab2%type;

BEGIN 
  -- I_ARRAY := NULL; Modify the code to initialize this parameter

  AOMS.TEST_ARRAY1.TEST_ARRAY2 ( I_ARRAY );
  COMMIT; 
END;

The issue seems to be in the caller; please post the code you use to call the procedure. — Aleksej
– Aleksej, Commented Jan 25, 2017 at 15:01

Aleksej · Accepted Answer · 2017-01-25 15:23:09Z

You have some issues both in the package and in the procedure call. This should work:

CREATE OR REPLACE PACKAGE test_array1 AS
    TYPE t1 IS RECORD
    (
        s1                                      VARCHAR2(1),
        i_part_no                               VARCHAR2(20),
        i_itc                                   VARCHAR2(20),
        s2                                      VARCHAR2(1),
        l_part_no                               VARCHAR2(20)
    );

    TYPE tab1 IS TABLE OF t1;

    tab2                                    tab1;

    PROCEDURE test_array2(i_array IN tab1);
END test_array1;
CREATE OR REPLACE PACKAGE BODY TEST_ARRAY1 AS
    I_ARRAY                                 VARCHAR2(1000);

    PROCEDURE test_array2(i_array IN tab1) IS
        l_s1                                    VARCHAR2(50);
        l_part_no1                              VARCHAR2(50);
        l_itc                                   VARCHAR2(50);
        l_s2                                    VARCHAR2(50);
        l_part_no2                              VARCHAR2(50);
    BEGIN
        IF i_array.COUNT > 0
        THEN
            FOR x IN i_array.FIRST .. i_array.LAST
            LOOP
                l_s1          := i_array(x).s1;
                l_part_no1    := i_array(x).i_part_no;
                l_itc         := i_array(x).i_itc;
                l_s2          := i_array(x).s2;
                l_part_no2    := i_array(x).l_part_no;

                UPDATE replacement_parts
                   SET frst_src        = l_s1,
                       frst_part_no    = l_part_no1,
                       ITC             = l_itc,
                       last_src        = l_s2,
                       last_part_no    = l_part_no2
                 WHERE     frst_src = 'P'
                       AND frst_part_no = '96424447            ';

                COMMIT;
            END LOOP;
        END IF;
    END test_array2;
END test_array1;
/

The call:

DECLARE 
  I_ARRAY TEST_ARRAY1.tab1;

BEGIN 
  I_ARRAY :=  TEST_ARRAY1.tab1();

  TEST_ARRAY1.TEST_ARRAY2 ( I_ARRAY );
  COMMIT; 
END;

The changes I made:

you define a type in your package, then use something like variable%type to declare the procedure, while you can simply use the type.
in the package, while scanning a collection, it's better to check if the collection has values before trying to use collection.first. Trying to access the .first on an empty collection can lead to an issue.
in the caller, you need to initialize the collection the way I showed to avoid the error you are having

As an aside, you should better try to use more explanatory names for variables, types, procedures, packages to avoid confusion between different objects.

Another thing: you have a commit inside a loop; this means that, keeping aside performances, if the first, say, 3 records are updated and then you have an error, you commit 3 updates; is this really what you need? Also, this way the commit in the caller is unuseful.

Thank you this was very helpful. My code was able to succesfully compile. Now I gotta figure out how the array will be passed in. :p

Collectives™ on Stack Overflow

Passing in a Java Array into a stored procedure

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related