Use SQLLDR to load control files of date type

Before the restoration of a unit in Shandong to restore the Oracle database, the data contained in the date type, at that time to provide customers with the Sqlldr way, because the volume of data is larger, with sqlldr loaded faster, so the use of this way, the result is also in the loading error, The solution is put out, make a summary.
The data is this:
|111| | lf0003.lob| |03-jun-2007 AD 00:00:00|
|222| | lf0004.lob| |01-jun-2007 AD 00:00:00|
The control file is written like this:
Load data
InFile ' Dump001.dat '
Insert
into table "BTGL". " Lobdemo "
("AA01" CHAR (3) enclosed by X ' 7C ',
Lobfile_col1 FILLER CHAR,
"AA02" Lobfile (lobfile_col1) TERMINATED by EOF nullif lobfile_col1 = ' NONE ',
"AA03" DATE "dd-mon-yyyy AD HH24:MI:SS" enclosed by X ' 7C ')
Result hint Error:

Sql*loader:release 9.0.1.1.1-production on Thu June 7 12:43:45 2007

(c) Copyright 2001 Oracle Corporation. All rights reserved.

Control file: Dump001.ctl
Data file: Dump001.dat
Error file: Dump001.bad
Obsolete file: Not specified
:
(All records can be discarded)

Load count: All
Skip: 0
Allowed Errors: 50
Bound array: 64 rows, maximum 256000 bytes
Continue: Not Specified
Path used: General

Table "BTGL". " Lobdemo "
Loaded from each logical record
Insert option takes effect on this table insert

Column name location length abort wrapper data type
------------------------------ ---------- ----- ---- ---- ---------------------
"AA01" First 3 | CHARACTER
Lobfile_col1 NEXT 1 CHARACTER
(FILLER FIELD)
"AA02" DERIVED * EOF CHARACTER
Dynamic Lobfile. The file name is in the field lobfile_col1
NULL if Lobfile_col1 =0x4e4f4e45 (character ' NONE ')
"AA03" NEXT * | DATE dd-mon-yyyy AD HH24:MI:SS

Record 1: Rejected-table "BTGL". " Lobdemo "column" AA03 "error occurred.
ORA-01858: Non-numeric characters are found at the required input number

Record 2: Rejected-table "BTGL". " Lobdemo "column" AA03 "error occurred.
ORA-01858: Non-numeric characters are found at the required input number

Record 3: Rejected-table "BTGL". " Lobdemo "column" AA03 "error occurred.
ORA-01858: Non-numeric characters are found at the required input number

Record 4: Rejected-table "BTGL". " Lobdemo "column" AA03 "error occurred.
ORA-01858: Non-numeric characters are found at the required input number

Table "BTGL". " Lobdemo ":
0 Rows loaded successfully
4 rows were not loaded due to data errors.
0 rows were not loaded because all when clauses failed.
Because all fields are empty, 0 rows are not loaded.

Space allocated for associative arrays: 17152 bytes (64 rows)
Read Buffer bytes: 1048576

Total number of logical records skipped: 0
Total Logical Records READ: 4
Total number of logical records rejected: 4
Total logical records discarded: 0

From Thursday June 07 12:43:45 2007 Start Running
At Thursday June 07 12:43:45 2007 Run End

Elapsed time: 00:00:00.15
CPU Time: 00:00:00.05

---------Data Recovery Oracle Database Recovery Expert 13352468096 qq:9417901 website: http://www.sosdb.com-----

There's a problem with the CTL file.

Modify the CTL file to:
Load data
InFile ' Dump001.dat '
Insert
into table "BTGL". " Lobdemo "
Fields TERMINATED by "" Optionally enclosed by ' | '
TRAILING Nullcols
("AA01" CHAR (3),
Lobfile_col1 FILLER CHAR,
"AA02" Lobfile (lobfile_col1) TERMINATED by EOF nullif lobfile_col1 = ' NONE ',
"AA03" DATE "dd-mon-yyyy AD HH24:MI:SS")

Then run, appear:

Column name location length abort wrapper data type
------------------------------ ---------- ----- ---- ---- ---------------------
"AA01" first 3 WHT O (|) CHARACTER
Lobfile_col1 NEXT * WHT O (|) CHARACTER
(FILLER FIELD)
"AA02" DERIVED * EOF CHARACTER
Dynamic Lobfile. The file name is in the field lobfile_col1
NULL if Lobfile_col1 =0x4e4f4e45 (character ' NONE ')
"AA03" NEXT * WHT O (|) DATE dd-mon-yyyy AD HH24:MI:SS

Record 1: Rejected-table "BTGL". " Lobdemo "column" AA03 "error occurred.
ORA-01843: Invalid month

Record 2: Rejected-table "BTGL". " Lobdemo "column" AA03 "error occurred.
ORA-01843: Invalid month

Record 3: Rejected-table "BTGL". " Lobdemo "column" AA03 "error occurred.
ORA-01843: Invalid month

Record 4: Rejected-table "BTGL". " Lobdemo "column" AA03 "error occurred.
ORA-01843: Invalid month

Table "BTGL". " Lobdemo ":
0 Rows loaded successfully
4 rows were not loaded due to data errors.
0 rows were not loaded because all when clauses failed.
Because all fields are empty, 0 rows are not loaded.

---------Data Recovery Oracle Database Recovery Expert 13352468096 qq:9417901 website: http://www.sosdb.com-----

You find the issue as a date format, and then you set Nls_lang to resolve the issue.
C:\>set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

C:\>sqlldr Btgl/pass Control=dump002.ctl Direct=true

Check to see that the data is fine.

Problem solving

[Align=right] [Color= #000066] [This post has been edited by admin on 2008-6-16 16:32:28] [/color] [/align]---------Data Recovery Oracle Database Recovery Expert 13352468096 qq:9417901 website: http://www.sosdb.com-----
Examples of using database sequence in Sqlldr:
In the first example, all of the fields is located in the datafile based on position, which makes this easier. Another example below covers data is comma delimited.

We want to load the data into the following table:
sql> CREATE TABLE load_db_seq_positional
2 (seq_number number,
3 data1 number,
4 data2 CHAR (15));

We'll use the following sequence:
sql> CREATE SEQUENCE Db_seq
2 START with 1
3 INCREMENT by 1;

The control file would look like:
LOAD DATA
INFILE *
Into TABLE load_db_seq_positional
(Seq_number "Db_seq.nextval",
Data1 POSITION (1:5),
Data2 POSITION (6:15),
)
Begindata
11111AAAAAAAAAA
22222BBBBBBBBBB

After we run Sql*loader, we have table data looks like:
Sql> SELECT * from load_db_seq_positional;
Seq_number DATA1 DATA2
---------- ---------- ---------------
1 11111 aaaaaaaaaa
2 22222 bbbbbbbbbb---------Data Recovery Oracle Database Recovery Expert 13352468096 qq:9417901 website: http://www.sosdb.com-----

Http://www.sosdb.com/tech/oracle/77bbs1.htm

Use SQLLDR to load control files of date type