OraFAQ Forum: Server Utilities » sqlldr: Load data with timestamp datatype

Home » RDBMS Server » Server Utilities » sqlldr: Load data with timestamp datatype

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sqlldr: Load data with timestamp datatype [message #220732]

Wed, 21 February 2007 16:34

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!

Report message to a moderator

Re: sqlldr: Load data with timestamp datatype [message #220735 is a reply to message #220732]

Wed, 21 February 2007 17:05

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: sqlldr: Load data with timestamp datatype [message #220746 is a reply to message #220735]

Wed, 21 February 2007 20:40

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!

Report message to a moderator

Previous Topic:	Full Import to new database
Next Topic:	Import export utility

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jan 13 04:18:11 CST 2025