SQL*LOADER Control script with System Date input

[wilsonk]

Good day.

I've inherited a load process that uploads data into our Oracle database (unix platform). The control scripts (.ctl) require that I manually edit the date field (type constant) each time so that I can have today's date associated with the uploaded data. The date is not part of the upload file, so I am looking for a way to have the control file upload the system date, eliminating the need for maintaining the control file. This seems like a simple task to this novice script writer, but I'm having trouble confirming the syntax.

Any suggestions are greatly appreciated.

Thanks!

 

[THoey]

I haven't tried it with the system date, but the following CTL file uses a constant:

options(errors=10)
load data -- Keywords to start SQL Loader
infile '/tmponly/sqlload/DATA/sat_xref.dat' -- Import file and path
into table AGENT_XREF TRUNCATE -- Table to import into
(
AGENT_ID position(01:04) CHAR,
AGENT_NAME position(05:24) CHAR "INITCAPAGENT_NAME)",
DIALER_ID position(25:31) CHAR "LOWERDIALER_ID)",
SUPERVISOR position(32:50) CHAR "INITCAPSUPERVISOR)",
WORK_TYPE position(60:62) CHAR,
CENTER CONSTANT 'SAT'
)

Can you try to run it with instead of 'SAT' put SYSDATE()? Might have to put it in double quotes like the INITCAP() and LOWER() functions. Not sure if it will work or not, but it is worth a try... Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[karluk]

This issue is discussed in the SQL*Loader documentation. The syntax

column_name SYSDATE

in your SQL*Loader control file will put the current sysdate into a table column. Note that there are some requirements about datatype, and the fact that sysdate will change periodically during the course of a load. If this is unacceptable, you probably will have to continue with your current method.

The following is an excerpt from chapter 5, SQL*Loader Control File Reference, from the Oracle 8i utilities manual.

"The database column must be of type CHAR or DATE. If the column is of type CHAR, then the date is loaded in the form 'dd-mon-yy.' After the load, it can be accessed only in that form. If the system date is loaded into a DATE column, then it can be accessed in a variety of forms that include the time and the date."

"A new system date/time is used for each array of records inserted in a conventional path load and for each block of records loaded during a direct path load."

 

[wilsonk]

Thank you for the information. I'm going to give these ideas a try and locate a copy of the 8i utilities manual.

Again,

Thanks!

 

[THoey]

Thanks also Karluk. I was just throwing a guess out there w/o opening a manual... Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...