INSERT useing Oracle stage fails - Special characters

algfr · Post by **algfr** » Wed Jul 29, 2009 7:52 am

Hi guys,

I have an issue with an Oracle stage to load a table.

At some point my job crashes saying it cannot insert a record (can't tell which one javascript:emoticon(':evil:')).

I figured it's cause by a varchar field that can contain all sorts of bad characters (commas, slashes, percents, etc).

My question is ? How to force DS to escape all characters in the INSERT STATEMENT it generates (ORCHESTRATE.XXX) ? This is quite annoying.

Thanks a lot

chulett · Post by **chulett** » Wed Jul 29, 2009 9:06 am

Nothing you've listed are "bad characters". How about some details? Tell us what stage and options there you are using and post the actual warnings / errors from your log.

algfr · Post by **algfr** » Wed Jul 29, 2009 9:19 am

Hi Craig,

The thing is that I cannot probe the output so that I can retrieve a good example. I just know that if I insert a constant string instead of the initial input, the job gets through.

I only trim an input field from DB2-AS400 and load it through a SCD stage. It crashes when getting into the ORA stage which upsert the table. I get the following error in the log :

ora_DWH_DIM_PRD_new,0: Unable to insert a record into the table. The error type is IIS-DSEE-TOUP-00022.

Otherwise, do you have an efficient way to see exactly what row fails ?

algfr · Post by **algfr** » Wed Jul 29, 2009 9:21 am

In my design I use reject tables but in this case this does not work. I assume this is because the INSERT STATEMENT sent to Oracle is invalid.

chulett · Post by **chulett** » Wed Jul 29, 2009 9:23 am

Too many assumptions here at the moment. What stage exactly - Oracle Enterprise? And are you doing an Upsert or a Load or ??? Details, please.

tippy1 · Post by **tippy1** » Wed Jul 29, 2009 9:55 am

The message your are getting "unable to insert row" is generic and will not give you the exact reason. Do the following

Try inserting the row into the table using SQL Plus or Toad and see if error occurs. If this is sucessful then have your DBA look at tablespaces
and other database reason that might cause a row to be rejected even though the data is clean.

algfr · Post by **algfr** » Wed Jul 29, 2009 9:59 am

Craig :

I'm upserting a table using an Oracle Enterprise.

Tippy:

This is a great idea but I don't know how to "capture" the wrong record.

miwinter · Post by **miwinter** » Wed Jul 29, 2009 10:00 am

Couldn't you try the entire data through the method suggested and see if the warning/error returned from that shows you which is the problem record?

algfr · Post by **algfr** » Wed Jul 29, 2009 10:06 am

Sorry, do you mean by extracting the output to a SEQ file and create INSERT STATEMENTS out of it ?

miwinter · Post by **miwinter** » Wed Jul 29, 2009 10:15 am

Yes, exactly, obviously not so feasible if you are dealing with any kind of volume, but I haven't seen that mentioned so far.

On the other side of it, though I'm not yet at v8, I heard that a reference was provided for error messages etc these days. Is "IIS-DSEE-TOUP-00022" not listed in that anywhere?

algfr · Post by **algfr** » Fri Jul 31, 2009 6:35 am

Ok I did it, a few records cannot be inserted using TOAD loader tool.

1) Some records exceed the max limit (though i KNOW it can't be). I suspec some special chars to be badly transmitted (soem become question marks ??), thus extending the string. Can this be solved ? I tried changing the NLS set but it did nothing

2) Some records contains semicolons. Can these be replaced , using the Convert function ?

Thanks for helping

Arnaud

ArndW · Post by **ArndW** » Fri Aug 07, 2009 2:22 am

That would not be a hex or binary viewer. The goal is to get the numeric representation of what is in that character.

algfr · Post by **algfr** » Fri Aug 07, 2009 2:40 am

ArndW wrote:That would not be a hex or binary viewer. The goal is to get the numeric representation of what is in that character. ...

Should I load the data into a dataset and open it afterwards ?

ArndW · Post by **ArndW** » Fri Aug 07, 2009 3:43 am

How about taking this step-by-step. Modify your job to put an output link from your Oracle stage directly to a flat file and in the Oracle stage options mark "Output reject records" as "true".
After the run do a "cat -v YourRejectFileName.txt" to get both the SQL error code and the data contents with undisplayable characters shown as octal or control codes.

algfr · Post by **algfr** » Thu Aug 13, 2009 6:04 am

ArndW wrote:How about taking this step-by-step. Modify your job to put an output link from your Oracle stage directly to a flat file and in the Oracle stage options mark "Output reject records" as "true".
After the run do a "cat -v YourRejectFileName.txt" to get both the SQL error code and the data contents with undisplayable characters shown as octal or control codes.

Hi again ArndW

I already tried using a reject link out of the Oracle stage but it does not work.

It seems DS is not able to identify the row and treat it just as if the INSERT STATEMENT used is wrong, this, because of the "weird" data...

Sorry I didn't understand correctly what you said.