[20260121]关于display_raw函数.txt

来源：这里教程网时间：2026-03-03 23:13:57 作者：

[20260121]关于display_raw函数.txt --//在查看表统计信息的字段最大以及最小值，经常会使用定义的函数display_raw，在tpt的desc11.sql脚本前面有一段该函数的定义以 --//及说明。 --//在生产系统我使用的脚本许多情况并没有使用该函数，主要原因我们许多系统是11g，许多情况下生产系统不允许在sys下建立函数， --//或许以前没有注意这些细节问题。 -- descxx11.sql is for Oracle 11.x and lower. Use descxx.sql if you are on Oracle 12.1 or newer -- -- On versions older than 12c you need to create a PL/SQL stored procedure: -- descxx.sql requires the display_raw function which is included in the comment section below. -- the display_raw function is taken from Greg Rahn's blog as I'm too lazy to write one myself -- http://structureddata.org/2007/10/16/how-to-display-high_valuelow_value-columns-from-user_tab_col_statistics/ -- -- create or replace function display_raw (rawval raw, type varchar2) --return varchar2 --is -- cn number; -- cv varchar2(128); -- cd date; -- cnv nvarchar2(128); -- cr rowid; -- cc char(128) --begin -- if (type = 'NUMBER') then -- dbms_stats.convert_raw_value(rawval, cn); -- return to_char(cn); -- elsif (type = 'VARCHAR2') then -- dbms_stats.convert_raw_value(rawval, cv); -- return to_char(cv); -- elsif (type = 'DATE') then -- dbms_stats.convert_raw_value(rawval, cd); -- return to_char(cd); -- elsif (type = 'NVARCHAR2') then -- dbms_stats.convert_raw_value(rawval, cnv); -- return to_char(cnv); -- elsif (type = 'ROWID') then -- dbms_stats.convert_raw_value(rawval, cr); -- return to_char(cnv); -- elsif (type = 'CHAR') then -- dbms_stats.convert_raw_value(rawval, cc); -- return to_char(cc); -- else -- return 'UNKNOWN DATATYPE'; -- end if; --end; --/ -- -- grant execute on display_raw to public; -- create public synonym display_raw for display_raw; --//今天检查发现rowid以及NVARCHAR2类型并不存在dbms_stats.convert_raw_value函数。通过测试说明问题。 1.环境： SYS@book> @ ver2 ============================== PORT_STRING : x86_64/Linux 2.4.xx VERSION : 21.0.0.0.0 BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production CON_ID : 0 PL/SQL procedure successfully completed. SYS@book> @ descv sys.dbms_stats "-A 3 convert_raw_value" PROCEDURE ALTER_DATABASE_TAB_MONITORING Argument Name Type In/Out Default? ------------------------------ ------------- ------ -------- PROCEDURE CONVERT_RAW_VALUE Argument Name Type In/Out Default? RAWVAL RAW IN RESVAL VARCHAR2 OUT PROCEDURE CONVERT_RAW_VALUE Argument Name Type In/Out Default? RAWVAL RAW IN RESVAL DATE OUT PROCEDURE CONVERT_RAW_VALUE Argument Name Type In/Out Default? RAWVAL RAW IN RESVAL NUMBER OUT PROCEDURE CONVERT_RAW_VALUE Argument Name Type In/Out Default? RAWVAL RAW IN RESVAL BINARY_FLOAT OUT PROCEDURE CONVERT_RAW_VALUE Argument Name Type In/Out Default? RAWVAL RAW IN RESVAL BINARY_DOUBLE OUT PROCEDURE CONVERT_RAW_VALUE_NVARCHAR ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Argument Name Type In/Out Default? RAWVAL RAW IN RESVAL NVARCHAR2 OUT PROCEDURE CONVERT_RAW_VALUE_ROWID ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Argument Name Type In/Out Default? RAWVAL RAW IN RESVAL ROWID OUT --//注意看下划线支持nvarchar2，rowid转换的类型的函数是CONVERT_RAW_VALUE_NVARCHAR，CONVERT_RAW_VALUE_ROWID。 --//仔细看转换不支持timestamp类型。 2.使用原来的函数测试看看。 COTT@book01p> create table t2 ( id number ,vc nvarchar2(32),vrowid rowid); Table created. --//表字段包含rowid类型，那里会使用不是很清楚。 SCOTT@book01p> insert into t2 (id ,vc ) select rownum,dbms_random.string('a',16) from dual connect by level <1e3; 999 rows created. SCOTT@book01p> update t2 set vrowid=rowid ; 999 rows updated. SCOTT@book01p> commit ; Commit complete. --//分析表略。 SCOTT@book01p> @ desc t2 Name Null? Type ------- ----- ------------- 1 ID NUMBER 2 VC NVARCHAR2(32) 3 VROWID ROWID SCOTT@book01p> @ desc12c t2 '' eXtended describe of t2 DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT OWNER.TABLE_NAME <filters> SAMPLE : @ desczz TABLE_NAME column_name1,column_name2 IF NOT INPUT <filters> ,USE "" . Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value ----- ---------- ----------- ------------------- ---- ----------- ----- ------------- ------------ -------------- ---------- --------- ----------- ---------------------------------------- ---------------------------------------- SCOTT T2 999 2026-01-21 10:45:38 1 ID NUMBER(,) 999 .00100100100 0 1 1 999 999 2026-01-21 10:45:38 2 VC NVARCHAR2(64) 999 .00100100100 0 1 A B U F j O D o h r a q w A f c z t K f P s K g P Q K z B Y t o 999 2026-01-21 10:45:38 3 VROWID ROWID(10) 999 .00100100100 0 1 訊C 訊N --//很明显vc，vrowid的转换出了问题,vc的显示字段包括空格。 --//注:desc12c.sql直接在sql语句的开头使用函数，另外发现1个小问题，查询的NVARCHAR2(64)，估计存在byte，字符单位不同导致的. --//如果插入汉字到vc字段可以看出明显的问题。 SCOTT@book01p> insert into t2 (id ,vc ) values (1e3 ,'文化'); 1 row created. SCOTT@book01p> commit ; Commit complete. --//分析表。 COTT@book01p> @ desc12c t2 '' eXtended describe of t2 DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT OWNER.TABLE_NAME <filters> SAMPLE : @ desczz TABLE_NAME column_name1,column_name2 IF NOT INPUT <filters> ,USE "" . Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value ----- ---------- ----------- ------------------- ---- ----------- ----- ------------- ------------ -------------- --------- --------- ----------- ---------------------------------------- --------------- SCOTT T2 1000 2026-01-21 11:05:20 1 ID NUMBER(,) 1000 .00100000000 0 1 1 1000 1000 2026-01-21 11:05:20 2 VC NVARCHAR2(64) 1000 .00100000000 0 1 A B U F j O D o h r a q w A f c e嘢 999 2026-01-21 11:05:20 3 VROWID ROWID(10) 999 .00100100100 1 1 訊C 訊N --//vc的最大值根本不对,采用descxx.sql脚本： SCOTT@book01p> @ tpt/descxx t2 Col# Column Name Null? Type # distinct Density # nulls Histogram # buckets Low Value High Value ---- ----------- ---------- ------------- ---------- -------------- ------- --------- --------- -------------------------------- ------------ 1 ID NUMBER(,) 1000 .00100000000 0 HYBRID 254 1 1000 2 VC NVARCHAR2(64) 1000 .00100000000 0 HYBRID 254 A B U F j O D o h r a q w A f c e嘢 3 VROWID ROWID(10) 999 .00100100000 1 HYBRID 254 訊C 訊N --//很明显vc，vrowid的转换出了问题,vc的显示字段包括空格,最大值也是乱码。 --//修改采用函数是CONVERT_RAW_VALUE_NVARCHAR，CONVERT_RAW_VALUE_ROWID后。 --//修改desc12c.sql脚本后测试。 SCOTT@book01p> @ desc12c t2 '' eXtended describe of t2 DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT OWNER.TABLE_NAME <filters> SAMPLE : @ desczz TABLE_NAME column_name1,column_name2 IF NOT INPUT <filters> ,USE "" . Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value ----- ---------- ----------- ------------------- ---- ----------- ----- ------------- ------------ -------------- --------- --------- ----------- ------------------ ------------------ SCOTT T2 1000 2026-01-21 11:05:20 1 ID NUMBER(,) 1000 .00100000000 0 1 1 1000 1000 2026-01-21 11:05:20 2 VC NVARCHAR2(64) 1000 .00100000000 0 1 ABUFjODohraqwAfc 文化 999 2026-01-21 11:05:20 3 VROWID ROWID(10) 999 .00100100100 1 1 AAAtONAAMAAAAhDAAA AAAtONAAMAAAAhOACH --//更正后显示正常!! --//使用我自己写的desczz.sql脚本，里面使用UTL_RAW.cast_to_XXXX函数。 SCOTT@book01p> @ descv sys.UTL_RAW "-A 5 cast_to_" FUNCTION BIT_AND RETURNS RAW Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- FUNCTION CAST_TO_BINARY_DOUBLE RETURNS BINARY_DOUBLE Argument Name Type In/Out Default? R RAW IN ENDIANESS BINARY_INTEGER IN DEFAULT FUNCTION CAST_TO_BINARY_FLOAT RETURNS BINARY_FLOAT Argument Name Type In/Out Default? R RAW IN ENDIANESS BINARY_INTEGER IN DEFAULT FUNCTION CAST_TO_BINARY_INTEGER RETURNS BINARY_INTEGER Argument Name Type In/Out Default? R RAW IN ENDIANESS BINARY_INTEGER IN DEFAULT FUNCTION CAST_TO_NUMBER RETURNS NUMBER Argument Name Type In/Out Default? R RAW IN FUNCTION CAST_TO_NVARCHAR2 RETURNS NVARCHAR2 Argument Name Type In/Out Default? R RAW IN FUNCTION CAST_TO_RAW RETURNS RAW Argument Name Type In/Out Default? C VARCHAR2 IN FUNCTION CAST_TO_VARCHAR2 RETURNS VARCHAR2 Argument Name Type In/Out Default? R RAW IN FUNCTION COMPARE RETURNS NUMBER Argument Name Type In/Out Default? R1 RAW IN --//该函数没有date,timestamp,rowid的转换。 COTT@book01p> @ desczz t2 '' eXtended describe of t2 DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT OWNER.TABLE_NAME <filters> SAMPLE : @ desczz TABLE_NAME column_name1,column_name2 IF NOT INPUT <filters> ,USE "" . Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value ----- ---------- ----------- ------------------- ---- ----------- ----- ------------- ------------ ------------ --------- --------- ------------ ---------------- ----------- SCOTT T2 1000 2026-01-21 11:05:20 1 ID NUMBER(,) 1000 .00100000000 0 1 1 1000 1000 2026-01-21 11:05:20 2 VC NVARCHAR2(64) 1000 .00100000000 0 1 ABUFjODohraqwAfc 文化 999 2026-01-21 11:05:20 3 VROWID ROWID(10) 999 .00100100100 1 1 --//注：我写的脚本不支持rowid转换。我一直使用我自己写的desczz.sql脚本，该问题一直很难发现或者忽略了。 --//说明该函数的正确写法如下： /* Formatted on 2026-01-21 11:01:45 (QP5 v5.277) */ CREATE OR REPLACE FUNCTION display_raw (rawval RAW, TYPE VARCHAR2) RETURN VARCHAR2 IS cn NUMBER; CV VARCHAR2 (128); cd DATE; cnv NVARCHAR2 (128); cr ROWID; cc CHAR (128); cbf BINARY_FLOAT; cbd BINARY_DOUBLE; BEGIN IF (TYPE = 'VARCHAR2') THEN DBMS_STATS.convert_raw_value (rawval, CV); RETURN TO_CHAR (CV); ELSIF (TYPE = 'DATE') THEN DBMS_STATS.convert_raw_value (rawval, cd); RETURN TO_CHAR (cd); ELSIF (TYPE = 'NUMBER') THEN DBMS_STATS.convert_raw_value (rawval, cn); RETURN TO_CHAR (cn); ELSIF (TYPE = 'BINARY_FLOAT') THEN DBMS_STATS.convert_raw_value (rawval, cbf); RETURN TO_CHAR (cbf); ELSIF (TYPE = 'BINARY_DOUBLE') THEN DBMS_STATS.convert_raw_value (rawval, cbd); RETURN TO_CHAR (cbd); ELSIF (TYPE = 'NVARCHAR2') THEN DBMS_STATS.convert_raw_value_nvarchar (rawval, cnv); RETURN TO_CHAR (cnv); ELSIF (TYPE = 'ROWID') THEN DBMS_STATS.convert_raw_value_rowid (rawval, cr); RETURN TO_CHAR (cr); ELSIF (TYPE = 'CHAR') THEN DBMS_STATS.convert_raw_value (rawval, cc); RETURN TO_CHAR (cc); ELSE RETURN 'UNKNOWN DATATYPE'; END IF; END; / 3.小结: --//任何问题都不要想当然，许多情况直接拿别人的脚本，在使用前还是给仔细阅读，特别对于生产系统使用，再次强调测试再测试, --//不要忽视一些小的细节。 4.附上测试使用的脚本代码： $ cat descv.sql set linesize 80 set term off def _desc_tmpfile=&_tpt_tempdir/desc_&_tpt_tempfile..tmp spool &_desc_tmpfile desc &1 spool off set term on set linesize 269 --host sed -n '1,/----------/p;/&2/Ip' tmpdesc.txt --host sed -n '1,/----------/p' &_desc_tmpfile ; egrep -i "&2" &_desc_tmpfile host sed -n '0,/----------/p' &_desc_tmpfile ; sed -n '/----------/,$p' &_desc_tmpfile | grep -v -- "----------"| egrep -i &2 --host &_DELETE tmpdesc.txt host &_DELETE &_desc_tmpfile --//注：_tpt_tempdir,_&_tpt_tempfile在tpt脚本包的init.sql初始化定义。另外参数2最好要使用双引号，特殊情况下还要加入单引号 --//，我以前也考虑写成egrep -i "&2",这样无法再追加参数,比如像前面的-A 3参数，但是如果查询包括|字符就存在问题，例子如下： SCOTT@book01p> @ descv t2 id|vc Name Null? Type ------------------------ ----- -------------- /bin/bash: vc: command not found grep: write error: Broken pipe SCOTT@book01p> @ descv t2 "id|vc" Name Null? Type ------------------------ ----- -------------- /bin/bash: vc: command not found grep: write error: Broken pipe SCOTT@book01p> @ descv t2 "'id|vc'" Name Null? Type ------------------------ ----- -------------- 1 ID NUMBER 2 VC NVARCHAR2(32) 3 VROWID ROWID SCOTT@book01p> @ descv t2 '"id|rowid"' Name Null? Type ------------------------ ----- -------------- 1 ID NUMBER 3 VROWID ROWID --//加入单双引号组合就可以查询，不知道有什么好方法解决该问题。 $ cat desc12c.sql /* Formatted on 2026-01-20 09:31:46 (QP5 v5.277) */ -- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions. COL desc_column_id HEAD "Col#" FOR A4 COL desc_column_name HEAD "Column Name" FOR A20 COL desc_data_type HEAD "Type" FOR A20 WORD_WRAP COL desc_nullable HEAD "Null?" FOR A10 COL desc_density HEAD "Density" FOR 9.99999999999 COL desc_owner HEAD Owner FOR A10 COL desc_table_name HEAD Table_Name FOR A20 COL trans_low HEAD "Low_value" FOR A40 COL trans_high HEAD "High_value" FOR A40 PROMPT eXtended describe of &1 PROMPT PROMPT DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. PROMPT INPUT OWNER.TABLE_NAME <filters> PROMPT SAMPLE : @ desczz TABLE_NAME column_name1,column_name2 PROMPT IF NOT INPUT <filters> ,USE "" . PROMPT SET TERMOUT OFF COLUMN column_string NEW_VALUE column_string FORMAT a200 COLUMN 2 NEW_VALUE 2 SELECT NULL "2" FROM DUAL WHERE 1 = 2; SELECT DECODE ('&2', NULL, '*', '', '*', '1=1', '*', TRANSLATE ('&2', ',%', '|*')) "2" ,DECODE ( '&2' ,'', '1=1' ,'1', '1=1' ,'1=1', '1=1' ,'column_name in (' || '''' || REPLACE (UPPER ('&2'), ',', ''',''') || ''')') column_string FROM DUAL; FROM DUAL; --select decode('&2','','1=1','1','1=1','1=1','1=1','column_name in ('||''''||replace(upper('&2'),',',''',''')||''')' ) column_string from dual ; --select decode('&2','','1=1','1','1=1','1=1','1=1',''''||replace(upper('&2'),',',''',''')||'''') column_string from dual ; SET TERMOUT ON BREAK ON desc_owner ON desc_table_name SKIP 1 WITH FUNCTION display_raw (rawval RAW, TYPE VARCHAR2) RETURN VARCHAR2 IS CV VARCHAR2 (128); cd DATE; cn NUMBER; cbf BINARY_FLOAT; cbd BINARY_DOUBLE; cnv NVARCHAR2 (128); cr ROWID; cc CHAR (128); BEGIN IF (TYPE = 'VARCHAR2') THEN DBMS_STATS.convert_raw_value (rawval, CV); RETURN TO_CHAR (CV); ELSIF (TYPE = 'DATE') THEN DBMS_STATS.convert_raw_value (rawval, cd); RETURN TO_CHAR (cd); ELSIF (TYPE = 'NUMBER') THEN DBMS_STATS.convert_raw_value (rawval, cn); RETURN TO_CHAR (cn); ELSIF (TYPE = 'BINARY_FLOAT') THEN DBMS_STATS.convert_raw_value (rawval, cbf); RETURN TO_CHAR (cbf); ELSIF (TYPE = 'BINARY_DOUBLE') THEN DBMS_STATS.convert_raw_value (rawval, cbd); RETURN TO_CHAR (cbd); ELSIF (TYPE = 'NVARCHAR2') THEN DBMS_STATS.convert_raw_value_nvarchar (rawval, cnv); RETURN TO_CHAR (cnv); ELSIF (TYPE = 'ROWID') THEN DBMS_STATS.convert_raw_value_rowid (rawval, cr); RETURN TO_CHAR (cr); ELSIF (TYPE = 'CHAR') THEN DBMS_STATS.convert_raw_value (rawval, cc); RETURN TO_CHAR (cc); ELSE RETURN 'UNKNOWN DATATYPE'; END IF; END; SELECT owner desc_owner ,table_name desc_table_name ,sample_size ,last_analyzed ,CASE WHEN hidden_column = 'YES' THEN 'H' ELSE ' ' END || LPAD (column_id, 3) desc_column_id ,column_name desc_column_name ,CASE WHEN nullable = 'N' THEN 'NOT NULL' ELSE NULL END AS desc_nullable , data_type || CASE -- WHEN data_type = 'NUMBER' THEN '('||data_precision||CASE WHEN data_scale = 0 THEN NULL ELSE ','||data_scale END||')' WHEN data_type = 'NUMBER' THEN '(' || data_precision || ',' || data_scale || ')' ELSE '(' || data_length || ')' END AS desc_data_type -- data_default, ,num_distinct ,density desc_density ,num_nulls ,CASE WHEN histogram = 'NONE' THEN NULL ELSE histogram END histogram ,num_buckets ,display_raw (low_value, data_type) trans_low ,display_raw (high_value, data_type) trans_high FROM dba_tab_cols WHERE UPPER (table_name) LIKE UPPER (CASE WHEN INSTR ('&&1', '.') > 0 THEN SUBSTR ('&&1', INSTR ('&&1', '.') + 1) ELSE '&&1' END) AND owner LIKE CASE WHEN INSTR ('&&1', '.') > 0 THEN UPPER (SUBSTR ('&&1', 1, INSTR ('&&1', '.') - 1)) ELSE USER END --AND column_name = decode('&&2','',column_name,upper('&&2')) --AND (&column_string) -- and column_name in (&column_string) AND (REGEXP_LIKE (LOWER (COLUMN_NAME), LOWER ('&&2'))) ORDER BY owner, table_name, column_id /

编辑推荐：

返回列表

[20260121]关于display_raw函数.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推