Home » RDBMS Server » Server Utilities » sqlldr: Load data with timestamp datatype
sqlldr: Load data with timestamp datatype [message #220732] Wed, 21 February 2007 16:34 Go to next message
syang
Messages: 30
Registered: February 2007
Member
Hi All,

SQL*LOADER question:

1. Does anyone know how to load data with the following timestamp:

1. 2/6/2007 4:05:32.704000 PM
2. 12/14/2006 12:00:00.000000 AM

SQL> desc cbss_user_validation_log;
Name Null? Type
------------------------- -------- ----------------------------
AUTHRZG_OFCL_1ST_NAME VARCHAR2(20)
AUTHRZG_OFCL_LAST_NAME VARCHAR2(20)
AUTHRZG_OFCL_5_PSTN_SSN CHAR(5)
AUTHRZG_OFCL_BIRTH_DT DATE
SUPLR_LGL_BUSNS_NAME VARCHAR2(100)
NSC_NUM CHAR(10)
USER_VLDTN_ATMPT_DT TIMESTAMP(6)
USER_VLDTN_RSLT_CD CHAR(18)
VLDTN_ATMPT_EMAIL_ADR VARCHAR2(80)
VALIDATED_USER_ID VARCHAR2(80)

2. In the sqlldr control file, how to set up the timestamp field USER_VLDTN_ATMPT_DT:

load data
infile cbss_user_validation_log.dat
insert
into table cbsstst.cbss_user_validation_log
fields terminated by ',' optionally enclosed by '"'
TRAILING NULLCOLS
(
...
USER_VLDTN_ATMPT_DT ????,
...
)

Any idea?

Thanks!
Re: sqlldr: Load data with timestamp datatype [message #220735 is a reply to message #220732] Wed, 21 February 2007 17:05 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
While waiting to be spoonfed an answer, you could try reading the fine Utilities Manual which contains the answer to your question.
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_field_list.htm#sthref1017
Re: sqlldr: Load data with timestamp datatype [message #220746 is a reply to message #220735] Wed, 21 February 2007 20:40 Go to previous message
syang
Messages: 30
Registered: February 2007
Member
Hi All,

Just to let everyone know that I figured it out how to make this work.

The control file looks like this:

load data
infile *
insert
into table cbsstst.cbss_user_validation_log
fields terminated by ',' optionally enclosed by '"'
TRAILING NULLCOLS
(
...
USER_VLDTN_ATMPT_DT timestamp 'mm/dd/yyyy HH:MI:SS.FF6AM',
...
)

Data has been loaded successfully.

Thanks!
Previous Topic: Full Import to new database
Next Topic: Import export utility
Goto Forum:
  


Current Time: Fri Dec 02 12:38:39 CST 2016

Total time taken to generate the page: 0.14537 seconds