Home » RDBMS Server » Server Utilities » Concatenate timestamp to a constant value in a control file (SQL Loader Release 10.2.0.2, oracle )
Concatenate timestamp to a constant value in a control file [message #557536] Wed, 13 June 2012 10:13 Go to next message
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 #557539 is a reply to message #557536] Wed, 13 June 2012 10:25 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
>Any suggestion on how the timestamp to a filename can be appended?
control file is plain OS text file.
plain OS text files are manipulated using OS utilities.
since you neglected to follow Posting Guidelines & post OS name & version, no additional assistance is possible now.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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 Go to previous messageGo to next message
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 #557546 is a reply to message #557543] Wed, 13 June 2012 11:50 Go to previous messageGo to next message
concorde800
Messages: 52
Registered: May 2007
Member
Thanks for the pointer, this works, was thinking the EXPRESSION would work.
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 Go to previous message
Barbara Boehmer
Messages: 8922
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)"
Previous Topic: Export data using expdp to remote host
Next Topic: Skip Last Line in External table
Goto Forum:
  


Current Time: Tue Sep 29 15:51:12 CDT 2020