Home » RDBMS Server » Server Utilities » SQL Loader loads all fields with double quotes into staging table (9iR2 DB/806 Libraries/Windows NT Server 2003 SE)
SQL Loader loads all fields with double quotes into staging table [message #429765] Thu, 05 November 2009 13:24 Go to next message
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 #429780 is a reply to message #429765] Thu, 05 November 2009 14:44 Go to previous messageGo to next message
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
Senior Member
I believe I have narrowed down the problem to the line in the control file that reads: OPTIONALLY ENCLOSED BY 'CHR(34)'

Can SQL*Loader not recognize ASCII representation of double quote?
Re: SQL Loader loads all fields with double quotes into staging table [message #429782 is a reply to message #429765] Thu, 05 November 2009 15:06 Go to previous messageGo to next message
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
Senior Member
Problem is resolved. There is a simple SQL fix.

Use REPLACE function around fields like this:
@echo item_code CHAR "REPLACE(TRIM(:item_code), CHR(34), '')", >> %CTLFILE%

Regards,
Steve
Re: SQL Loader loads all fields with double quotes into staging table [message #429937 is a reply to message #429765] Fri, 06 November 2009 06:44 Go to previous messageGo to next message
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
Senior Member
Of course the REPLACE function will not work when fields contain the double quote in them, but since I'm talking to myself here anyway, I will just implement another solution.
Re: SQL Loader loads all fields with double quotes into staging table [message #429947 is a reply to message #429937] Fri, 06 November 2009 07:52 Go to previous messageGo to next message
Littlefoot
Messages: 20824
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Obviously, your monologue was that perfect that nobody wanted to interfere Smile I might remove this very message some time later as it violates the harmony that lasts in the topic.
Re: SQL Loader loads all fields with double quotes into staging table [message #429974 is a reply to message #429947] Fri, 06 November 2009 10:45 Go to previous messageGo to next message
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
Senior Member
Littlefoot wrote on Fri, 06 November 2009 08:52
Obviously, your monologue was that perfect that nobody wanted to interfere Smile I might remove this very message some time later as it violates the harmony that lasts in the topic.


Haha, yes, I'm sure that's it! Laughing

Do what you gotta do LF.

Regards,
Steve
Re: SQL Loader loads all fields with double quotes into staging table [message #429987 is a reply to message #429947] Fri, 06 November 2009 12:48 Go to previous messageGo to next message
joy_division
Messages: 4615
Registered: February 2005
Location: East Coast USA
Senior Member
Littlefoot wrote on Fri, 06 November 2009 08:52
Obviously, your monologue was that perfect that nobody wanted to interfere Smile I might remove this very message some time later as it violates the harmony that lasts in the topic.


Unfortunately, now I have polluted the thread too.

Steve, when you say SQL*Loader executable, is it just a matter of you meaning your control file or that actual sqlldr executable?

And you said you "resolved" it, but what really happened? Why did an OS patch cause this to happen?
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 Go to previous messageGo to next message
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 #430062 is a reply to message #430000] Sat, 07 November 2009 21:46 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8586
Registered: November 2002
Location: California, USA
Senior Member
I don't think OPTIONALLY ENCLOSED BY 'CHR(34)' is valid syntax. Try:

OPTIONALLY ENCLOSED BY '"'

or:

OPTIONALLY ENCLOSED BY X'34'

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
Hi,
Steve Corey wrote on Mon, 16 November 2009 15:57
The 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:57
Any 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 Go to previous message
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
Previous Topic: ORA-01555: snapshot too old during export.
Next Topic: SQL*Loader-485
Goto Forum:
  


Current Time: Sat Sep 24 23:10:24 CDT 2016

Total time taken to generate the page: 0.09766 seconds