Oracle WMSYS Functions and Operators
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose A group of related Workspace Manager operators of great value in date comparison
Data Types CREATE OR REPLACE TYPE wm_period AS OBJECT (
validfrom TIMESTAMP WITH TIME ZONE,
validtill TIMESTAMP WITH TIME ZONE);
Dependencies
OWM_VT_PKG    
Security Model conn / as sysdba

ALTER USER wmsys ACCOUNT UNLOCK IDENTIFIED BY wmsys;
GRANT create session TO wmsys;

We do not recommend unlocking or altering the permissions of WMSYS in an operational environment. "Best Practice" dictates that you explicitly grant EXECUTE permission on WMSYS objects you wish to utilize. The direct connection to WMSYS on this page is only to facilitate education.
Operator Names
WM_CONTAINS WM_INTERSECTION WM_MEETS
WM_EQUALS WM_LDIFF WM_OVERLAPS
WM_GREATERTHAN WM_LESSTHAN WM_RDIFF
 
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@pdbdev

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

  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@pdbdev

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

  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@pdbdev

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

  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@pdbdev

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-2021');
 p1e    DATE := TO_DATE('10-JAN-2021');
 p2b    DATE := TO_DATE('08-JAN-2021');
 p2e    DATE := TO_DATE('12-JAN-2021');
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@pdbdev

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-2021');
 p1e    DATE := TO_DATE('31-DEC-2021');
 p2b    DATE := TO_DATE('29-NOV-2021');
 p2e    DATE := TO_DATE('01-DEC-2021');
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@pdbdev

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-2021');
 p1e    DATE := TO_DATE('02-JAN-2021');
 p2b    DATE := TO_DATE('03-JAN-2021');
 p2e    DATE := TO_DATE('16-JAN-2021');
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-2021');
  p1e := TO_DATE('10-JAN-2021');

  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@pdbdev

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

  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@pdbdev

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

  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@pdbdev

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-2021');
 p1e    DATE := TO_DATE('31-DEC-2021');
 p2b    DATE := TO_DATE('12-JAN-2020');
 p2e    DATE := TO_DATE('16-JAN-2021');
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
Analytic Functions
Built-in Functions
Cast
Character Functions
Collection Functions
Conversion Functions
Miscellaneous Functions
Numeric Functions
OLAP Functions
OWM_VT_PKG
Operators, User Defined
String Functions
Timestamp
What's New In 21c
What's New In 23c

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-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx