| Concatenate timestamp to a constant value in a control file [message #557536] |
Wed, 13 June 2012 10:13  |
concorde800
Messages: 52
Registered: May 2007
|
Member |
|
|
I am loading data using sqlldr command in unix to an oracle table and want to concatenate timestamp to a filename in the
"create_file_name" column in the code below.
I have the below code within the control file..
LOAD DATA
TRUNCATE
INTO TABLE TABLEA
TRAILING NULLCOLS
(
file_type POSITION(1:5) CHAR,
business_date POSITION(16:23) DATE "YYYYMMDD",
create_file_name "FILE_NAME" EXPRESSION "SELECT TO_CHAR(CURRENT_TIMESTAMP(3), 'YYYYMMDDHH24MISS') FROM DUAL")
The load fails with SQL Loader error: "Expecting valid column specification, ",", ")", found keyword EXPRESSION found instead of column. Any suggestion on how the timestamp to a filename can be appended?
[mod-edit: code tags fixed by bb; last tag was missing the / ]
[Updated on: Wed, 13 June 2012 10:36] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Concatenate timestamp to a constant value in a control file [message #557542 is a reply to message #557536] |
Wed, 13 June 2012 10:51  |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
It would have helped if you had provided a few rows of sample data, create table statement, and desired results. Please do so in the future, as I have done in the example below.
-- test.dat:
ABCDE..........20120611
EDCBA..........20120612
-- test.ctl:
LOAD DATA
TRUNCATE
INTO TABLE TABLEA
TRAILING NULLCOLS
(
file_type POSITION(1:5) CHAR,
business_date POSITION(16:23) DATE "YYYYMMDD",
create_file_name "'FILE_NAME' || TO_CHAR(CURRENT_TIMESTAMP(3), 'YYYYMMDDHH24MISS')")
-- table, load, and results:
SCOTT@orcl_11gR2> CREATE TABLE tablea
2 (file_type VARCHAR2(9),
3 business_date DATE,
4 create_file_name VARCHAR2(23))
5 /
Table created.
SCOTT@orcl_11gR2> HOST SQLLDR scott/tiger DATA=test.dat CONTROL=test.ctl LOG=test.log
SCOTT@orcl_11gR2> COLUMN business_date FORMAT A13
SCOTT@orcl_11gR2> SELECT * FROM tablea
2 /
FILE_TYPE BUSINESS_DATE CREATE_FILE_NAME
--------- ------------- -----------------------
ABCDE 11-JUN-12 FILE_NAME20120613084720
EDCBA 12-JUN-12 FILE_NAME20120613084720
2 rows selected.
|
|
|
|
| Re: Concatenate timestamp to a constant value in a control file [message #557543 is a reply to message #557539] |
Wed, 13 June 2012 10:51 |
concorde800
Messages: 52
Registered: May 2007
|
Member |
|
|
Linux x205 2.6.5-7.244-bigsmp
SQL loader 10.2.0.2
Oracle 11.2.0.2
Below is the screen shot of the error
{code}
SQL*Loader: Release 10.2.0.2.0 - Production on Wed Jun 13 11:11:50 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL*Loader-350: Syntax error at line 72.
Expecting valid column specification, "," or ")", found keyword expression.
create_file_name "FILE_NAME" EXPRESSION "SELECT TO_CHAR
{code}
|
|
|
|
|
|
| Re: Concatenate timestamp to a constant value in a control file [message #557547 is a reply to message #557546] |
Wed, 13 June 2012 13:23 |
|
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You could use EXPRESSION like below. Notice that there are parentheses around the entire select statement. However, I prefer the shorter syntax. I might use the EXPRESSION if I have to select from another table or some such thing where I could not use the shorter syntax without creating a user-defined function.
create_file_name EXPRESSION "(SELECT 'FILE_NAME' || TO_CHAR(CURRENT_TIMESTAMP(3), 'YYYYMMDDHH24MISS') FROM DUAL)"
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
