Home » RDBMS Server » Server Utilities » sqlloader multiple into table clause
sqlloader multiple into table clause [message #340137] Mon, 11 August 2008 11:08 Go to next message
brunins
Messages: 3
Registered: August 2008
Junior Member
Hi everybody,

I have a problem loading a Jboss properties file into an oracle table using sqlloader with multiple into table clauses.

Here's a sample of the source data:
prestation.activitycode.BOX_OFFICE_INIT=Opleiding Loket
prestation.activitycode.BOX_OFFICE_RFT=Versterking Loket
prestation.activitycode.CAR_MAINTENANCE=Onderhoud Wagenpark
premium.grouping.CASH=Kasvergoeding
premium.grouping.COMMERCIAL=Commercieel Attach\u00e9
premium.grouping.MEAL_VOUCHER=Maaltijdcheque
prestation.filter.filterResources=Filter lijst met medewerkers
prestation.filter.label.assigned=Toegewezen
prestation.filter.label.contractual=Contractueel baremiek
...

As you can see the input file is organized as key-value pairs separated by '='. Now I want to apply a filter while loading to exclude unwanted records, only the records beginning 'prestation.activitycode' and 'premium.grouping'.

I've created following control file:

OPTIONS (SKIP=3)
LOAD DATA
TRUNCATE
INTO TABLE MISSING_DESCRIPTIONS
WHEN (01:16) = 'premium.grouping'
FIELDS TERMINATED BY "="
( code "SUBSTR(:code, INSTR(:code,'.', -1, 1)+1)",
description,
language CONSTANT 'NL'
)
INTO TABLE MISSING_DESCRIPTIONS
WHEN (01:23) = 'prestation.activitycode'
FIELDS TERMINATED BY "="
( code "SUBSTR(:code, INSTR(:code,'.', -1, 1)+1)",
description,
language CONSTANT 'NL'
)

I skip the first 3 records( -> header records). If value of first 16 charchters is equal to 'premium.grouping' I do a substring to extract only the part after the last dot.
I take the value as is for the second column (-> description) and for the third column (language) I use the constant 'NL'.
Second into table clause is practically the same except that the condition in the WHEN clause is different.
As a result only the 'premium.grouping' are inserted. Nothing is inserted for 'prestation.activitycode' records. Sqlloader generates following data errors: Rejected - Error on table MISSING_DESCRIPTIONS, column CODE.
Column not found before end of logical record (use TRAILING NULLCOLS).
Adding trailing nullcols to the control file, inserts NULL records into my target table. Sad

Everyhting goes well when i process the into table clauses separatly.

What is wrong in my control file ?
sqlloader log in attachment.

Thanks for you precious help.
  • Attachment: log.log
    (Size: 12.00KB, Downloaded 201 times)
Re: sqlloader multiple into table clause [message #340144 is a reply to message #340137] Mon, 11 August 2008 11:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.orafaq.com/forum/m/294759/102589/?#msg_294759

Regards
Michel
Re: sqlloader multiple into table clause [message #340287 is a reply to message #340144] Tue, 12 August 2008 03:41 Go to previous messageGo to next message
brunins
Messages: 3
Registered: August 2008
Junior Member
Thanks Michel for your quick reply.

You suggest me using the position parameter in my second into table clause. But how is this possible with variable length values. The position expects begin-end parameters such as POSITION(25:39).
For eg. How do I substract the CAR_MAINTENANCE part in value 'prestation.activitycode.CAR_MAINTENANCE' ? I do know where it begins (after 'prestation.activitycode.') but not where it ends !

Thx in advance,
Cheers
Re: sqlloader multiple into table clause [message #340301 is a reply to message #340287] Tue, 12 August 2008 04:41 Go to previous messageGo to next message
brunins
Messages: 3
Registered: August 2008
Junior Member
Ok I found it ! Smile

I my second WHEN clause I 've added POSITION(1).

My correct control looks like:
OPTIONS (SKIP=3)
LOAD DATA
TRUNCATE
INTO TABLE MISSING_DESCRIPTIONS
WHEN (01:16) = 'premium.grouping'
FIELDS TERMINATED BY "="
( code "SUBSTR(:code, INSTR(:code,'.', -1, 1)+1)",
description,
language CONSTANT 'NL'
)
INTO TABLE MISSING_DESCRIPTIONS
WHEN (01:23) = 'prestation.activitycode'
FIELDS TERMINATED BY "="
( code POSITION(1) "SUBSTR(:code, INSTR(:code,'.', -1, 1)+1)",
description,
language CONSTANT 'NL'
)


thx Michel
Re: sqlloader multiple into table clause [message #340321 is a reply to message #340301] Tue, 12 August 2008 05:39 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
thx Michel

I should say Thanks to Barbara, I just pointed to her solution.

Regards
Michel
Previous Topic: sql loader
Next Topic: NO RECORDS SELECTED error
Goto Forum:
  


Current Time: Sat Dec 10 22:50:58 CST 2016

Total time taken to generate the page: 0.04258 seconds