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
######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 admin@25.16.1.1:/tmp/dba
- SQL>@$ORACLE_HOME\RDBMS\ADMIN\owmctab.plb;
- SQL>@$ORACLE_HOME\RDBMS\ADMIN\owmaggrs.plb
- 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.
相关文章
- 数据库异常:SQL Error: 0, SQLState: S0022
- SQL SERVER 2012启动失败 because upgrade step 'SSIS_hotfix_install.sql' 失败
- SQL Server 中WITH (NOLOCK)浅析
- hive SQL COALESCE 函数
- SQL 语句递归查询 With AS 查找所有子节点
- 读书笔记--SQL必知必会05--高级数据过滤
- SQL server 中 COUNT DISTINCT 函数
- 编写高性能SQL的注意事项
- 读书笔记--SQL必知必会21--使用游标
- 读书笔记--SQL必知必会08--使用函数处理数据
- C#各种扩展名文件存入sql server数据库及读取到本地文件
- mysql 必知必会整理—sql 计算函数[六]
- 未能正确加载“VSTS for Database Professionals Sql Server Data-tier Application”包。
- 请教如何实现SQL查询24小时内,当前时间之前20分钟的信息
- sql server中自定义函数
- Sql Server 获取指定表、视图结构
- Sql Server 常用函数
- Sql Server REPLACE函数
- SQL SERVER CHARINDEX函数
- Database之SQLSever:SQL函数(字符串函数、数学函数、文本和图像函数、日期和时间函数、系统函数等)相关概念及其相关案例之详细攻略
- Database之SQLSever:SQL函数(字符串函数、数学函数、文本和图像函数、日期和时间函数、系统函数等)相关概念及其相关案例之详细攻略
- 云小课|使用SQL加密函数实现数据列的加解密
- 省时提效!5个必知必会的 SQL 窗口函数!
- 力扣 SQL 596. 超过5名学生的课
- 【SQL开发实战技巧】系列(十九):数据仓库中时间类型操作(进阶)如何一个SQL打印当月或一年的日历?如何确定某月内第一个和最后—个周内某天的日期?
- SQL中的聚合函数运用
- sql优化的几种方法
- SQL每日一练——第7天:必会常用函数