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  |
 |
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 #514141 is a reply to message #514041] |
Fri, 01 July 2011 14:50  |
 |
Barbara Boehmer
Messages: 9082
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.
|
|
|
|
Goto Forum:
Current Time: Tue Apr 16 04:27:27 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
