ORA-00911 error

sagar deshmukh · Post by **sagar deshmukh** » Mon Feb 16, 2009 6:32 am

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.?

rsaliah · Post by **rsaliah** » Mon Feb 16, 2009 6:48 am

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?

sagar deshmukh · Post by **sagar deshmukh** » Mon Feb 16, 2009 6:56 am

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

rsaliah · Post by **rsaliah** » Mon Feb 16, 2009 7:14 am

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?

chulett · Post by **chulett** » Mon Feb 16, 2009 7:24 am

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.

sagar deshmukh · Post by **sagar deshmukh** » Mon Feb 16, 2009 7:27 am

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...

Code: Select all

ldiuICVEcoupon..xfm: ORA-00911: invalid character
  The value of the row is: COUPON_SET_NBR = 46 COUPON_SEQ_NBR = 4905 ECOUPON_ID = 7701993900100044980253 ACCOUNT_NBR = NULL LOC_NBR = NULL DISTRIBUTION_DTS = NULL PRINT_DTS = 2009-02-16 04:00:01 RECYCLE_DTS = NULL LAST_UPDATE_DTS = 2009-02-13 16:50:31 POST_DTS = 2009-01-01 00:00:00 LAST_UPDATE_USER = ETL

sagar deshmukh · Post by **sagar deshmukh** » Mon Feb 16, 2009 7:30 am

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

chulett · Post by **chulett** » Mon Feb 16, 2009 7:36 am

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

sagar deshmukh · Post by **sagar deshmukh** » Mon Feb 16, 2009 7:45 am

array size is already 1

chulett · Post by **chulett** » Mon Feb 16, 2009 7:59 am

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.

rsaliah · Post by **rsaliah** » Mon Feb 16, 2009 8:14 am

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.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Mon Feb 16, 2009 8:28 am

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

Identify the offending columns by process of elimination.

girija · Post by **girija** » Mon Feb 16, 2009 10:48 am

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.

mk_ds09 · Post by **mk_ds09** » Mon Feb 16, 2009 10:49 am

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.

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

sagar deshmukh · Post by **sagar deshmukh** » Tue Feb 17, 2009 12:56 am

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.....