3 Replies Latest reply: Feb 20, 2013 2:16 PM by SamFisher RSS

    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
            Sg049
            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');