SQL* Loader problem with timestamp data

SQL* Loader problem with timestamp data

Post by Marco Mapel » Fri, 26 Oct 2001 04:06:52



Hello all,

I am desperately trying to load timestamp data into
Oracle 8i Rel. 8.1.6 under Linux Red Hat 6.1.
The timestamp field has the following format :

dd-mon-yyyy hh:mi:ss

example :

12-DEC-1999 12:14:54

I tried to specify the field in the control file as
both CHAR and DATE with various format with no success.
When I take the time part of the date off I can successfully
load into the DB.
Any suggestion ?.

Thanks in advance

Marco Mapelli

 
 
 

SQL* Loader problem with timestamp data

Post by Sybrand Bakke » Fri, 26 Oct 2001 05:42:51



Quote:> Hello all,

> I am desperately trying to load timestamp data into
> Oracle 8i Rel. 8.1.6 under Linux Red Hat 6.1.
> The timestamp field has the following format :

> dd-mon-yyyy hh:mi:ss

> example :

> 12-DEC-1999 12:14:54

> I tried to specify the field in the control file as
> both CHAR and DATE with various format with no success.
> When I take the time part of the date off I can successfully
> load into the DB.
> Any suggestion ?.

> Thanks in advance

> Marco Mapelli

Please check in the Oracle Server Utilites command you can specify a mask
with the date datatype in sql*loader

Hth,

Sybrand Bakker
Senior Oracle DBA

 
 
 

SQL* Loader problem with timestamp data

Post by Mark D Powe » Fri, 26 Oct 2001 22:04:38





> > Hello all,

> > I am desperately trying to load timestamp data into
> > Oracle 8i Rel. 8.1.6 under Linux Red Hat 6.1.
> > The timestamp field has the following format :

> > dd-mon-yyyy hh:mi:ss

> > example :

> > 12-DEC-1999 12:14:54

> > I tried to specify the field in the control file as
> > both CHAR and DATE with various format with no success.
> > When I take the time part of the date off I can successfully
> > load into the DB.
> > Any suggestion ?.

> > Thanks in advance

> > Marco Mapelli

> Please check in the Oracle Server Utilites command you can specify a mask
> with the date datatype in sql*loader

> Hth,

> Sybrand Bakker
> Senior Oracle DBA

Marco, please post the sqlldr control statements along with one line
of data so that we can see what you are doing and perhaps someone will
spot the problem and point it out.

-- Mark D Powell --

 
 
 

SQL* Loader problem with timestamp data

Post by Marco Mapel » Tue, 30 Oct 2001 05:15:47






> > > Hello all,

> > > I am desperately trying to load timestamp data into
> > > Oracle 8i Rel. 8.1.6 under Linux Red Hat 6.1.
> > > The timestamp field has the following format :

> > > dd-mon-yyyy hh:mi:ss

> > > example :

> > > 12-DEC-1999 12:14:54

> > > I tried to specify the field in the control file as
> > > both CHAR and DATE with various format with no success.
> > > When I take the time part of the date off I can successfully
> > > load into the DB.
> > > Any suggestion ?.

> > > Thanks in advance

> > > Marco Mapelli

> > Please check in the Oracle Server Utilites command you can specify a mask
> > with the date datatype in sql*loader

> > Hth,

> > Sybrand Bakker
> > Senior Oracle DBA

> Marco, please post the sqlldr control statements along with one line
> of data so that we can see what you are doing and perhaps someone will
> spot the problem and point it out.

> -- Mark D Powell --

I changes the date mask as suggested by Sybrand to DD-MON-YYYY HH24:MI:SS
in control file and I finally succedded.

Thanks again and regards

Marco Mapelli

 
 
 

1. Problem loading to Timestamp column using SQL Loader

Hello ,

I have data to be loaded to an Oracle table. The data is in a flat
file and this data has been unloaded from an Informix database. The
loading is fine but for one problem. The 'time' part of the TimeStamp
field gets the value of 00:00:00 though there is a valid time in the
DateTime field.

The definition in the control file for this column is -
BASETS "to_date(:BASETS,'YYYY-MM-DD HH24:MI:SS')"

The data for this column is -
2002-11-10 09:00:48

So, what could be the problem? any pointers? the 'date' portion of the
field is loaded corrctly, but the 'time' portion is not.

Thanx in advance for your help.

2. Is There Something Better Than AOL?

3. SQL Loader error: SQL*Loader-282

4. Bringing back the right row Description values

5. SQL timestamp data type - correspondency with c++ data type

6. problems with timestamp data type

7. UDB 5.2 JDBC timestamp format different from java.sql.timestamp

8. SQL*Loader and Long data types

9. Using SQL*Loader to normalize data during load...

10. Unix Data Export Script for SQL Loader?

11. SQL loader - SKIP FIELD + ADD DATA