oracle中long数据类型的一个转换错误以及如何转为字符类型

在oracle中对long数据类型的字段进行操作时，需要注意： long无法直接转化成char/varchar2字符类型，需要先转成clob，才能转成char/varchar2 注意官方文档中关于具体函数的使用方式，其参数的数据类型错误可能导致查询结果的错误! 如下实验： （1）在19c版本的库中，查看某个long数据类型的列，确认值不为空： Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> select high_value   2  from dba_tab_partitions where high_value is not null and rownum<2; HIGH_VALUE -------------------------------------------------------------------------------- TO_DATE(' 2010-01-01 01:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA （2）创建varchar2字符类型的列，使用to_lob对该字段进行转化，并存储字符型字段中，结果错误（为空） SQL> create table ta (high_value varchar2(2000)); Table created. SQL> insert into ta   2  select to_lob(high_value)    3  from dba_tab_partitions where high_value is not null and rownum<2; 1 rows created. SQL> commit; Commit complete. SQL> select * from ta; HIGH_VALUE -------------------------------------------------------------------------------- SQL>  （3）内层使用to_lob，外层使用to_char函数，报错 SQL> insert into ta   2  select to_char(to_lob(high_value))   3  from dba_tab_partitions where high_value is not null and rownum<2; select to_char(to_lob(high_value))                * ERROR at line 2: ORA-00932: inconsistent datatypes: expected - got LONG （4）将to_lob的结果存入clob类型的字段中，结果正确 SQL> create table tb (high_value clob); Table created. SQL> insert into tb   2  select to_lob(high_value)    3  from dba_tab_partitions where high_value is not null and rownum<2; 1 rows created. SQL> SQL> commit; Commit complete. SQL> select * from tb; HIGH_VALUE -------------------------------------------------------------------------------- TO_DATE(' 2010-01-01 01:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA SQL> 总结： oracle数据库内部很多字段仍然使用LONG类型存储数据，为了将该结果进行字符拼接，可以创建表，将该字段存储CLOB字段中，再进行查询时即可使用to_char函数改为字符类型了。 步骤2中的错误结果的确容易造成误导，但官方文档中to_lob函数的要求是结果只能放入clob，严格按文档来使用是没问题的。 TO_LOB函数的说明： TO_LOB converts LONG or LONG RAW values in the column long_column to LOB values. You can apply this function only to a LONG or LONG RAW column, and only in the select list of a subquery in an INSERT statement. Before using this function, you must create a LOB column to receive the converted LONG values. To convert LONG values, create a CLOB column. To convert LONG RAW values, create a BLOB column. You cannot use the TO_LOB function to convert a LONG column to a LOB column in the subquery of a CREATE TABLE ... AS SELECT statement if you are creating an index-organized table. Instead, create the index-organized table without the LONG column, and then use the TO_LOB function in an INSERT ... AS SELECT statement. You cannot use this function within a PL/SQL package. Instead use the TO_CLOB (character) or TO_BLOB (raw) functions. TO_CHAR函数的参数有以下四种： TO_CHAR (bfile|blob) TO_CHAR (character) TO_CHAR (datetime) TO_CHAR (number) 其中TO_CHAR (character)的说明为： TO_CHAR (character) converts NCHAR, NVARCHAR2, CLOB, or NCLOB data to the database character set. The value returned is always VARCHAR2. When you use this function to convert a character LOB into the database character set, if the LOB value to be converted is larger than the target type, then the database returns an error.