Solved

Loading SELECTIVE Data using SQL Loader

Posted on 2004-08-23
8
623 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:oracop
8 Comments
 
LVL 13

Expert Comment

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

0
 
LVL 7

Expert Comment

by:bvanderveen
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.
0
 
LVL 6

Expert Comment

by:morphman
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:oracop
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?

Thanks.

0
 
LVL 13

Expert Comment

by:riazpk
ID: 12062154
goto

http://asktom.oracle.com/

and search for "external table"

tons of thread alongwith examples.
0
 
LVL 1

Author Comment

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

Please refund and close the question.

Regards.

0
 

Accepted Solution

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

modulo
Community Support Moderator
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

760 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now