This discussion is archived
5 Replies Latest reply: Mar 7, 2013 8:08 AM by avish16 RSS

Pick value from file in FOR loop

985196 Newbie
Currently Being Moderated
Hello All,

I was trying to delete records from database day by day and commit. Is it possible to pick the dates from file and then delete the database record for that day. Something like below.

For day IN <File.txt>
LOOP

delete from table where date = '<1st value from File.txt>';
commit;

END LOOP;

I have to do this from unix box, so i can place the file (with list of dates as input) in the path from where i am issuing sqlplus.

Please suggest any other way to delete it day by day is most welcome.

Thanks.
  • 1. Re: Pick value from file in FOR loop
    jeneesh Guru
    Currently Being Moderated
    982193 wrote:
    Hello All,

    I was trying to delete records from database day by day and commit. Is it possible to pick the dates from file and then delete the database record for that day. Something like below.
    You an use EXTERNAL TABLE (Or SQL Loader, if the file cannot be moved to the DB server) to read from a file and it can be used in your DELETE statements just as a normal table.
    >
    For day IN <File.txt>
    LOOP

    delete from table where date = '<1st value from File.txt>';
    commit;

    END LOOP;
    LOOP is a bad idea.. You could do it in a single statement..
    delete from table where date in (select the_date_coumn from the external_table);
    And COMMIT inside LOOP is again not a good idea..
  • 2. Re: Pick value from file in FOR loop
    avish16 Explorer
    Currently Being Moderated
    You can load the unix file in an oracle table and then pick the date from there as per your requirement or
    you can also use unix script and generate a sql using the time mentioned in unix file and just run that statement in oracle by connecting sqlplus and running it.
  • 3. Re: Pick value from file in FOR loop
    985196 Newbie
    Currently Being Moderated
    Hello Jineesh.

    I have 75 Million records in a year of time stamp to delete. Firing a straight query to db to delete a year data will not have any impact in terms of load ? Also i dont want any changes to be done in that time period so i am doing commit after every delete.
    I cannot load the date in another table (as i am not allowed to do that). Please suggest.

    Thanks
  • 4. Re: Pick value from file in FOR loop
    985196 Newbie
    Currently Being Moderated
    Hello Avish,

    I cannot load file in another table, at max i can keep the file on unix box path from where i am issuing sqlplus.

    I already tried scripting but that seems not to work. Below is the code i was trying.

    #!/usr/bin/ksh

    LogDirectory=/path/
    DataDirectory=/path/

    for date in `cat list.txt`
    do
    echo "delete record for $date"

    sqlplus -s user/passwd@DB <<EOF > ${LogDirectory}/output.log

    set linesize 32767
    set feedback off
    set heading off

    spool ${DataDirectory}/query_output.txt

    delete from table where TO_CHAR(date_column, 'MM/DD/YY') = '$date';

    commit;
    spool off

    EOF

    done
  • 5. Re: Pick value from file in FOR loop
    avish16 Explorer
    Currently Being Moderated
    Hi, regarding your scenario, i am a bit confused as firstly you mentioned taht you ahve to remove day-by-day records then you have mentioned 75 million records for a year. If you do have 75 million records to deleet you will find severe problem puting them in a unix file and operating through it.

    Meanwhile still not sure what u wanna do, but if you do have managable records in unix try -

    ur first sql file (assume it to be 1.sql , do not put any spool here)
    {
    delete from tbl where date = '&1';
    commit;
    }

    now if u do have ur file with dates only(assume dt.txt) then simply modify this file as -

    sed 's/^/@1.sql /g' dt.txt > run_1.sql

    Now u can simply run this run_1.sql and use spool options in this file as desired.
    Additionally I will repeat that hitting commit everytime for each single sql is not a proper way, but seems this is what u were trying.

    Please reply if I interpreted wrongly.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points