Daily Oracle

short Oracle take-away tips that you can put to use in under an hour.

Tuesday, March 28, 2006

using function and constant in sql loader

After a long break from Oracle work, I recently had to load some data from Excel into an Oracle database.

I googled for a ready made solution but the Oracle expert says the easiest is to convert the Excel file into a csv and use sql loader to load the file.

In my solution, I wanted to be able to load data files that :
1. contain ','
2. contain date columns in this format '31-12-06' (31st Dec 06)

To fulfil 1., my control file cannot use the standard fields terminated by ',' quoted in most examples.
Instead my fields will be terminated by tab.
There were no examples on the internet that I could find.
I experimented with '\t' and it worked.

Secondly, I wanted sql loader to convert '31-12-06' to 31 Dec 06 before loading, otherwise I get the invalid date error.
I needed to use a function to process the field first.

GL_DATE "to_date(:gl_date, 'dd-mm-yy')" - worked

Thirdly, I populated a column with a constant value (C)

STATEMENT_BASIS constant 'C'


My eventual control fileĀ  is like this:

load data
infile 'C:/1/sql/t.txt'
into table MY_TABLE
append
fields terminated by '\t'
trailing nullcols
(CSL_UID,
COMPANY,
RC_UID,
REPORT_CENTER_NAME,
BILL_CODE,
GROSS_WORKING_INTEREST,
PARTY_UID,
PARTY_NAME,
AMOUNT,
GL_DATE "to_date(:gl_date, 'dd-mm-yy')",
MAJOR_ACCOUNT,
LINK_INFO,
STATEMENT_BASIS constant 'C')

SQL Loader is a very useful and powerful tool.


Links

0 Comments:

Post a Comment

<< Home