Home » RDBMS Server » Server Utilities » SQL*Loader, Referencing fillier columns
SQL*Loader, Referencing fillier columns [message #263548] Thu, 30 August 2007 05:10 Go to next message
Barry Brierley
Messages: 10
Registered: November 2004
Junior Member
Hi,

Apparantly you can't reference filler columns. Is there any other way I can take two physical columns from a file and use them to decide how to populate one column on the database?

To explain: Table XXSYK_DC_OHQ_STAGING is used by a number of other processes and has a field called LOT_SERIAL. However in the file I get either a lot number or a serial number. I was going to set the lot number and serial numbers as filler fields then use a decode to decide which one to use. changing the table is not an option.

If I can't get the right I have to leave it in SmartDB (yuck), create yet another staging table, or create a trigger on the table itself. My (incorrect) control file is below.

APPEND
INTO TABLE "XXSYK_DC_OHQ_STAGING"
APPEND

FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS

(RECORD_NR "xxsyk_dc_ohq_staging_seq.nextval",
SOURCE constant "CONS",
SITE,
CUSTOMER_NR,
ORDER_NR,
ORDER_TYPE,
PART,
LOT_NUMBER filler,
EXPIRY_DATE,
SERIAL_NUMBER filler,
QUANTITY,
QUANTITY_SUM,
STATUS,
UNIT_MEASURE,
LD_REF filler,
SERIAL_OR_LOT "decode(:LOT_NUMBER, null, decode(:SERIAL_NUMBER,null,'None','Serial'), 'Lot')",
LOT_SERIAL "decode(:LOT_NUMBER, null, :SERIAL_NUMBER, :LOT_NUMBER)"
)

Thanks,
Barry
Re: SQL*Loader, Referencing fillier columns [message #263585 is a reply to message #263548] Thu, 30 August 2007 08:03 Go to previous messageGo to next message
Michel Cadot
Messages: 57650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Quote:
Apparantly you can't reference filler columns

What was the problem? Was there any error message?...

Regards
Michel

[Updated on: Thu, 30 August 2007 08:06]

Report message to a moderator

Re: SQL*Loader, Referencing fillier columns [message #263600 is a reply to message #263585] Thu, 30 August 2007 08:37 Go to previous messageGo to next message
Barry Brierley
Messages: 10
Registered: November 2004
Junior Member
The error message I get is
SQL*Loader-291: Invalid bind variable LOT_NUMBER in SQL string for column SERIAL_OR_LOT.

Reading through the Oracle utilities manual this is because of the following:
"Filler fields can be used in field condition specifications in NULLIF, DEFAULTIF, and WHEN clauses. However, they cannot be used in SQL strings. "



Re: SQL*Loader, Referencing fillier columns [message #263603 is a reply to message #263600] Thu, 30 August 2007 08:43 Go to previous messageGo to next message
Michel Cadot
Messages: 57650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use an external table.

Regards
Michel
Re: SQL*Loader, Referencing fillier columns [message #263612 is a reply to message #263548] Thu, 30 August 2007 09:05 Go to previous messageGo to next message
Barry Brierley
Messages: 10
Registered: November 2004
Junior Member
Correct me if I'm wrong, but using external tables is not something I can do from a client like SQL*Loader is it? I'd need Unix access, given that the database is on a Unix machine.
?
Re: SQL*Loader, Referencing fillier columns [message #263619 is a reply to message #263612] Thu, 30 August 2007 09:14 Go to previous messageGo to next message
Michel Cadot
Messages: 57650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can create it as for any other table but loaded file must be on the server.

Regards
Michel
Re: SQL*Loader, Referencing fillier columns [message #263643 is a reply to message #263619] Thu, 30 August 2007 10:28 Go to previous messageGo to next message
Barry Brierley
Messages: 10
Registered: November 2004
Junior Member
That doesn't help me then since I won't have server access.

It seems I can't use SQL*Loader to do any "complex" logic. I'll use SQL*Loader to load into a spare column then use a trigger to sort out the logic.
Not ideal but anything is better than SmartDB.

Thanks anyway.
Re: SQL*Loader, Referencing fillier columns [message #263648 is a reply to message #263643] Thu, 30 August 2007 10:42 Go to previous messageGo to next message
Michel Cadot
Messages: 57650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Not ideal but anything is better than SmartDB.

Yes, external table is better.
I don't understand why you can't put (or ask to put) some files on server.
Do you mean you transit all your data via Net services (SQL*Net)? Not very efficient.

Regards
Michel
Re: SQL*Loader, Referencing fillier columns [message #263692 is a reply to message #263548] Thu, 30 August 2007 14:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7861
Registered: November 2002
Location: California, USA
Senior Member
You need to use the keyword BOUNDFILLER in your SQL*Loader control file, in order to be able to reference it, instead of just FILLER.
Re: SQL*Loader, Referencing fillier columns [message #263698 is a reply to message #263692] Thu, 30 August 2007 15:46 Go to previous messageGo to next message
Michel Cadot
Messages: 57650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Wow! I only found 2 references to this in 10gR2 documentation:
A note in http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_field_list.htm#sthref946
and an example in
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_loading.htm#sthref1328

There is also an HOWTO note on Metalink (204243.1).

Regards
Michel
icon14.gif  Re: SQL*Loader, Referencing fillier columns [message #265025 is a reply to message #263692] Wed, 05 September 2007 05:05 Go to previous messageGo to next message
Barry Brierley
Messages: 10
Registered: November 2004
Junior Member
Thank you very much, bound filler solved my problem. the metalink how-to document was spot-on.
Re: SQL*Loader, Referencing fillier columns [message #265072 is a reply to message #265025] Wed, 05 September 2007 06:18 Go to previous message
Michel Cadot
Messages: 57650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.
Knowing that we help encourage us to keep it up.

Regards
Michel
Previous Topic: prob with setting DISPLAY env var.
Next Topic: SQL*LOADER ERROR 500 Unable to open File , File not found
Goto Forum:
  


Current Time: Thu Apr 24 23:38:52 CDT 2014

Total time taken to generate the page: 0.12233 seconds