背景

业务部门最近有个需求，把ALL_TAB_PARTITIONS中的high_value快速查出来，因为high_value字段在Oracle 存储为LONG 类型，直接在plsql中查询，会把字段显示为LONG，需要点击后才能查看到具体的值，非常的不方便，是否有比较简洁的方法，本文档将提供几种实现方法。

SQL> desc ALL_TAB_PARTITIONS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_OWNER                                        VARCHAR2(30)
 TABLE_NAME                                         VARCHAR2(30)
 COMPOSITE                                          VARCHAR2(3)
 PARTITION_NAME                                     VARCHAR2(30)
 SUBPARTITION_COUNT                                 NUMBER
 HIGH_VALUE                                         LONG      <================
 HIGH_VALUE_LENGTH                                  NUMBER
 PARTITION_POSITION                                 NUMBER
 TABLESPACE_NAME                                    VARCHAR2(30)
 PCT_FREE                                           NUMBER
 PCT_USED                                           NUMBER
 .....

LONG 类型说明

在查阅Oracle 11g/12C/19C官方文档后，文档中关于LONG类型的说明都是一样。

首先，不建议在表中使用LONG 类型，可以是用LOB字段替代，LONG列仅支持向后兼容性。

LONG列存储可变长度字符串，在使用过程中有诸多的限制和不支持的地方，如下：

1、限制：一张表只可有一个LONG字段

2、限制：LONG字段不能创建索引

3、限制：LONG字段不能使用常规的表达式 等等

4、不支持在SELECT中使用GROUP BY 、ORDER BY 、DISTINCT等语法

5、不支持UNIQUE运算符

6、不支持CREATE CLUSTER语句的

7、不支持ALTER TABLE … MOVE 等等

具体的详细可参考官方文档

Oracle Database SQL Language Reference–Basic Elements of Oracle SQL–Data Types–Oracle Built-in Data Types-LONG Data Type 章节

下面针对如何查询LONG类型字段，进行实践验证。

操作实践

方式一 TO_LOB 转换

在官方文档中有说明，可以使用TO_LOB函数将LONG类型转换为LOB类型。

Oracle also recommends that you convert existing LONG columns to LOB columns. LOB columns 
are subject to far fewer restrictions than LONG columns. Further, LOB functionality is enhanced 
in every release, whereas LONG functionality has been static for several releases. 
See the modify_col_properties clause of ALTER TABLE and TO_LOB for more information on converting LONG columns to LOB.

如果直接SELECT 使用 TO_LOB转换会出现ORA-00932（数据不一致）报错，TO_LOB也有说明，只支持在insert 子句中使用TO_LOB。

看来需要用临时表过渡下。

1、 使用CTAS 创建t_lob_tmp临时表。

create table t_lob_tmp as select t.table_owner,t.table_name,t.partition_name,to_lob(t.high_value) high_value from ALL_TAB_PARTITIONS t  WHERE T.table_owner = 'V7PRO';

select table_owner,table_name,high_value from t_lob_tmp;

select table_owner,table_name,to_char(high_value) from t_lob_tmp;

2、查询t_lob_tmp，发现high_value显示为CLOB，使用to_char再次转换。

3、经过to_char转换后，high_value已经成功读取。

方式二 利用dbms_xmlgen.getxmltype

实现原理：利用dbms_xmlgen.getxmltype将sql返回值转换为XML文档，然后通过extractvalue进行抽取文本值，过程也是相当流畅。

