Associative array loop and call stored procedure PL/SQL

Question

I am using oracle and I get a Associative Array from C#. The code in my pl/sql package is:

    PROCEDURE SG_DATA_ARRAY (PROVA IN T_ASSOCIATIVE_ARRAY, P_RESULT OUT VARCHAR2) AS 

    BEGIN

    SAVEPOINT SP1;

    FOR indx IN 1..PROVA.count
    LOOP
        TA_DATA.TA_TABLE_INS (PROVA(indx), INDX);
    END LOOP;

    P_RESULT:='SUCCESS INS';

    EXCEPTION
       WHEN OTHERS THEN
         ROLLBACK TO SAVEPOINT SP1;

           P_RESULT:='ERROR INS';

    END SG_DATA_ARRAY;

I am doing a loop of associative array and then I call my stored procedure TA_TABLE_INS from the package TA_DATA. In the procedure I call, I save into several variables the data from associative array:

    PROCEDURE TA_TABLE_INS (PROVA IN T_ASSOCIATIVE_ARRAY, P_DESCR IN NUMBER) AS

      P_ID_TMS NUMBER;

      D1 NVARCHAR2 (20);
      DESCRIPTION NVARCHAR2 (255);
      D3 NVARCHAR2(20);
      D4 NVARCHAR2 (255);
      D5 NVARCHAR2(1000);
      D6_REV_N NUMBER;
      D7_REV_N NUMBER;
      D8 NVARCHAR2(20);
      D9 NVARCHAR2(1000);
      D10 NUMBER;

      INSERT INTO TA_TABLE1 
      VALUES (D1, D2, D3, D4, D5, D6, D7, D8, D9, D10);

Is that the right way to save data?

Your code doesn't seem to make sense. As written, it shouldn't compile. Your call to TA_TABLE_INS passes an element of the collection and an index. The procedure is defined to take a t_associative_array as the first parameter, not an element of that collection. Your procedure doesn't appear to do anything with the input parameters, it just inserts 10 NULL values into the table. You haven't posted the definition of your collection. If it is an associative array, your code assumes that it is indexed by an integer and dense. That is possible but would be unlikely in an associative array. — Justin Cave
TYPE T_ASSOCIATIVE_ARRAY IS TABLE OF VARCHAR(4000) INDEX BY PLS_INTEGER. The code compile, but I am doing something wrong I know. I don't undestrand how to insert the element of the collection into TA_TABLE1. How can I do it? — Emanuele Antico
If that's the definition, the code can't compile successfully. You're passing a varchar(4000) to the TA_TABLE_INS procedure. That procedure expects a t_associative_array. That won't compile. Your collection has a single string. Your table has 10 columns with a number of different data types. How would you want to take a single string and insert it into a table with 10 columns? — Justin Cave

HCekic HCekic · Accepted Answer · 2016-11-07T15:52:09

Your second procedure TA_TABLE_INS is not necessary. Your can use a bulk insert rather than passing your associative array row to a procedure.

Hope, below code will help

PROCEDURE SG_DATA_ARRAY (PROVA IN T_ASSOCIATIVE_ARRAY, P_RESULT OUT VARCHAR2) AS 
BEGIN

  SAVEPOINT SP1;

  INSERT INTO TA_TABLE1 
  SELECT SEQUENCE.NETXVAL, --You may use a seq number instead of your prova associative array order 
         tmp.d1, tmp.d2, tmp.d3, 
    FROM TABLE(CAST(PROVA AS T_ASSOCIATIVE_ARRAY) tmp; -- You can directly cast an associative array into a table    


  P_RESULT:='SUCCESS INS';

  EXCEPTION
     WHEN OTHERS THEN
       ROLLBACK TO SAVEPOINT SP1;

         P_RESULT:='ERROR INS';

END SG_DATA_ARRAY;

Associative array loop and call stored procedure PL/SQL

1 Answers