Oracle WMSYS Functions
Version 11.2
GENERAL
Data Types CREATE OR REPLACE TYPE wm_period AS OBJECT (
validfrom TIMESTAMP WITH TIME ZONE,
validtill TIMESTAMP WITH TIME ZONE);
Security conn / as sysdba
ALTER USER wmsys ACCOUNT UNLOCK IDENTIFIED BY wmsys;
WM_CONCAT
Cross-tabulates a comma delimited list WM_CONCAT(p1 IN VARCHAR2) RETURN VARCHAR2
CREATE TABLE t (
col1 VARCHAR2(5),
col2 VARCHAR2(20));
INSERT INTO t VALUES (111, 'This');
INSERT INTO t VALUES (111, 'is');
INSERT INTO t VALUES (111, 'a');
INSERT INTO t VALUES (111, 'test');
INSERT INTO t VALUES (222, 'This is not');
SELECT * FROM t;
col concat format a40
SELECT col1, wmsys.wm_concat(col2) CONCAT
FROM t
GROUP BY col1;
SELECT col1, TRANSLATE(wmsys.wm_concat(col2), 'A,', 'A ') CONCAT
FROM t
GROUP BY col1;
WM_CONTAINS
Checks if the first period contains the second period WM_CONTAINS(p1 IN WM_PERIOD, p2 IN WM_PERIOD) RETURN INTEGER;
conn wmsys/wmsys
SELECT number_of_binds
FROM user_operators
WHERE owner = 'WMSYS'
AND operator_name = 'WM_CONTAINS';
set long 1000000
SELECT dbms_metadata.get_ddl('OPERATOR', 'WM_CONTAINS') FROM dual;
set serveroutput on
DECLARE
retval INTEGER;
p1b DATE := TO_DATE('01-JAN-2009');
p1e DATE := TO_DATE('05-JAN-2009');
p2b DATE := TO_DATE('01-JAN-2009');
p2e DATE := TO_DATE('03-JAN-2009');
BEGIN
SELECT WM_CONTAINS(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
INTO retval
FROM dual;
dbms_output.put_line(retval);
p1b := TO_DATE('02-JAN-2009');
p1e := TO_DATE('06-JAN-2009');
SELECT WM_CONTAINS(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
INTO retval
FROM dual;
dbms_output.put_line(retval);
END;
/
WM_EQUALS
Checks if two periods are equal (that is, their start and end times are the same) WM_EQUALS(p1 IN WM_PERIOD, p2 IN WM_PERIOD) RETURN INTEGER;
conn wmsys/wmsys
SELECT number_of_binds
FROM user_operators
WHERE owner = 'WMSYS'
AND operator_name = 'WM_EQUALS';
set long 1000000
SELECT dbms_metadata.get_ddl('OPERATOR', 'WM_EQUALS') FROM dual;
set serveroutput on
DECLARE
retval INTEGER;
p1b DATE := TO_DATE('01-JAN-2009');
p1e DATE := TO_DATE('03-JAN-2009');
p2b DATE := TO_DATE('01-JAN-2009');
p2e DATE := TO_DATE('03-JAN-2009');
BEGIN
SELECT WM_EQUALS(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
INTO retval
FROM dual;
dbms_output.put_line(retval);
p1b := TO_DATE('01-JAN-2009');
p1e := TO_DATE('04-JAN-2009');
SELECT WM_EQUALS(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
INTO retval
FROM dual;
dbms_output.put_line(retval);
END;
/
WM_GREATERTHAN
Checks if the start of the first period is greater than (that is, later than) the end of the second period WM_GREATERTHAN(p1 IN WM_PERIOD, p2 IN WM_PERIOD) RETURN INTEGER;
conn wmsys/wmsys
SELECT number_of_binds
FROM user_operators
WHERE owner = 'WMSYS'
AND operator_name = 'WM_GREATERTHAN';
set long 1000000
SELECT dbms_metadata.get_ddl('OPERATOR', 'WM_GREATERTHAN') FROM dual;
set serveroutput on
DECLARE
retval INTEGER;
p1b DATE := TO_DATE('01-FEB-2009');
p1e DATE := TO_DATE('03-FEB-2009');
p2b DATE := TO_DATE('01-JAN-2009');
p2e DATE := TO_DATE('31-JAN-2009');
BEGIN
SELECT WM_GREATERTHAN(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
INTO retval
FROM dual;
dbms_output.put_line(retval);
p2b := TO_DATE('02-FEB-2009');
p2e := TO_DATE('31-DEC-2009');
SELECT WM_GREATERTHAN(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
INTO retval
FROM dual;
dbms_output.put_line(retval);
END;
/
WM_INTERSECTION
Returns the intersection of the two periods, that is, the time range common to both periods WM_INTERSECTION(p1 IN WM_PERIOD, p2 IN WM_PERIOD) RETURN WM_PERIOD;
conn wmsys/wmsys
SELECT number_of_binds
FROM user_operators
WHERE owner = 'WMSYS'
AND operator_name = 'WM_INTERSECTION';
set long 1000000
SELECT dbms_metadata.get_ddl('OPERATOR', 'WM_INTERSECTION') FROM dual;
set serveroutput on
DECLARE
retval WM_PERIOD;
p1b DATE := TO_DATE('01-JAN-2009');
p1e DATE := TO_DATE('10-JAN-2009');
p2b DATE := TO_DATE('08-JAN-2009');
p2e DATE := TO_DATE('12-JAN-2009');
BEGIN
SELECT WM_INTERSECTION(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
INTO retval
FROM dual;
dbms_output.put_line(retval.validfrom);
dbms_output.put_line(retval.validtill);
END;
/
WM_LDIFF
Returns the difference between the two periods on the left (that is, earlier in time) WM_LDIFF(p1 IN WM_PERIOD, p2 IN WM_PERIOD) RETURN WM_PERIOD;
conn wmsys/wmsys
SELECT number_of_binds
FROM user_operators
WHERE owner = 'WMSYS'
AND operator_name = 'WM_LDIFF';
set long 1000000
SELECT dbms_metadata.get_ddl('OPERATOR', 'WM_LDIFF') FROM dual;
set serveroutput on
DECLARE
retval WM_PERIOD;
p1b DATE := TO_DATE('01-NOV-2008');
p1e DATE := TO_DATE('31-DEC-2008');
p2b DATE := TO_DATE('29-NOV-2008');
p2e DATE := TO_DATE('01-DEC-2008');
BEGIN
SELECT WM_LDIFF(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
INTO retval
FROM dual;
dbms_output.put_line(retval.validfrom);
dbms_output.put_line(retval.validtill);
END;
/
WM_LESSTHAN
Checks if the end of the first period is less than (that is, earlier than) the start of the second period WM_LESSTHAN(p1 IN WM_PERIOD, p2 IN WM_PERIOD) RETURN INTEGER;
conn wmsys/wmsys
SELECT number_of_binds
FROM user_operators
WHERE owner = 'WMSYS'
AND operator_name = 'WM_LESSTHAN';
set long 1000000
SELECT dbms_metadata.get_ddl('OPERATOR', 'WM_LESSTHAN') FROM dual;
set serveroutput on
DECLARE
retval INTEGER;
p1b DATE := TO_DATE('01-JAN-2009');
p1e DATE := TO_DATE('02-JAN-2009');
p2b DATE := TO_DATE('03-JAN-2009');
p2e DATE := TO_DATE('16-JAN-2009');
BEGIN
SELECT WM_LESSTHAN(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
INTO retval
FROM dual;
dbms_output.put_line(retval);
p1b := TO_DATE('04-JAN-2009');
p1e := TO_DATE('10-JAN-2009');
SELECT WM_LESSTHAN(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
INTO retval
FROM dual;
dbms_output.put_line(retval);
END;
/
WM_MEETS
Checks if the end of the first period is the start of the second period WM_MEETS(p1 IN WM_PERIOD, p2 IN WM_PERIOD) RETURN INTEGER;
conn wmsys/wmsys
SELECT number_of_binds
FROM user_operators
WHERE owner = 'WMSYS'
AND operator_name = 'WM_MEETS';
set long 1000000
SELECT dbms_metadata.get_ddl('OPERATOR', 'WM_OVERLAPS') FROM dual;
set serveroutput on
DECLARE
retval INTEGER;
p1b DATE := TO_DATE('01-JAN-2009');
p1e DATE := TO_DATE('03-JAN-2009');
p2b DATE := TO_DATE('03-JAN-2009');
p2e DATE := TO_DATE('06-JAN-2009');
BEGIN
SELECT WM_MEETS(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
INTO retval
FROM dual;
dbms_output.put_line(retval);
p1b := TO_DATE('01-JAN-2009');
p1e := TO_DATE('04-JAN-2009');
SELECT WM_MEETS(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
INTO retval
FROM dual;
dbms_output.put_line(retval);
END;
/
WM_OVERLAPS
Checks if two periods overlap WM_OVERLAPS(p1 IN WM_PERIOD, p2 IN WM_PERIOD) RETURN NUMBER;
conn wmsys/wmsys
SELECT number_of_binds
FROM user_operators
WHERE owner = 'WMSYS'
AND operator_name = 'WM_OVERLAPS';
set long 1000000
SELECT dbms_metadata.get_ddl('OPERATOR', 'WM_OVERLAPS') FROM dual;
set serveroutput on
DECLARE
retval INTEGER;
p1b DATE := TO_DATE('01-JAN-2009');
p1e DATE := TO_DATE('31-JAN-2009');
p2b DATE := TO_DATE('31-DEC-2008');
p2e DATE := TO_DATE('02-JAN-2009');
BEGIN
SELECT WM_OVERLAPS(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
INTO retval
FROM dual;
dbms_output.put_line(retval);
p1b := TO_DATE('03-JAN-2009');
p1e := TO_DATE('31-JAN-2009');
SELECT WM_OVERLAPS(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
INTO retval
FROM dual;
dbms_output.put_line(retval);
END;
/
WM_RDIFF
Returns the difference between the two periods on the right (that is, later in time) WM_RDIFF(p1 IN WM_PERIOD, p2 IN WM_PERIOD) RETURN WM_PERIOD;
conn wmsys/wmsys
SELECT number_of_binds
FROM user_operators
WHERE owner = 'WMSYS'
AND operator_name = 'WM_RDIFF';
set long 1000000
SELECT dbms_metadata.get_ddl('OPERATOR', 'WM_RDIFF') FROM dual;
set serveroutput on
DECLARE
retval WM_PERIOD;
p1b DATE := TO_DATE('01-JAN-2009');
p1e DATE := TO_DATE('31-DEC-2009');
p2b DATE := TO_DATE('12-JAN-2008');
p2e DATE := TO_DATE('16-JAN-2009');
BEGIN
SELECT WM_RDIFF(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
INTO retval
FROM dual;
dbms_output.put_line(retval.validfrom);
dbms_output.put_line(retval.validtill);
END;
/
分享到：
相关推荐
Oracle 重建WMSYS用户的WMSYS.WM_CONCAT函数
WMSYS.WM_CONCAT标识符无效，需要oracle重建WMSYS用户脚本，导入该脚本后可解决该问题
解决ORA-00904: "WMSYS"."WM_CONCAT": 标识符无效 在sqlplus中执行包里的owmctab.plb、owmaggrs.plb、owmaggrb.plb三个脚本即可。 简单来说，用PL/SQL执行下一下几个脚本就可以了。 特别要注意：PL/SQL登录时，要...
解决ORA-00904: "WMSYS"."WM_CONCAT": 标识符无效 在sqlplus中执行包里的owmctab.plb、owmaggrs.plb、owmaggrb.plb三个脚本即可。
Oracle10g之后有些版本已不包含WMSYS.WM_CONCAT函数，若用到此函数， 系统会提示异常：ORA-00904: "WM_CONCAT": invalid identifier 因此需单独重建此函数，方法如下： 解压附件，在sqlplus中执行包里的owmctab....
linux 下 oracle11 XE 无WMSYS，将文件解压，并使用dba执行 @+地址+文件名 即可。例 @/home/oracle/app/oracle/product/WMSYS/owmctab.plb @/home/oracle/app/oracle/product/WMSYS/owmaggrs.plb @/home/oracle/...
Oracle 11g 重建WMSYS用户的WMSYS.WM_CONCAT函数,WMSYS用户
oracle12c_wmsys用户函数创建(1).sql
重建WMSYS用户的WMSYS.WM_CONCAT函数的3个文件重建WMSYS用户的WMSYS.WM_CONCAT函数的3个文件
ORA-00904 WMSYS.WM_CONCAT标识符无效解决方案
没有WMSYS.WM_CONCAT方法 需要的文件 owmctab.plb owmaggrs.plb owmaggrb.plb 拷贝三个文件到 $ORACLE_HOME\RDBMS\ADMIN\ 目录下 打开命令行 sqlpus , 用sys 登陆 执行命令 @$ORACLE_HOME\RDBMS\ADMIN\owmctab.plb...
功能强大的wmsys.wm_concat详细示例。
1.安装时选择的自动安装，忘了用户名和密码导致现在试了几个Oracle默认用户名密码後（表格中附带默认用户名及密码），都提示无效的用户名、密码， Username Password Description See Also CTXSYS CTXSYS The Oracle...
行转列函数，对于学习sql查询的朋友们，可能会遇到这样的要求，此文档中做了详细记载，希望可以帮到你！
wmsys_wm_concat函数结果拆解/wmsys_wm_concat函数结果拆解/wmsys_wm_concat函数结果拆解/wmsys_wm_concat函数结果拆解/
oracle 12c 兼容 11g 的 wm_concat 方法。 oracle 12c wm_concat。 owmaggrb.plb，owmaggrs.plb，owmctab.plb 1、下载三个文件：owmctab.plb 、 owmaggrs.plb 、 owmaggrb.plb 2、用sqlplus登录：sqlplus sys/123 as...
一、Oracle 10G以前使用WMSYS.WM_CONCAT： wmsys.wm_concat将字段的值用”,”来隔开。 select id,wm_concat(name) from tab_name group by id; 二、使用sys_connect_by_path sys_connect_by_path(字段名, 2个字段...