3 Replies Latest reply on Feb 20, 2013 8:16 PM by SamFisher

    Deleting/Updating records from an object table in PL/SQL

    SamFisher
      Hello All,

      VER:
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      PL/SQL Release 11.2.0.3.0 - Production
      CORE     11.2.0.3.0     Production
      TNS for Linux: Version 11.2.0.3.0 - Production
      NLSRTL Version 11.2.0.3.0 - Production

      I have created an object and inserted records in it. Is there any way we can delete/update records from it. I do not want to delete based on iteration like delete.collection but I would like to know if we can delete directly from obj like delete from table...
      CREATE OR REPLACE TYPE test_type AS OBJECT
      (
      col1 number,
      col2 varchar2(100)
      );
      
      CREATE OR REPLACE TYPE tab_type is table of test_type;
      
      DECLARE
      test_tab tab_type;
      l_cnt NUMBER;
      BEGIN
      select test_type(col1,col2) bulk collect 
      into test_tab from (select 1 as col1,'test1' as col2 from dual 
                          union all
                          select 2,'test2' from dual);
      IF test_tab.count>0
      THEN
      DELETE FROM TABLE(CAST(test_tab as tab_type)) a
      where a.col1=1;
      END IF;
      
      l_cnt := test_tab.count;
                                                                  
      END;
      Thx
      Shank.
        • 1. Re: Deleting/Updating records from an object table in PL/SQL
          sb92075
          SamFisher wrote:
          Hello All,

          VER:
          Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
          PL/SQL Release 11.2.0.3.0 - Production
          CORE     11.2.0.3.0     Production
          TNS for Linux: Version 11.2.0.3.0 - Production
          NLSRTL Version 11.2.0.3.0 - Production

          I have created an object and inserted records in it.
          WHY?
          What is the advantage of of doing so?
          • 2. Re: Deleting/Updating records from an object table in PL/SQL
            Sreenivas Gudipudi
            You do not have to use "Delete" statement to delete the values from collection. Oracle provides "delete" method and also you should initialize the collection before populating it; See below
            CREATE OR REPLACE TYPE test_type AS OBJECT
            (
            col1 number,
            col2 varchar2(10)
            );
             
            CREATE OR REPLACE TYPE tab_type is table of test_type;
             
            DECLARE
            test_tab tab_type:=tab_type();
            l_cnt NUMBER;
            BEGIN
            select test_type(col1,col2) bulk collect 
            into test_tab from (select 1 as col1,'test1' as col2 from dual 
                                union all
                                select 2,'test2' from dual);
            DBMS_OUTPUT.PUT_LINE('Before deleting:'||test_tab.count);                     
            IF test_tab.count>0
            THEN
            test_tab.delete(1);
            END IF;
            DBMS_OUTPUT.PUT_LINE('After deleting:'||test_tab.count);   
            l_cnt := test_tab.count;
            END;
            o/p
            
            Before deleting:2
            After deleting:1
            Edited by: Sg049 on Feb 20, 2013 2:57 PM
            • 3. Re: Deleting/Updating records from an object table in PL/SQL
              SamFisher
              SB,

              I have a scenario wherein I insert few records into a collection table. I'm gonna filter few records from collection table based on the filter.
              I want to delete the records that didn't match the filter. Right now, I'm inserting the records into a physical table and deleting from there. I do no want to use a physical table. Trying to avoid it.
              Would like to delete from collection itself.
              DELETE FROM TABLE(CAST(lv_attr_filter_tab as EDMS_CSPP_DISC_REQ_TAB_TYPE))
                                           WHERE NOT EXISTS (SELECT 1
                                                 FROM edms_disc_lines_stg edls
                                                 WHERE edls.req_id = edrg.request_id
                                                    AND edls.disc_line_id = edrg.discount_id
                                                    AND UPPER(edls.disc_status) IN ('ACTIVE');