Loading a mixture of CSV and Hard coded values into a table

[DrSmyth]

Hi, I'm brand new to Oracle and am very much learning the ropes, so please go easy on me.

We're currently loading a table from a csv using a control file like this:

OPTIONS (SILENT=(HEADER, FEEDBACK), DIRECT=FALSE, SKIP=1)
LOAD DATA
CHARACTERSET US7ASCII
INFILE 'TEST.CSV'
TRUNCATE
INTO TABLE TEST
FIELDS  TERMINATED  BY ','
TRAILING NULLCOLS
(
PARTITION_KEY                   "TO_CHAR(UNISTR(:PARTITION_KEY))"
,A                              "TO_NUMBER(:A)"
,B                              CHAR(5) "TO_CHAR(UNISTR(:B))"
,C                              "TO_NUMBER(:C)"
)

However, the first field PARTITION_KEY won't be available in the csv file that we're using to load the table. Is it possible to hard code the PARTITION_KEY value into the control file for the load. Something like this maybe:

OPTIONS (SILENT=(HEADER, FEEDBACK), DIRECT=FALSE, SKIP=1)
LOAD DATA
CHARACTERSET US7ASCII
INFILE 'TEST.CSV'
TRUNCATE
INTO TABLE TEST
FIELDS  TERMINATED  BY ','
TRAILING NULLCOLS
(
"xxx"                  "TO_CHAR(UNISTR(:PARTITION_KEY))"
,A                              "TO_NUMBER(:A)"
,B                              CHAR(5) "TO_CHAR(UNISTR(:B))"
,C                              "TO_NUMBER(:C)"
)

In the above example "xxx" is my PARTITION_KEY.

Thanks,

Smithy.....

 

[taupirho]

Let me get this straight, you want to put a default value of 'xxx' into your partition_key field in your table. If so you have a number of options:-

1) Before doing the sqlload simply do

update test set partition_key = 'xxx'

then alter you control file and remove reference to partition_key and run the sqlload

2) Tag a 'xxx' string onto the start of each line in your data file and run your original sqlload

3) Investigate use of the CONSTANT keyword in your control file


In order to understand recursion, you must first understand recursion.

 

[Dagon]

You've got the syntax somewhat incorrect in:

"xxx" "TO_CHAR(UNISTRPARTITION_KEY))"

The first part is still your column name, which is partition_key, not xxx. It's in the SQL expression that you'd hard-code a value. Or use the CONSTANT keyword, as Taupirho suggests.

 

[DrSmyth]

Cheers guys, thanks for the suggestions. Due to the fact that I want to paramiterise the field in question as part of a batch process and the data files are huge so don't want to increase storage requirements I've gone with:

OPTIONS (SILENT=(HEADER, FEEDBACK), DIRECT=FALSE, SKIP=1)
LOAD DATA
CHARACTERSET US7ASCII
INFILE 'TEST.CSV'
TRUNCATE
INTO TABLE TEST
FIELDS  TERMINATED  BY ','
TRAILING NULLCOLS
(
PARTITION_KEY CONSTANT "XXX"
,A                              "TO_NUMBER(:A)"
,B                              CHAR(5) "TO_CHAR(UNISTR(:B))"
,C                              "TO_NUMBER(:C)"
)

which seems to work.

Thanks for the pointer.