OraFAQ Forum: Server Utilities » Concatenate fields in sql loader

Home » RDBMS Server » Server Utilities » Concatenate fields in sql loader

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

Concatenate fields in sql loader [message #115066]

Thu, 07 April 2005 03:34

alisonseedat
Messages: 2
Registered: April 2005

Junior Member

Is it possible to concatenate data into one column? For example, for the field record_type, I would like to put in the data from position 2:5 concatenated with data from position 11:12. E.g. in 2:5 there is text '0318' and in 11:12 there is text '05' so I would like field record_type to store '031805'.

(RECORD_TYPE POSITION(2:5),
TRX_DATE POSITION(9:16),
TRX_TYPE POSITION(19:24),
....
)

Any assistance would be appreciated!

Report message to a moderator

Re: Concatenate fields in sql loader [message #115140 is a reply to message #115066]

Thu, 07 April 2005 14:04

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

this should help.
the easiest method is load the data as-is into a stagin table and apply sql/plsql logics.

bash-2.03$ desc mutation mag.test

Table:mag.test
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 DNAME                                        VARCHAR2(10)
 LOC                                          VARCHAR2(10)
 DNAMELOC                                     VARCHAR2(20)

bash-2.03$ cat somefile.dat
RESEARCH       DALLAS
SALES          CHICAGO
OPERATIONS     BOSTON
Computing      Seattle
ACCOUNTING     xxx

bash-2.03$ cat somectl.ctl
load data
infile 'somefile.dat'
replace into table test
(loc position (1:10),
dname position (16:23),
dnameloc "substr(:dname,2,4)||substr(:loc,1,3)")
bash-2.03$ 
bash-2.03$ sqlldr userid=mag/mag control=somectl.ctl

SQL*Loader: Release 9.2.0.4.0 - Production on Thu Apr 7 13:57:31 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 6
bash-2.03$ 
bash-2.03$ query mutation mag.test

DNAME      LOC        DNAMELOC
---------- ---------- --------------------
DALLAS     RESEARCH   ALLARES
CHICAGO    SALES      HICASAL
BOSTON     OPERATIONS OSTOOPE
Seattle    Computing  eattCom
xxx        ACCOUNTING xxACC

Report message to a moderator

Re: Concatenate fields in sql loader [message #115217 is a reply to message #115140]

Fri, 08 April 2005 05:24

alisonseedat
Messages: 2
Registered: April 2005

Junior Member

Thanks very much for your help, Mahesh. That did work.
Alison

Report message to a moderator

Re: Concatenate fields in sql loader [message #115533 is a reply to message #115066]

Tue, 12 April 2005 09:23

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

I like the staging table approach myself, but as an exercise to see if you COULD do it in sqlloader, could you perhaps load the field as positions 2:12 (get the whole thing) and then apply a custom function to the load (if memory serves that might limit you to conventional path) that substrings out to just keep the portion you want? You'd still need a separate position line to capture the field in between the two fields you are concatenating.

But, I gotta think this would be slower than a staging table.

Report message to a moderator

Previous Topic:	Using SQL*Loader on flat file without delimiter
Next Topic:	Referencing data from earlier lines or immediate previous line ...

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 12 16:33:36 CST 2025