OraFAQ Forum: Server Utilities » concatenation using sql loader

Home » RDBMS Server » Server Utilities » concatenation using sql loader (9i,)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

concatenation using sql loader [message #310865]

Wed, 02 April 2008 12:29

riki
Messages: 12
Registered: April 2008
Location: ny

Junior Member

Friends, thanks in advance.

I get data in ',' delimitted flat file with date and time fields.Date is DATE datatype and Time is TIMESTAMP datatype..I would like to store my date field with date and time without fractional seconds and time with just the time part .
My control file looks like:
LOAD DATA
infile 'test.dat'
replace INTO TABLE TEST

FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
START_DATE DATE "dd/mm/yyyy hh24:mi:ss",
START_TIME TIME "dd/mm/yyyy hh24:mi:ss.ff1"
)

My data looks like:
03/10/2008,10:12:30.0
03/02/2008,10:15:00.9

I have to see the data as 03/10/2008 10:12:30 in the START_DATE field and 03/10/2008 10:12:30.0 in my STRAT_TIME field.

How can i concatenate these two fields,yet store them seperately?

regards,
riki.

[Updated on: Wed, 02 April 2008 12:38]

Report message to a moderator

Re: concatenation using sql loader [message #310866 is a reply to message #310865]

Wed, 02 April 2008 12:37

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

There is no TIME datatype in Oracle.

Regards
Michel

Report message to a moderator

Re: concatenation using sql loader [message #310868 is a reply to message #310866]

Wed, 02 April 2008 12:43

riki
Messages: 12
Registered: April 2008
Location: ny

Junior Member

Michel you are true .But that is the control file.If i don't store it as time how can i store the timestamp?

Pardon my ignorance.
I have just started working with oracle.

Regard,
riki.

Report message to a moderator

Re: concatenation using sql loader [message #310869 is a reply to message #310865]

Wed, 02 April 2008 12:46

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Sorry, wrong answer for SQL*Loader.
I don't have Oracle by hand to test it but you should test a fixed-format something like:

start_date position(01:10) date "DD/MM/YYYY"
start_time position(01:22) timestamp(1)

As, afaik, you can't give a timestamp format in SQL*Loader, set your environment variable previously:

export NLS_TIMESTAMP_FORMAT='DD/MM/YYYY,HH24:MI:SS.FF1'

or the like depending on your OS and shell.

Regards
Michel

Report message to a moderator

Re: concatenation using sql loader [message #310881 is a reply to message #310869]

Wed, 02 April 2008 13:40

riki
Messages: 12
Registered: April 2008
Location: ny

Junior Member

The file is ',' delimitted.And the field(s) preceeding the START_DATE are not of fixed length.

Regards,
riki.

[Updated on: Wed, 02 April 2008 13:59]

Report message to a moderator

Re: concatenation using sql loader [message #310886 is a reply to message #310881]

Wed, 02 April 2008 13:53

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Post an example of your data file and table description and actual control file.

Regards
Michel

Report message to a moderator

Re: concatenation using sql loader [message #310896 is a reply to message #310886]

Wed, 02 April 2008 14:40

riki
Messages: 12
Registered: April 2008
Location: ny

Junior Member

The table descprition is as follows:
Name Type
-------------- -------------
DD NUMBER(2)
TYPE VARCHAR2(12)
NAME VARCHAR2(23)
ID VARCHAR2(18)
SYSTEM_TICKS NUMBER(10)
ZONE VARCHAR2(23)
START_DATE DATE
START_TIME VARCHAR2(11)

the data file is like:
STEP,op8ed01rs,0x0001001901F6EFAD,153434196,GMT,04/02/2008,18:51:50.3
ATTESST,ne8pd01tg,0x0001001901F72244,153480435,GMT,04/02/2008,18:59:42.5
ATTESST,rf8vf01gh,0x0001001901F7241D,153484173,GMT,04/02/2008,19:00:00.5
ATTESST,ws8ga01pa,0x0001001801F7231E,153481359,GMT,04/02/2008,18:59:51.8
STEP,er8ga01,0x0001001901F6ED19,153431976,GMT,04/02/2008,18:51:28.1
STEP,se8ga01tv,0x0001001901F722F9,153483135,GMT,04/02/2008,18:59:50.1

The control file is like:

OPTIONS (ROWS=1000, readsize=12582912, BINDSIZE=12582912, SILENT=FEEDBACK,DISCARDS)
LOAD DATA
infile 'test.dat'
APPEND INTO TABLE TEST
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
TYPE,
NAME,
ID,
SYSTEM_TICKS,
ZONE,
START_DATE date "mm/dd/yyyy",
START_TIME ,
DD "to_char(to_date(:START_DATE, 'mm/dd/yyyy'),'dd')"
)

Regards,
riki

Report message to a moderator

Re: concatenation using sql loader [message #310900 is a reply to message #310896]

Wed, 02 April 2008 14:51

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

I have to see the data as 03/10/2008 10:12:30 in the START_DATE field and 03/10/2008 10:12:30.0 in my STRAT_TIME field.

Quote:

START_TIME VARCHAR2(11)

How can you put this string in 11 characters?

Quote:

Time is TIMESTAMP datatype

Is it TIMESTAMP or VARCHAR2(11)?

Regards
Michel

Report message to a moderator

Re: concatenation using sql loader [message #310901 is a reply to message #310900]

Wed, 02 April 2008 15:20

riki
Messages: 12
Registered: April 2008
Location: ny

Junior Member

The table is partitoned on day and earlier I was getting the date part of timestamp to be the 1st day of month irrespective of the current date.

So i wanted to get rid of the date part and changed it to VARCHAR,thinking that i have start_date field which refers to date and start_time soley dedicated for time portion.

But when using "to_date" function I doubted my previous step may be wrong.

I thought may be combining the two fields and loading may be a possible solution for the problem.

I thought of writing a PERL script which combines the two fields yet keeping them available seperately (as the flat files are sent to me by some outer source).But that may not be a good idea for me to follow as i have to load data every 5 mins (100 files at the minimum each load )

And hence here. Razz

Razz

Regards,
riki.

Report message to a moderator

Re: concatenation using sql loader [message #310904 is a reply to message #310901]

Wed, 02 April 2008 15:47

Barbara Boehmer
Messages: 9103
Registered: November 2002
Location: California, USA

Senior Member

I am not entirely sure what you want to do or why, so the following demonstrates usage of boundfiller and concatenation for loading the date and time into a date datatype, the time only into a varchar2 data type and the date time and fractional seconds into a timestamp datatype. You can use whatever pieces you decide.

-- test.dat:

STEP,op8ed01rs,0x0001001901F6EFAD,153434196,GMT,04/02/2008,18:51:50.3
ATTESST,ne8pd01tg,0x0001001901F72244,153480435,GMT,04/02/2008,18:59:42.5
ATTESST,rf8vf01gh,0x0001001901F7241D,153484173,GMT,04/02/2008,19:00:00.5
ATTESST,ws8ga01pa,0x0001001801F7231E,153481359,GMT,04/02/2008,18:59:51.8
STEP,er8ga01,0x0001001901F6ED19,153431976,GMT,04/02/2008,18:51:28.1
STEP,se8ga01tv,0x0001001901F722F9,153483135,GMT,04/02/2008,18:59:50.1

-- test.ctl:

OPTIONS (ROWS=1000, readsize=12582912, BINDSIZE=12582912, SILENT=FEEDBACK,DISCARDS)
LOAD DATA
infile 'test.dat'
APPEND INTO TABLE TEST
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
TYPE,
NAME,
ID,
SYSTEM_TICKS,
ZONE,
filler_date BOUNDFILLER,
START_TIME ,
DD "to_char(to_date(:filler_date, 'mm/dd/yyyy'),'dd')",
START_DATE "TO_DATE(:filler_date || SUBSTR (:start_time, 1, 8), 'mm/dd/yyyyhh24:mi:ss')",
start_timestamp "TO_TIMESTAMP(:filler_date || :start_time, 'mm/dd/yyyyhh24:mi:ss.ff1')"
)

-- table, load, and results:

SCOTT@orcl_11g> CREATE TABLE test
  2    (DD		 NUMBER(2),
  3  	TYPE		 VARCHAR2(12),
  4  	NAME		 VARCHAR2(23),
  5  	ID		 VARCHAR2(18),
  6  	SYSTEM_TICKS	 NUMBER(10),
  7  	ZONE		 VARCHAR2(23),
  8  	START_DATE	 DATE,
  9  	START_TIME	 VARCHAR2(11),
 10  	start_timestamp  TIMESTAMP)
 11  /

Table created.

SCOTT@orcl_11g> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log

SCOTT@orcl_11g> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'
  2  /

Session altered.

SCOTT@orcl_11g> COLUMN dd   FORMAT 99
SCOTT@orcl_11g> COLUMN type FORMAT A7
SCOTT@orcl_11g> COLUMN name FORMAT A9
SCOTT@orcl_11g> COLUMN zone FORMAT A4
SCOTT@orcl_11g> SELECT * FROM test
  2  /

 DD TYPE    NAME      ID                 SYSTEM_TICKS ZONE START_DATE           START_TIME  START_TIMESTAMP
--- ------- --------- ------------------ ------------ ---- -------------------- ----------- ---------------------------------------------------------------------------
  2 STEP    op8ed01rs 0x0001001901F6EFAD    153434196 GMT  02-APR-2008 18:51:50 18:51:50.3  02-APR-08 06.51.50.300000 PM
  2 ATTESST ne8pd01tg 0x0001001901F72244    153480435 GMT  02-APR-2008 18:59:42 18:59:42.5  02-APR-08 06.59.42.500000 PM
  2 ATTESST rf8vf01gh 0x0001001901F7241D    153484173 GMT  02-APR-2008 19:00:00 19:00:00.5  02-APR-08 07.00.00.500000 PM
  2 ATTESST ws8ga01pa 0x0001001801F7231E    153481359 GMT  02-APR-2008 18:59:51 18:59:51.8  02-APR-08 06.59.51.800000 PM
  2 STEP    er8ga01   0x0001001901F6ED19    153431976 GMT  02-APR-2008 18:51:28 18:51:28.1  02-APR-08 06.51.28.100000 PM
  2 STEP    se8ga01tv 0x0001001901F722F9    153483135 GMT  02-APR-2008 18:59:50 18:59:50.1  02-APR-08 06.59.50.100000 PM

6 rows selected.

SCOTT@orcl_11g>

Report message to a moderator

Re: concatenation using sql loader [message #310910 is a reply to message #310904]

Wed, 02 April 2008 16:21

riki
Messages: 12
Registered: April 2008
Location: ny

Junior Member

It worked.Thank you so much.
Really. Thank you.

Regards,
riki.

Report message to a moderator

Re: concatenation using sql loader [message #310914 is a reply to message #310910]

Wed, 02 April 2008 17:06

riki
Messages: 12
Registered: April 2008
Location: ny

Junior Member

It works fine on my test table .But for my original table it gives the date part correctly where as the time part it is rounding it to 12:00:00 AM
DAT file:
STEP,op8ed01rs,0x0001001901F6EFAD,153434196,GMT,04/02/2008,18:51:50.3
ATTESST,ne8pd01tg,0x0001001901F72244,153480435,GMT,04/02/2008,18:59:42.5
ATTESST,rf8vf01gh,0x0001001901F7241D,153484173,GMT,04/02/2008,19:00:00.5
ATTESST,ws8ga01pa,0x0001001801F7231E,153481359,GMT,04/02/2008,18:59:51.8
STEP,er8ga01,0x0001001901F6ED19,153431976,GMT,04/02/2008,18:51:28.1
STEP,se8ga01tv,0x0001001901F722F9,153483135,GMT,04/02/2008,18:59:50.1

CTL file:OPTIONS (ROWS=1000, readsize=12582912, BINDSIZE=12582912, SILENT=FEEDBACK,DISCARDS)
LOAD DATA
infile 'test.dat'
INSERT INTO TABLE TEST
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
TYPE,
NAME,
ID,
SYSTEM_TICKS,
ZONE,
filler_date BOUNDFILLER,
DD "to_char(to_date(:filler_date, 'mm/dd/yyyy'),'dd')",
START_DATE "TO_DATE(:filler_date || SUBSTR (:start_time, 1, 8), 'mm/dd/yyyyhh24:mi:ss')",
start_time "TO_TIMESTAMP(:filler_date || :start_time, 'mm/dd/yyyyhh24:mi:ss.ff1')"
)

RESULT:

2 STEP op8ed01rs 0x0001001901F6EFAD 153434196 GMT 02-APR-2008 00:00:00 02-APR-08 12.00.00.0
2 ATTESST ne8pd01tg 0x0001001901F72244 153480435 GMT 02-APR-2008 00:00:00 02-APR-08 12.00.00.0
2 ATTESST rf8vf01gh 0x0001001901F7241D 153484173 GMT 02-APR-2008 00:00:00 02-APR-08 12.00.00.0
2 ATTESST ws8ga01pa 0x0001001801F7231E 153481359 GMT 02-APR-2008 00:00:00 02-APR-08 12.00.00.0
2 STEP er8ga01 0x0001001901F6ED19 153431976 GMT 02-APR-2008 00:00:00 02-APR-08 12.00.00.0
2 STEP se8ga01tv 0x0001001901F722F9 153483135 GMT 02-APR-2008 00:00:00 02-APR-08 12.00.00.0

am clueless ,what step did i miss on?

Regards,
riki.

[Updated on: Wed, 02 April 2008 19:47] by Moderator

Report message to a moderator

Re: concatenation using sql loader [message #310927 is a reply to message #310914]

Wed, 02 April 2008 20:00

Barbara Boehmer
Messages: 9103
Registered: November 2002
Location: California, USA

Senior Member

I don't know if you are still using the same table structure that you posted or if you have changed the start_time to timestamp datatype. Any calculated fields need to go at the end, after all other fields. If you are going to name your timestamp data type start_time, then you need to use filler_time or some such other name declared as boundfiller type before that, just as filler_date was declared, in order to use it in calculations. If you are still having problems, then please post all of the matching current pieces: data file, control file, table structure, select from table after loading, and sql*loader log file.

Report message to a moderator

Re: concatenation using sql loader [message #310929 is a reply to message #310914]

Wed, 02 April 2008 20:05

Barbara Boehmer
Messages: 9103
Registered: November 2002
Location: California, USA

Senior Member

If your current data structure is:

CREATE TABLE test
  (DD               NUMBER(2),
   TYPE             VARCHAR2(12),
   NAME             VARCHAR2(23),
   ID               VARCHAR2(18),
   SYSTEM_TICKS     NUMBER(10),
   ZONE             VARCHAR2(23),
   START_DATE       DATE,
   START_TIME       TIMESTAMP)
/

then your control file should be:

LOAD DATA
infile 'test.dat'
INSERT INTO TABLE TEST
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
TYPE,
NAME,
ID,
SYSTEM_TICKS,
ZONE,
filler_date BOUNDFILLER,
filler_time BOUNDFILLER,
DD "to_char(to_date(:filler_date, 'mm/dd/yyyy'),'dd')",
START_DATE "TO_DATE(:filler_date || SUBSTR (:filler_time, 1, 8), 'mm/dd/yyyyhh24:mi:ss')",
start_time "TO_TIMESTAMP(:filler_date || :filler_time, 'mm/dd/yyyyhh24:mi:ss.ff1')"
)

Report message to a moderator

Re: concatenation using sql loader [message #310986 is a reply to message #310904]

Thu, 03 April 2008 03:05

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Barbara,
As always, your post on SQL*Loader is a great lesson for me. /forum/fa/2115/0/

/forum/fa/2115/0/

Thanks.

Regards
Michel

[Updated on: Thu, 03 April 2008 03:06]

Report message to a moderator

Re: concatenation using sql loader [message #311129 is a reply to message #310986]

Thu, 03 April 2008 09:02

riki
Messages: 12
Registered: April 2008
Location: ny

Junior Member

Thank you Brabara,

That was a great help for me.

Regards,
riki.

Report message to a moderator

Previous Topic:	Commit point reached - logical record count
Next Topic:	DIRECT PATH SQLLOADER

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 25 20:01:28 CST 2025