Loading SELECTIVE Data using SQL Loader

Posted on 2004-08-23
Last Modified: 2012-06-27
     I need to use SQL loader to load data in Oracle.

    Following issues need to be considered during data load:
    1- If primary key clash occurs during load, ignore that record.
    2- In order to load go successful, I *NEED TO MAKE SURE* that
        certain fields are not null. I can not make those columns NOT NULL
        in DB as for some other LOADS/Application they need to be NULL in table definition.
        IF ANY REQUIRED COLUMN is found NULL whole LOAD should rollback.
        So question is how to define certain columns to be REQUIRED in .ctl file and
        how to rollback whole load as soon as a record is encountered having required field with NULL value.
Thanks a lot
Question by:oracop
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 13

Expert Comment

ID: 11872990
1 cane be acheived easily
2 is not possible (to rollback the whole LOAD) using your current approach.


Expert Comment

ID: 11873378
Easiest way here is to handle using a temp table instead of just SQL*Loader.

Load everything into a copy of your target table, then check for
   1. PK existing in target table
   2. Null columns

You can delete these rows, then load everything else into the target table.  Staging tables are frequently used for this type of problem, especially when you are worried that the data may be incomplete or dirty.

Expert Comment

ID: 11875650
9i Introduces EXTERNAL TABLES for this exact reason. You are able to load and transform in the same step.

You can define the file as an external table, and then use a plsql cursor to read the table and insert into target table, and ignore on constraint errors, and you can also check for NULL and rollback if neccessary.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 11943289
morphman: I like this concept of external tables. Would it be possible to provide an example of external table and related Pl/sql code to process dirty data and load good data in target table to accomplish whole thing in one step?


LVL 13

Expert Comment

ID: 12062154

and search for "external table"

tons of thread alongwith examples.

Author Comment

ID: 12589979
No answer was satisfactory enough and to the point.

Please refund and close the question.



Accepted Solution

modulo earned 0 total points
ID: 12591517
PAQed with points refunded (200)

Community Support Moderator

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Email query results in HTML 6 49
SQL2016 to ORACLE11G linked-server 6 79
oracle collections 2 42
Check for any ASM patches and install them. 1 25
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question