Forum Stats

  • 3,874,720 Users
  • 2,266,768 Discussions
  • 7,911,958 Comments

Discussions

Insert, Update and Delete within collection

Ambuj Kumar
Ambuj Kumar Member Posts: 2 Red Ribbon
edited Oct 8, 2018 9:38PM in Database Ideas - Ideas

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.

Ambuj KumarSven W.
4 votes

Active · Last Updated

Comments

  • Sven W.
    Sven W. Member Posts: 10,559 Gold Crown

    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
    Ambuj Kumar Member Posts: 2 Red Ribbon

    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.
    Sven W. Member Posts: 10,559 Gold Crown

    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.

    Peter Hraško