Searching special data by 'WHERE' command for CLOB type-CodePudding

Look at below table.

I tried to print 'CORP_CODE' out with tuple which has same 'CORP_NAME' in it. SO, I wrote this code.

SELECT CORP_CODE
FROM COMPANY_INFO
WHERE CORP_NAME = '다코'

However, There is important error. The above code show ORA-00932: "inconsistent datatypes: expected %s got %s" I changed '' to "", But It isn't helpful.

I found more information, The column named 'CORP_NAME' is type of CLOB data. It seems that I need to use special methods for do it.

That table, I dind't make it myself. It was just made by python pandas function 'dataframe.to_sql'.

In this situlation, I have three questions for you.

How can I get CORP_CODE with CLOB data 'CORP_NAME' by WHERE command or anything else?
Should I re-make table and define 'CORP_NAME' as VARCHAR2? Is it the only way for me?
In pandas inner function 'to_sql', Can I set detail options for making table?

I make another table that 'CORP_NAME' as VARCHAR2(146). However, I want to know how can I select something by WHERE sentences.

CodePudding user response：

Try it like below:

WITH
    tbl As
        (
            Select 111 "CODE", SubStr('물을 흘린다', 1, 100) "MULTIBYTE_VARCHAR_NAME", 'spill water' "VARCHAR_NAME", To_CLOB('물을 흘린다') "CLOB_NAME" From Dual Union All
            Select 222 "CODE", SubStr('흘린다', 1, 100) "MULTIBYTE_VARCHAR_NAME", 'spill' "VARCHAR_NAME", To_CLOB('흘린다') "CLOB_NAME" From Dual Union All
            Select 333 "CODE", SubStr('물을', 1, 100) "MULTIBYTE_VARCHAR_NAME", 'water' "VARCHAR_NAME", To_Clob('물을') "CLOB_NAME" From Dual
        )

-- where condition on VARCHAR column
SELECT * FROM tbl WHERE VARCHAR_NAME = 'spill water';
--  Result:
--        CODE MULTIBYTE_VARCHAR_NAME   VARCHAR_NAME  CLOB_NAME   
--  ---------- ----------------------   ------------ -------------
--         111 물을 흘린다                spill water  물을 흘린다   

-- where condition on MULTIBYTE_VARCHAR column   
    SELECT * FROM tbl WHERE MULTIBYTE_VARCHAR_NAME = '물을';
--  Result:
--        CODE MULTIBYTE_VARCHAR_NAME  VARCHAR_NAME CLOB_NAME       
--  ---------- ----------------------- ------------ ----------------
--         333 물을                     water        물을             
       
-- where condition on CLOB column using DBMS_LOB.SubStr() and SubStr() functions
SELECT * FROM tbl WHERE DBMS_LOB.SubStr(CLOB_NAME, 100, 1) = SubStr('흘린다', 1, 100);
--  Result:
--        CODE MULTIBYTE_VARCHAR_NAME VARCHAR_NAME CLOB_NAME        
--  ---------- ---------------------- ------------ -----------------
--         222 흘린다                   spill        흘린다

It looks like pandas made it of the type of CLOB. Your WHERE clause tried to compare that CLOB with your multibyte character string. This could cause the error "expecting something else than CLOB/MULTIBYTE". More about checking multibytes here and about DBMS_LOB package here
Regards...

CodePudding user response：

You can use the below to overcome your problem by comparing CLOB with CLOB using dbms_lob.compare

SELECT CORP_CODE
FROM COMPANY_INFO
WHERE dbms_lob.compare(CORP_NAME, to_clob('다코')) = 0;