oracle 函数

 

/* SQL Analyze(776,1) */
SELECT /*+ full(t) no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */
      TO_CHAR (COUNT ("SCOREMULTI")),
       TO_CHAR (SUBSTRB (DUMP (MIN ("SCOREMULTI"),
                               16,
                               0,
                               32),
                         1,
                         120)),
       TO_CHAR (SUBSTRB (DUMP (MAX ("SCOREMULTI"),
                               16,
                               0,
                               32),
                         1,
                         120)),
       TO_CHAR (COUNT ("MULTIMODE")),
       TO_CHAR (SUBSTRB (DUMP (MIN ("MULTIMODE"),
                               16,
                               0,
                               32),
                         1,
                         120)),
       TO_CHAR (SUBSTRB (DUMP (MAX ("MULTIMODE"),
                               16,
                               0,
                               32),
                         1,
                         120))
  FROM "xxx"."xxx" t

empty_b|clob()
返回一个空的LOB定位符，用在初始化LOB变量，或用在INSERT及UPDATE声明去初始化LOB列或将其属性置为空。
INSERT INTO TABLE1 VALUES(EMPTY_BLOB())；
UPDATE TABLE1 SET CLOB_COL=EMPTY_BLOB()；

 LOWER (TO_CHAR (WMSYS.WM_CONCAT (column_name))) col

listagg 函数--oracle 11g release 2  新增函数
LISTAGG(ename, ',') WITHIN GROUP(ORDER BY ename) AS employees

select  'select nvl(sum(dbms_lob.getlength('||column_name||')),0) from "000".'||table_name||';'
from   all_tab_columns a
where owner='xxx' 
and data_type in ('CLOB','BLOB')