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!

Delete from a table with between dates.

Discussion in 'SQL PL/SQL' started by kam, Dec 7, 2011.

  1. kam

    kam Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi All,

    I need some help with below function....

    I am trying to schedule a store procedure and wanted to remove some data from a table starting from a date and increase the delete by day until a specified date.

    I wrote the below function; but i can't get to work.

    Any help will he appreciated....

    --Start
    CREATE OR REPLACE FUNCTION remove_DateField return VARCHAR2 IS

    i PLS_INTEGER;
    startdate Date ;
    endDate Date;
    currentDate Date;
    stopDate Date;

    BEGIN
    -- Start Date will be the staring point for
    startdate := to_date('2010/11/26','YYYY/MM/DD');
    currentDate :=(to_date(TO_CHAR(SYSDATE,'YYYY/MM/DD'),'YYYY/MM/DD'));
    stopDate:=to_date('2011/12/20','YYYY/MM/DD');

    IF startdate < stopDate THEN
    -- endDate:=currentdate - 44;
    endDate:=currentdate -10;

    delete
    FROM Table_1
    where ((timestamp) between (startdate) and (to_date(endDate))
    and
    service='EBSService';

    RETURN 'Sucessfull Run - remove_DateField ';

    END IF;

    EXCEPTION
    WHEN OTHERS THEN
    RETURN 'Failed Deleting';
    END remove_DateField;

    --end



    SELECT remove_DateField FROM dual;
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I don't understand why the variable i is declared as it's not used. I also don't understand this piece of code:

    currentDate :=(to_date(TO_CHAR(SYSDATE,'YYYY/MM/DD'),'YYYY/MM/DD'));

    as you could have used this:

    currentDate := trunc(sysdate);

    and achieved the same result.

    Also this is a bit suspect:

    IF startdate < stopDate THEN
    -- endDate:=currentdate - 44;
    endDate:=currentdate -10;

    You're not checking if endDate will be within the range of startDate and stopDate so you could be processing far more records than you expect. If you do this:

    endDate:=currentdate -10;

    if startDate < stopDate and endDate <= stopDate then

    delete
    FROM Table_1
    where ((timestamp) between (startdate) and (endDate))
    and
    service='EBSService';

    RETURN 'Sucessfull Run - remove_DateField ';

    ELSE

    RETURN 'endDate is greater than stopDate -- no records processed';

    END IF;

    Notice also that executing to_date() on a date field will likely return an error thus the to_date() call for endDate was removed.

    Why do you not code this function to take an input value or values? Functions operate on input; this would be a better function:

    Code (SQL):
    CREATE OR REPLACE FUNCTION remove_DateField (startDate IN DATE) RETURN VARCHAR2 IS

    endDate DATE;

    BEGIN

    endDate:=trunc(sysdate) -10;

    IF startDate <= endDate THEN

        DELETE
        FROM Table_1
        WHERE TIMESTAMP BETWEEN startDate AND endDate
        AND
        service='EBSService';

        RETURN 'Sucessful Run -- remove_DateField ';

    END IF;

    EXCEPTION
    WHEN OTHERS THEN
        RETURN 'Failed Deleting';
    END remove_DateField;
    /
     
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    I prefer you to modify this function into a PROCEDURE as the functionality of this code mostly resembles of a procedure rather than a function.

    ANYWAY, Also you have to add RETURN At the end of the function so that even if the "IF" condition is not satisfied, the function will return some string as below.

    Code (SQL):

    CREATE OR REPLACE FUNCTION remove_DateField (startDate IN DATE) RETURN VARCHAR2 IS
     
    endDate DATE;
     
    BEGIN
     
    endDate:=trunc(sysdate) -10;
     
    IF startDate <= endDate THEN
     
        DELETE
        FROM Table_1
        WHERE TIMESTAMP BETWEEN startDate AND endDate
        AND
        service='EBSService';
     
        RETURN 'Sucessful Run -- remove_DateField ';
     
    END IF;
     
        RETURN 'Sucessful Run -- NOT DELETED';
    EXCEPTION
    WHEN OTHERS THEN
        RETURN 'Failed Deleting';
    END remove_DateField;
     
     
  4. kam

    kam Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Thanks for all your help.

    I was able to get to work.

    Regards
    Kam