ITPub博客

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

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

原创 Oracle 作者:to_be_Dba 时间:2020-10-22 23:54:36 0 删除 编辑

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


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26451536/viewspace-2728962/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-11-23

  • 博文量
    170
  • 访问量
    440005