Question 40110 in PHP Question posted in
Official manual can be found here.

Bulk insert failing in Oracle Database – Reference to uninitialized collection – PHP

I am working on an application that performs bulk insertion(~12k but it can be more than that) in the Oracle database.
Below is the SQL script:-

DECLARE
    errstr VARCHAR2(4000) := '';
    errors NUMBER;
    i      NUMBER;
    er     NUMBER;
    bk1    acct_common.type_tabnumber;
    bk2    acct_common.type_tabdate;
    bk3    acct_common.type_tabnumber;
    bk49   acct_common.type_tabnumber;
BEGIN
    bk1 := :1;
    bk2 := :2;
    bk3 := :3;
    bk49 := :49;
    FORALL i IN 1..11655
        INSERT INTO table_name (
            col1,
            col2,
            col3,
            col49
        ) VALUES (
            bk1(i),
            bk2(i),
            bk3(i),
            bk49(i)
        );
END;

And the insertion fails with the below error:-

oci error 6531 (ORA-06531: Reference to uninitialized collection ORA-06512: at line 126 ORA-22160: element at index [1] does not exist ORA-06512: at line 106 )

As there are thousands of records, I am not able to figure out which record is having the data issue.
(This query is being generated dynamically with PHP and is being executed with Oracle OCI8)

Can someone please help me with how can i debug/print the specific record data which is failing the script?
Would really appreciate your help. Thanks!

Tags:

2

Answers


  1. 0

    Collections are to be initialized like in the example below:

    declare
  type t_tab_n is table of number;
  tab_n t_tab_n;
begin
  tab_n:=t_tab_n(1,3,12,13,33,44);
end;

    The previous example would initialize the collection with a number of elements.
    Or it could be something like:

    declare
  type t_tab_n is table of number;
  tab_n t_tab_n;
begin
  tab_n:=t_tab_n(); -- initialize collection with no elements
  tab_n.extend; --- add one element to collection
  tab_n(tab_n.last):=3; --- assign value to the added element
  tab_n.extend;
  tab_n(tab_n.last):=12;
end;

    You may not pass a collection as a simple bind-variable, like you wish. That’s from the Oracle side. As to the client application that sends data to Oracle I don’t know how you would bulk bind there a collection.

    Login or Signup to reply.
  2. 0

    As the exception states, you have not initialised the collection.

    Assuming that :1, :2, :3 and :49 are bind variables and you are binding a PL/SQL associative array defined in a package. Based on the oci_bind_array_by_name documentation you can use something like (untested as I do not have a PHP environment):

    $stid = oci_parse(
  $conn,
  "DECLARE
    errstr VARCHAR2(4000) := '';
    errors NUMBER;
    i      NUMBER;
    er     NUMBER;
    bk1    acct_common.type_tabnumber;
    bk2    acct_common.type_tabnumber;
    bk3    acct_common.type_tabnumber;
    bk49   acct_common.type_tabnumber;
BEGIN
    bk1 := :1;
    bk2 := :2;
    bk3 := :3;
    bk49 := :49;
    FORALL i IN INDICES OF bk1
        INSERT INTO table_name (
            col1,
            col2,
            col3,
            col49
        ) VALUES (
            bk1(i),
            DATE '1970-01-01' + bk2(i) * INTERVAL '1' SECOND,
            bk3(i),
            bk49(i)
        );
END;"
);
$bk1s  = array(1,2,3,4,5);
$bk2s  = array(
           mktime(0, 0, 0, 1, 1, 2023),
           mktime(0, 0, 0, 1, 2, 2023),
           mktime(0, 0, 0, 1, 3, 2023),
           mktime(0, 0, 0, 1, 4, 2023),
           mktime(0, 0, 0, 1, 5, 2023)
         );
$bk3s  = array(6,7,8,9,10);
$bk49s = array(11,12,13,14,15);
oci_bind_array_by_name($stid, ":1",  $bk1s,  5, -1, SQLT_INT);
oci_bind_array_by_name($stid, ":2",  $bk2s,  5, -1, SQLT_INT);
oci_bind_array_by_name($stid, ":3",  $bk3s,  5, -1, SQLT_INT);
oci_bind_array_by_name($stid, ":49", $bk49s, 5, -1, SQLT_INT);
oci_execute($stid);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search