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