1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

IS There any date function is present in the oracle to get the 2 years old date

Discussion in 'Oracle Apps Technical' started by Venkiibm, Jul 7, 2014.

  1. Venkiibm

    Venkiibm Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Hi Guys,

    Is there any function to get the 2 years old date from today, I have to pass this date to one filed in the select statement to get the data from the table which are older than 2 years

    SELECT * FROM osd_appt
    WHERE UNLD_TS >= 2 YEARS FROM TODAY DATE( HERE I HAVE TO PASS 2 YEARS OLD DATE FROM TODAY)

    Regards
    Venkat
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Venkiibm likes this.
  3. Venkiibm

    Venkiibm Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Hi Guys,
    I have written below stored procedure for purging the 2 years old data from different tables and client is looking as optimized stored procedure . could you please let me know is there any changes required to optimize the below stored procedure.

    create or replace
    PROCEDURE purge_Logistic_OSD_Tbls(i_purge_ts IN DATE,
    o_err_num OUT NOCOPY NUMBER,
    o_err_msg OUT NOCOPY CHAR,
    o_err_osp_name OUT NOCOPY CHAR)
    IS
    ModRecs INTEGER := 0;
    DeletedRecs INTEGER := 0;
    CommitCount INTEGER := 0;
    CommitAfterRecs INTEGER := 0;
    TYPE PurgeNbr IS TABLE OF NUMBER (15) INDEX BY BINARY_INTEGER;
    RawOsdAppt PurgeNbr;
    RawOsdfb PurgeNbr;
    RawOsdRcpt PurgeNbr;
    RawOsdDM PurgeNbr;
    RawOsdCom PurgeNbr;
    RetRows1 BINARY_INTEGER := 0;
    RetRows2 BINARY_INTEGER := 0;
    RetRows3 BINARY_INTEGER := 0;
    RetRows4 BINARY_INTEGER := 0;
    RetRows5 BINARY_INTEGER := 0; BEGIN
    begin

    o_err_num := 1;
    DELETE FROM LOGISTIC_OSD_TMP;
    INSERT INTO LOGISTIC_OSD_TMP
    (SELECT AP.OSD_APPT_NBR,FB.OSD_FB_NBR,RCPT.OSD_RCPT_NBR,dm.CNTR_NBR
    FROM OSD_FB FB ,OSD_RCPT RCPT,OSD_DAMAGED_FLO_CNTR DM,OSD_APPT AP
    WHERE FB.OSD_FB_NBR = RCPT.OSD_FB_NBR AND
    RCPT.OSD_RCPT_NBR = DM.OSD_RCPT_NBR
    and exists (SELECT AP.OSD_APPT_NBR
    FROM OSD_APPT AP,OSD_FB FB
    WHERE AP.OSD_APPT_NBR = FB.OSD_APPT_NBR
    and ap.UNLD_TS >= ( SYSDATE - INTERVAL '2' YEAR)));
    COMMIT;
    EXCEPTION
    WHEN OTHERS
    THEN
    o_err_num := SQLCODE;
    o_err_msg := SUBSTR (SQLERRM, 1, 100);
    o_err_osp_name := 'purge_Logistic_OSD_Tbls';
    ROLLBACK;
    RETURN;
    END;

    BEGIN
    CommitAfterRecs := 1000;

    o_err_num := 1;
    FOR ApptRecs IN (SELECT tmp_osd_appt_nbr,
    tmp_osd_fb_nbr,tmp_osd_rcpt_nbr,
    tmp_cntr_nbr FROM LOGISTIC_OSD_TMP)
    LOOP
    RetRows1 := RetRows1 + 1;
    RetRows2 := RetRows2 + 1;
    RetRows3 := RetRows3 + 1;
    RetRows4 := RetRows4 + 1;
    RetRows5 := RetRows5 + 1;
    RawOsdAppt (retrows1) := ApptRecs.tmp_osd_appt_nbr;
    RawOsdfb (RetRows2) := ApptRecs.tmp_osd_fb_nbr;
    RawOsdRcpt (RetRows3) := ApptRecs.tmp_osd_rcpt_nbr;
    RawOsdDM (RetRows4) := ApptRecs.tmp_cntr_nbr;
    RawOsdCom (RetRows5) := ApptRecs.tmp_osd_appt_nbr;
    END LOOP;
    /* Purging OSD_APPT, OSD_FB, OSD_RCPT, OSD_DAMAGED_CNTR and OSD_COMMENTS table */
    /* Delete all records from above Table which were
    created before sysdate - 2 years */

    ModRecs := 0;
    DeletedRecs := 0;
    CommitCount := 0;
    RetRows1 :=RawOsdAppt.FIRST;
    RetRows2 :=RawOsdFb.FIRST;
    RetRows3 :=RawOsdRcpt.FIRST;
    RetRows4 :=RawOsdDM.FIRST;
    RetRows5 :=RawOsdCom.FIRST;
    WHILE retrows4 IS NOT NULL
    LOOP
    DELETE osd_damaged_flo_cntr
    WHERE cntr_nbr = rawosddm(retrows4);

    ModRecs := SQL%ROWCOUNT + ModRecs;
    DeletedRecs := SQL%ROWCOUNT + DeletedRecs;

    /* Commit after deleting 1000 records */
    IF (DeletedRecs > CommitAfterRecs)
    THEN
    COMMIT;
    DeletedRecs := 0;
    CommitCount := CommitCount + 1;
    END IF;

    retrows4 := rawosddm.NEXT (RetRows4);
    END LOOP;

    COMMIT;
    CommitCount := CommitCount + 1;
    /* Display CommitCount */
    DBMS_OUTPUT.PUT_LINE ('OSD_FB recs purged = ' || NVL (ModRecs, 0));
    EXCEPTION
    WHEN OTHERS
    THEN
    o_err_num := SQLCODE;
    o_err_msg := SUBSTR (SQLERRM, 1, 100);
    o_err_osp_name := 'purge_Logistic_OSD_Tbls';
    ROLLBACK;
    END;
    BEGIN
    /* Purging OSD_APPT, OSD_FB, OSD_RCPT, OSD_DAMAGED_CNTR and OSD_COMMENTS table */
    /* Delete all records from above Table which were
    created before sysdate - 2 years */
    --venkat
    ModRecs := 0;
    DeletedRecs := 0;
    CommitCount := 0;
    RetRows3 :=RawOsdRcpt.FIRST;
    WHILE retrows3 IS NOT NULL
    LOOP
    DELETE osd_rcpt
    WHERE osd_rcpt_nbr = rawosdRcpt(retrows3);

    ModRecs := SQL%ROWCOUNT + ModRecs;
    DeletedRecs := SQL%ROWCOUNT + DeletedRecs;

    /* Commit after deleting 1000 records */
    IF (DeletedRecs > CommitAfterRecs)
    THEN
    COMMIT;
    DeletedRecs := 0;
    CommitCount := CommitCount + 1;
    END IF;

    retrows3 := rawosdRcpt.NEXT (RetRows3);
    END LOOP;

    COMMIT;
    CommitCount := CommitCount + 1;
    /* Display CommitCount */
    DBMS_OUTPUT.PUT_LINE ('OSD_Rcpt recs purged = ' || NVL (ModRecs, 0));
    EXCEPTION
    WHEN OTHERS
    THEN
    o_err_num := SQLCODE;
    o_err_msg := SUBSTR (SQLERRM, 1, 100);
    o_err_osp_name := 'purge_Logistic_OSD_Tbls';
    ROLLBACK;
    -- return;
    END;
    begin
    /* Purging OSD_FB table Delete all records from above Table which were*/
    /* created before sysdate - 2 years */
    ModRecs := 0;
    DeletedRecs := 0;
    CommitCount := 0;
    RetRows2 :=RawOsdFb.FIRST;
    WHILE retrows2 IS NOT NULL
    LOOP
    DELETE osd_fb
    WHERE osd_fb_nbr = rawosdFb(retrows2);

    ModRecs := SQL%ROWCOUNT + ModRecs;
    DeletedRecs := SQL%ROWCOUNT + DeletedRecs;

    /* Commit after deleting 1000 records */
    IF (DeletedRecs > CommitAfterRecs)
    THEN
    COMMIT;
    DeletedRecs := 0;
    CommitCount := CommitCount + 1;
    END IF;

    retrows2 := rawosdFb.NEXT (RetRows2);
    END LOOP;

    COMMIT;
    CommitCount := CommitCount + 1;
    /* Display CommitCount */
    DBMS_OUTPUT.PUT_LINE ('OSD_Fb recs purged = ' || NVL (ModRecs, 0));
    EXCEPTION
    WHEN OTHERS
    THEN
    o_err_num := SQLCODE;
    o_err_msg := SUBSTR (SQLERRM, 1, 100);
    o_err_osp_name := 'purge_Logistic_OSD_Tbls';
    ROLLBACK;
    --
    END;
    begin
    /* Purging OSD_APPT, OSD_FB, OSD_RCPT, OSD_DAMAGED_CNTR and OSD_COMMENTS table */
    /* Delete all records from above Table which were
    created before sysdate - 2 years */

    ModRecs := 0;
    DeletedRecs := 0;
    CommitCount := 0;
    RetRows3 :=RawOsdCom.FIRST;
    WHILE retrows1 IS NOT NULL
    LOOP
    DELETE osd_comment
    WHERE osd_appt_nbr = rawosdRcpt(retrows1);

    ModRecs := SQL%ROWCOUNT + ModRecs;
    DeletedRecs := SQL%ROWCOUNT + DeletedRecs;

    /* Commit after deleting 1000 records */
    IF (DeletedRecs > CommitAfterRecs)
    THEN
    COMMIT;
    DeletedRecs := 0;
    CommitCount := CommitCount + 1;
    END IF;

    retrows3 := rawosdCom.NEXT (RetRows1);
    END LOOP;

    COMMIT;
    CommitCount := CommitCount + 1;
    /* Display CommitCount */
    DBMS_OUTPUT.PUT_LINE ('OSD_Comment recs purged = ' || NVL (ModRecs, 0));
    EXCEPTION
    WHEN OTHERS
    THEN
    o_err_num := SQLCODE;
    o_err_msg := SUBSTR (SQLERRM, 1, 100);
    o_err_osp_name := 'purge_Logistic_OSD_Tbls';
    ROLLBACK;
    -- return;
    END;

    BEGIN

    /* Purging OSD_APPT, OSD_FB, OSD_RCPT, OSD_DAMAGED_CNTR and OSD_COMMENTS table */
    /* Delete all records from above Table which were
    created before sysdate - 2 years */

    ModRecs := 0;
    DeletedRecs := 0;
    CommitCount := 0;
    RetRows3 :=RawOsdAppt.FIRST;
    WHILE retrows3 IS NOT NULL
    LOOP
    DELETE osd_Appt
    WHERE osd_appt_nbr = rawosdRcpt(retrows1);

    ModRecs := SQL%ROWCOUNT + ModRecs;
    DeletedRecs := SQL%ROWCOUNT + DeletedRecs;

    /* Commit after deleting 1000 records */
    IF (DeletedRecs > CommitAfterRecs)
    THEN
    COMMIT;
    DeletedRecs := 0;
    CommitCount := CommitCount + 1;
    END IF;

    retrows3 := rawosdAppt.NEXT (RetRows1);
    END LOOP;

    COMMIT;
    CommitCount := CommitCount + 1;
    /* Display CommitCount */
    DBMS_OUTPUT.PUT_LINE ('OSD_Appt recs purged = ' || NVL (ModRecs, 0));
    EXCEPTION
    WHEN OTHERS
    THEN
    o_err_num := SQLCODE;
    o_err_msg := SUBSTR (SQLERRM, 1, 100);
    o_err_osp_name := 'purge_Logistic_OSD_Tbls';
    ROLLBACK;
    -- return;
    --- END;
    END;
    END purge_Logistic_OSD_Tbls;

    --- END purge_Logistic_OSD_Tbls;
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    it is better to use package operations:"....fetch your-cursor bulk collect into. limit N... and forall "

    for example , one of possible scenarios :
    Code (SQL):


    DECLARE
        TYPE tt_delete IS TABLE OF rowid;
        t_delete tt_delete;
        cursor c_delete IS SELECT rowid FROM your_table WHERE your condition;
        l_delete_buffer pls_integer := 10000;
    BEGIN
        OPEN c_delete;
        loop
            fetch c_delete bulk collect
            INTO t_delete LIMIT l_delete_buffer;
            forall i IN 1..t_delete.COUNT
            DELETE test_tbl
            WHERE rowid = t_delete (i);
            exit WHEN c_delete%notfound;
            commit;
        END loop;
        close c_delete;
    END;
     
    Additional links :
    http://www.dba-oracle.com/plsql/t_plsql_bulk_update.htm

    https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5033906925164
     
  5. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Venki,

    There are several optimizations you can do: you'll probably have to re-write most of the code ! :eek:

    1)
    As you're deleting ALL records from the temporary table, you better do a "TRUNCATE" like :

    Code (SQL):
    EXECUTE IMMEDIATE 'TRUNCATE TABLE LOGISTIC_OSD_TMP';
    2) The rest of the overall logic would need to be changed using BULK COLLECT and FORALL.
    Please have a look at the following link with examples on how to do it:
    http://www.oracle-base.com/articles/9i/bulk-binds-and-record-processing-9i.php

    I would suggest you do everything in 1 LOOP if possible.

    Your first INSERT .. SELECT .. FROM (after the DELETE), transform into Cursor, open, FETCH BULK COLLECT into a table and use the various elements of the table to :
    - FOR ALL INSERT into LOGISTIC_OSD_TMP (I wonder if you still need this table - or it's just a temporary one)
    - FOR ALL DELETE from osd_damaged_flo_cntr (bulk delete)
    - FOR ALL DELETE from osd_rcpt (bulk delete)
    - FOR ALL DELETE from osd_fb (bulk delete)
    - FOR ALL DELETE from osd_comment (bulk delete)
    - FOR ALL DELETE osd_Appt (bulk delete)

    Start by trying the BULK operations and use them to re-write your procedure.

    Good Luck ! :)
     
  6. Venkiibm

    Venkiibm Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Thanks Rajan This is first time I am writing stored procedure in oracle . Here LOGISTIC_OSD_TMP is a temp table I will try to re-write the query by applying your sugestions.