CODEWITHSUNDEEP
arraysoracle-databasestored-proceduresplsql
Jules
Jules

Reputation: 245

Passing in a Java Array into a stored procedure

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;

Upvotes: 2

Views: 738

Answers (1)

Aleksej
Aleksej

Reputation: 22949

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.

Upvotes: 1

Related Questions