This discussion is archived
7 Replies Latest reply: Nov 25, 2012 5:23 AM by 943841 RSS

SQL Loader Help in 10g

943841 Newbie
Currently Being Moderated
Hello,

Is it possible to forcefully abort the sql loader when a value is not present? I've the data file like this

1|XXX123|XXX|20121121||
4|XXX123|XXX|||
5|XXX123|XXX||1|
5|XXX123|XXX||2|
5|XXX123|XXX|||
9|XXX123|XXX|||

Template:
record type|batch number|batch desc|date|detail line num|others

1,4,5,9 are the record types, if you see this line 5|XXX123|XXX||1| .. 1 represents a detail line number, My requirement is if the detail line number is null for record type 5 then I want to abort the sqlloader.

Is it possible?

Edited by: 940838 on Nov 21, 2012 11:54 PM
  • 1. Re: SQL Loader Help in 10g
    BluShadow Guru Moderator
    Currently Being Moderated
    I imagine there may be a way, but if you used external tables instead, then you'd have more control as you could utilise all the power of SQL, including analytical functions in your query.
  • 2. Re: SQL Loader Help in 10g
    943841 Newbie
    Currently Being Moderated
    Unfortuntely this is all set since a decade., I've a new requirment so finding the solutions.
  • 3. Re: SQL Loader Help in 10g
    AlbertoFaenza Expert
    Currently Being Moderated
    940838 wrote:
    Unfortuntely this is all set since a decade., I've a new requirment so finding the solutions.
    You can check the possibility to use errors=0 if line number NULL is considered as an error by sqlldr
    sqlldr userid=user/pass control=yourfile.ctl errors=0
    Any data inserted up that point, however, is committed.

    Regards.
    Al

    Edited by: Alberto Faenza on Nov 21, 2012 5:39 PM
  • 4. Re: SQL Loader Help in 10g
    user9944600 Newbie
    Currently Being Moderated
    i think you should use the following syntax

    sqlldr userid=<username/pwd> control=<script_name.ctl> log=<script_name>.log bad=<scriput_name>.bad data=<input_file_name.dat> discard=<script_file_name>.dsc errors=<script_name.err>

    whenever the record did not match the control file requirement, it will make an entry in .bad and .dsc files.
  • 5. Re: SQL Loader Help in 10g
    943841 Newbie
    Currently Being Moderated
    I think i am not clear in my requirement...

    The question was how to abort the loader if the detail line number is not present in record type 5. It is however normal that detail line num is not mandatory for other record types. any insights.
  • 6. Re: SQL Loader Help in 10g
    AlbertoFaenza Expert
    Currently Being Moderated
    940838 wrote:
    I think i am not clear in my requirement...

    The question was how to abort the loader if the detail line number is not present in record type 5. It is however normal that detail line num is not mandatory for other record types. any insights.
    Hi,

    you have been clear and I have made a quick test. Unfortunately you cannot do such check in SQL*Loader as the WHEN clause in control file does not allow any OR.

    Even if you add this check using a constraint in your table and specify the maximum number of errors to be 0, SQL*Loader will load the records up to that error.

    Let me show you an example:

    1) create the table with a constraint that for record_type 5 detail_line_number cannot be null.
    CREATE TABLE test
    (
       record_type    INTEGER
     , batch_number   VARCHAR2 (10)
     , batch_desc     VARCHAR2 (10)
     , batch_date     DATE
     , detail_line_num INTEGER
     , other          VARCHAR2 (10)
    );
    
    ALTER TABLE test
      ADD CONSTRAINT check_rec_5 
         CHECK (   record_type = 5 AND detail_line_num IS NOT NULL 
                OR record_type != 5) ENABLE;
                
    In this table you will not be able to load rows having record_type=5 and detail_line_num NULL as this will be considered as an error.

    Let's prepare your input file:
    1|XXX123|XXX|20121121||
    4|XXX123|XXX|||
    5|XXX123|XXX||1|
    5|XXX123|XXX|||
    5|XXX123|XXX|||
    9|XXX123|XXX|||
    1|XXX123|XXX|20121121||
    4|XXX123|XXX|||
    5|XXX123|XXX||1|
    5|XXX123|XXX||2|
    5|XXX123|XXX|||
    9|XXX123|XXX|||1|XXX123|XXX|20121121||
    4|XXX123|XXX|||
    5|XXX123|XXX||1|
    5|XXX123|XXX||2|
    5|XXX123|XXX|||
    9|XXX123|XXX|||1|XXX123|XXX|20121121||
    4|XXX123|XXX|||
    5|XXX123|XXX||1|
    5|XXX123|XXX||2|
    As you can see the input file has the fourth line with record_type = 5 and detail_line_num NULL. This will be an error for the constraint.

    Here the control file I have used:
    --test.ctl
    load data
    INFILE 'test.dat'
    APPEND
    INTO TABLE test
    FIELDS TERMINATED BY '|'
    TRAILING NULLCOLS
    (
     record_type     ,
     batch_number    ,
     batch_desc      ,
     batch_date      Date 'YYYYMMDD',
     detail_line_num ,
     other
    )
    If I try to execute the SQL*Loader and ask to stop at first error in this way:
    sqlldr userid=yourname/yourpass@yourdb control=test.ctl errors=0 rows=100
    SQL*Loader will load only 3 records because it encounters an error at line 4 and having specified errors=0 will not continue to load. Actually the process will continue until it reach the commit point (100 rows in this case) but it will not load any record after the error nor continue to read the file.

    So if I check the table
    SELECT * FROM test;
    
    RECORD_TYPE BATCH_NUMBER BATCH_DESC BATCH_DATE            DETAIL_LINE_NUM OTHER     
    ----------- ------------ ---------- --------------------- --------------- ----------
              1 XXX123       XXX        21-11-2012 00:00:00                             
              4 XXX123       XXX                                                        
              5 XXX123       XXX                                            1           
    You will see only records until you have reached the error.

    This cannot be avoided as documented in SQL*Loader reference manual:
    <h3>Load Discontinued Because Maximum Number of Errors Exceeded</h3>
    If the maximum number of errors is exceeded, SQL*Loader stops loading records into any table and the work done to that point is committed.
    As you can see SQL*Loader abort the processing but it will anyway commit the records until that error.

    One alternative solution is to create an external table in Oracle and do all the checks you want before copying your external table into a database table, as BluShadow suggested.

    Regards.
    Al
  • 7. Re: SQL Loader Help in 10g
    943841 Newbie
    Currently Being Moderated
    Thanks alberto.

    We have a mechanism to truncate the table if the sql loader is aborted, so if it gets aborted due to the check constraint then the process of ours will allow us to truncate the table, remove the problematic record from the file and then rerun the procesS.

    Also ill learn external tables and see if i can convert the existing sql loader process.

Legend

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