2个sql 函数dbms_lob.substr 和 wm_concat

转自：

http://blog.csdn.net/wenzhongyan/article/details/50315473

http://blog.csdn.net/ojerryzuo/article/details/53927057

1、通过dbms_lob.substr（）转换clob字段为varchar2类型

在查询dba_stat_extensions视图的时候，其中extension字段是clob类型，直接通过select语句无法显示，如下：

需要通过dbms_lob.substr（）转换

SELECT owner ,a.table_name,trim(dbms_lob.substr(extension,4000)) as extension  FROM dba_stat_extensions a 转换后显示如下：

2.

首先让我们来看看这个神奇的函数wm_concat(列名),该函数可以把列值以","号分隔起来,并显示成一行,接下来上例子,看看这个神奇的函数如何应用

准备测试数据

SQL> create table test(id number,name varchar2(20));

SQL> insert into test values(1,'a');

SQL> insert into test values(1,'b');

SQL> insert into test values(1,'c');

SQL> insert into test values(2,'d');

SQL> insert into test values(2,'e');

SQL> commit;

效果1 : 行转列

SQL> select wm_concat(name) from test;

WM_CONCAT(NAME)

-------------------------------------------------------------------------

a,b,c,d,e

效果2: 把结果里的逗号替换成"|"

SQL> select replace(wm_concat(name),',','|') from test;

REPLACE(WM_CONCAT(NAME),',','|')

-----------------------------------------------------------------------

a|b|c|d|e

效果3:按ID分组合并name

SQL> select id,wm_concat(name) name from test group by id;

ID NAME

---------- ------------------------------

1 a,b,c

2 d,e

懒人扩展用法:

案例:我要写一个视图,类似"create or replace view as select 字段1,...字段50 from tablename" ,基表有50多个字段,要是靠手工写太麻烦了,有没有什么简便的方法? 当然有了,看我如果应用wm_concat来让这个需求变简单

SQL> select 'create or replace view as select '|| wm_concat(column_name) || ' from dept'from user_tab_columns where table_name='DEPT';

'CREATEORREPLACEVIEWASSELECT'||WM_CONCAT(COLUMN_NAME)||'FROMDEPT'

--------------------------------------------------------------------------------

create or replace view as select DEPTNO,DNAME,LOC from dept

 

 

issue 1:ORA-0103

######1
SELECT TRIM(DBMS_LOB.SUBSTR(WM_CONCAT(ABC))) DATAVAL
*
ERROR at line 63:
ORA-01031: insufficient privileges

###########2
select wm_concat('aaa') from dual;

*
ERROR at line 1:
ORA-01031: insufficient privileges

###### fix  3:

grant execute on wm_concat to Dbmonopr;

#########re-create function

scp owmctab.plb owmaggrs.plb owmaggrb.plb [email protected]:/tmp/dba

1. SQL>@$ORACLE_HOME\RDBMS\ADMIN\owmctab.plb;
2. SQL>@$ORACLE_HOME\RDBMS\ADMIN\owmaggrs.plb
3. SQL>@$ORACLE_HOME\RDBMS\ADMIN\owmaggrb.plb

####

WMSYS Packages/Functions missing on 12c (文档 ID 2368194.1)

There are WMSYS functionalities that are gone in 12c, while available in 11g.

Please find the list of missing ones below, they all belong to WMSYS Owner

select * from SYS.DBA_TAB_PRIVS WHERE PRIVILEGE = 'EXECUTE' and OWNER = 'WMSYS';
WM_CONCAT_IMPL
WM_CONCAT
WM$GETDBVERSIONSTR
WM$DISALLOWQNDML
WM$CONVERTDBVERSION
OWM_9IP_PKG

Is there any change, new function or substitute for using this on Oracle 12c with WMSYS?

SOLUTION

All of those packages/functions are meant for internal use only and should not be used by customers. This is expected behavior for 12g. Some of them were dropped, others were moved into a package.

WM_CONCAT : Use listagg.
WM$GETDBVERSIONSTR : Use the v$version view or the dbms_utility.db_version procedure.
WM$DISALLOWQNDML : All this does is unconditionally raise an error. Create a procedure that raises a similar error.
WM$CONVERTDBVERSION : No alternative. Would need to write their own procedure to do the version substitution.
OWM_9IP_PKG : They should not be directly altering the session context used by Workspace Manager.