May 26th, 2005, 11:31 AM
|
|
|
That' usually not the standard solution to this problem- the main reason is that if you have a many records the insert file size becomes enormous. Anyway most of the file is taken up with sql statements, not data.
try sqlldr, that's what it is meant for.
You need to create two files - a sql extract script, and a ctl file, then run sqlldr
example:
Code:
/* sql script to extract a table into a | delimited format */
set pagesize 0
set linesize 2000
set heading off
set trimspool off
set echo off
set tab off
set colsep |
spool textract_mytable.txt
SELECT
CUST_CODE,
PREM_CODE,
CHARGE_DATE,
AR_TRANS,
PRIORITY,
SCAT_CODE,
SRAT_CODE,
DUE_DATE,
REVENUE_IND
FROM
MYTABLE;
spool off
This is the control file textract_mytable.ctl:
Code:
OPTIONS (DIRECT=false)
LOAD DATA
INFILE 'textract_mytable.txt'
APPEND
INTO TABLE uimsmgr.uabopen
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
CUST_CODE,
PREM_CODE,
CHARGE_DATE,
AR_TRANS,
PRIORITY,
SCAT_CODE,
SRAT_CODE,
DUE_DATE,
REVENUE_IND
)
create the ctl file, run the sql script, then :
Code:
sqlldr USER/PASSWORD control=textract_mytable.ctl errors=200 log=textract_mytable.log
|
Dev Shed Forums Sponsor:
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
