Hi guys
Looking for a bit of help.
Ive got a number of sql loader files Im trying to use on an Oracle 11 database.
Some work perfectly, others are creating zero rows and I've no idea why.
Here is an example :
Table
SQL> desc src_braccnt_d
Name Type Nullable Default Comments
------------- ------------ -------- ------------ --------
NO VARCHAR2(9) Y
CODE VARCHAR2(5) Y
LOCATION VARCHAR2(30) Y
ADD1 VARCHAR2(40) Y
ADD2 VARCHAR2(40) Y
ADD3 VARCHAR2(40) Y
ADD4 VARCHAR2(40) Y
ADD5 VARCHAR2(40) Y
ADD6 VARCHAR2(40) Y
ADD7 VARCHAR2(40) Y
CUSTODIAN VARCHAR2(40) Y
MIG_ID NUMBER Y
MIG_RUN_ID NUMBER Y
MIG_TIMESTAMP TIMESTAMP(6) SYSTIMESTAMP
MIG_STATUS VARCHAR2(10) Y 'NEW'
src_braccnt_d.ctl Control file
OPTIONS (SKIP = 1)
LOAD DATA
INFILE 'src_braccnt_d.dat' "str '¬¬'" BADFILE 'src_braccnt_d.bad' DISCARDFILE 'src_braccnt_d.dsc'
APPEND INTO TABLE src_braccnt_d
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
no CHAR,
code CHAR,
location CHAR,
add1 CHAR,
add2 CHAR,
add3 CHAR,
add4 CHAR,
add5 CHAR,
add6 CHAR,
add7 CHAR,
custodian CHAR,
mig_id INTEGER EXTERNAL "mig_id.nextval",
mig_run_id INTEGER EXTERNAL "pp_mig.seq_val('MIG_RUN_ID')"
)
{code}
src_braccnt_d.dat Data file
{code}
NO|CODE|LOCATION|ADD1|ADD2|ADD3|ADD4|ADD5|ADD6|ADD7|CUSTODIAN¬¬
2|CP-02|call. |35 Kris Road||New boston|NH|USA|sing he'd next out it's in a i smoke. ||sing. ¬¬
6|KB-01|it and you sit all been. |6 Pitt Ave||Chirignago||Italy|i'm killing the put. ||and you're a good. ¬¬
{code}
src_braccnt_d.log Log file
{code}
SQL*Loader: Release 11.1.0.7.0 - Production on Fri Oct 29 13:17:49 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Control File: src_braccnt_d.ctl
Data File: src_braccnt_d.dat
File processing option string: "str '¬¬'"
Bad File: src_braccnt_d.bad
Discard File: src_braccnt_d.dsc
(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 SRC_BRACCNT_D, 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
------------------------------ ---------- ----- ---- ---- ---------------------
NO FIRST * | O(") CHARACTER
CODE NEXT * | O(") CHARACTER
LOCATION NEXT * | O(") CHARACTER
ADD1 NEXT * | O(") CHARACTER
ADD2 NEXT * | O(") CHARACTER
ADD3 NEXT * | O(") CHARACTER
ADD4 NEXT * | O(") CHARACTER
ADD5 NEXT * | O(") CHARACTER
ADD6 NEXT * | O(") CHARACTER
ADD7 NEXT * | O(") CHARACTER
CUSTODIAN NEXT * | O(") CHARACTER
MIG_ID NEXT * | O(") CHARACTER
SQL string for column : "mig_id.nextval"
MIG_RUN_ID NEXT * | O(") CHARACTER
SQL string for column : "pp_mig.seq_val('MIG_RUN_ID')"
Table SRC_BRACCNT_D:
0 Rows successfully loaded.
0 Rows 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: 214656 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 1
Total logical records read: 0
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Fri Oct 29 13:17:49 2010
Run ended on Fri Oct 29 13:17:50 2010
Elapsed time was: 00:00:00.64
CPU time was: 00:00:00.05
It should skip the first row with the headers, and then insert the next 2, but nothing.
I know the functions work, as they are in the other control files Ive got working perfectly.
Any ideas ??
Baffled.
Thanks