| SQL Loader loads all fields with double quotes into staging table [message #429765] |
Thu, 05 November 2009 13:24  |
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
|
Senior Member |
|
|
Hi All,
A recent Microsoft OS update has rendered all of my SQL*Loader executables within the E-Business Suite invalid. I currently have Oracle Support looking into this issue at the OS level.
As a workaround, I have to redefine all of my SQL*Loader executables to Host (command script) executables. This isn't really a big deal, but I am noticing some odd behavior and perhaps it is something very simple. My control file is generated by the calling host script on the fly (because the file name of the .CSV files being processed changes from run to run). The control file gets generated successfully and SQL*Loader runs without error. The problem I am having is that all of the fields that SQL*Loader inputs into the staging table are surrounded by double quotes, rendering the data useless. For example, the field that should be inserted into staging table reads "2005CB045" when it should read simply 2005CB045. Any ideas on what is causing this and how I can get it to stop? Please note that the double quotes around the TRIM are mandatory, otherwise the data will not load at all.
load data
INFILE "F:\oracleprod\prodappl\ridot\ap_inbound\2003CB045-133_30OCT09.csv"
REPLACE
INTO TABLE ridot_cms_payment_standard
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY 'CHR(34)'
TRAILING NULLCOLS
(line_number CHAR "TRIM(:line_number)",
item_code CHAR "TRIM(:item_code)",
item_description CHAR "TRIM(:item_description)",
item_quantity CHAR "TRIM(:item_quantity)",
book CHAR "TRIM(:book)",
page CHAR "TRIM(:page)",
oracle_po_number CHAR "TRIM(:oracle_po_number)",
project_number CHAR "TRIM(:project_number)",
task_number CHAR "TRIM(:task_number)",
invoice_number CHAR "TRIM(:invoice_number)")
Thank you,
Steve
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: SQL Loader loads all fields with double quotes into staging table [message #430000 is a reply to message #429987] |
Fri, 06 November 2009 14:49  |
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
|
Senior Member |
|
|
Why are responses considered polluting? The problem is still not resolved. A band-aid workaround is being explored right now using various TRIM functions to preserve the data being sent to us in .CSV format.
A quick rundown: One of the MS Updates for NT Server 2003 in August or September caused some conflict between OS and SQL*Loader executable programs run through the E-Business Sutie. Running SQLLDR from the command prompt on the server processed correctly. I am not sure if this is permissions based or what and I really don't even know how I would figure that out. In any event, all of the concurrent executable SQL*Loader programs finished with error: SQL*Loader-523: error -2 writing to file ((null)). I've looked far and wide for an explanation for this, have 2 SR's open on Metalink and still no resolution.
Now, I am trying to change all SQL*Loader executable definitions to HOST files (to avoid the concurrent processing error shown above). This is working, except for when my host file has to generate the control file on the fly (at runtime). For whatever reason, when the control file is generated, it has a problem reading the entire line: FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY 'CHR(34)'. It understands FIELDS TERMINATED BY, but the OPTIONALLY ENCLOSED bit is not working. The best case scenario I can get to is loading all the fields correctly into staging table, but all fields surrounded by double quotes. This is why I think the control file is not able to read that option, because the terminated by segregates data properly, but the data includes the double quotes. The kicker is that some of the data can include double quotes (to indicate inches for instance) and I have to preserve those entries, just not the double quotes at front and back of the field. I am trying to fix via SQL commands, but this seems more of a band-aid than a permanent fix.
|
|
|
|
|
|
| Re: SQL Loader loads all fields with double quotes into staging table [message #431281 is a reply to message #430062] |
Mon, 16 November 2009 07:40 |
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
|
Senior Member |
|
|
Hi Barbara,
Sorry for the delayed response, I have been out of town for DBA training. Thank you for taking the time to post a suggestion.
I was using the CHR(34) because that is usually what I will use in batch scripts (this is a HOST file executable). I think you are correct though, when I introduce the single quotes surrounding the ASCII, the script doesn't know what to make of that, so it just ignores the entire clause.
I will try this suggestion: OPTIONALLY ENCLOSED BY X'34' and let you know what the outcome is.
Regards,
Steve
|
|
|
|
| Re: SQL Loader loads all fields with double quotes into staging table [message #431294 is a reply to message #431281] |
Mon, 16 November 2009 08:57 |
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
|
Senior Member |
|
|
Hi Barbara,
The suggestion to try OPTIONALLY ENCLOSED BY X'34' was unsuccessful. I am at a loss as to why the command line hates this syntax so much. Does anyone have any other ideas on how to write the clause: OPTIONALLY ENCLOSED BY '"' in a way that the command line will accept it? I have already tried these options:
OPTIONALLY ENCLOSED BY '"'
OPTIONALLY ENCLOSED BY 'CHR(34)'
OPTIONALLY ENCLOSED BY CHR(34)
OPTIONALLY ENCLOSED BY CHR(39)||CHR(34)||CHR(39)
OPTIONALLY ENCLOSED BY '\"'
Any suggestions are appreciated.
Thank you,
Steve
|
|
|
|
| Re: SQL Loader loads all fields with double quotes into staging table [message #431299 is a reply to message #431294] |
Mon, 16 November 2009 10:03 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Hi,
Steve Corey wrote on Mon, 16 November 2009 15:57The suggestion to try OPTIONALLY ENCLOSED BY X'34' was unsuccessful. Maybe you should specify what "unsuccessful" exactly means. Does it fail with error or is it just ignored?
Steve Corey wrote on Mon, 16 November 2009 15:57Any suggestions are appreciated. Instead of ready-fire-aim approach, you should open Oracle documentation. It is available e.g. online on http://tahiti.oracle.com/ (although the site is rather slow now).
SQL Loader is described in Utilities book. The syntax of termination and enclosure specification for R9i2 is located here Quote:X'hexstr'
The delimiter is a string that has the value specified by X'hexstr' in the character encoding scheme, such as X'1F' (equivalent to 31 decimal). "X"can be either lowercase or uppercase 34h = 52d = '4'
22h = 34d = '"'
|
|
|
|
| Re: SQL Loader loads all fields with double quotes into staging table [message #431304 is a reply to message #431299] |
Mon, 16 November 2009 10:47 |
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
|
Senior Member |
|
|
Unsuccessful = the entire OPTIONALLY ENCLOSED BY clause was ignored. This is the behavior I described in a previous post.
In regards to the "ready-aim-fire" approach, you do realize that the post you are quoting is a continuation post? I was soliciting advice as to how I could otherwise write the clause in question. This is not what you are describing, and is a bit insulting to insinuate I haven't done my homework.
The hex-string example is exactly what I needed. Barbara's example was correct, but had the incorrect hex value. Once I translated to the correct value (from 34 to 22) the load works. I simply had the wrong hexadecimal value.
Thank you for your assistance everyone!
Regards,
Steve
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
I might remove this very message some time later as it violates the harmony that lasts in the topic.
