Inconsistent datatypes: expected-got CLOB error example

Inconsistent datatypes: expected-got CLOB error example

The tester reported that an SQL statement can be executed normally in one of the test environments, but cannot be executed in another test environment. The error is:

ORA-00932: inconsistent datatypes: expected-got CLOB.

I started to check whether there is a CLOB field in the table, but I checked the related table structure. There are no CLOB, BLOB, and other types of fields. Next, I analyzed the SQL, which caught my attention. In this SQL, The WMSYS. WM_CONCAT function is used. You can view the definition of this function in two test environments and find the difference:

Execute the correct WM_CONCAT definition for the database:

SQL> desc wmsys. wm_concat FUNCTION wmsys. wm_concat RETURNS VARCHAR2 Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- P1 VARCHAR2 IN

WM_CONCAT definition of the database for which an exception is executed:

SQL> desc wmsys. wm_concat FUNCTION wmsys. wm_concat RETURNS CLOB Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- P1 VARCHAR2 IN

The return values of these two functions are different! When the library with the execution error returns CLOB, it is no wonder that the inconsistent datatypes: expected-got CLOB error will be reported. But why? Go to metalink to find the answer.

WM_CONCAT is used as the keyword to search for this article [ID 1300595.1. Oracle explains that WM_CONCAT is an internal function in Workspace Manager and may vary with different database versions. In 10.2.0.4/11.1.0.7/11.2.0.1, WM_CONCAT returns VARCHAR2, while in 10.2.0.5/11.2.0.2, the returned value is CLOB. Therefore, the function definitions of the two databases are different. Therefore, Oracle does not recommend the use of internal functions such as WM_CONCAT (internal undocumentd function), and Oracle is not responsible for the loss caused by the use of such functions, in addition, changes to such functions will not be notified to users. Therefore, you must think twice before using such functions!