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; / |