| Data Loading into a table with Identity type column(Sequence generator used) Direct path [message #166471] |
Thu, 06 April 2006 08:46  |
nimi_patels
Messages: 6
Registered: April 2006
Location: NY
|
Junior Member |
|
|
I am trying to implement a Direct Path SQL Loader to load a '|' seperated .txt file into a table in which the primary key is populated thru a sequence generator. I understand that the sequence gen is not supported by Direct Path. Is there a solution/alternative to this? I want to use Direct and not conventional as the volumne is LARGE. The target table will be empty when I load.
The load works great if I did it the conventional way - FYI
Any suggestions to optimize and boost the load performance are welcome.
Here's the contents of control file
LOAD DATA
INFILE 'c:\extract.txt'
BADFILE 'c:\extract.bad'
DISCARDFILE 'c:\extract.dsc'
APPEND
INTO TABLE PA0101.ALERT_MESSAGE
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(
SRC_SYS_CD CONSTANT 'NB',
MKTR_NO CHAR,
GO_CD CHAR,
ALERT_DT INTEGER EXTERNAL,
ALERT_TYPE_CD CONSTANT "NB",
READ_IR CONSTANT "N",
POL_NO CHAR NULLIF POL_NO=BLANKS,
INSD_NM CHAR NULLIF INSD_NM=BLANKS,
CASE_STATUS_TX CHAR NULLIF CASE_STATUS_TX=BLANKS,
SYSTEM_TIMESTAMP INTEGER EXTERNAL,
PRIORITY_CD CONSTANT 1,
CATGY_CD CHAR NULLIF CATGY_CD=BLANKS,
ALERT_SUBJECT_TX CHAR(256) NULLIF ALERT_SUBJECT_TX=BLANKS,
PLAN_TYPE CHAR(32) NULLIF PLAN_TYPE=BLANKS,
ALERT_DETAIL_TX CHAR(155000) NULLIF ALERT_DETAIL_TX=BLANKS,
SEQUENCE_NO "PA0101.S_ALERT_MESSAGE.nextval"
)
Below is the SQL LDR load statement:
sqlldr pa01011/alerts_load@ORAM11.WORLD control=C:\extract.ctl DIRECT=true log=c:\extract.log errors=5000
Attached is the .txt data file.
Thank You all.
-
Attachment: extract.txt
(Size: 4.24KB, Downloaded 1401 times)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
