OraFAQ Forum: Server Utilities » SQL*LOAD

Home » RDBMS Server » Server Utilities » SQL*LOAD - manipulating data

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

SQL*LOAD - manipulating data [message #164016]

Tue, 21 March 2006 05:44

pday@tullib.com
Messages: 11
Registered: March 2006
Location: London

Junior Member

HI

Have a problem with loader that I assume should be easy to sort but can't seem to find any reference for it.

My table has say 5 col's A,B,C,D,E

My Data file that I'm trying to load has 3 col's A,C,E and is comma delimeted

What I want to see is col's B and D being loaded with data derived from col A.

col A contains data in the format 123456/654321
Everything before the / is col B and everything after is col D

I've worked out the sql to manipulate the data and this works fine is sqlplus

for col B
SUBSTR(A,1,INSTR(A,'/')-1)
for D
SUBSTR(A,INSTR(A,'/')+1,LENGTH(A))

My current ctl file looks like this:

load data
infile aa.csv
into table abc
fields terminated by "," optionally enclosed by '"'
(A,
C DATE 'DD/MON/YYYY HH24:MI',
E)

I have tried adding the following (with various combinations quotes) but with no sucess.

load data
infile aa.csv
into table abc
fields terminated by "," optionally enclosed by '"'
(A,
B SUBSTR(:A,1,INSTR(:A,'/')-1),
C 'DD/MON/YYYY HH24:MI',
D SUBSTR(:A,INSTR(:A,'/')+1,LENGTH(:A))
E)
any help would be appreciated.

thanks

Report message to a moderator

Re: SQL*LOAD - manipulating data [message #164021 is a reply to message #164016]

Tue, 21 March 2006 06:10

pday@tullib.com
Messages: 11
Registered: March 2006
Location: London

Junior Member

I've just found a ref to the keywork 'EXPRESSION'

I've tried the following:

.....
B EXPRESSION "SUBSTR(:A,1,INSTR(:A,'/')-1)",
....

but get syntax error

Expecting "," or ")", found "EXPRESSION".

thanks

Report message to a moderator

Re: SQL*LOAD - manipulating data [message #164064 is a reply to message #164021]

Tue, 21 March 2006 10:27

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

Senior Member

Columns in the control file need to be in the same order as values in the csv file. Any columns that are calculated based on other columns need to go at the bottom. You also need to include trailing nullcols, so that it does not stop looking for column definitions after the end of the physical record. I also added date in front of your date format.

load data
infile aa.csv
into table abc
fields terminated by "," optionally enclosed by '"'
trailing nullcols
(A,
C DATE 'DD/MON/YYYY HH24:MI',
E,
B "SUBSTR(:A,1,INSTR(:A,'/')-1)",
D "SUBSTR(:A,INSTR(:A,'/')+1)")

Report message to a moderator

Re: SQL*LOAD - manipulating data [message #164067 is a reply to message #164016]

Tue, 21 March 2006 10:57

pday@tullib.com
Messages: 11
Registered: March 2006
Location: London

Junior Member

many thanks - that worked a treat.

One more question if I may, the last step I need to do update one more col, this time using data from another database table but using the field B as the primary key.

I have tried the following:

load data
infile aa.csv
into table abc
fields terminated by "," optionally enclosed by '"'
trailing nullcols
(A,
C DATE 'DD/MON/YYYY HH24:MI',
E,
B "SUBSTR(:A,1,INSTR(:A,'/')-1)",
D "SUBSTR(:A,INSTR(:A,'/')+1)"),
F "select tag from tag_tab tt where tt.tag = "SUBSTR(:A,1,INSTR(:A,'/')-1)"

I have also tried
F "select tag from tag_tab tt where tt.tag = :B"

I get the error missing expression.

thanks in advance.

Report message to a moderator

Re: SQL*LOAD - manipulating data [message #164098 is a reply to message #164067]

Tue, 21 March 2006 16:52

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

Senior Member

You can create a function to select the data, then use that function in your control file. Since you are selecting the same thing that you are comparing to, it appears you are attempting to do some sort of validation. If there is no match (no_data_found exception) then you will need to decide how you want to handle that. In the example below, I just returned null. If the column you are attempting to insert the validated tag into, has a not null constraint, then the insert of that row will be rejected and go into your bad file, which I assume is the desired result.

create or replace function validate_tag
  (p_tag in number)
  return    number
as
  v_tag     number;
begin
  select tag
  into   v_tag
  from   tag_tab
  where  tag = p_tag;
  return v_tag;
exception
  when no_data_found then
    return null;
end validate_tag;
/

load data
infile aa.csv
into table abc
fields terminated by "," optionally enclosed by '"'
trailing nullcols
(A,
 C DATE 'DD/MON/YYYY HH24:MI',
 E,
 B "SUBSTR(:A,1,INSTR(:A,'/')-1)",
 D "SUBSTR(:A,INSTR(:A,'/')+1)",
 F "validate_tag (SUBSTR(:A,1,INSTR(:A,'/')-1))")

Report message to a moderator

Re: SQL*LOAD - manipulating data [message #164099 is a reply to message #164067]

Tue, 21 March 2006 16:55

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

Senior Member

I should add that an easier method of validation would be to just create a foreign key constraint, then just insert the same value in F as in B and any values not in the referenced table will cause the row to be rejected and go in the bad file. If this was just an overly simplified example and you are actually selecting a different column, then modify the function appropriately.

Report message to a moderator

Re: SQL*LOAD - manipulating data [message #164213 is a reply to message #164099]

Wed, 22 March 2006 06:04

pday@tullib.com
Messages: 11
Registered: March 2006
Location: London

Junior Member

many thanks - thats great

Report message to a moderator

Previous Topic:	sqlldr invalid number problem
Next Topic:	loading xml file using sql*loader (10g)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 05 18:53:05 CDT 2024