oracop
asked on
Loading SELECTIVE Data using SQL Loader
Hello,
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
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
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.
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.
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.
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.
ASKER
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?
Thanks.
Thanks.
ASKER
No answer was satisfactory enough and to the point.
Please refund and close the question.
Regards.
Please refund and close the question.
Regards.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
2 is not possible (to rollback the whole LOAD) using your current approach.