Home » RDBMS Server » Server Utilities » Problem Loading Data into Table- Tow fields appended & Converted into DateTime
Problem Loading Data into Table- Tow fields appended & Converted into DateTime [message #520057] Thu, 18 August 2011 06:12 Go to next message
mani_jagtap
Messages: 7
Registered: May 2010
Location: glasgow
Junior Member
Hi,

Need help in SQLLoader.

I have an issue my source data file will send Data and time which are char field in two separate fields and my control file should put it in one column (DATE+TIME)

I have tried putting it in DATE_1 field using "TO_DATE (:DATE_1,'DDMMYYYY HHMISS')" but this didn't work and Date went into one column and time into other field(resulting shifting all data by one column in table)

I also tried putting data and time which are character fields in to different vchar field and then appended these two fields and convert to Data format as below but it did gave me SQL*Loader -930- Error parsing insert statement for column and ORA-00984 column not allowed here error.

DATE_1 "TO_DATE(VCHAR_24||VCHAR_25,'DDMMYYYY HHMISS')",

Please find attached control file and data file.

Thanks,
Manisha
Re: Problem Loading Data into Table- Tow fields appended & Converted into DateTime [message #520064 is a reply to message #520057] Thu, 18 August 2011 06:44 Go to previous messageGo to next message
Michel Cadot
Messages: 58856
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table t (id integer, tim date);

Table created.

SQL> host type t.ctl
LOAD DATA
INFILE *
INTO TABLE T
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  ID,
  DT1 BOUNDFILLER,
  DT2 BOUNDFILLER,
  TIM "TO_DATE(:DT1||:DT2, 'DDMMYYYYHH24MISS')"
)
BEGINDATA
1,01082011,141000,

SQL> host sqlldr michel/michel control=t.ctl

SQL*Loader: Release 10.2.0.4.0 - Production on Jeu. Ao¹t 18 13:43:21 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Commit point reached - logical record count 1

SQL> select * from t;
        ID TIM
---------- -------------------
         1 01/08/2011 14:10:00

1 row selected.

Regards
Michel
Re: Problem Loading Data into Table- Tow fields appended & Converted into DateTime [message #520070 is a reply to message #520057] Thu, 18 August 2011 07:22 Go to previous messageGo to next message
mani_jagtap
Messages: 7
Registered: May 2010
Location: glasgow
Junior Member
Thanks for Reply.

I tried solution suggested by you, but it did through syntax error at Boundfiller ,may be because my SQLLOADER version is 7.3.4.

Thanks,
Manisha
Re: Problem Loading Data into Table- Tow fields appended & Converted into DateTime [message #520072 is a reply to message #520070] Thu, 18 August 2011 07:36 Go to previous message
Michel Cadot
Messages: 58856
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is why we ask to ALWAYS post the Oracle version.
I don't know how you can do it in 7.3.4 without first storing the data into a staging table and then insert into the final one using INSERT SELECT.
Maybe Barbara which will come here in about 6-8 hours would know the solution.

Regards
Michel

[Updated on: Thu, 18 August 2011 07:37]

Report message to a moderator

Previous Topic: problem in loading data into table
Next Topic: import
Goto Forum:
  


Current Time: Thu Aug 21 21:59:13 CDT 2014

Total time taken to generate the page: 0.09265 seconds