Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1739
  • Last Modified:

ORA-00932: inconsistent datatypes: expected BLOB got CHAR

We have two 9i databases, same NLS_LENGTH_SEMANTICS setting (BYTE). Following query works fine on one but failed on another. Table definitions are identical.

select nvl(t1bin,'') from t1  where t1bin is null
0
liuh2
Asked:
liuh2
  • 10
  • 7
4 Solutions
 
sdstuberCommented:
''  is NULL but it is a typed NULL

so, if t1bin is a BLOB

what that statement says is,  find all t1bin blobs that are null and return a CHAR null instead.

0
 
sdstuberCommented:
I'm not sure what you're trying to do anyway.  The select is looking for nulls then returning null by running thruough a function that looks for null and returns something else (where the something else is null)

Seems kind of roundabout don't you think?  
0
 
schwertnerCommented:
You need to use a convert function like:

select nvl(t1bin,TO_CLOB('')) from t1  where t1bin is null
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
liuh2Author Commented:
This might not be a perfect example, we are looking for the reason why one return result fine on one database, but failed on the other one.
0
 
liuh2Author Commented:
Thanks schwertner, the To_clob is working, but the code is not able to use it at this point of time. Does OCI version matter? The one works fine use 9.2, the one that is not working is 8.1.
0
 
sdstuberCommented:
it "should not" matter, but yes, that's likely the problem.

The reason to_clob works is because it's explicitly changing the type from char to clob.

If you are using blob's instead of clobs I suggest using to_blob

select nvl(t1bin,TO_BLOB(null)) from t1  where t1bin is null

or...  even simpler.  don't use '' when you mean NULL

NVL will return the same type as the first parameter unless you specify something else.

select nvl(t1bin,NULL) from t1 where t1bin is null

since NULL is, by itself untyped,  it will be forced to be the same type as t1bin  which, if it's BLOB will force the NULL to be a BLOB too.




0
 
sdstuberCommented:
again though,  seems like a pretty contrived example.

What is it you're really trying to do?  Turning a NULL into a NULL doesn't seem very helpful.

select t1bin from t1 where t1bin is null  

would suffice
0
 
liuh2Author Commented:
Hi, sdstuber, can you explain more why this OCI version matter? The query may not make sense, I just want to find out why this query returned result from on database but failed on another one. In the code, the real query is more complex. Thanks. There must be some setting diff and that's what I am looking for. Thanks again
0
 
sdstuberCommented:
there are a few bugs in various releases of 8 and 9.  Every patch set of 9i fixes at least one ora-932 bug.

So, maybe you're running into one of those.  It's hard to say from the simplified example which, if any, of them you might be running into.

In any case,  doing explicit conversion of any NULL's into the type you need is a good way to insulate yourself from these types of errors.
0
 
liuh2Author Commented:
The development tool we use is PL/SQL Developer. What's the relationship between the software version and the OCI version? The one with lower PL/SQL developer version (5.x)is using OCI: version 8.1 and the higher PL/SQL developer version (7.X) has OCI: version 9.2. Both Test and Prod database version is 9.2.0.6 and both Oracle client on the Test and Prod Application server is 10.2.0.4. The PL/SQL Developer on both boxes are using \Oracle\product\10.2.0\client_1\bin\oci.dll. Shouldnt the OCI version be the same?  
0
 
sdstuberCommented:
check which oracle home they are using.

Preferences->Oracle->Connection  
0
 
sdstuberCommented:
and yes, if the oracle home is 10g they should be using a 10g oci

note there is a an oci option on the Connection screen also.
0
 
sdstuberCommented:
I don't have version 5 of pl/sql developer so I can't say for sure what it's doing or how to configure it, I assume it will be something like the version 7 though.
0
 
liuh2Author Commented:
Preferences>Options> Oracle Home are both pointing to the 10g Oracle Home: OraClient10g_home1, and OCI library is pointing to \oracle\product\10.2.0\client_1\bin\oci.dll.  
0
 
liuh2Author Commented:
So this OCI version(8.1, 9.2) is tight with PL/SQL Developer version, even when they are using the same version of oci.dll(10.2.0.4)? btw, the query failed on Test box. I noticed that the PL/SQL developer can not automatically pick the Oracle Home and OCI library. I had to manually set them in Preferences>Options.
 
0
 
sdstuberCommented:
If you can pick your home and oci it should use whatever you pick.  If it does not, then there must be a bug in the program.
0
 
sdstuberCommented:
I think I answered this question and all of the followups.

schwertner should probably get some portion of a split though since he was the first to post an actual code example
0
 
liuh2Author Commented:
Thank you all for the help. It turns out the problem is with parameter CURSOR_SHARING. Both databases version is on 9.2.0.4. The setting on the problematic 9i database is SIMILAR, and the other database which returned correct data is EXACT. The problem is solved after we change back to EXACT. This is not a issue for 10gR2.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now