Home » RDBMS Server » Server Utilities » Trapping SQL Loader summary counts (10G, 10.2.0, on Windows)
Trapping SQL Loader summary counts [message #514041] Thu, 30 June 2011 20:30 Go to next message
rsager
Messages: 17
Registered: June 2011
Location: Sydney, Australia
Junior Member
Hi folks....

I have got another curly one with regard to Sql Loader. My original issue via [message #513696] was resolved thanks to a guru on the US. In summary, SQL Loader opens a comma delimited csv file and it is able to successfully populate the appropriate Oracle table. However, during the run to process each unique csv file, SQL Loader produces a both a .log file (indicating what transpired during the load) and a .bad file of the records from the csv file that where rejected by SQL Loader.

In this regard, the users have requested that "is it possible to update a separate table of the summary results of what went wrong and what was right?"

It just so happens that the csv file contains some summary records (rows) with processing audit infor...as the following .bad file wrote out....
___________________________
ModuleID,ModuleSN,Lat,Lon,GMT Time,Tag Count,GMT Date,Client,Farm,Field,Variety,Machine
No Module ID ,No SN ,-30.178958,149.63844,9:55:58,0,21/04/2011,101,Widgen ,9,71BRF ,5987
3500B9880611170260179BFB,10202094587,-30.199379,149.661333,7:10:35,2,22/04/2011,101,Widgen ,30,71BRF ,5987
,,,,,,,,,,,
Total Modules,1602,,,,,,,,,,
Mis-reads,1,,,,,,,,,,
Mis-read %,0.06,,,,,,,,,,
_______________________
1. The first record (starting in the 1st postion is "ModuleID") is a heading record in the csv file and can be disregarded.
2. The secord record (starting in the 1st postion is "No Module ID") is an error record in the csv file and can be disregarded.
3. The third record (starting in the 1st postion 3500B9880611170260179BFB, ) is a valid record, however, is was rejected as it is a duplicate of a preceeding record ...and the log indicates ...
------------
Record 124: Rejected - Error on table PTLIVE.MODULE_CSV_LOADS.
ORA-00001: unique constraint (PTLIVE.MODULE_SERIAL_NUMBER_UK) violated
--------------
4. The fourth record (starting in the 1st postion is ,,,,,,,,,,,
) is a blank record in the csv file and can be disregarded.
5. The fifth record (starting in the 1st postion is "Total Modules" has a value of 1602 which I need to capture to write into a separate table.
6. The sixth record (starting in the 1st postion is "Mis-reads" ) has a value of 1 which I need to capture to write into a separate table.
7. The last record in the csv file and can be disregarded.
-----------

The summary Oracle table is
CREATE TABLE PTLIVE.SUBMODULES_SUMMARY
( IMPORT_CSV_FILE_NAME VARCHAR2(256 ),
TOTAL_SUBMODULES_LOADED NUMBER (5),
TOTAL_SUBMODULES_MIS_READ NUMVER (5),
DATEIMPORTED DATE default SYSDATE
)

Now I would like to trap the value 1602 and load that into the Oracle column TOTAL_SUBMODULES_LOADED and the value 1 and load this into TOTAL_SUBMODULES_MIS_READ.

The name of the csv file is known and I can (as the previous [message #513696] resolved) simply use a SQL Loader control file command > IMPORT_CSV_FILE_NAME CONSTANT "Batch_2011Jun29_TEST.csv"

I hope the above makes sense? Any suggestions?

Cheers
Roger
Re: Trapping SQL Loader summary counts [message #514045 is a reply to message #514041] Thu, 30 June 2011 22:25 Go to previous messageGo to next message
BlackSwan
Messages: 26759
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/


You could map the bad file as an EXTERNAL TABLE with a single VARCHAR2(4000) column.
Write a small custom procedure to spin through the records, parse the content & extract the desired details before doing DML against SUMMARY table
Re: Trapping SQL Loader summary counts [message #514141 is a reply to message #514041] Fri, 01 July 2011 14:50 Go to previous message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
Roger,

I think you may be confusing some things. The rows in the bad file that are:

Total Modules,1602,,,,,,,,,,
Mis-reads,1,,,,,,,,,,

originate from your csv file and are not generated by SQL*Loader. These can be loaded from your csv file using a separate control file and "when" clauses. To load data from the log file generated by SQL*Loader, you can do as BlackSwan suggested or you can use SQL*Loader again to load the file using one column to hold the whole row of data, then select and insert what you want into some table using whatever criteria you want. I have demonstrated below loading of the data from the csv file into the destination table, then loading of the summary data from the csv file into the summary table, then loading of the log file produced by the first load into a log table. I have used "skip=1" to skip the header row and "when" clauses to determine which rows to load or not load.

-- contents of Batch_2011Jun29_TEST.csv file:
ModuleID,ModuleSN,Lat,Lon,GMT Time,Tag Count,GMT Date,Client,Farm,Field,Variety,Machine
No Module ID ,No SN ,-30.178958,149.63844,9:55:58,0,21/04/2011,101,Widgen ,9,71BRF ,5987
3500B9880611180257084XXX,99050096146,-30.162415,149.636484,3:34:02,4,13/04/2011,101,Widgen,9,brf71,5988
3500B9880611170260179BFB,10202094587,-30.199379,149.661333,7:10:35,2,22/04/2011,101,Widgen ,30,71BRF ,5987
3500B9880611170260179BFB,10202094587,-30.199379,149.661333,7:10:35,2,22/04/2011,101,Widgen ,30,71BRF ,5987
Total Modules,1602,,,,,,,,,,
Mis-reads,1,,,,,,,,,,
Mis-read %,0.06,,,,,,,,,,


-- load of data from csv file to destination table:
-- contents of test1.ctl file:
OPTIONS (SKIP=1)
load data
infile 'Batch_2011Jun29_TEST.csv'
BADFILE 'Batch_2011Jun29_TEST.bad'
append into table SUBMODULES
WHEN submoduletagnumber != 'Total Modules'
AND  submoduletagnumber != 'Mis-reads'
AND  submoduletagnumber != 'Mis-read %'
FIELDS TERMINATED BY ","
( SUBMODULETAGNUMBER,
SUBMODULESERIALNUMBER,
LATITUDE,
LONGITUDE,
GMT_TIME,
TAGCOUNT,
DATEPICKED "to_date(:DATEPICKED||:GMT_TIME, 'DD/MM/YYYYHH24:MI:SS')",
GROWER "to_char(:GROWER)||'/00' ",
FARM,
FIELD,
VARIETY,
PICKER,
IMPORT_CSV_FILE_NAME CONSTANT "Batch_2011Jun29_TEST.csv"
)

SCOTT@orcl_11gR2> CREATE TABLE SUBMODULES
  2  ( SUBMODULEID	      NUMBER (20),
  3    SUBMODULETAGNUMBER     VARCHAR2(100) UNIQUE,
  4    SUBMODULESERIALNUMBER  number(20,0),
  5    LATITUDE 	      NUMBER(20,10),
  6    LONGITUDE	      NUMBER(20,10),
  7    GMT_TIME 	      VARCHAR2(10),
  8    TAGCOUNT 	      NUMBER,
  9    DATEPICKED	      DATE,
 10    GROWER		      VARCHAR2(10 ),
 11    FARM		      VARCHAR2(30 ),
 12    FIELD		      VARCHAR2(30 ),
 13    VARIETY		      VARCHAR2(30 ),
 14    PICKER		      VARCHAR2(10 ),
 15    IMPORT_CSV_FILE_NAME   VARCHAR2(256 ),
 16    DATEIMPORTED	      DATE  default SYSDATE
 17  )
 18  /

Table created.

SCOTT@orcl_11gR2> CREATE SEQUENCE submoduleid_seq
  2  /

Sequence created.

SCOTT@orcl_11gR2> CREATE OR REPLACE TRIGGER submodules_bir
  2    BEFORE INSERT ON submodules
  3    FOR EACH ROW
  4  BEGIN
  5    SELECT submoduleid_seq.NEXTVAL
  6    INTO   :NEW.submoduleid
  7    FROM   DUAL;
  8  END submoduleid_bir;
  9  /

Trigger created.

SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> HOST SQLLDR scott/tiger CONTROL=test1.ctl LOG=test1.log

SCOTT@orcl_11gR2> COLUMN import_csv_file_name FORMAT A24
SCOTT@orcl_11gR2> SELECT * FROM submodules
  2  /

SUBMODULEID
-----------
SUBMODULETAGNUMBER
----------------------------------------------------------------------------------------------------
SUBMODULESERIALNUMBER   LATITUDE  LONGITUDE GMT_TIME     TAGCOUNT DATEPICKE GROWER
--------------------- ---------- ---------- ---------- ---------- --------- ----------
FARM                           FIELD                          VARIETY
------------------------------ ------------------------------ ------------------------------
PICKER     IMPORT_CSV_FILE_NAME     DATEIMPOR
---------- ------------------------ ---------
          1
3500B9880611180257084XXX
           9.9050E+10 -30.162415 149.636484 3:34:02             4 13-APR-11 101/00
Widgen                         9                              brf71
5988       Batch_2011Jun29_TEST.csv 01-JUL-11

          2
3500B9880611170260179BFB
           1.0202E+10 -30.199379 149.661333 7:10:35             2 22-APR-11 101/00
Widgen                         30                             71BRF
5987       Batch_2011Jun29_TEST.csv 01-JUL-11


2 rows selected.


-- load of summary information from csv file:
-- contents of test2.ctl file:
OPTIONS (SKIP=1)
load data
infile 'Batch_2011Jun29_TEST.csv'
APPEND
INTO TABLE submodules_summary
WHEN import_csv_file_name = 'Total Modules'
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(import_csv_file_name POSITION (1) "REPLACE (:import_csv_file_name, :import_csv_file_name, 'Batch_2011Jun29_TEST.csv')",
total_submodules_loaded)
INTO TABLE submodules_summary
WHEN import_csv_file_name = 'Mis-reads'
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(import_csv_file_name POSITION (1) "REPLACE (:import_csv_file_name, :import_csv_file_name, 'Batch_2011Jun29_TEST.csv')",
total_submodules_mis_read)

SCOTT@orcl_11gR2> CREATE TABLE SUBMODULES_SUMMARY
  2  ( IMPORT_CSV_FILE_NAME	  VARCHAR2(256 ),
  3    TOTAL_SUBMODULES_LOADED	  NUMBER (5),
  4    TOTAL_SUBMODULES_MIS_READ  NUMBER (5),
  5    DATEIMPORTED DATE default  SYSDATE
  6  )
  7  /

Table created.

SCOTT@orcl_11gR2> HOST SQLLDR scott/tiger CONTROL=test2.ctl LOG=test2.log

SCOTT@orcl_11gR2> UPDATE submodules_summary t1
  2  SET    t1.total_submodules_mis_read =
  3  	    (SELECT t2.total_submodules_mis_read
  4  	     FROM   submodules_summary t2
  5  	     WHERE  t2.import_csv_file_name = t1.import_csv_file_name
  6  	     AND    t2.total_submodules_loaded IS NULL)
  7  WHERE  total_submodules_mis_read IS NULL
  8  /

1 row updated.

SCOTT@orcl_11gR2> DELETE FROM submodules_summary
  2  WHERE  total_submodules_loaded IS NULL
  3  /

1 row deleted.

SCOTT@orcl_11gR2> SELECT * FROM submodules_summary
  2  /

IMPORT_CSV_FILE_NAME     TOTAL_SUBMODULES_LOADED TOTAL_SUBMODULES_MIS_READ DATEIMPOR
------------------------ ----------------------- ------------------------- ---------
Batch_2011Jun29_TEST.csv                    1602                         1 01-JUL-11

1 row selected.


-- load of log generated by first load into log file:
-- contents of test3.ctl file:
LOAD DATA
INFILE test1.log
APPEND
INTO TABLE log_table
FIELDS
(log_row POSITION (1:255))

SCOTT@orcl_11gR2> CREATE TABLE log_table
  2    (log_row  VARCHAR2 (255))
  3  /

Table created.

SCOTT@orcl_11gR2> HOST SQLLDR scott/tiger control=test3.ctl

SCOTT@orcl_11gR2> SELECT * FROM log_table
  2  /

LOG_ROW
----------------------------------------------------------------------------------------------------
SQL*Loader: Release 11.2.0.1.0 - Production on Fri Jul 1 12:32:28 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Control File:   test1.ctl
Data File:      Batch_2011Jun29_TEST.csv
  Bad File:     Batch_2011Jun29_TEST.bad
  Discard File:  none specified
 (Allow all discards)
Number to load: ALL
Number to skip: 1
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional
Table SUBMODULES, loaded when SUBMODULETAGNUMBER != 0X546f74616c204d6f64756c6573(character 'Total Mo
dules')

                  and SUBMODULETAGNUMBER != 0X4d69732d7265616473(character 'Mis-reads')
                  and SUBMODULETAGNUMBER != 0X4d69732d726561642025(character 'Mis-read %')
Insert option in effect for this table: APPEND
   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
SUBMODULETAGNUMBER                  FIRST     *   ,       CHARACTER
SUBMODULESERIALNUMBER                NEXT     *   ,       CHARACTER
LATITUDE                             NEXT     *   ,       CHARACTER
LONGITUDE                            NEXT     *   ,       CHARACTER
GMT_TIME                             NEXT     *   ,       CHARACTER
TAGCOUNT                             NEXT     *   ,       CHARACTER
DATEPICKED                           NEXT     *   ,       CHARACTER
    SQL string for column : "to_date(:DATEPICKED||:GMT_TIME, 'DD/MM/YYYYHH24:MI:SS')"
GROWER                               NEXT     *   ,       CHARACTER
    SQL string for column : "to_char(:GROWER)||'/00' "
FARM                                 NEXT     *   ,       CHARACTER
FIELD                                NEXT     *   ,       CHARACTER
VARIETY                              NEXT     *   ,       CHARACTER
PICKER                               NEXT     *   ,       CHARACTER
IMPORT_CSV_FILE_NAME                                      CONSTANT
    Value is 'Batch_2011Jun29_TEST.csv'
Record 5: Discarded - failed all WHEN clauses.
Record 6: Discarded - failed all WHEN clauses.
Record 7: Discarded - failed all WHEN clauses.
Record 1: Rejected - Error on table SUBMODULES, column SUBMODULESERIALNUMBER.
ORA-01722: invalid number
Record 4: Rejected - Error on table SUBMODULES.
ORA-00001: unique constraint (SCOTT.SYS_C0065451) violated
Table SUBMODULES:
  2 Rows successfully loaded.
  2 Rows not loaded due to data errors.
  3 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.
Space allocated for bind array:                 199744 bytes(64 rows)
Read   buffer bytes: 1048576
Total logical records skipped:          1
Total logical records read:             7
Total logical records rejected:         2
Total logical records discarded:        3
Run began on Fri Jul 01 12:32:28 2011
Run ended on Fri Jul 01 12:32:28 2011
Elapsed time was:     00:00:00.12
CPU time was:         00:00:00.03

57 rows selected.







Previous Topic: Using Sql Loader issue
Next Topic: Checking column value in SQL Loader
Goto Forum:
  


Current Time: Sat Feb 27 00:51:01 CST 2021