Insert, Update and Delete within collection

Ambuj Kumar

Within Oracle database, Collection can be used in "Select" with help of TABLE operator. But we can not perform insert, update and delete as we can do with relational table. At least if we would be able to perform update then it will make data manipulation fast and easy and It will be very cool feature to have.

Sven W.

I agree there could be some improvement when working with collections especially regarding DML.

However a SELECT statement is a kind of processing instruction or mapper between the data in the database and your plsql collection.

When you want to do DML (INSERT;UPDATE, DELETE, MERGE) you need a similar kind of processing instruction or at least a mapper.

Typically this is also a SELECT.  And we do already have the FORALL statement.

Maybe you should give a syntax example that explains your idea in a better (more complete) way.

Ambuj Kumar

I agree there could be some improvement when working with collections especially regarding DML.

However a SELECT statement is a kind of processing instruction or mapper between the data in the database and your plsql collection.

When you want to do DML (INSERT;UPDATE, DELETE, MERGE) you need a similar kind of processing instruction or at least a mapper.

Typically this is also a SELECT.  And we do already have the FORALL statement.

Maybe you should give a syntax example that explains your idea in a better (more complete) way.

This is what i would like to do

CREATE OR REPLACE PACKAGE Test_Pkg IS

  TYPE l_Type_Temp IS TABLE OF Xx_Temp1%ROWTYPE;

  l_Temp  l_Type_Temp;

  PROCEDURE Test;

END Test_Pkg;

/

CREATE OR REPLACE PACKAGE BODY Test_Pkg IS

  PROCEDURE Test IS

 

  BEGIN

    BEGIN

      SELECT * BULK COLLECT

        INTO l_Temp

        FROM Xx_Temp1

       WHERE 1 = 1;

    EXCEPTION

      WHEN OTHERS THEN

        Dbms_Output.Put_Line(SQLERRM);

    END;

 

    --data manipulation

    FOR i IN 1 .. l_Temp.Count LOOP

      IF l_Temp(i).Batch_Approval_Status = 'Yes' THEN

        l_Temp(i).Batch_Name := 'Approved';

      END IF;

    END LOOP;

 

    --Rather than iterating in loop this what i would like to do, if possible

   /* UPDATE TABLE(l_Temp)

       SET Batch_Name = 'Approved'

     WHERE Batch_Approval_Status = 'Yes';*/

    

    

 

    --then push to relational table

    FORALL k IN 1 .. l_Temp.Count

      INSERT INTO Xx_Temp2

        (Batch_Id,

         Period_Name,

         Ledger_Name,

         Batch_Name,

         Batch_Approval_Status,

         Batch_User_Name,

         Batch_User_Id)

      VALUES

        (l_Temp(k).Batch_Id,

         l_Temp(k).Period_Name,

         l_Temp(k).Ledger_Name,

         l_Temp(k).Batch_Name,

         l_Temp(k).Batch_Approval_Status,

         l_Temp(k).Batch_User_Name,

         l_Temp(k).Batch_User_Id);

 

  END Test;

END Test_Pkg;

/

If we have ability to perform Update or Delete within collection then data manipulation would be easier and also faster.

Sven W.

This is what i would like to do

CREATE OR REPLACE PACKAGE Test_Pkg IS

  TYPE l_Type_Temp IS TABLE OF Xx_Temp1%ROWTYPE;

  l_Temp  l_Type_Temp;

  PROCEDURE Test;

END Test_Pkg;

/

CREATE OR REPLACE PACKAGE BODY Test_Pkg IS

  PROCEDURE Test IS

 

  BEGIN

    BEGIN

      SELECT * BULK COLLECT

        INTO l_Temp

        FROM Xx_Temp1

       WHERE 1 = 1;

    EXCEPTION

      WHEN OTHERS THEN

        Dbms_Output.Put_Line(SQLERRM);

    END;

 

    --data manipulation

    FOR i IN 1 .. l_Temp.Count LOOP

      IF l_Temp(i).Batch_Approval_Status = 'Yes' THEN

        l_Temp(i).Batch_Name := 'Approved';

      END IF;

    END LOOP;

 

    --Rather than iterating in loop this what i would like to do, if possible

   /* UPDATE TABLE(l_Temp)

       SET Batch_Name = 'Approved'

     WHERE Batch_Approval_Status = 'Yes';*/

    

    

 

    --then push to relational table

    FORALL k IN 1 .. l_Temp.Count

      INSERT INTO Xx_Temp2

        (Batch_Id,

         Period_Name,

         Ledger_Name,

         Batch_Name,

         Batch_Approval_Status,

         Batch_User_Name,

         Batch_User_Id)

      VALUES

        (l_Temp(k).Batch_Id,

         l_Temp(k).Period_Name,

         l_Temp(k).Ledger_Name,

         l_Temp(k).Batch_Name,

         l_Temp(k).Batch_Approval_Status,

         l_Temp(k).Batch_User_Name,

         l_Temp(k).Batch_User_Id);

 

  END Test;

END Test_Pkg;

/

If we have ability to perform Update or Delete within collection then data manipulation would be easier and also faster.

I understand you want to replace that part:

--data manipulation    FOR i IN 1 .. l_Temp.Count LOOP      IF l_Temp(i).Batch_Approval_Status = 'Yes' THEN        l_Temp(i).Batch_Name := 'Approved';      END IF;    END LOOP;

with this part

   --Rather than iterating in loop this what i would like to do, if possibleUPDATE TABLE(l_Temp)       SET Batch_Name = 'Approved'WHERE Batch_Approval_Status = 'Yes';

First of all you should understand that a plsql loop over a plsql collection is not slow. Do your own measurements, if you don't believe it.

A loop only becomes slow, if you do sql statements inside it. The context switch between the plsql engine and the sql engine is what is expensive.

There is a very good chance that your suggestion would be slower than the plsql only loop. Why? Because it would require to move the collection from plsql to sql including any data type checks. Run the update there (possibly on a temp table) and afterwards move the results back from sql to plsql.

An option that already works is to do those manipulations already during the fetch. So instead of doing the plsql loop your bulk select would do the condition.

SELECT col1, col2, col3, ....,  case when Batch_Approval_Status = 'Yes' then Batch_Name = 'Approved' else batch_name as batch_nameBULK COLLECT INTO l_Temp        FROM Xx_Temp1       WHERE 1 = 1;

One slight disadvantage is, that you now need to list all column names. Many would argue that is an advantage, but I don't. But if that is an issue, there are workarounds for it.