Shell Programming and Scripting

This is the sample sqlldr log:

------------------------------------------------------------------------------------------------------------

SQL*Loader: Release 9.2.0.7.0 - Production on Sun Feb 8 23:37:02 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Control File: /home/oracle9/dba_area/billed_recurr_charges.ctl
Data File: /tmw/oradata12/bill_recc_charges_bp01_semenanjung.data
Bad File: /tmw/oradata18/bad/bill_recc_charges_bp01_semenanjung.data.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table CRISPADM.BILLED_RECURR_CHARGES, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
SERVICE_NUM 1:16 16 CHARACTER
BILL_DATE 17:24 8 DATE YYYYMMDD
NULL if BILL_DATE = 0X3030303030303030(character '00000000')
SEF_CODE 25:28 4 CHARACTER
ACCOUNT_NUM 29:41 13 CHARACTER
BILL_PERIOD 42:43 2 CHARACTER
SERVICE_TYPE 44:46 3 CHARACTER
CHARGE_AMT 47:61 15 CHARACTER
CHARGE_RATE 62:76 15 CHARACTER
CHARGE_QTY 77:91 15 CHARACTER
EQUIPMENT_OWNERSHIP_CODE 92:92 1 CHARACTER
CHARGE_EXEMPTION_IND 93:93 1 CHARACTER
SOURCE CONSTANT
Value is 'CASS'

Record 719882: Rejected - Error on table CRISPADM.BILLED_RECURR_CHARGES.
ORA-01400: cannot insert NULL into ("CRISPADM"."BILLED_RECURR_CHARGES"."SEF_CODE")


Table CRISPADM.BILLED_RECURR_CHARGES:
1722967 Rows successfully loaded.
1 Row not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.


Space allocated for bind array: 8128 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 1722968
Total logical records rejected: 1
Total logical records discarded: 0

Run began on Sun Feb 08 23:37:02 2009
Run ended on Sun Feb 08 23:42:19 2009

Elapsed time was: 00:05:17.27
CPU time was: 00:01:30.26

------------------------------------------------------------------------------------------------------------

So, I need to ouput the bolded info into a table:

Code:

insert into PROCEDURE_TRACKING_LOG
(procedure_name, table_name, rows_inserted, rows_updated, rows_deleted, rows_inserted_of_deletion, 
rows_inserted_after_deletion, rows_rejected, start_time, end_time, elapse_time, remarks, subject_area)
values
(proc_name,table_name,row_insert,NULL,NULL,NULL,
NULL,row_reject,start_time,sysdate,elapsed_time,v_ErrorText,'NETWORK INVENTORY');

whereby

Code:

proc_name = 'the filename which runs the sqlldr'
table_name = 'BILLED_RECURR_CHARGES'
row_insert = 1722967
row_reject = 1
start_time = 'Sun Feb 08 23:37:02 2009'
sysdate = 'Sun Feb 08 23:42:19 2009'
elapsed_time = 317 (after conversion to rounded seconds)
v_ErrorText = 'ORA-01400: cannot insert NULL into ("CRISPADM"."BILLED_RECURR_CHARGES"."SEF_CODE")'

Please help me.

Thank you very much.

All your help are much appreciated..