DSXchange

i am trying to insert update data into oracle table and i am getting ora - 00911 error.

my data is clean only---
Input data is--
46|4905|"7701993900100044980253"||||"2009-02-16 04:00:01"||"2009-02-13 16:50:31"
46|4906|"7701993900100044990268"||||"2009-02-16 04:00:01"||"2009-02-13 16:50:31"


Can anybody tell me whats the problem.?

Looks like Oracle's complaining about invalid characters. Have you checked and validated your data against the Stage column definitions? I'm guessing because you haven't said but your data is read from a sequential file stage delimited by pipe. Have you specified that columns 3, 7 and 9 can be enclosed by double quotes?

there is no problm about the quotes.....
also i have validated the data it is correct only.

OK - What type of Stage is the source and are you using OCI or ODBC to do the updates. Which is the key column(s) and which are the updates. Out of interest what is the SQL type definition for column 3 on the Stage used for update?

User-defined sql? Make sure there's no semi-colon at the end. Using job parameters? Make sure all are properly spelled and cased, if they don't resolve the # will cause that error.

i have used OCI....
i am reading from a seq. file then transformer then OCI....

though data is getting read properly...it is showing below error in Director...

there is no issue with the parameter....i checked....also am using autogenerated querry....

If not already, set your Array Size to 1 and rerun, let us know if the logged "value of the row" message changes.

array size is already 1

Then we know that's the actual row being processed. You need to keep digging, find out what is causing this, we've checked the common stuff. Could be your data is not as clean as you think.

Checkout the third column (ECOUPON_ID). It looks as if it start's life as a varchar because your sample data shows double-quotes, and ends up in a number column. If this is the case make sure the quotes are taken care of and there is nothing other then a number being pushed to Oracle. Failing that then capture the offending row and double check the data types against the Oracle table definition. The other thing to check is the column names in the OCI stage match the actual table and that they contain no non-ascii chars.

Can you perform the same insert outside ds (i.e. through other client like SQL*Plus?

Identify the offending columns by process of elimination.

Hi,
Open your source file in a file editor that can handle ASCII as well as NON_ASCII character, let see whether there is any invalid character there or not. It may be a non printable character, not shown in your log from PEEK or from other stage.

ORA-00911 is due to invalid character..

1.Check if there are any Varchar columns and the characters which are appended by the DS.

2. As you are saying you are trying to update the data, which columns you are tying to update and which columns you are using in where clause ?

3. 'Data is clean only'
What method you had applied to verify the data ?

If there a file..can you check with cat -v in the unix box.

---------------------------------------------------------

I have again checked in the unix the file isgood only..no problem with the data...even the columns are properly defined with data types.....
what could be the error....?
As i tried to change the data also...Gave some different data but it is not workking.....again same error only.....