SQLLDR and to_timestamp

[toddyl]

Hi,

I have data in the format of server, process, day, time, cpu and value which I am loading into a database table using sqlldr. The day is in the format of DD/MM/YYYY and time is HH24:MI. I am trying to introduce a new column called TIMESTMP of type TIMESTAMP so that I can build an index on this to improve performance of queries. I have been able to do this on a static table I created but when I try to adjust my sqlldr control file to load data I am getting the following error: Column not found before end of logical record (use TRAILING NULLCOLS)

My control file for sqlldr had:

append into table mytable
fields terminated by "," optionally enclosed by "'"
( server, process, cpu, day DATE 'MM/DD/YYYY', time, value)

but now that I want to load to column TIMESTMP I have:

append into table mytable
fields terminated by "," optionally enclosed by "'"
( server, process, cpu, day DATE 'MM/DD/YYYY', time, value, timestmp "(to_timestamp((day||''||time), 'MM/DD/YYYY hh24:mi'))")

I can see what the error means as my input file only has elements for server, process, cpu, time and value and does not have a timestamp field. However, there must be a way to reuse these values to populate the timestamp.

Please help,

Toddyl

 

[SantaMufasa]

Toddyl,

If your objective is to obtain an index to improve access speed, then you can create such an index without manufacturing redundant TIMESTAMP data.

Every Oracle DATE expression contains both DATE and TIME components, whether you load DATE and TIME values into the DATE data item or not. Why do you not simply SQL*Load your DATE and TIME information into a single DATE column in "mytable", then create an index on that resulting column?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Dagon]

I don't have SQL*Loader installed on my system, so I can't test it out but I think you need to put a colon in front of the field names, as in this example from the manual.

LOAD DATA
INFILE *
APPEND INTO TABLE XXX
( "Last" position(1:7) char "UPPER\"Last\")"
first position(8:15) char "UPPERfirst || :FIRST || :\"FIRST\")"
)