Oracle WMSYS Functions
Version 11.2.0.3

General Information
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-2010');
 p1e    DATE := TO_DATE('05-JAN-2010');
 p2b    DATE := TO_DATE('01-JAN-2010');
 p2e    DATE := TO_DATE('03-JAN-2010');
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-2010');
  p1e := TO_DATE('06-JAN-2010');

  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-2010');
 p1e    DATE := TO_DATE('03-JAN-2010');
 p2b    DATE := TO_DATE('01-JAN-2010');
 p2e    DATE := TO_DATE('03-JAN-2010');
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-2010');
  p1e := TO_DATE('04-JAN-2010');

  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-2010');
 p1e    DATE := TO_DATE('03-FEB-2010');
 p2b    DATE := TO_DATE('01-JAN-2010');
 p2e    DATE := TO_DATE('31-JAN-2010');
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-2010');
  p2e := TO_DATE('31-DEC-2010');

  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-2010');
 p1e    DATE := TO_DATE('10-JAN-2010');
 p2b    DATE := TO_DATE('08-JAN-2010');
 p2e    DATE := TO_DATE('12-JAN-2010');
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-2010');
 p1e    DATE := TO_DATE('31-DEC-2010');
 p2b    DATE := TO_DATE('29-NOV-2010');
 p2e    DATE := TO_DATE('01-DEC-2010');
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-2010');
 p1e    DATE := TO_DATE('02-JAN-2010');
 p2b    DATE := TO_DATE('03-JAN-2010');
 p2e    DATE := TO_DATE('16-JAN-2010');
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-2010');
  p1e := TO_DATE('10-JAN-2010');

  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-2010');
 p1e    DATE := TO_DATE('03-JAN-2010');
 p2b    DATE := TO_DATE('03-JAN-2010');
 p2e    DATE := TO_DATE('06-JAN-2010');
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-2010');
  p1e := TO_DATE('04-JAN-2010');

  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-2010');
 p1e    DATE := TO_DATE('31-JAN-2010');
 p2b    DATE := TO_DATE('31-DEC-2009');
 p2e    DATE := TO_DATE('02-JAN-2010');
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-2010');
  p1e := TO_DATE('31-JAN-2010');

  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-2010');
 p1e    DATE := TO_DATE('31-DEC-2010');
 p2b    DATE := TO_DATE('12-JAN-2009');
 p2e    DATE := TO_DATE('16-JAN-2010');
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;
/

Related Topics
Built-in Functions
Indexes
SKIP LOCKED
System Events

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved