Oracle long 类型转 varchar2

最新推荐文章于 2023-09-07 10:04:00 发布

原创最新推荐文章于 2023-09-07 10:04:00 发布 · 8.4k 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #sql #数据库

数据库专栏收录该内容

23 篇文章

订阅专栏

博客讨论了在Oracle中使用SQL查询时遇到的LONG类型数据处理问题，由于LONG类型不支持直接的字符串搜索，导致报错。文章指出Oracle推荐使用LOB而非LONG，并提供了一个简单的自定义函数LONG_TO_CHAR来转换LONG数据为VARCHAR2，以便进行搜索。该函数通过动态SQL执行查询。示例展示了如何在视图和普通表中应用此函数进行特定字符串查找。

本想查一下新建VIEW的代码里是否包含特定的字串，结果报错：

SQL> select * from USER_VIEWS where upper(text) like '%MAP%';
select * from USER_VIEWS where upper(text) like '%MAP%'
ORA-00932: 数据类型不一致: 应为 CHAR, 但却获得 LONG

大概补充一下Oracle long类型的知识：

Oracle中的LONG类型有两种：
LONG文本类型，能存储2GB的文本。与VARCHAR2或CHAR类型一样，存储在LONG类型中的文本要进行字符集转换。
LONG RAW类型，能存储2GB的原始二进制数据（不用进行字符集转换的数据）。
在此并不解释如何使用LONG类型，而是会解释为什么你不希望在应用中使用LONG（或LONG RAW）类型。首先要注意的是，Oracle文档在如何处理LONG类型方面描述得很明确。Oracle SQL Reference手册指出：
不要创建带LONG列的表，而应该使用LOB列（CLOB、NCLOB、BLOB）。支持LONG列只是为了保证向后兼容性。

找了一圈，没有内置的函数做转换，参考了一下网上的处理方案，有些太复杂，有些兼容性不太好，反正也不会经常用，本着猿类的懒惰原则，写了个最简的处理方法：

CREATE OR REPLACE FUNCTION LONG_TO_CHAR(IN_WHERE      VARCHAR,
                                        IN_TABLE_NAME VARCHAR,
                                        IN_COLUMN     VARCHAR2)
  RETURN VARCHAR2 AS
  V_RET VARCHAR2(32767);
  V_SQL VARCHAR2(2000);

BEGIN
  V_SQL := 'select ' || UPPER(IN_COLUMN) || ' from
    ' || UPPER(IN_TABLE_NAME) || ' where ' || IN_WHERE;
  EXECUTE IMMEDIATE V_SQL
    INTO V_RET;

  RETURN V_RET;

END;
/

使用方法：

--视图应用例子

WITH ALLVIEW AS
 (SELECT UV.*,
         LONG_TO_CHAR('view_name=''' || VIEW_NAME || '''', 'user_views',
                       'TEXT') VTEXT
    FROM USER_VIEWS UV)
SELECT * FROM ALLVIEW WHERE UPPER(VTEXT) LIKE '%MAP%'


--普通表

SELECT T.*,
       LONG_TO_CHAR('rowid=''' || ROWID || '''', 'conf_user', 'user_profile') VTEXT
  FROM CONF_USER T