Home » SQL & PL/SQL » SQL & PL/SQL » How to commit for every 10000 records in execute immediate. (Oracle 11g)
How to commit for every 10000 records in execute immediate. [message #577106] Tue, 12 February 2013 07:17 Go to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi Experts,

This procedure is deleting records from 10 tables.
I want to commit for every 10000 records for each table.
How can we do it in execute immediate.

CREATE OR REPLACE PROCEDURE TEST (
   p_schema_name           VARCHAR2,
   p_actual_schema_name    VARCHAR2,
   p_buid                  NUMBER
)
IS
   l_sql           VARCHAR2 (4000);
  CURSOR c_parents
   IS
      SELECT   mainn.seq_id,
               mainn.act_type,
               NVL (
                  (SELECT   inn.retention_period
                     FROM   APPS_GLOBAL.Category_Details inn
                    WHERE   inn.category_id = mainn.category_id
                            AND inn.buid = p_buid),
                  (SELECT   inn.retention_period
                     FROM   APPS_GLOBAL.Category_Details inn
                    WHERE   inn.category_id = mainn.category_id
                            AND inn.buid = 0)
               )
                  AS retention_period
        FROM   (SELECT   seq_id, act_type, cd.category_id
                  FROM   APPS_GLOBAL.gomt_master gm, APPS_GLOBAL.control_Details cd
                 WHERE   gm.category_id = cd.category_id
                         AND parent_id IS NULL
                         AND flag IS NULL
                         AND schema_name = p_schema_name
                         AND act_type = 'P') mainn;

   CURSOR c_tables (p_parent_seq NUMBER)
   IS
          SELECT   DISTINCT tablename,
                            parent_column,
                            child_column,
                            parent_id,
                            schema_name,
                            LEVEL,
                            PRIOR tablename par_tablename,
                            PRIOR parent_column par_parent_column
            FROM   APPS_GLOBAL.control_Details
            WHERE flag IS NULL
      START WITH   seq_id = p_parent_seq
      CONNECT BY   PRIOR seq_id = parent_id
        ORDER BY   LEVEL DESC;
BEGIN
  
   FOR parents IN c_parents
   LOOP
      FOR tabs IN c_tables (parents.seq_id)
      LOOP
         l_sql :=
               'DELETE FROM '
            || p_actual_schema_name
            || '.'
            || tabs.tablename
            || ' ch';

         IF tabs.parent_id IS NOT NULL
         THEN
            l_sql :=
                  l_sql
               || ' WHERE EXISTS (SELECT 1 FROM '
               || p_actual_schema_name
               || '.'
               || tabs.par_tablename
               || ' par'
               || ' WHERE par.'
               || tabs.parent_column
               || ' = '
               || ' ch.'
               || tabs.child_column
               || ' AND '
               || ' par.'
               || '"'
               || tabs.par_parent_column
               || '"'
               || '<=ADD_MONTHS(TRUNC(SYSDATE+1)-(1/(24*60*60)),-'
               || parents.retention_period
               || ')'
               || ')';
         ELSE
            l_sql :=
                  l_sql
               || ' WHERE '
               || ' ch.'
               || '"'
               || tabs.parent_column
               || '"'
               || '<=ADD_MONTHS(TRUNC(SYSDATE+1)-(1/(24*60*60)),-'
               || parents.retention_period
               || ')';
         END IF;
         EXECUTE IMMEDIATE l_sql;
         COMMIT;
      END LOOP;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      RAISE;
END TEST;
/

Please help me.
Thanks.
Re: How to commit for every 10000 records in execute immediate. [message #577110 is a reply to message #577106] Tue, 12 February 2013 08:05 Go to previous messageGo to next message
joy_division
Messages: 4485
Registered: February 2005
Location: East Coast USA
Senior Member
Before you do that, consider reading up on error ORA-01555
Re: How to commit for every 10000 records in execute immediate. [message #577111 is a reply to message #577110] Tue, 12 February 2013 08:10 Go to previous messageGo to next message
BlackSwan
Messages: 22481
Registered: January 2009
Senior Member
EXCEPTION
   WHEN OTHERS
   THEN
      RAISE;


what does above accomplish different than when it is 100% completely eliminated?
Re: How to commit for every 10000 records in execute immediate. [message #577250 is a reply to message #577111] Wed, 13 February 2013 13:30 Go to previous message
Bill B
Messages: 1066
Registered: December 2004
Senior Member
if you have a parent and a bunch of child tables why are you not using foreign keys with cascade delete?
Previous Topic: Need help in reducing the steps in query which is attached
Next Topic: ORA - 00972 : identifier too long
Goto Forum:
  


Current Time: Wed Jul 23 03:38:03 CDT 2014

Total time taken to generate the page: 0.11334 seconds