SELECT * from (
  SELECT  t.table_owner,t.table_name,PARTITION_NAME,
    extractvalue
      ( dbms_xmlgen.getxmltype
        ( 'select high_value
           from DBA_TAB_PARTITIONS where table_owner = ''' || t.table_owner || ''' and table_name = ''' || t.table_name || ''' and PARTITION_NAME = ''' || t.PARTITION_NAME || ''''),
        '//text()' ) as high_value 
  FROM DBA_TAB_PARTITIONS t
  WHERE T.table_owner = 'V7PRO' )
;

方式三 利用DBMS_OUTPUT.PUT_LINE

通过游标CURSOR（猜测是游标的隐式转换功能），利用DBMS_OUTPUT.PUT_LINE将游标中的值进行输出，同样可以达到快速读取的目的。

DECLARE
   CURSOR c_par_list IS
   SELECT TABLE_OWNER,TABLE_NAME,HIGH_VALUE, PARTITION_NAME
   FROM ALL_TAB_PARTITIONS
   WHERE TABLE_OWNER = 'V7PRO' ORDER BY TABLE_OWNER,TABLE_NAME,PARTITION_NAME;
BEGIN
   FOR c_rec IN c_par_list LOOP
      DBMS_OUTPUT.PUT_LINE('TABLE_OWNER:'||c_rec.TABLE_OWNER||' TABLE_NAME:'||c_rec.TABLE_NAME||' PART_NAME:'||c_rec.PARTITION_NAME||' HIGH_VALUE:'||c_rec.HIGH_VALUE);
   END LOOP;
END;
/

方式四 在SQLPLUS执行

在sqlplus 中执行，无需多余的操作，直接就可以看到对应的high_value值，通过开启10046发现，其实也用了CURSOR形式进行了转换。

SQL> set pagesize 999
SQL> set linesize 200
SQL> col table_owner for a20
SQL> col TABLE_NAME for a20
SQL> col PARTITION_NAME for a40
SQL> col HIGH_VALUE for a30
SQL> SELECT TABLE_OWNER,TABLE_NAME, PARTITION_NAME,HIGH_VALUE
  2     FROM ALL_TAB_PARTITIONS
  3     WHERE TABLE_OWNER = 'V7PRO' ORDER BY TABLE_OWNER,TABLE_NAME,PARTITION_NAME;

TABLE_OWNER          TABLE_NAME           PARTITION_NAME                           HIGH_VALUE
-------------------- -------------------- ---------------------------------------- ------------------------------
V7PRO                DATE_TYPE_DAY_DZZ_01 DATE_TYPE_DAY_DZZ_01_20221001            '20221002'
V7PRO                DATE_TYPE_DAY_DZZ_01 DATE_TYPE_DAY_DZZ_01_20221002            '20221003'
V7PRO                DATE_TYPE_DAY_DZZ_01 DATE_TYPE_DAY_DZZ_01_20221003            '20221004'
V7PRO                DATE_TYPE_DAY_DZZ_01 DATE_TYPE_DAY_DZZ_01_20221004            '20221005'
V7PRO                DATE_TYPE_DAY_DZZ_01 DATE_TYPE_DAY_DZZ_01_20221005            '20221006'
V7PRO                DATE_TYPE_DAY_DZZ_01 DATE_TYPE_DAY_DZZ_01_20221006            '20221007'
V7PRO                DATE_TYPE_DAY_DZZ_01 DATE_TYPE_DAY_DZZ_01_20221007            '20221008'
V7PRO                DATE_TYPE_DAY_DZZ_01 DATE_TYPE_DAY_DZZ_01_20221008            '20221009'
V7PRO                DATE_TYPE_DAY_DZZ_01 DATE_TYPE_DAY_DZZ_01_20221009            '20221010'
V7PRO                DATE_TYPE_DAY_DZZ_01 DATE_TYPE_DAY_DZZ_01_20221010            '20221011'
V7PRO                DATE_TYPE_DAY_DZZ_01 DATE_TYPE_DAY_DZZ_01_20221011            '20221012'
V7PRO                DATE_TYPE_DAY_DZZ_01 DATE_TYPE_DAY_DZZ_01_20221012            '20221013'
V7PRO                DATE_TYPE_DAY_DZZ_01 DATE_TYPE_DAY_DZZ_01_20221013            '20221014'
V7PRO                DATE_TYPE_DAY_DZZ_01 DATE_TYPE_DAY_DZZ_01_20221014            '20221015'
V7PRO                DATE_TYPE_DAY_DZZ_01 DATE_TYPE_DAY_DZZ_01_20221015            '20221016'
V7PRO                DATE_TYPE_DAY_DZZ_01 DATE_TYPE_DAY_DZZ_01_20221016            '20221017'
V7PRO                DATE_TYPE_DAY_DZZ_01 DATE_TYPE_DAY_DZZ_01_20221017            '20221018'
V7PRO                DATE_TYPE_DAY_DZZ_01 DATE_TYPE_DAY_DZZ_01_20221018            '20221019'
V7PRO                DATE_TYPE_DAY_DZZ_01 DATE_TYPE_DAY_DZZ_01_20221019            '20221020'
V7PRO                DATE_TYPE_DAY_DZZ_01 DATE_TYPE_DAY_DZZ_01_20221020            '20221021'

10046 trace 文件 部分信息

=====================
PARSING IN CURSOR #140041420475264 len=161 dep=0 uid=0 oct=3 lid=0 tim=1733814102154708 hv=1505680370 ad='ac274770' sqlid='ft4fb9jcvxqzk'   <=======
SELECT TABLE_OWNER,TABLE_NAME, PARTITION_NAME,HIGH_VALUE
   FROM ALL_TAB_PARTITIONS
   WHERE TABLE_OWNER = 'V7PRO' ORDER BY TABLE_OWNER,TABLE_NAME,PARTITION_NAME
END OF STMT
PARSE #140041420475264:c=48848,e=48849,p=0,cr=7,cu=0,mis=1,r=0,dep=0,og=1,plh=2138864332,tim=1733814102154705
EXEC #140041420475264:c=80,e=80,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2138864332,tim=1733814102154888
WAIT #140041420475264: nam='SQL*Net message to client' ela= 6 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1733814102154945
WAIT #140041420475264: nam='SQL*Net message from client' ela= 165 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1733814102155539

总结

通过以上4种方法均可快速查找到ALL_TAB_PARTITIONS中的HIGH_VALUE值，其中方式一在开发人员看来，比较容易接受，也有利于数据的后续分析，方式三、四作为日常的查询，也是相当不错。