OraFAQ Forum: Server Utilities » Problem Loading Data into Table- Tow fields appended & Converted into DateTime

Home » RDBMS Server » Server Utilities » Problem Loading Data into Table- Tow fields appended & Converted into DateTime

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

Problem Loading Data into Table- Tow fields appended & Converted into DateTime [message #520057]

Thu, 18 August 2011 06:12

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

Attachment: DESINT.csv.CTL
(Size: 2.81KB, Downloaded 2089 times)

Report message to a moderator

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

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

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

Report message to a moderator

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

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 10 22:02:35 CST 2025