weird "inconsistent datatype" error

[DBAwhosaysNIE]

I am working w/a "shrinkwrap" vendor of ours to get them to certify their app on 10 and am getting a weird error:

their application tries to do a (sanitized for security/NDA):

insert into table (col1, col2, col3, col4) values(1,:1,'abc',:2) returning col2 into :3

and gets a "ORA-00932 inconsistent datatypes: expected NUMBER and got CLOB"

I'm 99% sure the problem is with the "returning" clause as none of the number columns have bind variables in the values clause and col2 is a clob. I tried setting an event in the init file to force a core dump but it does not tell me what the submitted/attempted bound values were.

if it makes a difference the application server runs on w2k(/3) and talks odbc so I don't know if something wacky is happenening in the translation (to/from oci). we are trying to use the 10 client as well while existing prod is 8.1.7 client/db.

 

[SantaMufasa]

NIE-man,

What I try to do when troubleshooting something wierd like this is to cut back the "bells and whistles" to the point that I get a successful-update message. Next, I start adding previously removed components back into the code until it fails. Then I can focus on the component that is cause Oracle grief.

If you try this on an important dataset, remember to ROLLBACK each time you are successful with the UPDATE.

Try this strategy and let us know what you discover.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[DBAwhosaysNIE]

it looks like it is a backward (in)compatibility issue w/the 10 client. I rolled the client back to 9.1 and it works (so the application admin tells me). guess I'll log a tar on this and see if they can fix it. I don't mind running the 9 client but really ${expative} me off that this has drug on for weeks (not continuoustly, but still). unfortunately this vendor is a m$ shop who a couple of times a year tries to tell our mgt "you wouldn't have these problem if you just used sql/server" to which I reply "fine, just take me of the paging list for this app" (which runs 24/7/365) and we repeat the cycle in 6 mo.

 

[VacationLand]

Is the SQL as simple as you say or does it rely on collection types? in-line views? or other constructions like a CAST operator? Did the RETURNING use a BULK COLLECT clause?

You didn't say whether you're using 10.1 or 10.2... I'm finding more flexibility in 10.2.