0

Look at below table. enter image description here

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.

  1. How can I get CORP_CODE with CLOB data 'CORP_NAME' by WHERE command or anything else?
  2. Should I re-make table and define 'CORP_NAME' as VARCHAR2? Is it the only way for me?
  3. 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.

Null물을 흘린다
  • 31
  • 4
  • VARCHAR2 can have a length up to 4000 bytes, so it should be sufficient even for Chinese names. In general your query should work, I guess pandas breaks it. – Wernfried Domscheit Nov 27 '22 at 07:31
  • It's not chinese, but korean... Anyway, the max length of name is 73 so 146 bytes. So It could be. pandas-to-sql was NOT helpful for me., At least this case – Null물을 흘린다 Nov 27 '22 at 07:47

2 Answers2

0

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...

d r
  • 3,848
  • 2
  • 4
  • 15
0

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;
psaraj12
  • 4,772
  • 2
  • 21
  • 30
  • Feature "SQL semantics for LOB" was introduced in Oracle 10g. Since then, direct comparison `CORP_NAME = '다코'` should work. But as mentioned before, pandas-to-sql seems to be the culprit. Maybe it can be solved by setting `NLS_LANG`, see https://stackoverflow.com/questions/45283182/writing-exotic-non-ascii-characters-to-oracle-db-using-pandas-to-sql-in-python – Wernfried Domscheit Nov 27 '22 at 18:53
  • Recent versions of cx_Oracle ignore the char set in NLS_LANG and always use UTF-8. – Christopher Jones Nov 28 '22 at 22:58