This discussion is archived
9 Replies Latest reply: Feb 14, 2011 12:00 AM by KannanB RSS

SQL LOADER Delete Records

KannanB Newbie
Currently Being Moderated
Hello All,

I want to load records into a table using SQL Loader. I want to do the following(using a column in the data file),

1. If the flag is I insert the record.
2. If the flag is U update the record.
3. If the flag is D delete the record.

What are the options available in SQL Loader to achieve this.


Thanks,
Kannan.
  • 1. Re: SQL LOADER Delete Records
    Karthick_Arp Guru
    Currently Being Moderated
    Where does the file exist? Is it in the Server where your DB is installed or in the Client system?

    If your file is in the db server then you can use External Tables. It is a much better option than SQL Loader. Once you create an external table its just like operating on a normal table. You can run all your Select statement against that external table.

    How to create External table is discussed here you can check that out.

    http://www.orafaq.com/node/848
  • 2. Re: SQL LOADER Delete Records
    Achyut K Journeyer
    Currently Being Moderated
    Hi Kannan,
    Kannan B wrote:
    Hello All,

    I want to load records into a table using SQL Loader. I want to do the following(using a column in the data file),

    1. If the flag is I insert the record.
    2. If the flag is U update the record.
    3. If the flag is D delete the record.

    What are the options available in SQL Loader to achieve this.


    Thanks,
    Kannan.
    You have 2 solutions to acheive the result.

    1.If you are running the sql loader on unix environment,then i suggest you to use AWK script to filter out the records,which you need (for Insertion/Updation) and discard the records which is of flag D in your data file.

    For Example
    If the name of your control file is load.txt,with file delimitter as "|" (pipe) the flag column is at 4th position then you can use awk script .
    /home/bin/cat load.txt|nawk -F "|" '{ if ($4=="I" || $4=="U")  print $0 }'|more
    2. Just load all the data onto table and filter out based upon the flag in table(Insertion/Updation/Deletion).

    Hope this helps..

    Regards,
    Achyut
  • 3. Re: SQL LOADER Delete Records
    KannanB Newbie
    Currently Being Moderated
    I unerstand external tables.

    But I dont understand your post.

    1. With awk I can filter out the data in data file -- OK
    2. Load all the data onto table -- All data here means insert, delete and update.
    3. There is no column in the staging table to hold the flag, then how can I load all the records and how can i filter which record to be inserted or update or deleted.

    Kindly explain more, I new to this awk.


    Thanks,

    Kannan
  • 4. Re: SQL LOADER Delete Records
    Karthick_Arp Guru
    Currently Being Moderated
    SQL Loader cannot perfor UPDATE and DELETE its a tool to load data not to modify the data in the DB. Said that if you can use external table its fine. Else you need to create a Temp table that will get loaded with your file and you go forward from there.
  • 5. Re: SQL LOADER Delete Records
    Achyut K Journeyer
    Currently Being Moderated
    Hi,
    Kannan B wrote:

    I want to load records into a table using SQL Loader. I want to do the following(using a column in the data file),

    1. If the flag is I insert the record.
    2. If the flag is U update the record.
    3. If the flag is D delete the record.
    As per your post, I assume that you just want to load the records which are marked as "I" for Insertion and "U" Updation. But after loading onto table How do differentiate the Insertion records( "I") with that of Updation records("U") or "Deletion".
    So my suggestion is to add a flag column in a temporary staging table and load the records(I/U/D) on that table using sql loader and filter out based upon the flag
    like
    select * from your_temp_table
    where flag='I'
    Hope this helps.

    Regards,
    Achyut

    Edited by: Achyut K on Feb 13, 2011 10:21 PM
  • 6. Re: SQL LOADER Delete Records
    KannanB Newbie
    Currently Being Moderated
    Hello All,
    Thank You All.

    I am going to use external table and perform DML operation.

    There is another drawback. When using SQLLoader, we can dynamically change the staging table structure, only thing is CTL file needs to be changed with respect to the base table structure. So the person who is going to send the data file can change the CTL file and there is no need to change the shell script and all.

    But when using External Table can we have this functionality.

    Thanks,
    Kannan.
  • 7. Re: SQL LOADER Delete Records
    KannanB Newbie
    Currently Being Moderated
    Will the column names in an external table stored in another table(like all_table_columns).

    Thanks,
    Kannan
  • 8. Re: SQL LOADER Delete Records
    Karthick_Arp Guru
    Currently Being Moderated
    Kannan B wrote:
    Will the column names in an external table stored in another table(like all_table_columns).

    Thanks,
    Kannan
    YES
  • 9. Re: SQL LOADER Delete Records
    KannanB Newbie
    Currently Being Moderated
    Is that the same table "all_tab_columns".

    Can I create the table and immediately collect the column names.

    Thanks,
    Kannan.

Legend

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