 Decode is not using CLOB as its argument :ORA-00932 [message #127513] |
Tue, 12 July 2005 00:44  |
d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta
|
Senior Member |
|
|
hi all,
i am using 9i ,but when running a query having decode and its argument is a CLOB value,it gives an error ORA-00932 inconsistent data type,expected - got CLOB.
But if i change this CLOB variable to varchar2 then decode is working properly.
Thanks
Dinesh
|
|
|
|
|
|
| Re: Decode is not using CLOB as its argument :ORA-00932 [message #127551 is a reply to message #127513] |
Tue, 12 July 2005 03:05  |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You can use DBMS_LOB.SUBSTR to return a varchar2 that DECODE can handle, as shown below.
scott@ORA92> CREATE TABLE clob_tab
2 (clob_col CLOB)
3 /
Table created.
scott@ORA92> INSERT INTO clob_tab (clob_col) VALUES ('test')
2 /
1 row created.
scott@ORA92> SELECT DECODE (clob_col, 'test', 'match', 'no match')
2 FROM clob_tab
3 /
SELECT DECODE (clob_col, 'test', 'match', 'no match')
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB
scott@ORA92> SELECT DECODE (DBMS_LOB.SUBSTR (clob_col, 30), 'test', 'match', 'no match')
2 FROM clob_tab
3 /
DECODE(D
--------
match
scott@ORA92>
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
