asked on

sqlldr - Generate Files "BAD" from several "INFILES" data

Hi experts, I have the next control_file:

LOAD DATA
INFILE '/xahome/xa21/CAP/INPUTS_GIS/Transfer_data.20141202020202'
INFILE '/xahome/xa21/CAP/INPUTS_GIS/Transfer_data.20141203030303'
APPEND
INTO TABLE "INT_OPERATIONS"
FIELDS TERMINATED BY '!'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
...COLUMNS...
 )

Open in new window

and executing:

sqlldr $USER_GIS/$PASS_GIS@$DB_GIS control=$TEMP_GIS/control_$dt.ctl log=$LOGS_GIS/sqlldr_$dt.log bad=$BAD_GIS/Transfer_$dt.bad discard=$DISCARD_GIS/Transfer_$dt.dsc

Open in new window

The sqlldr log says:

SQL*Loader: Release 11.2.0.3.0 - Production on Tue Dec 2 12:33:07 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Control File:   /xahome/xa21/CAP/TEMP_GIS/control_20141202123304.ctl

There are 2 data files:
Data File:      /xahome/xa21/CAP/INPUTS_GIS/Transfer_data.20141202020202
  Bad File:     /xahome/xa21/CAP/BAD_GIS/Transfer_data.20141202020202.bad
  Discard File: /xahome/xa21/CAP/DISCARD_GIS/Transfer_data.20141202020202.dsc
 (Allow all discards)
Data File:      /xahome/xa21/CAP/INPUTS_GIS/Transfer_data.20141203030303
  Bad File:     /xahome/xa21/CAP/TEMP_GIS/Transfer_data.bad
  Discard File:  none specified

Open in new window

That is for the second file, it forms a "Bad file" undated, like the "Discard file"
I would like to see:

Data File:      /xahome/xa21/CAP/INPUTS_GIS/Transfer_data.20141202020202
  Bad File:     /xahome/xa21/CAP/BAD_GIS/Transfer_data.20141202020202.bad
  Discard File: /xahome/xa21/CAP/DISCARD_GIS/Transfer_data.20141202020202.dsc
 (Allow all discards)
Data File:      /xahome/xa21/CAP/INPUTS_GIS/Transfer_data.20141203030303
  Bad File:     /xahome/xa21/CAP/BAD_GIS/Transfer_data.20141203030303.bad
  Discard File: /xahome/xa21/CAP/DISCARD_GIS/Transfer_data.20141203030303.dsc

Open in new window

The generation of the control file is dynamic and is obtained by reading a list of data files to run, modifying a generic control file:

     for f in $(cat $TEMP_GIS/lista_$dt.lst)

       do

          sed -i ${line}" i\INFILE '$f'" $TEMP_GIS/control_$dt.ctl  #Modifica archivo de control con los files a cargar

       done

Open in new window

And date:

dt=`date +%Y%m%d%H%M%S`

Open in new window

The generic control file is:

LOAD DATA
APPEND
INTO TABLE "INT_OPERATIONS"
FIELDS TERMINATED BY '!'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
...COLUMNS...
 )

Open in new window

It is possible to solve this from the "FOR", or should I use another form of logging?
I would appreciate any contribution to achieving individualized logging.
Thanks and regards

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

johnsone

I wouldn't even specify infile in the controlfile. I would run a different loader for each file.

for f in $(cat $TEMP_GIS/lista_$dt.lst)
  do
    dt=`date +%Y%m%d%H%M%S`
    sqlldr $USER_GIS/$PASS_GIS@$DB_GIS control=$TEMP_GIS/control_$dt.ctl data=$f log=$LOGS_GIS/sqlldr_$dt.log bad=$BAD_GIS/Transfer_$dt.bad discard=$DISCARD_GIS/Transfer_$dt.dsc
  done

Open in new window

If your load could run in less than a second, you would have to do something to sequence the log files further, but specifying the data file on the command line removes the need to have to modify a file.

carlino70

ASKER

johnsone, including "$ dt" in the "FOR", "sqlldr" works,
inserting records from all files lista.ctl

    for f in $(cat $TEMP_GIS/lista.lst)

    do

        dt=`date +%Y%m%d%H%M%S`

        sqlldr $USER_GIS/$PASS_GIS@$DB_GIS control=$TEMP_GIS/control.ctl data=$f log=$LOGS_GIS/sqlldr_$dt.log bad=$BAD_GIS/Transfer_$dt.bad discard=$DISCARD_GIS/Transfer_$dt.dsc

    done

Open in new window

but the log files, bad and discard (these last only if there are problems), is generated only once:

SQL*Loader: Release 11.2.0.3.0 - Production on Wed Dec 3 10:02:03 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Control File:   /xahome/xa21/CAP/TEMP_GIS/control.ctl
Data File:      /xahome/xa21/CAP/INPUTS_GIS/Transfer_data.20141203123543
Bad File:     /xahome/xa21/CAP/BAD_GIS/Transfer_20141203100203.bad
Discard File: /xahome/xa21/CAP/DISCARD_GIS/Transfer_20141203100203.dsc
 (Allow all discards)
...

Open in new window

I would get a log for each execution sqlldr for each data file, is this possible ?, could help me with an example?
Thank you

carlino70

ASKER

Thanks Slightwv, I'm looking to make log files, bad and discard take the date of each of the data files, is this possible ?

carlino70

ASKER

johnsone, adding:

Could I get the date of name of each of the data files and thus form the name of the log files, bad and discard, dento the "FOR"?

Thanks

slightwv (䄆 Netminder)

>> I'm looking to make log files, bad and discard take the date of each of the data files, is this possible ?

Yes. Based on the documentation link I provided: One INFILE per control file.

ASKER CERTIFIED SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

carlino70

ASKER

Thanks!

sqlldr - Generate Files &quot;BAD&quot; from several &quot;INFILES&quot; data

sqlldr - Generate Files "BAD" from several "INFILES" data