Home » SQL & PL/SQL » SQL & PL/SQL » please help me to implement this logic. (Oracle 11g)
please help me to implement this logic. [message #575850] Mon, 28 January 2013 07:36 Go to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
The data in the GOMT_TABLE as follows.

CATEGORY_ID	CATEGORY_NAME	ACT_TYPE
XX1	         OFFER		 A
XX2	         ORL		 A
XX3	         DAM		 A

The data in the CATEGORY_TABLE as follows.

CATEGORY_ID  RETENTION_PERIOD  BUID
XX1		24		0
XX2		24		0
XX1		44		5959


The data in the CONTROL_TABLE as follows.

CATEGORY_ID   SEQ_ID   TABLENAME		PARENT_COLUMN	CHILD_COLUMN	PARENT_ID   SCHEMA_NAME
XX1		1	GEDIS_OFFER_HEADER	LAST_UPDATE	LAST_UPDATE	 NULL	     APPS_XX
XX1		2	GEDIS_ORDER_BUILDS	OFFER_NO	OFFER_NO	  1	     APPS_XX
XX2		3	FAX_HEADER		UPDATE_DATE	UPDATE_DATE	 NULL	     ORL


My requirement is to delete data from parent and respective child tables.
My below code is working for one scenario and it is not working for another scenario.
I am passing schema_name and buid from the procedure.
If the variable p_buid(which is sending through the procedure) is equals to BUID in CATEGORY_TABLE it has to use the same buid
and take the RETENTION_PERIOD for that BUID else it should use another BUID i.e.0.

We never pass BUID "0" through the procedure.

For example If I pass the p_buid as "5959" and p_schema name as "APPS_XX" through the procedure
it has to generate the delete statements as below.

DELETE FROM GEDIS_ORDER_BUILDS ch WHERE EXISTS (SELECT 1 FROM GEDIS_OFFER_HEADER par WHERE par.OFFER_NO =  ch.OFFER_NO 
AND  (TRUNC(SYSDATE) - par.LAST_UPDATE_DATE_UTC) < 44);
DELETE FROM GEDIS_OFFER_HEADER ch WHERE (TRUNC(SYSDATE) - ch.LAST_UPDATE_DATE_UTC) < 44;


If BUID 5959 is not existed in the CATEGORY_TABLE it has to generate the delete statements as below.

DELETE FROM GEDIS_ORDER_BUILDS ch WHERE EXISTS (SELECT 1 FROM GEDIS_OFFER_HEADER par WHERE par.OFFER_NO =  ch.OFFER_NO 
AND  (TRUNC(SYSDATE) - par.LAST_UPDATE_DATE_UTC) < 24);
DELETE FROM GEDIS_OFFER_HEADER ch WHERE (TRUNC(SYSDATE) - ch.LAST_UPDATE_DATE_UTC) < 24;


If I pass some other BUID as 6789, if 6789 is not there in CATEGORY_TABLE table it should take BUID as "0".

My below code is working fine if it is only one entry for each category in the CATEGORY_TABLE table as below.

CATEGORY_ID RETENTION_PERIOD BUID
XX1 24 0

Please help me how to achieve this.

I tried using case in the first cursor but it's not working.

WHERE       gomt_table.category_id = category_table2.category_id
                AND category_table2.CATEGORY_ID = control_table2.category_id
               AND parent_id IS NULL
               AND schema_name=p_schema_name
               AND buid=CASE WHEN buid=p_buid THEN buid
               ELSE 0
               END;


I am posting the test case scripts also.

CREATE OR REPLACE PROCEDURE populate_target133
(p_schema_name VARCHAR2)
IS
   l_sql   VARCHAR2 (4000);

   CURSOR c_parents
   IS
      SELECT   control_table.category_id,
               category_name,
               seq_id,
               tablename,
               parent_column,
               child_column,
               parent_id,
               schema_name,
               act_type,
               retention_period,
               buid
        FROM   gomt_table, category_table, control_table
       WHERE       gomt_table.category_id = category_table.category_id
               AND CATEGORY_TABLE.CATEGORY_ID = control_table.category_id
               AND parent_id IS NULL;

   CURSOR c_tables (p_parent_seq NUMBER)
   IS
          SELECT   control_table.category_id,
                   category_name,
                   seq_id,
                   tablename,
                   parent_column,
                   child_column,
                   parent_id,
                   schema_name,
                   act_type,
                   retention_period,
                   buid,
                   prior tablename par_tablename,
                   prior parent_column par_parent_column
            FROM   gomt_table, category_table, control_table
           WHERE   gomt_table.category_id = category_table.category_id
                   AND CATEGORY_TABLE.CATEGORY_ID = control_table.category_id
      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
      
      IF tabs.act_type = 'A' THEN
         l_sql :=
               'DELETE FROM '
            || tabs.tablename
            || ' ch';

         IF tabs.parent_id IS NOT NULL
         THEN
            l_sql :=
                  l_sql
               || ' WHERE EXISTS (SELECT 1 FROM '
               || tabs.par_tablename
               || ' par'
               || ' WHERE par.'
               || tabs.parent_column
               || ' = '
               || ' ch.'
               || tabs.child_column
               || ' AND '
               || ' (TRUNC(SYSDATE) - par.'
               || tabs.par_parent_column
               || ') < '
               || tabs.retention_period
               || ')';
         ELSE
            l_sql :=
                  l_sql
               || ' WHERE (TRUNC(SYSDATE) - ch.'
               || tabs.parent_column
               || ') < '
               || tabs.retention_period;
         END IF;

         --execute immediate l_sql;
         DBMS_OUTPUT.put_line (l_sql);
      END IF;

      END LOOP;                                          
   END LOOP;                                             

 -- commit;

EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error: ' || SQLERRM);
END populate_target133;
/


CREATE TABLE gomt_table
(
   Category_Id     VARCHAR2 (10),
   Category_Name   VARCHAR2 (50),
   act_type        CHAR (1),
   );


INSERT INTO gomt_table VALUES('XX1','OFFER','A');		
INSERT INTO gomt_table VALUES('XX2','ORL','A');		
INSERT INTO gomt_table VALUES('XX3','DAM','A');	

CREATE TABLE Category_Table
(
   Category_Id        VARCHAR2 (10),
   Retention_period   NUMBER,
   Buid               NUMBER,
);

INSERT INTO category_table VALUES('XX1',24,0);
INSERT INTO category_table VALUES('XX2',24,0);
INSERT INTO category_table VALUES('XX1',44,5959);

CREATE TABLE control_table
(
   category_id        VARCHAR2(30),
   seq_id             NUMBER,
   tablename         VARCHAR2(30),
   parent_column   VARCHAR2(30),
   child_column   VARCHAR2(30),
   parent_id          NUMBER,
   schema_name        VARCHAR2(30)
   );

INSERT INTO control_table values('XX1',1,'GEDIS_OFFER_HEADER','LAST_UPDATE','LAST_UPDATE',NULL,'APPS_XX');
INSERT INTO control_table values('XX1',2,'GEDIS_ORDER_BUILDS','OFFER_NO','OFFER_NO',1,'APPS_XX');
INSERT INTO control_table values('XX2',3,'FAX_HEADER','UPDATE_DATE','UPDATE_DATE',NULL,'ORL');

Thanks.
Re: please help me to implement this logic. [message #575896 is a reply to message #575850] Mon, 28 January 2013 23:20 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Could any body please help me.
How to implement my new requirement in my existed code.

Thanks.
Re: please help me to implement this logic. [message #575918 is a reply to message #575850] Tue, 29 January 2013 03:36 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
CREATE OR REPLACE PROCEDURE populate_target133
(p_schema_name VARCHAR2,p_buid number)
IS
   l_sql   VARCHAR2 (4000);
   
l_count integer:=0;
a_buid integer:=0;
   CURSOR c_parents(p_schema_name VARCHAR2,a_buid number)
   IS
      SELECT   control_table.category_id,
               category_name,
               seq_id,
               tablename,
               parent_column,
               child_column,
               parent_id,
               schema_name,
               act_type,
               retention_period,
               buid
        FROM   gomt_table, category_table, control_table
       WHERE       gomt_table.category_id = category_table.category_id
               AND CATEGORY_TABLE.CATEGORY_ID = control_table.category_id
               AND parent_id IS NULL AND schema_name=p_schema_name
               AND buid=a_buid;

   CURSOR c_tables (p_parent_seq NUMBER)
   IS
          SELECT   control_table.category_id,
                   category_name,
                   seq_id,
                   tablename,
                   parent_column,
                   child_column,
                   parent_id,
                   schema_name,
                   act_type,
                   retention_period,
                   buid,
                   prior tablename par_tablename,
                   prior parent_column par_parent_column
            FROM   gomt_table, category_table, control_table
           WHERE   gomt_table.category_id = category_table.category_id
                   AND CATEGORY_TABLE.CATEGORY_ID = control_table.category_id
      START WITH   seq_id = p_parent_seq
      CONNECT BY   PRIOR seq_id = parent_id
        ORDER BY   LEVEL DESC;
BEGIN

select count(*) into l_count from CATEGORY_TABLE where buid=p_buid;
if l_count=0 then
a_buid:=0;
else
a_buid:=p_buid;
end if;


   FOR parents IN c_parents(p_schema_name,a_buid)
   LOOP
      FOR tabs IN c_tables (parents.seq_id)
      LOOP
      
      --dbms_output.put_line('BUID VALUE='||parents.buid||'--- retention='||parents.RETENTION_PERIOD);
      
      IF tabs.act_type = 'A' THEN
         l_sql :=
               'DELETE FROM '
            || tabs.tablename
            || ' ch';

         IF tabs.parent_id IS NOT NULL
         THEN
            l_sql :=
                  l_sql
               || ' WHERE EXISTS (SELECT 1 FROM '
               || tabs.par_tablename
               || ' par'
               || ' WHERE par.'
               || tabs.parent_column
               || ' = '
               || ' ch.'
               || tabs.child_column
               || ' AND '
               || ' (TRUNC(SYSDATE) - par.'
               || tabs.par_parent_column
               || ') < '
               || tabs.retention_period
               || ')';
         ELSE
            l_sql :=
                  l_sql
               || ' WHERE (TRUNC(SYSDATE) - ch.'
               || tabs.parent_column
               || ') < '
               || tabs.retention_period;
         END IF;

         --execute immediate l_sql;
         DBMS_OUTPUT.put_line (l_sql);
      END IF;

      END LOOP;                                          
   END LOOP;                                             

 -- commit;

EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error: ' || SQLERRM);
END populate_target133;
/


buid as 0
SQL> exec populate_target133('APPS_XX',0);
BUID VALUE=0--- retention=24
BUID VALUE=0--- retention=24
BUID VALUE=0--- retention=24
BUID VALUE=0--- retention=24
BUID VALUE=0--- retention=24
BUID VALUE=0--- retention=24

PL/SQL procedure successfully completed.


buid as 5959
SQL> exec populate_target133('APPS_XX',5959);
BUID VALUE=5959--- retention=44
BUID VALUE=5959--- retention=44
BUID VALUE=5959--- retention=44
BUID VALUE=5959--- retention=44
BUID VALUE=5959--- retention=44
BUID VALUE=5959--- retention=44

PL/SQL procedure successfully completed.

buid as random
SQL> exec populate_target133('APPS_XX',87);
BUID VALUE=0--- retention=24
BUID VALUE=0--- retention=24
BUID VALUE=0--- retention=24
BUID VALUE=0--- retention=24
BUID VALUE=0--- retention=24
BUID VALUE=0--- retention=24

Re: please help me to implement this logic. [message #575923 is a reply to message #575918] Tue, 29 January 2013 05:49 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Thanks you very much for your reply.
However it's not giving the expected result.
If I execute the procedure

exec populate_target133('APPS_XX',87)
It is taking 24 and as well as 44 and it's generating duplicate statements also.
It's generated the below delete statements.

DELETE FROM GEDIS_ORDER_BUILDS ch WHERE EXISTS (SELECT 1 FROM GEDIS_OFFER_HEADER par WHERE par.OFFER_NO =  ch.OFFER_NO AND  (TRUNC(SYSDATE) - par.LAST_UPDATE_DATE_UTC) < 24)
DELETE FROM GEDIS_ORDER_BUILDS ch WHERE EXISTS (SELECT 1 FROM GEDIS_OFFER_HEADER par WHERE par.OFFER_NO =  ch.OFFER_NO AND  (TRUNC(SYSDATE) - par.LAST_UPDATE_DATE_UTC) < 44)
DELETE FROM GEDIS_ORDER_BUILDS ch WHERE EXISTS (SELECT 1 FROM GEDIS_OFFER_HEADER par WHERE par.OFFER_NO =  ch.OFFER_NO AND  (TRUNC(SYSDATE) - par.LAST_UPDATE_DATE_UTC) < 44)
DELETE FROM GEDIS_ORDER_BUILDS ch WHERE EXISTS (SELECT 1 FROM GEDIS_OFFER_HEADER par WHERE par.OFFER_NO =  ch.OFFER_NO AND  (TRUNC(SYSDATE) - par.LAST_UPDATE_DATE_UTC) < 24)
DELETE FROM GEDIS_OFFER_HEADER ch WHERE (TRUNC(SYSDATE) - ch.LAST_UPDATE_DATE_UTC) < 24
DELETE FROM GEDIS_OFFER_HEADER ch WHERE (TRUNC(SYSDATE) - ch.LAST_UPDATE_DATE_UTC) < 44


exec populate_target133('APPS_XX',5959)
It is taking 24 and as well as 44 and it's generating duplicate statements also.
It's genarated the below delete statements.
DELETE FROM GEDIS_ORDER_BUILDS ch WHERE EXISTS (SELECT 1 FROM GEDIS_OFFER_HEADER par WHERE par.OFFER_NO =  ch.OFFER_NO AND  (TRUNC(SYSDATE) - par.LAST_UPDATE_DATE_UTC) < 24)
DELETE FROM GEDIS_ORDER_BUILDS ch WHERE EXISTS (SELECT 1 FROM GEDIS_OFFER_HEADER par WHERE par.OFFER_NO =  ch.OFFER_NO AND  (TRUNC(SYSDATE) - par.LAST_UPDATE_DATE_UTC) < 44)
DELETE FROM GEDIS_ORDER_BUILDS ch WHERE EXISTS (SELECT 1 FROM GEDIS_OFFER_HEADER par WHERE par.OFFER_NO =  ch.OFFER_NO AND  (TRUNC(SYSDATE) - par.LAST_UPDATE_DATE_UTC) < 44)
DELETE FROM GEDIS_ORDER_BUILDS ch WHERE EXISTS (SELECT 1 FROM GEDIS_OFFER_HEADER par WHERE par.OFFER_NO =  ch.OFFER_NO AND  (TRUNC(SYSDATE) - par.LAST_UPDATE_DATE_UTC) < 24)
DELETE FROM GEDIS_OFFER_HEADER ch WHERE (TRUNC(SYSDATE) - ch.LAST_UPDATE_DATE_UTC) < 24
DELETE FROM GEDIS_OFFER_HEADER ch WHERE (TRUNC(SYSDATE) - ch.LAST_UPDATE_DATE_UTC) < 44


exec populate_target133('APPS_XX',0)
It is taking 24 and as well as 44 and also it's generating duplicate statements.
It's genarated the below delete statements.
DELETE FROM GEDIS_ORDER_BUILDS ch WHERE EXISTS (SELECT 1 FROM GEDIS_OFFER_HEADER par WHERE par.OFFER_NO =  ch.OFFER_NO AND  (TRUNC(SYSDATE) - par.LAST_UPDATE_DATE_UTC) < 24)
DELETE FROM GEDIS_ORDER_BUILDS ch WHERE EXISTS (SELECT 1 FROM GEDIS_OFFER_HEADER par WHERE par.OFFER_NO =  ch.OFFER_NO AND  (TRUNC(SYSDATE) - par.LAST_UPDATE_DATE_UTC) < 44)
DELETE FROM GEDIS_ORDER_BUILDS ch WHERE EXISTS (SELECT 1 FROM GEDIS_OFFER_HEADER par WHERE par.OFFER_NO =  ch.OFFER_NO AND  (TRUNC(SYSDATE) - par.LAST_UPDATE_DATE_UTC) < 44)
DELETE FROM GEDIS_ORDER_BUILDS ch WHERE EXISTS (SELECT 1 FROM GEDIS_OFFER_HEADER par WHERE par.OFFER_NO =  ch.OFFER_NO AND  (TRUNC(SYSDATE) - par.LAST_UPDATE_DATE_UTC) < 24)
DELETE FROM GEDIS_OFFER_HEADER ch WHERE (TRUNC(SYSDATE) - ch.LAST_UPDATE_DATE_UTC) < 24
DELETE FROM GEDIS_OFFER_HEADER ch WHERE (TRUNC(SYSDATE) - ch.LAST_UPDATE_DATE_UTC) < 44


Please help me.
Your Quick response is appreciated.

THANKS.
Re: please help me to implement this logic. [message #575927 is a reply to message #575923] Tue, 29 January 2013 07:19 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
CREATE OR REPLACE PROCEDURE populate_target133
(p_schema_name VARCHAR2,p_buid number)
IS
   l_sql   VARCHAR2 (4000);
   
l_count integer:=0;
a_buid integer:=0;
   CURSOR c_parents(p_schema_name VARCHAR2,a_buid number)
   IS
      SELECT   control_table.category_id,
               category_name,
               seq_id,
               tablename,
               parent_column,
               child_column,
               parent_id,
               schema_name,
               act_type,
               retention_period,
               buid
        FROM   gomt_table, category_table, control_table
       WHERE       gomt_table.category_id = category_table.category_id
               AND CATEGORY_TABLE.CATEGORY_ID = control_table.category_id
               AND parent_id IS NULL AND schema_name=p_schema_name
               AND buid=a_buid;

   CURSOR c_tables (p_parent_seq NUMBER)
   IS
          SELECT   control_table.category_id,
                   category_name,
                   seq_id,
                   tablename,
                   parent_column,
                   child_column,
                   parent_id,
                   schema_name,
                   act_type,
                   retention_period,
                   buid,
                   prior tablename par_tablename,
                   prior parent_column par_parent_column
            FROM   gomt_table, category_table, control_table
           WHERE   gomt_table.category_id = category_table.category_id
                   AND CATEGORY_TABLE.CATEGORY_ID = control_table.category_id and buid=a_buid
      START WITH   seq_id = p_parent_seq
      CONNECT BY   PRIOR seq_id = parent_id
        ORDER BY   LEVEL DESC;
BEGIN

select count(*) into l_count from CATEGORY_TABLE where buid=p_buid;
if l_count=0 then
a_buid:=0;
else
a_buid:=p_buid;
end if;


   FOR parents IN c_parents(p_schema_name,a_buid)
   LOOP
      FOR tabs IN c_tables (parents.seq_id)
      LOOP
      
      --dbms_output.put_line('BUID VALUE='||parents.buid||'--- retention='||parents.RETENTION_PERIOD);
      
      IF tabs.act_type = 'A' THEN
         l_sql :=
               'DELETE FROM '
            || tabs.tablename
            || ' ch';

         IF tabs.parent_id IS NOT NULL
         THEN
            l_sql :=
                  l_sql
               || ' WHERE EXISTS (SELECT 1 FROM '
               || tabs.par_tablename
               || ' par'
               || ' WHERE par.'
               || tabs.parent_column
               || ' = '
               || ' ch.'
               || tabs.child_column
               || ' AND '
               || ' (TRUNC(SYSDATE) - par.'
               || tabs.par_parent_column
               || ') < '
               || tabs.retention_period
               || ')';
         ELSE
            l_sql :=
                  l_sql
               || ' WHERE (TRUNC(SYSDATE) - ch.'
               || tabs.parent_column
               || ') < '
               || tabs.retention_period;
         END IF;

         --execute immediate l_sql;
         DBMS_OUTPUT.put_line (l_sql);
      END IF;

      END LOOP;                                          
   END LOOP;                                             

 -- commit;

EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error: ' || SQLERRM);
END populate_target133;
/


exec populate_target133('APPS_XX',87)
SQL> exec populate_target133('APPS_XX',87);
DELETE FROM GEDIS_ORDER_BUILDS ch WHERE EXISTS (SELECT 1 FROM GEDIS_OFFER_HEADER

par WHERE par.OFFER_NO =  ch.OFFER_NO AND  (TRUNC(SYSDATE) - par.LAST_UPDATE) <
24)
DELETE FROM GEDIS_ORDER_BUILDS ch WHERE EXISTS (SELECT 1 FROM GEDIS_OFFER_HEADER

par WHERE par.OFFER_NO =  ch.OFFER_NO AND  (TRUNC(SYSDATE) - par.LAST_UPDATE) <
24)
DELETE FROM GEDIS_OFFER_HEADER ch WHERE (TRUNC(SYSDATE) - ch.LAST_UPDATE) < 24

PL/SQL procedure successfully completed.


exec populate_target133('APPS_XX',5959)
SQL> exec populate_target133('APPS_XX',5959);
DELETE FROM GEDIS_ORDER_BUILDS ch WHERE EXISTS (SELECT 1 FROM GEDIS_OFFER_HEADER

par WHERE par.OFFER_NO =  ch.OFFER_NO AND  (TRUNC(SYSDATE) - par.LAST_UPDATE) <
44)
DELETE FROM GEDIS_ORDER_BUILDS ch WHERE EXISTS (SELECT 1 FROM GEDIS_OFFER_HEADER

par WHERE par.OFFER_NO =  ch.OFFER_NO AND  (TRUNC(SYSDATE) - par.LAST_UPDATE) <
44)
DELETE FROM GEDIS_OFFER_HEADER ch WHERE (TRUNC(SYSDATE) - ch.LAST_UPDATE) < 44

PL/SQL procedure successfully completed.


exec populate_target133('APPS_XX',0)
SQL> exec populate_target133('APPS_XX',0);
DELETE FROM GEDIS_ORDER_BUILDS ch WHERE EXISTS (SELECT 1 FROM GEDIS_OFFER_HEADER

par WHERE par.OFFER_NO =  ch.OFFER_NO AND  (TRUNC(SYSDATE) - par.LAST_UPDATE) <
24)
DELETE FROM GEDIS_ORDER_BUILDS ch WHERE EXISTS (SELECT 1 FROM GEDIS_OFFER_HEADER

par WHERE par.OFFER_NO =  ch.OFFER_NO AND  (TRUNC(SYSDATE) - par.LAST_UPDATE) <
24)
DELETE FROM GEDIS_OFFER_HEADER ch WHERE (TRUNC(SYSDATE) - ch.LAST_UPDATE) < 24

PL/SQL procedure successfully completed.

Re: please help me to implement this logic. [message #575938 is a reply to message #575927] Tue, 29 January 2013 10:32 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Thank you very much for your reply .
It's generating correct delete statements,however as I told to you in my previous mail it's generating duplicate delete statements.

exec populate_target133('APPS_XX',5959);

DELETE FROM GEDIS_ORDER_BUILDS ch WHERE EXISTS (SELECT 1 FROM GEDIS_OFFER_HEADER

par WHERE par.OFFER_NO =  ch.OFFER_NO AND  (TRUNC(SYSDATE) - par.LAST_UPDATE) <
44)
DELETE FROM GEDIS_ORDER_BUILDS ch WHERE EXISTS (SELECT 1 FROM GEDIS_OFFER_HEADER

par WHERE par.OFFER_NO =  ch.OFFER_NO AND  (TRUNC(SYSDATE) - par.LAST_UPDATE) <
44)
DELETE FROM GEDIS_OFFER_HEADER ch WHERE (TRUNC(SYSDATE) - ch.LAST_UPDATE) < 44


It should genarate two delete statement only.

To avoid duplication I have used DISTINCT clause in my code,but it's giving an error.

SELECT  DISTINCT control_table.category_id,
                   category_name,
                   seq_id,
                   tablename,
                   parent_column,
                   child_column,
                   parent_id,
                   schema_name,
                   act_type,
                   retention_period,
                   buid,
                   prior tablename par_tablename,
                   prior parent_column par_parent_column
            FROM   gomt_table, category_table, control_table
           WHERE   gomt_table.category_id = category_table.category_id
                   AND CATEGORY_TABLE.CATEGORY_ID = control_table.category_id and buid=a_buid
      START WITH   seq_id = p_parent_seq
      CONNECT BY   PRIOR seq_id = parent_id
        ORDER BY   LEVEL DESC;

Please help me how to avoid duplicate delete statements.

Thanks.
Re: please help me to implement this logic. [message #575960 is a reply to message #575938] Tue, 29 January 2013 16:01 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
are you going to tell us what the error is?
Re: please help me to implement this logic. [message #575972 is a reply to message #575960] Tue, 29 January 2013 23:51 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
use below cursor

CURSOR c_tables (p_parent_seq NUMBER)
   IS
          SELECT   distinct control_table.category_id,
                   category_name,
                   seq_id,
                   tablename,
                   parent_column,
                   child_column,
                   parent_id,
                   schema_name,
                   act_type,
                   retention_period,
                   buid,
                   prior tablename par_tablename,
                   prior parent_column par_parent_column
            FROM   gomt_table, category_table, control_table
           WHERE   gomt_table.category_id = category_table.category_id
                   AND CATEGORY_TABLE.CATEGORY_ID = control_table.category_id and buid=a_buid
      START WITH   seq_id = p_parent_seq
      CONNECT BY   PRIOR seq_id = parent_id;


SQL> exec populate_target133('APPS_XX',87);
DELETE FROM GEDIS_ORDER_BUILDS ch WHERE EXISTS (SELECT 1 FROM GEDIS_OFFER_HEADER

par WHERE par.OFFER_NO =  ch.OFFER_NO AND  (TRUNC(SYSDATE) - par.LAST_UPDATE) <
24)
DELETE FROM GEDIS_OFFER_HEADER ch WHERE (TRUNC(SYSDATE) - ch.LAST_UPDATE) < 24

PL/SQL procedure successfully completed.
Re: please help me to implement this logic. [message #576432 is a reply to message #575972] Mon, 04 February 2013 23:46 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
I have inserted below mentioned some new records into my tables.
Now this logic is not working.

INSERT INTO gomt_table VALUES('XX4','CUSTOMER','A');
INSERT INTO gomt_table VALUES('XX5','AUDITLOGS','A');

INSERT INTO category_table VALUES('XX4',50,0);
INSERT INTO category_table VALUES('XX4',70,202);
INSERT INTO category_table VALUES('XX4',90,6969);
INSERT INTO category_table VALUES('XX5',100,0);

INSERT INTO control_table2 values('XX4',4,'GEDIS_NOTES','LASTUPDATEDDATE','LASTUPDATEDDATE',NULL,'APPS_XX',NULL);
INSERT INTO control_table values('XX5',5,'GEDIS_MASSUPDATE','LASTUPDATEDDATE','LASTUPDATEDDATE',NULL,'APPS_XX',NULL);
INSERT INTO control_table2 values('XX5',6,'GEDIS_MASSUPDATE_REPORTS','LASTUPDATEDDATE','LASTUPDATEDDATE',NULL,'APPS_XX',NULL);


If I execute the procedure as

exec populate_target133('APPS_XX',5959)
It's not taking other BUID's which are having 0 (or) specific BUIDs.

My expectation is if particular BUID is there then it has to take that buid and for reaming it has to take "0".

In this case it has to generate the delete statements as below.


DELETE FROM GEDIS_ORDER_BUILDS ch WHERE EXISTS (SELECT 1 FROM GEDIS_OFFER_HEADER
par WHERE par.OFFER_NO =  ch.OFFER_NO AND  (TRUNC(SYSDATE) - par.LAST_UPDATE) <44)
DELETE FROM GEDIS_OFFER_HEADER ch WHERE (TRUNC(SYSDATE) - ch.LAST_UPDATE) < 44
DELETE FROM GEDIS_NOTES ch WHERE (TRUNC(SYSDATE) - ch.LASTUPDATEDDATE) < 50
DELETE FROM GEDIS_MASSUPDATE ch WHERE (TRUNC(SYSDATE) - ch.LASTUPDATEDDATE) < 100
DELETE FROM GEDIS_MASSUPDATE_REPORTS ch WHERE (TRUNC(SYSDATE) - ch.LASTUPDATEDDATE) < 100



If I execute the procedure as

exec populate_target133('APPS_XX',202)

In this case it has to generate the delete statements as below.

DELETE FROM GEDIS_ORDER_BUILDS ch WHERE EXISTS (SELECT 1 FROM GEDIS_OFFER_HEADER
par WHERE par.OFFER_NO =  ch.OFFER_NO AND  (TRUNC(SYSDATE) - par.LAST_UPDATE) <24)
DELETE FROM GEDIS_OFFER_HEADER ch WHERE (TRUNC(SYSDATE) - ch.LAST_UPDATE) < 24
DELETE FROM GEDIS_NOTES ch WHERE (TRUNC(SYSDATE) - ch.LASTUPDATEDDATE) < 70
DELETE FROM GEDIS_MASSUPDATE ch WHERE (TRUNC(SYSDATE) - ch.LASTUPDATEDDATE) < 100
DELETE FROM GEDIS_MASSUPDATE_REPORTS ch WHERE (TRUNC(SYSDATE) - ch.LASTUPDATEDDATE) < 100


Please help me.

Thanks.
Re: please help me to implement this logic. [message #576438 is a reply to message #576432] Tue, 05 February 2013 01:18 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Ajay,

Have you used the cursor mentioned in my previous post?
SQL> exec populate_target133('APPS_XX',5959)
DELETE FROM GEDIS_OFFER_HEADER ch WHERE (TRUNC(SYSDATE) - ch.LAST_UPDATE) < 44
DELETE FROM GEDIS_ORDER_BUILDS ch WHERE EXISTS (SELECT 1 FROM GEDIS_OFFER_HEADER

par WHERE par.OFFER_NO =  ch.OFFER_NO AND  (TRUNC(SYSDATE) - par.LAST_UPDATE) <
44)

PL/SQL procedure successfully completed.


SQL> exec populate_target133('APPS_XX',202)
DELETE FROM GEDIS_NOTES ch WHERE (TRUNC(SYSDATE) - ch.LASTUPDATEDDATE) < 70

PL/SQL procedure successfully completed.

Well this is not the expected o/p
Check your cursor conditions it works fine for first but fails for second, Its your business logic, i have no say
Re: please help me to implement this logic. [message #576453 is a reply to message #576432] Tue, 05 February 2013 03:22 Go to previous message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Thanks for your response.

Yes I have used your previous cursor.

My requirement is as below

Please help me.

exec populate_target133('APPS_XX',5959)
It's not taking other BUID's which are having 0 (or) specific BUIDs.

My expectation is if particular BUID is there then it has to take that buid and for reaming it has to take "0".

In this case it has to generate the delete statements as below.


DELETE FROM GEDIS_ORDER_BUILDS ch WHERE EXISTS (SELECT 1 FROM GEDIS_OFFER_HEADER
par WHERE par.OFFER_NO =  ch.OFFER_NO AND  (TRUNC(SYSDATE) - par.LAST_UPDATE) <44)
DELETE FROM GEDIS_OFFER_HEADER ch WHERE (TRUNC(SYSDATE) - ch.LAST_UPDATE) < 44
DELETE FROM GEDIS_NOTES ch WHERE (TRUNC(SYSDATE) - ch.LASTUPDATEDDATE) < 50
DELETE FROM GEDIS_MASSUPDATE ch WHERE (TRUNC(SYSDATE) - ch.LASTUPDATEDDATE) < 100
DELETE FROM GEDIS_MASSUPDATE_REPORTS ch WHERE (TRUNC(SYSDATE) - ch.LASTUPDATEDDATE) < 100


If I execute the procedure as

exec populate_target133('APPS_XX',202)

In this case it has to generate the delete statements as below.

DELETE FROM GEDIS_ORDER_BUILDS ch WHERE EXISTS (SELECT 1 FROM GEDIS_OFFER_HEADER
par WHERE par.OFFER_NO =  ch.OFFER_NO AND  (TRUNC(SYSDATE) - par.LAST_UPDATE) <24)
DELETE FROM GEDIS_OFFER_HEADER ch WHERE (TRUNC(SYSDATE) - ch.LAST_UPDATE) < 24
DELETE FROM GEDIS_NOTES ch WHERE (TRUNC(SYSDATE) - ch.LASTUPDATEDDATE) < 70
DELETE FROM GEDIS_MASSUPDATE ch WHERE (TRUNC(SYSDATE) - ch.LASTUPDATEDDATE) < 100
DELETE FROM GEDIS_MASSUPDATE_REPORTS ch WHERE (TRUNC(SYSDATE) - ch.LASTUPDATEDDATE) < 100



Thanks
Previous Topic: ORACLE - SYS.DBMS_JOB causes error "ORA-01461 can bind a LONG value only for insert into a LONG colu
Next Topic: Cursor not fetching special character
Goto Forum:
  


Current Time: Fri Sep 19 21:48:22 CDT 2014

Total time taken to generate the page: 0.07062 seconds