Constant date SQL Loader 1

[Matsul]

I need to load a date as a constant into a table.

I tried;

LOAD DATA
APPEND INTO TABLE "HLR_OWNER"."FOR"
FIELDS TERMINATED BY ','
(MS,
IM,
Filedate "to_date('20090309','YYYYMMDD')"
)

but I get;

Record 1: Rejected - Error on table "FOR", column FILEDATE.
Column not found before end of logical record (use TRAILING NULLCOLS)

Any clues ?

thx

 

[SantaMufasa]

Matsul,

I infer from your post that your table, HLR_OWNER.FOR, already exists with all the rows that you want updated with the literal date of March 9, 2009? Is my presumption valid?

If so, then you don't want to use SQL*Loader to populate the column...you want SQL*Plus:

update hlr_owner set filedate = to_date('20090309','YYYYMMDD');

If my presumption is incorrect, then an alternative is to load your data, then execute the above SQL UPDATE command.

Let us know your outcome.

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.”

 

[Matsul]

Hi Mufasa,

the thing is I have to load data from files having a date in the file name (MM_20090309_EXEC_2.csv) and need to have the date in the name as one of the entries.

So I used sed in Unix to change the controlfile to the date and need to load hope to load it as a constant.


I have a controlfile having;

Filedate "to_date('##FILEDATE##', 'YYYYMMDD')",

and use

cat org.ctl| sed "s/##FILEDATE##/$SCDATE/g" > $TMP/onl.ctl

and end up with the;

Filedate "to_date('20090309','YYYYMMDD')"

in the controlfile which is not working.

thanks,
Matt

 

[LKBrwnDBA]

Try:

LOAD DATA
-- Etc...
Filedate SYSDATE
)

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Matsul]

That would be the sysdate, while I need a constant which is a date.

thx,

Matt

 

[LKBrwnDBA]

Then try this:

LOAD DATA
-- Etc...
Filedate EXPRESSION "To_Date('##FILEDATE##','YYYYMMDD'"
)

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb