Hi Tom. I have tried it a lot before asking here.
I need to extract the table name and amount of records loaded and rejected per table using the sqlloader log.
The hard case is to get it when we load to mulitple tables using 1 CTL file
example
Table CDS_OWNER.TABLE_R1: 222455 Rows successfully loaded.
0 Rows not loaded due to data errors.
1229074 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Table CDS_OWNER.WMI_SEC:
202547 Rows successfully loaded.
0 Rows not loaded due to data errors.
1248982 Rows not loaded because all WHEN clauses were failed.
1248982 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Table CDS_OWNER.Table_random_hr_4:
952092 Rows successfully loaded.
0 Rows not loaded due to data errors.
499437 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Table CDS_OWNER.Table_random_4:
74373 Rows successfully loaded.
0 Rows not loaded due to data errors.
1377156 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Table CDS_OWNER.Table_random_5:
62 Rows successfully loaded.
0 Rows not loaded due to data errors.
1451467 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 11920000 bytes(10000 rows)
Read buffer bytes:60485760
Total logical records skipped: 0
Total logical records read: 1451529
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Mon Jan 02 16:47:04 2017
Run ended on Mon Jan 02 16:47:42 2017
Elapsed time was: 00:00:38.17
Elapsed time was: 00:00:38.17
CPU time was: 00:00:32.61
The result should be table name, amount of records loaded, amount of no loaded
I have tried using SUBSTR,INSTR. it's too hard since the table names are dynamic for each load.
I really appreciate the help
thanks
Yaniv
OK, I've put that data into a flat file and did this:
SQL> CREATE TABLE sqlldr_log (
2 msg varchar2(200)
3 )
4 ORGANIZATION EXTERNAL (
5 TYPE ORACLE_LOADER
6 DEFAULT DIRECTORY temp
7 ACCESS PARAMETERS (
8 RECORDS DELIMITED BY NEWLINE
9 FIELDS
10 (
11 msg position(1:200)
12 )
13 )
14 LOCATION ('sqlldr.log')
15 )
16 REJECT LIMIT UNLIMITED;
Table created.
SQL>
SQL> select * from sqlldr_log;
MSG
-------------------------------------------------------------------------------------
Table CDS_OWNER.TABLE_R1: 222455 Rows successfully loaded.
0 Rows not loaded due to data errors.
1229074 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Table CDS_OWNER.WMI_SEC:
202547 Rows successfully loaded.
0 Rows not loaded due to data errors.
1248982 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Table CDS_OWNER.Table_random_hr_4:
952092 Rows successfully loaded.
0 Rows not loaded due to data errors.
499437 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Table CDS_OWNER.Table_random_4:
74373 Rows successfully loaded.
0 Rows not loaded due to data errors.
1377156 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Table CDS_OWNER.Table_random_5:
62 Rows successfully loaded.
0 Rows not loaded due to data errors.
1451467 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 11920000 bytes(10000 rows)
Read buffer bytes:60485760
Total logical records skipped: 0
Total logical records read: 1451529
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Mon Jan 02 16:47:04 2017
Run ended on Mon Jan 02 16:47:42 2017
Elapsed time was: 00:00:38.17
Elapsed time was: 00:00:38.17
CPU time was: 00:00:32.61
35 rows selected.
Now I'll extract the rows I'm interested in
SQL> with base_data as (
2 select
3 rownum r
4 ,substr(msg,1,50)
5 ,case when msg like 'Table%' then substr(msg,7,instr(msg,':')-7) end tname
6 ,case when msg like ' %Rows%successfully%' then substr(msg,3,instr(msg,'Rows')-4) end loaded
7 ,case when msg like ' %Rows%data errors%' then substr(msg,3,instr(msg,'Rows')-4) end errs_data
8 ,case when msg like ' %Rows%all WHEN cla%' then substr(msg,3,instr(msg,'Rows')-4) end errs_when
9 ,case when msg like ' %Rows%were null%' then substr(msg,3,instr(msg,'Rows')-4) end errs_null
10 from sqlldr_log
11 where msg like 'Table%' or msg like ' %Rows%'
12 )
13 select * from base_data;
R SUBSTR(MSG,1,50) TNAME LOADED ERRS_DATA ERRS_WHEN ERRS_NULL
---------- -------------------------------------------------- ------------------------------ ------------ ------------ ------------ ---------
1 Table CDS_OWNER.TABLE_R1: 222455 Rows successfully CDS_OWNER.TABLE_R1
2 0 Rows not loaded due to data errors. 0
3 1229074 Rows not loaded because all WHEN clauses 1229074
4 0 Rows not loaded because all fields were null. 0
5 Table CDS_OWNER.WMI_SEC: CDS_OWNER.WMI_SEC
6 202547 Rows successfully loaded. 202547
7 0 Rows not loaded due to data errors. 0
8 1248982 Rows not loaded because all WHEN clauses 1248982
9 0 Rows not loaded because all fields were null. 0
10 Table CDS_OWNER.Table_random_hr_4: CDS_OWNER.Table_random_hr_4
11 952092 Rows successfully loaded. 952092
12 0 Rows not loaded due to data errors. 0
13 499437 Rows not loaded because all WHEN clauses 499437
14 0 Rows not loaded because all fields were null. 0
15 Table CDS_OWNER.Table_random_4: CDS_OWNER.Table_random_4
16 74373 Rows successfully loaded. 74373
17 0 Rows not loaded due to data errors. 0
18 1377156 Rows not loaded because all WHEN clauses 1377156
19 0 Rows not loaded because all fields were null. 0
20 Table CDS_OWNER.Table_random_5: CDS_OWNER.Table_random_5
21 62 Rows successfully loaded. 62
22 0 Rows not loaded due to data errors. 0
23 1451467 Rows not loaded because all WHEN clauses 1451467
24 0 Rows not loaded because all fields were null. 0
24 rows selected.
And with that, I can use some analytics and a group by to bring it all together
SQL> with base_data as (
2 select
3 rownum r
4 ,substr(msg,1,50)
5 ,case when msg like 'Table%' then substr(msg,7,instr(msg,':')-7) end tname
6 ,case when msg like ' %Rows%successfully%' then substr(msg,3,instr(msg,'Rows')-4) end loaded
7 ,case when msg like ' %Rows%data errors%' then substr(msg,3,instr(msg,'Rows')-4) end errs_data
8 ,case when msg like ' %Rows%all WHEN cla%' then substr(msg,3,instr(msg,'Rows')-4) end errs_when
9 ,case when msg like ' %Rows%were null%' then substr(msg,3,instr(msg,'Rows')-4) end errs_null
10 from sqlldr_log
11 where msg like 'Table%' or msg like ' %Rows%'
12 ), pad_table_name as
13 (
14 select
15 last_value(tname ignore nulls) over ( order by r ) as tname,
16 loaded,
17 errs_data,
18 errs_when,
19 errs_null
20 from base_data
21 )
22 select
23 tname,
24 max(loaded) loaded,
25 max(errs_data) errs_data,
26 max(errs_when) errs_when,
27 max(errs_null) errs_null
28 from pad_table_name
29 where loaded is not null
30 or errs_data is not null
31 or errs_when is not null
32 or errs_null is not null
33 group by tname
34 order by 1;
TNAME LOADED ERRS_DATA ERRS_WHEN ERRS_NULL
------------------------------ ------------ ------------ ------------ ------------
CDS_OWNER.TABLE_R1 0 1229074 0
CDS_OWNER.Table_random_4 74373 0 1377156 0
CDS_OWNER.Table_random_5 62 0 1451467 0
CDS_OWNER.Table_random_hr_4 952092 0 499437 0
CDS_OWNER.WMI_SEC 202547 0 1248982 0