5 Replies Latest reply: Mar 7, 2013 10:08 AM by avish16 RSS

    Pick value from file in FOR loop

    985196
      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
          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
            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
              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
                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
                  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.