This discussion is archived
8 Replies Latest reply: Nov 7, 2012 9:29 AM by user8922650 RSS

Oracle Workspace Mgr as documented and are not getting the expected results

user8922650 Newbie
Currently Being Moderated
FYI, there is a SR 3-6382714641 and logged bug 14846323 for this...

I am just posting this to see if anyone else can answer this.

I would appreciate any help!

Thank you!

Here is documentation:
http://docs.oracle.com/cd/E11882_01/appdev.112/e11826/long_intro.htm

Environment:
Windows XP 32 bit Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production (vanilla install with all defaults no patches)
I get the same in Solaris 11g R2 db

Here is what I am trying to with Oracle Workspace Manager:
1. Version enable a table(with data in the table) with ‘VIEW_WO_OVERWRITE’
2. Update the table in three points in time(beginning, middle and end) setting wm_valid=WMSYS.WM_PERIOD(date, DBMS_WM.UNTIL_CHANGED) in the update stmt
3. Query the Version table enabled in three points in time using dbms_wm.gotoDate(date) and WM_CONTAINS(bdate, edate)

The issue I am having is I can query the version enabled table for the beginning before any changes and the newest/future wm_valid time. But, I cannot query for the middle time.

Below is example of what response I am getting from Oracle Workspace Mgr and what I am expecting. The full scenario is below
SQL>
SQL> begin
2 dbms_wm.gotoDate(TO_DATE('10-18-1998', 'MM-DD-YYYY'));
3 end;
4 /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT a.stuff_id,
2 a.PLACE,
3 a.name,
4 TO_CHAR (a.wm_valid.VALIDFROM, 'dd-mon-yyyy hh24:mi:ss') a_valid_start,
5 TO_CHAR (a.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') a_valid_end
6 from BBH_STUFF a
7 WHERE a.stuff_ID = 'IGPE';

no rows selected

SQL>
SQL>

Above should have returned:
STUFF_ current NAME A_VALID_START A_VALID_END
------ ---------- ------------------------- -------------------- --------------------
IGPE 15 Los Angeles Intl 17-oct-1998 00:00:00


Here is the full scenario:
SQL>
SQL> @report_test3o
SQL> set echo off
MY_WORKSPACE CUR_SYSDATE GETVALIDFROM GETVALIDTILL
-------------------- -------------------- -------------------- --------------------
LIVE 01-nov-2012 09:40:24

SQL> SELECT a.stuff_id,
2           a.PLACE "current",
3           a.name,
4           TO_CHAR (a.wm_valid.VALIDFROM, 'dd-mon-yyyy hh24:mi:ss') a_valid_start,
5           TO_CHAR (a.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') a_valid_end
6           from BBH_STUFF a
7           WHERE a.stuff_ID = 'IGPE';
STUFF_ current NAME A_VALID_START A_VALID_END
------ ---------- ------------------------- -------------------- --------------------
IGPE 32.3088 Los Angeles Intl 01-jan-1900 00:00:00 01-jan-9999 00:00:00

SQL> set echo off
STUFF_ current HISTORY NAME NAME WM_VERSION W A_VALID_START A_VALID_END H_VALID_START H_VALID_END TRANACTION_START TRANSACTION_END
------ ---------- ---------- ------------------------- ------------------------- ---------- - -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
IGPE 32.3088 32.3088 Los Angeles Intl Los Angeles Intl 0 I 01-jan-1900 00:00:00 01-jan-9999 00:00:00 01-jan-1900 00:00:00 01-jan-9999 00:00:00 01-nov-2012 09:39:09

SQL>
SQL> -- Set valid time period to virtually all time.
SQL> rem EXECUTE DBMS_WM.SetValidTime(TO_DATE('01-01-1900', 'MM-DD-YYYY'), TO_DATE('01-01-9999', 'MM-DD-YYYY'));
SQL> rem @report_test3o
SQL>
SQL>
SQL> var dt_1 varchar2(21)
SQL>
SQL> begin
2 select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') into :dt_1 from dual;
3 end;
4 /

PL/SQL procedure successfully completed.

SQL>
SQL> select :dt_1 from dual;
:DT_1
--------------------------------
01.11.2012 09:40:24

SQL>
SQL>
SQL>
SQL>
SQL> UPDATE BBH_STUFF
2 SET PLACE=15,
3 wm_valid=WMSYS.WM_PERIOD(TO_DATE('10-17-1998', 'MM-DD-YYYY'), DBMS_WM.UNTIL_CHANGED)
4 WHERE stuff_ID = 'IGPE';

1 row updated.

SQL> @report_test3o
SQL> set echo off
MY_WORKSPACE CUR_SYSDATE GETVALIDFROM GETVALIDTILL
-------------------- -------------------- -------------------- --------------------
LIVE 01-nov-2012 09:40:24

SQL> SELECT a.stuff_id,
2           a.PLACE "current",
3           a.name,
4           TO_CHAR (a.wm_valid.VALIDFROM, 'dd-mon-yyyy hh24:mi:ss') a_valid_start,
5           TO_CHAR (a.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') a_valid_end
6           from BBH_STUFF a
7           WHERE a.stuff_ID = 'IGPE';
STUFF_ current NAME A_VALID_START A_VALID_END
------ ---------- ------------------------- -------------------- --------------------
IGPE 15 Los Angeles Intl 17-oct-1998 00:00:00

SQL> set echo off
STUFF_ current HISTORY NAME NAME WM_VERSION W A_VALID_START A_VALID_END H_VALID_START H_VALID_END TRANACTION_START TRANSACTION_END
------ ---------- ---------- ------------------------- ------------------------- ---------- - -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
IGPE 15 32.3088 Los Angeles Intl Los Angeles Intl 0 I 17-oct-1998 00:00:00 01-jan-1900 00:00:00 01-jan-9999 00:00:00 01-nov-2012 09:39:09 01-nov-2012 09:40:24
IGPE 15 15 Los Angeles Intl Los Angeles Intl 0 D 17-oct-1998 00:00:00 01-jan-1900 00:00:00 17-oct-1998 00:00:00 01-nov-2012 09:40:24
IGPE 15 15 Los Angeles Intl Los Angeles Intl 0 I 17-oct-1998 00:00:00 17-oct-1998 00:00:00 01-nov-2012 09:40:24

SQL>
SQL>
SQL>
SQL>
SQL> UPDATE BBH_STUFF
2 SET name=name||'BBH',
3 wm_valid=WMSYS.WM_PERIOD(to_date(:dt_1, 'dd.mm.yyyy hh24:mi:ss'), DBMS_WM.UNTIL_CHANGED)
4 WHERE stuff_ID = 'IGPE';

1 row updated.

SQL> @report_test3o
SQL> set echo off
MY_WORKSPACE CUR_SYSDATE GETVALIDFROM GETVALIDTILL
-------------------- -------------------- -------------------- --------------------
LIVE 01-nov-2012 09:40:24

SQL> SELECT a.stuff_id,
2           a.PLACE "current",
3           a.name,
4           TO_CHAR (a.wm_valid.VALIDFROM, 'dd-mon-yyyy hh24:mi:ss') a_valid_start,
5           TO_CHAR (a.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') a_valid_end
6           from BBH_STUFF a
7           WHERE a.stuff_ID = 'IGPE';
STUFF_ current NAME A_VALID_START A_VALID_END
------ ---------- ------------------------- -------------------- --------------------
IGPE 15 Los Angeles IntlBBH 01-nov-2012 09:40:24

SQL> set echo off
STUFF_ current HISTORY NAME NAME WM_VERSION W A_VALID_START A_VALID_END H_VALID_START H_VALID_END TRANACTION_START TRANSACTION_END
------ ---------- ---------- ------------------------- ------------------------- ---------- - -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
IGPE 15 32.3088 Los Angeles IntlBBH Los Angeles Intl 0 I 01-nov-2012 09:40:24 01-jan-1900 00:00:00 01-jan-9999 00:00:00 01-nov-2012 09:39:09 01-nov-2012 09:40:24
IGPE 15 15 Los Angeles IntlBBH Los Angeles Intl 0 D 01-nov-2012 09:40:24 01-jan-1900 00:00:00 17-oct-1998 00:00:00 01-nov-2012 09:40:24
IGPE 15 15 Los Angeles IntlBBH Los Angeles Intl 0 I 01-nov-2012 09:40:24 17-oct-1998 00:00:00 01-nov-2012 09:40:24 01-nov-2012 09:40:24
IGPE 15 15 Los Angeles IntlBBH Los Angeles IntlBBH 0 D 01-nov-2012 09:40:24 17-oct-1998 00:00:00 01-nov-2012 09:40:24 01-nov-2012 09:40:24
IGPE 15 15 Los Angeles IntlBBH Los Angeles IntlBBH 0 I 01-nov-2012 09:40:24 01-nov-2012 09:40:24 01-nov-2012 09:40:24

SQL>
SQL>
SQL>
SQL>
SQL> UPDATE BBH_STUFF
2 SET PLACE=14,
3 wm_valid=WMSYS.WM_PERIOD(TO_DATE('02-26-2014', 'MM-DD-YYYY'), DBMS_WM.UNTIL_CHANGED)
4 WHERE stuff_ID = 'IGPE';

1 row updated.

SQL> @report_test3o
SQL> set echo off
MY_WORKSPACE CUR_SYSDATE GETVALIDFROM GETVALIDTILL
-------------------- -------------------- -------------------- --------------------
LIVE 01-nov-2012 09:40:24

SQL> SELECT a.stuff_id,
2           a.PLACE "current",
3           a.name,
4           TO_CHAR (a.wm_valid.VALIDFROM, 'dd-mon-yyyy hh24:mi:ss') a_valid_start,
5           TO_CHAR (a.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') a_valid_end
6           from BBH_STUFF a
7           WHERE a.stuff_ID = 'IGPE';
STUFF_ current NAME A_VALID_START A_VALID_END
------ ---------- ------------------------- -------------------- --------------------
IGPE 14 Los Angeles IntlBBH 26-feb-2014 00:00:00

SQL> set echo off
STUFF_ current HISTORY NAME NAME WM_VERSION W A_VALID_START A_VALID_END H_VALID_START H_VALID_END TRANACTION_START TRANSACTION_END
------ ---------- ---------- ------------------------- ------------------------- ---------- - -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
IGPE 14 32.3088 Los Angeles IntlBBH Los Angeles Intl 0 I 26-feb-2014 00:00:00 01-jan-1900 00:00:00 01-jan-9999 00:00:00 01-nov-2012 09:39:09 01-nov-2012 09:40:24
IGPE 14 15 Los Angeles IntlBBH Los Angeles Intl 0 D 26-feb-2014 00:00:00 01-jan-1900 00:00:00 17-oct-1998 00:00:00 01-nov-2012 09:40:24
IGPE 14 15 Los Angeles IntlBBH Los Angeles Intl 0 I 26-feb-2014 00:00:00 17-oct-1998 00:00:00 01-nov-2012 09:40:24 01-nov-2012 09:40:24
IGPE 14 15 Los Angeles IntlBBH Los Angeles IntlBBH 0 D 26-feb-2014 00:00:00 17-oct-1998 00:00:00 01-nov-2012 09:40:24 01-nov-2012 09:40:24
IGPE 14 15 Los Angeles IntlBBH Los Angeles IntlBBH 0 I 26-feb-2014 00:00:00 01-nov-2012 09:40:24 01-nov-2012 09:40:24 01-nov-2012 09:40:24
IGPE 14 14 Los Angeles IntlBBH Los Angeles IntlBBH 0 D 26-feb-2014 00:00:00 01-nov-2012 09:40:24 26-feb-2014 00:00:00 01-nov-2012 09:40:24
IGPE 14 14 Los Angeles IntlBBH Los Angeles IntlBBH 0 I 26-feb-2014 00:00:00 26-feb-2014 00:00:00 01-nov-2012 09:40:24

7 rows selected.

SQL>
SQL>
SQL> SELECT a.stuff_id,
2           a.PLACE,
3           a.name,
4           TO_CHAR (a.wm_valid.VALIDFROM, 'dd-mon-yyyy hh24:mi:ss') a_valid_start,
5           TO_CHAR (a.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') a_valid_end
6           from BBH_STUFF a
7           WHERE a.stuff_ID = 'IGPE'
8           AND WM_CONTAINS(a.wm_valid, WM_PERIOD(TO_DATE('10-17-1998', 'MM-DD-YYYY'), DBMS_WM.UNTIL_CHANGED)) = 1;

no rows selected

SQL>
SQL>
SQL> SELECT a.stuff_id,
2           a.PLACE,
3           a.name,
4           TO_CHAR (a.wm_valid.VALIDFROM, 'dd-mon-yyyy hh24:mi:ss') a_valid_start,
5           TO_CHAR (a.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') a_valid_end
6           from BBH_STUFF a
7           WHERE a.stuff_ID = 'IGPE'
8           AND WM_CONTAINS(a.wm_valid,WM_PERIOD(TO_DATE('02-26-2014', 'MM-DD-YYYY'), DBMS_WM.UNTIL_CHANGED)) = 1;
STUFF_ PLACE NAME A_VALID_START A_VALID_END
------ ---------- ------------------------- -------------------- --------------------
IGPE 14 Los Angeles IntlBBH 26-feb-2014 00:00:00

SQL>
SQL> SELECT a.stuff_id,
2           a.PLACE,
3           a.name,
4           TO_CHAR (a.wm_valid.VALIDFROM, 'dd-mon-yyyy hh24:mi:ss') a_valid_start,
5           TO_CHAR (a.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') a_valid_end
6           from BBH_STUFF a
7           WHERE a.stuff_ID = 'IGPE';
STUFF_ PLACE NAME A_VALID_START A_VALID_END
------ ---------- ------------------------- -------------------- --------------------
IGPE 14 Los Angeles IntlBBH 26-feb-2014 00:00:00

SQL>
SQL>
SQL> begin
2 dbms_wm.gotoDate(TO_DATE('10-10-1998', 'MM-DD-YYYY'));
3 end;
4 /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT a.stuff_id,
2           a.PLACE,
3           a.name,
4           TO_CHAR (a.wm_valid.VALIDFROM, 'dd-mon-yyyy hh24:mi:ss') a_valid_start,
5           TO_CHAR (a.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') a_valid_end
6           from BBH_STUFF a
7           WHERE a.stuff_ID = 'IGPE';

no rows selected

SQL>
SQL> begin
2 dbms_wm.gotoDate(TO_DATE('10-18-1998', 'MM-DD-YYYY'));
3 end;
4 /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT a.stuff_id,
2           a.PLACE,
3           a.name,
4           TO_CHAR (a.wm_valid.VALIDFROM, 'dd-mon-yyyy hh24:mi:ss') a_valid_start,
5           TO_CHAR (a.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') a_valid_end
6           from BBH_STUFF a
7           WHERE a.stuff_ID = 'IGPE';

no rows selected

SQL>
SQL>
SQL> BEGIN DBMS_WM.SetValidTime(TO_DATE('10-18-1998', 'MM-DD-YYYY'), DBMS_WM.UNTIL_CHANGED); END;
2 /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT a.stuff_id,
2           a.PLACE,
3           a.name,
4           TO_CHAR (a.wm_valid.VALIDFROM, 'dd-mon-yyyy hh24:mi:ss') a_valid_start,
5           TO_CHAR (a.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') a_valid_end
6           from BBH_STUFF a
7           WHERE a.stuff_ID = 'IGPE';

no rows selected

SQL>
SQL>
SQL> begin
2 dbms_wm.gotoDate(TO_DATE('02-27-2014', 'MM-DD-YYYY'));
3 end;
4 /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT a.stuff_id,
2           a.PLACE,
3           a.name,
4           TO_CHAR (a.wm_valid.VALIDFROM, 'dd-mon-yyyy hh24:mi:ss') a_valid_start,
5           TO_CHAR (a.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') a_valid_end
6           from BBH_STUFF a
7           WHERE a.stuff_ID = 'IGPE';
STUFF_ PLACE NAME A_VALID_START A_VALID_END
------ ---------- ------------------------- -------------------- --------------------
IGPE 14 Los Angeles IntlBBH 26-feb-2014 00:00:00

SQL>
SQL> begin
2 dbms_wm.gotoDate(to_date(:dt_1, 'dd.mm.yyyy hh24:mi:ss'));
3 end;
4 /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT a.stuff_id,
2           a.PLACE,
3           a.name,
4           TO_CHAR (a.wm_valid.VALIDFROM, 'dd-mon-yyyy hh24:mi:ss') a_valid_start,
5           TO_CHAR (a.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') a_valid_end
6           from BBH_STUFF a
7           WHERE a.stuff_ID = 'IGPE';
STUFF_ PLACE NAME A_VALID_START A_VALID_END
------ ---------- ------------------------- -------------------- --------------------
IGPE 32.3088 Los Angeles Intl 01-jan-1900 00:00:00 01-jan-9999 00:00:00

SQL>
SQL> begin
2 dbms_wm.gotoDate(to_date(:dt_1, 'dd.mm.yyyy hh24:mi:ss')+1);
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select TO_CHAR (to_date(:dt_1, 'dd.mm.yyyy hh24:mi:ss')+1, 'dd-mon-yyyy hh24:mi:ss') from dual;
TO_CHAR(TO_DATE(:DT_
--------------------
02-nov-2012 09:40:24

SQL>
SQL> SELECT a.stuff_id,
2           a.PLACE,
3           a.name,
4           TO_CHAR (a.wm_valid.VALIDFROM, 'dd-mon-yyyy hh24:mi:ss') a_valid_start,
5           TO_CHAR (a.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') a_valid_end
6           from BBH_STUFF a
7           WHERE a.stuff_ID = 'IGPE';
STUFF_ PLACE NAME A_VALID_START A_VALID_END
------ ---------- ------------------------- -------------------- --------------------
IGPE 14 Los Angeles IntlBBH 26-feb-2014 00:00:00

SQL>
SQL>
SQL> begin
2 dbms_wm.gotoDate(to_date(:dt_1, 'dd.mm.yyyy hh24:mi:ss')+(1/86400));
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select TO_CHAR (to_date(:dt_1, 'dd.mm.yyyy hh24:mi:ss')+(1/86400), 'dd-mon-yyyy hh24:mi:ss') from dual;
TO_CHAR(TO_DATE(:DT_
--------------------
01-nov-2012 09:40:25

SQL>
SQL> SELECT a.stuff_id,
2           a.PLACE,
3           a.name,
4           TO_CHAR (a.wm_valid.VALIDFROM, 'dd-mon-yyyy hh24:mi:ss') a_valid_start,
5           TO_CHAR (a.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') a_valid_end
6           from BBH_STUFF a
7           WHERE a.stuff_ID = 'IGPE';
STUFF_ PLACE NAME A_VALID_START A_VALID_END
------ ---------- ------------------------- -------------------- --------------------
IGPE 14 Los Angeles IntlBBH 26-feb-2014 00:00:00

SQL>
SQL> rollback;

Rollback complete.

SQL> @report_test3o
SQL> set echo off
MY_WORKSPACE CUR_SYSDATE GETVALIDFROM GETVALIDTILL
-------------------- -------------------- -------------------- --------------------
LIVE 01-nov-2012 09:40:25 18-oct-1998 00:00:00

SQL> SELECT a.stuff_id,
2           a.PLACE "current",
3           a.name,
4           TO_CHAR (a.wm_valid.VALIDFROM, 'dd-mon-yyyy hh24:mi:ss') a_valid_start,
5           TO_CHAR (a.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') a_valid_end
6           from BBH_STUFF a
7           WHERE a.stuff_ID = 'IGPE';
STUFF_ current NAME A_VALID_START A_VALID_END
------ ---------- ------------------------- -------------------- --------------------
IGPE 32.3088 Los Angeles Intl 01-jan-1900 00:00:00 01-jan-9999 00:00:00

SQL> set echo off
STUFF_ current HISTORY NAME NAME WM_VERSION W A_VALID_START A_VALID_END H_VALID_START H_VALID_END TRANACTION_START TRANSACTION_END
------ ---------- ---------- ------------------------- ------------------------- ---------- - -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
IGPE 32.3088 32.3088 Los Angeles Intl Los Angeles Intl 0 I 01-jan-1900 00:00:00 01-jan-9999 00:00:00 01-jan-1900 00:00:00 01-jan-9999 00:00:00 01-nov-2012 09:39:09

SQL> spool off
  • 1. Re: Oracle Workspace Mgr as documented and are not getting the expected results
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    GotoDate and SetValidTime are independent of each other. GotoDate has nothing to do with the valid time values of any rows, only the transaction times. So, when you execute dbms_wm.gotoDate(TO_DATE('10-18-1998', 'MM-DD-YYYY')), that will give you a view of the data as it existed in 1998. Since the table wasn't created until 2012, no rows are going to be returned. Instead, you would want to execute dbms_wm.SetValidTime(to_date('01-01-1998', 'mm-dd-yyyy'), null). That will give you all of the rows that have a valid range that overlaps 1998 to dbms_wm.MAX_TIME. If you want to guarantee that you only get a single row for a particular primary key you could use the dbms_wm.SetValidTimeFilterON procedure.

    Regards,
    Ben
  • 2. Re: Oracle Workspace Mgr as documented and are not getting the expected results
    user8922650 Newbie
    Currently Being Moderated
    Hi Ben,

    Thank you very much for your post! I appreciate it! I tried what I interrupted you had said in your post. Unfortunately, that did not resolve the problem with getting correct answer on the 1998 valid time in the three points in time(beginning, middle and end). I get the same answer to my select regardless of what I set for DBMS_WM.SetValidTime. It does not matter if I set the validthrough to null or dbms_wm.MAX_TIME. If I set dbms_wm.SetValidTimeFilterON and DBMS_WM.SetValidTime(TO_DATE('01-01-1998', 'MM-DD-YYYY'), dbms_wm.MAX_TIME) I get no rows back.

    If there is a place I can upload the scripts so you could play with them... please let me know at bryan.houghton@jeppesen.com.

    Thank you!

    I should get the following answer for the year 1998:
    STUFF_ current NAME A_VALID_START A_VALID_END
    ---------- --------------------
    IGPE 15 Los Angeles Intl 17-oct-1998 00:00:00

    Here is what I tried:
    I did the three updates as before(see below full log) Below select is what I get without setting DBMS_WM.SetValidTime :
    SQL> SELECT a.stuff_id,
    2           a.PLACE,
    3           a.name,
    4           TO_CHAR (a.wm_valid.VALIDFROM, 'dd-mon-yyyy hh24:mi:ss') a_valid_start,
    5           TO_CHAR (a.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') a_valid_end
    6           from BBH_STUFF a
    7           WHERE a.stuff_ID = 'IGPE';
    STUFF_ PLACE NAME A_VALID_START A_VALID_END
    ------ ---------- ------------------------- -------------------- --------------------
    IGPE 14 Los Angeles IntlBBH 26-feb-2014 00:00:00

    SQL>
    SQL>
    SQL>
    SQL> execute DBMS_WM.SetValidTime(TO_DATE('01-01-1998', 'MM-DD-YYYY'), null);

    PL/SQL procedure successfully completed.

    SQL>
    SQL> SELECT DBMS_WM.GetWorkspace() my_Workspace,
    2      TO_CHAR (sysdate, 'dd-mon-yyyy hh24:mi:ss') cur_sysdate,
    3      TO_CHAR(DBMS_WM.GetValidFrom, 'dd-mon-yyyy hh24:mi:ss') GetValidFrom,
    4      TO_CHAR(DBMS_WM.GetValidTill, 'dd-mon-yyyy hh24:mi:ss') GetValidTill FROM DUAL;
    MY_WORKSPACE CUR_SYSDATE GETVALIDFROM GETVALIDTILL
    -------------------- -------------------- -------------------- --------------------
    LIVE 06-nov-2012 10:21:08 01-jan-1998 00:00:00

    SQL>
    SQL> SELECT a.stuff_id,
    2           a.PLACE,
    3           a.name,
    4           TO_CHAR (a.wm_valid.VALIDFROM, 'dd-mon-yyyy hh24:mi:ss') a_valid_start,
    5           TO_CHAR (a.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') a_valid_end
    6           from BBH_STUFF a
    7           WHERE a.stuff_ID = 'IGPE';
    STUFF_ PLACE NAME A_VALID_START A_VALID_END
    ------ ---------- ------------------------- -------------------- --------------------
    IGPE 14 Los Angeles IntlBBH 26-feb-2014 00:00:00

    SQL>
    SQL>
    SQL> execute DBMS_WM.SetValidTime(TO_DATE('01-01-1998', 'MM-DD-YYYY'), dbms_wm.MAX_TIME);

    PL/SQL procedure successfully completed.

    SQL>
    SQL> SELECT DBMS_WM.GetWorkspace() my_Workspace,
    2      TO_CHAR (sysdate, 'dd-mon-yyyy hh24:mi:ss') cur_sysdate,
    3      TO_CHAR(DBMS_WM.GetValidFrom, 'dd-mon-yyyy hh24:mi:ss') GetValidFrom,
    4      TO_CHAR(DBMS_WM.GetValidTill, 'dd-mon-yyyy hh24:mi:ss') GetValidTill FROM DUAL;
    MY_WORKSPACE CUR_SYSDATE GETVALIDFROM GETVALIDTILL
    -------------------- -------------------- -------------------- --------------------
    LIVE 06-nov-2012 10:21:08 01-jan-1998 00:00:00 31-dec-9999 23:59:59

    SQL>
    SQL> SELECT a.stuff_id,
    2           a.PLACE,
    3           a.name,
    4           TO_CHAR (a.wm_valid.VALIDFROM, 'dd-mon-yyyy hh24:mi:ss') a_valid_start,
    5           TO_CHAR (a.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') a_valid_end
    6           from BBH_STUFF a
    7           WHERE a.stuff_ID = 'IGPE';
    STUFF_ PLACE NAME A_VALID_START A_VALID_END
    ------ ---------- ------------------------- -------------------- --------------------
    IGPE 14 Los Angeles IntlBBH 26-feb-2014 00:00:00

    SQL>
    SQL>
    SQL> execute dbms_wm.SetValidTimeFilterON;

    PL/SQL procedure successfully completed.

    SQL>
    SQL> SELECT DBMS_WM.GetWorkspace() my_Workspace,
    2      TO_CHAR (sysdate, 'dd-mon-yyyy hh24:mi:ss') cur_sysdate,
    3      TO_CHAR(DBMS_WM.GetValidFrom, 'dd-mon-yyyy hh24:mi:ss') GetValidFrom,
    4      TO_CHAR(DBMS_WM.GetValidTill, 'dd-mon-yyyy hh24:mi:ss') GetValidTill FROM DUAL;
    MY_WORKSPACE CUR_SYSDATE GETVALIDFROM GETVALIDTILL
    -------------------- -------------------- -------------------- --------------------
    LIVE 06-nov-2012 10:21:08 01-jan-1998 00:00:00 31-dec-9999 23:59:59

    SQL>
    SQL> SELECT a.stuff_id,
    2           a.PLACE,
    3           a.name,
    4           TO_CHAR (a.wm_valid.VALIDFROM, 'dd-mon-yyyy hh24:mi:ss') a_valid_start,
    5           TO_CHAR (a.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') a_valid_end
    6           from BBH_STUFF a
    7           WHERE a.stuff_ID = 'IGPE';

    no rows selected



    Here is the full log:
    SQL>
    SQL> drop table BBH_STUFF;
    drop table BBH_STUFF
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist


    SQL> CREATE TABLE BBH_STUFF
    2 (
    3 STUFF_SYS_ID     NUMBER(16,4),
    4 STUFF_UNQ_SYS_ID NUMBER(16),
    5 STUFF_ID      VARCHAR2(6 BYTE),
    6 NAME          VARCHAR2(50 BYTE),
    7 PLACE          NUMBER(16,10)
    8 );

    Table created.

    SQL>
    SQL> ALTER TABLE BBH_STUFF ADD (
    2 PRIMARY KEY
    3 (STUFF_UNQ_SYS_ID));

    Table altered.

    SQL>
    SQL>
    SQL> Insert into BBH_STUFF(STUFF_SYS_ID, STUFF_UNQ_SYS_ID, STUFF_ID, NAME, PLACE)
    2 Values (10000005087, 594476, 'IGPE', 'Los Angeles Intl', 32.3088);

    1 row created.

    SQL>
    SQL> COMMIT;

    Commit complete.

    SQL>
    SQL>
    SQL> begin
    2 DBMS_WM.EnableVersioning (
    3 table_name=>'BBH_STUFF',
    4 hist=>'VIEW_WO_OVERWRITE',
    5 isTopology=>FALSE,
    6 validTime=>TRUE,
    7 validTimeRange=>WM_PERIOD(TO_DATE('01-01-1900', 'MM-DD-YYYY'), TO_DATE('01-01-9999', 'MM-DD-YYYY'))
    8 );
    9 end;
    10 /

    PL/SQL procedure successfully completed.

    SQL> var dt_1 varchar2(21)
    SQL>
    SQL> begin
    2 select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') into :dt_1 from dual;
    3 end;
    4 /

    PL/SQL procedure successfully completed.

    SQL>
    SQL> select :dt_1 from dual;
    :DT_1
    --------------------------------
    06.11.2012 10:21:08

    SQL>
    SQL>
    SQL> UPDATE BBH_STUFF
    2 SET PLACE=15,
    3 wm_valid=WMSYS.WM_PERIOD(TO_DATE('10-17-1998', 'MM-DD-YYYY'), DBMS_WM.UNTIL_CHANGED)
    4 WHERE stuff_ID = 'IGPE';

    1 row updated.

    SQL>
    SQL>
    SQL> UPDATE BBH_STUFF
    2 SET name=name||'BBH',
    3 wm_valid=WMSYS.WM_PERIOD(to_date(:dt_1, 'dd.mm.yyyy hh24:mi:ss'), DBMS_WM.UNTIL_CHANGED)
    4 WHERE stuff_ID = 'IGPE';

    1 row updated.

    SQL>
    SQL>
    SQL>
    SQL> UPDATE BBH_STUFF
    2 SET PLACE=14,
    3 wm_valid=WMSYS.WM_PERIOD(TO_DATE('02-26-2014', 'MM-DD-YYYY'), DBMS_WM.UNTIL_CHANGED)
    4 WHERE stuff_ID = 'IGPE';

    1 row updated.

    SQL>
    SQL> @report_test3o_sh
    SQL> set echo off
    SQL>
    SQL>
    SQL> SELECT DBMS_WM.GetWorkspace() my_Workspace,
    2      TO_CHAR (sysdate, 'dd-mon-yyyy hh24:mi:ss') cur_sysdate,
    3      TO_CHAR(DBMS_WM.GetValidFrom, 'dd-mon-yyyy hh24:mi:ss') GetValidFrom,
    4      TO_CHAR(DBMS_WM.GetValidTill, 'dd-mon-yyyy hh24:mi:ss') GetValidTill FROM DUAL;
    MY_WORKSPACE CUR_SYSDATE GETVALIDFROM GETVALIDTILL
    -------------------- -------------------- -------------------- --------------------
    LIVE 06-nov-2012 10:21:08

    SQL>
    SQL>
    SQL> set echo on
    SQL> SELECT a.stuff_id,
    2           a.PLACE "current",
    3           a.name,
    4           TO_CHAR (a.wm_valid.VALIDFROM, 'dd-mon-yyyy hh24:mi:ss') a_valid_start,
    5           TO_CHAR (a.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') a_valid_end
    6           from BBH_STUFF a
    7           WHERE a.stuff_ID = 'IGPE';
    STUFF_ current NAME A_VALID_START A_VALID_END
    ------ ---------- ------------------------- -------------------- --------------------
    IGPE 14 Los Angeles IntlBBH 26-feb-2014 00:00:00

    SQL>
    SQL>
    SQL> SELECT a.stuff_id,
    2           a.PLACE "current",
    3           h.PLACE History,
    4           a.name,
    5           h.name,
    6           h.wm_version,
    7           h.wm_optype,
    8           TO_CHAR (a.wm_valid.VALIDFROM, 'dd-mon-yyyy hh24:mi:ss') a_valid_start,
    9           TO_CHAR (a.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') a_valid_end,
    10           TO_CHAR (h.wm_valid.VALIDFROM,'dd-mon-yyyy hh24:mi:ss') h_valid_start,
    11           TO_CHAR (h.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') h_valid_end,
    12           TO_CHAR (h.wm_createtime, 'dd-mon-yyyy hh24:mi:ss') tranaction_start,
    13           TO_CHAR (h.wm_retiretime, 'dd-mon-yyyy hh24:mi:ss') transaction_end
    14           from BBH_STUFF a,BBH_STUFF_hist h
    15           WHERE a.stuff_ID = 'IGPE' AND
    16           h.stuff_sys_id(+) = a.stuff_sys_id;
    STUFF_ current HISTORY NAME NAME WM_VERSION W A_VALID_START A_VALID_END H_VALID_START H_VALID_END TRANACTION_START TRANSACTION_END
    ------ ---------- ---------- ------------------------- ------------------------- ---------- - -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
    IGPE 14 32.3088 Los Angeles IntlBBH Los Angeles Intl 0 I 26-feb-2014 00:00:00 01-jan-1900 00:00:00 01-jan-9999 00:00:00 06-nov-2012 09:33:57 06-nov-2012 10:21:08
    IGPE 14 15 Los Angeles IntlBBH Los Angeles Intl 16 D 26-feb-2014 00:00:00 01-jan-1900 00:00:00 17-oct-1998 00:00:00 06-nov-2012 10:21:08
    IGPE 14 15 Los Angeles IntlBBH Los Angeles Intl 16 I 26-feb-2014 00:00:00 17-oct-1998 00:00:00 06-nov-2012 10:21:08 06-nov-2012 10:21:08
    IGPE 14 15 Los Angeles IntlBBH Los Angeles IntlBBH 16 D 26-feb-2014 00:00:00 17-oct-1998 00:00:00 06-nov-2012 10:21:08 06-nov-2012 10:21:08
    IGPE 14 15 Los Angeles IntlBBH Los Angeles IntlBBH 16 I 26-feb-2014 00:00:00 06-nov-2012 10:21:08 06-nov-2012 10:21:08 06-nov-2012 10:21:08
    IGPE 14 14 Los Angeles IntlBBH Los Angeles IntlBBH 16 D 26-feb-2014 00:00:00 06-nov-2012 10:21:08 26-feb-2014 00:00:00 06-nov-2012 10:21:08
    IGPE 14 14 Los Angeles IntlBBH Los Angeles IntlBBH 16 I 26-feb-2014 00:00:00 26-feb-2014 00:00:00 06-nov-2012 10:21:08

    7 rows selected.

    SQL>
    SQL> SELECT a.stuff_id,
    2           a.PLACE,
    3           a.name,
    4           TO_CHAR (a.wm_valid.VALIDFROM, 'dd-mon-yyyy hh24:mi:ss') a_valid_start,
    5           TO_CHAR (a.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') a_valid_end
    6           from BBH_STUFF a
    7           WHERE a.stuff_ID = 'IGPE';
    STUFF_ PLACE NAME A_VALID_START A_VALID_END
    ------ ---------- ------------------------- -------------------- --------------------
    IGPE 14 Los Angeles IntlBBH 26-feb-2014 00:00:00

    SQL>
    SQL>
    SQL>
    SQL> execute DBMS_WM.SetValidTime(TO_DATE('01-01-1998', 'MM-DD-YYYY'), null);

    PL/SQL procedure successfully completed.

    SQL>
    SQL> SELECT DBMS_WM.GetWorkspace() my_Workspace,
    2      TO_CHAR (sysdate, 'dd-mon-yyyy hh24:mi:ss') cur_sysdate,
    3      TO_CHAR(DBMS_WM.GetValidFrom, 'dd-mon-yyyy hh24:mi:ss') GetValidFrom,
    4      TO_CHAR(DBMS_WM.GetValidTill, 'dd-mon-yyyy hh24:mi:ss') GetValidTill FROM DUAL;
    MY_WORKSPACE CUR_SYSDATE GETVALIDFROM GETVALIDTILL
    -------------------- -------------------- -------------------- --------------------
    LIVE 06-nov-2012 10:21:08 01-jan-1998 00:00:00

    SQL>
    SQL> SELECT a.stuff_id,
    2           a.PLACE,
    3           a.name,
    4           TO_CHAR (a.wm_valid.VALIDFROM, 'dd-mon-yyyy hh24:mi:ss') a_valid_start,
    5           TO_CHAR (a.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') a_valid_end
    6           from BBH_STUFF a
    7           WHERE a.stuff_ID = 'IGPE';
    STUFF_ PLACE NAME A_VALID_START A_VALID_END
    ------ ---------- ------------------------- -------------------- --------------------
    IGPE 14 Los Angeles IntlBBH 26-feb-2014 00:00:00

    SQL>
    SQL>
    SQL> execute DBMS_WM.SetValidTime(TO_DATE('01-01-1998', 'MM-DD-YYYY'), dbms_wm.MAX_TIME);

    PL/SQL procedure successfully completed.

    SQL>
    SQL> SELECT DBMS_WM.GetWorkspace() my_Workspace,
    2      TO_CHAR (sysdate, 'dd-mon-yyyy hh24:mi:ss') cur_sysdate,
    3      TO_CHAR(DBMS_WM.GetValidFrom, 'dd-mon-yyyy hh24:mi:ss') GetValidFrom,
    4      TO_CHAR(DBMS_WM.GetValidTill, 'dd-mon-yyyy hh24:mi:ss') GetValidTill FROM DUAL;
    MY_WORKSPACE CUR_SYSDATE GETVALIDFROM GETVALIDTILL
    -------------------- -------------------- -------------------- --------------------
    LIVE 06-nov-2012 10:21:08 01-jan-1998 00:00:00 31-dec-9999 23:59:59

    SQL>
    SQL> SELECT a.stuff_id,
    2           a.PLACE,
    3           a.name,
    4           TO_CHAR (a.wm_valid.VALIDFROM, 'dd-mon-yyyy hh24:mi:ss') a_valid_start,
    5           TO_CHAR (a.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') a_valid_end
    6           from BBH_STUFF a
    7           WHERE a.stuff_ID = 'IGPE';
    STUFF_ PLACE NAME A_VALID_START A_VALID_END
    ------ ---------- ------------------------- -------------------- --------------------
    IGPE 14 Los Angeles IntlBBH 26-feb-2014 00:00:00

    SQL>
    SQL>
    SQL> execute dbms_wm.SetValidTimeFilterON;

    PL/SQL procedure successfully completed.

    SQL>
    SQL> SELECT DBMS_WM.GetWorkspace() my_Workspace,
    2      TO_CHAR (sysdate, 'dd-mon-yyyy hh24:mi:ss') cur_sysdate,
    3      TO_CHAR(DBMS_WM.GetValidFrom, 'dd-mon-yyyy hh24:mi:ss') GetValidFrom,
    4      TO_CHAR(DBMS_WM.GetValidTill, 'dd-mon-yyyy hh24:mi:ss') GetValidTill FROM DUAL;
    MY_WORKSPACE CUR_SYSDATE GETVALIDFROM GETVALIDTILL
    -------------------- -------------------- -------------------- --------------------
    LIVE 06-nov-2012 10:21:08 01-jan-1998 00:00:00 31-dec-9999 23:59:59

    SQL>
    SQL> SELECT a.stuff_id,
    2           a.PLACE,
    3           a.name,
    4           TO_CHAR (a.wm_valid.VALIDFROM, 'dd-mon-yyyy hh24:mi:ss') a_valid_start,
    5           TO_CHAR (a.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') a_valid_end
    6           from BBH_STUFF a
    7           WHERE a.stuff_ID = 'IGPE';

    no rows selected

    SQL>
    SQL> execute dbms_wm.SetValidTimeFilterOFF;

    PL/SQL procedure successfully completed.

    SQL> execute DBMS_WM.SetValidTime(TO_DATE('01-01-2014', 'MM-DD-YYYY'), dbms_wm.MAX_TIME);

    PL/SQL procedure successfully completed.

    SQL>
    SQL> SELECT a.stuff_id,
    2           a.PLACE,
    3           a.name,
    4           TO_CHAR (a.wm_valid.VALIDFROM, 'dd-mon-yyyy hh24:mi:ss') a_valid_start,
    5           TO_CHAR (a.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') a_valid_end
    6           from BBH_STUFF a
    7           WHERE a.stuff_ID = 'IGPE';
    STUFF_ PLACE NAME A_VALID_START A_VALID_END
    ------ ---------- ------------------------- -------------------- --------------------
    IGPE 14 Los Angeles IntlBBH 26-feb-2014 00:00:00

    SQL>
    SQL>
    SQL>
    SQL> execute DBMS_WM.SetValidTime(TO_DATE('02-27-2014', 'MM-DD-YYYY'), dbms_wm.MAX_TIME);

    PL/SQL procedure successfully completed.

    SQL>
    SQL> SELECT a.stuff_id,
    2           a.PLACE,
    3           a.name,
    4           TO_CHAR (a.wm_valid.VALIDFROM, 'dd-mon-yyyy hh24:mi:ss') a_valid_start,
    5           TO_CHAR (a.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') a_valid_end
    6           from BBH_STUFF a
    7           WHERE a.stuff_ID = 'IGPE';
    STUFF_ PLACE NAME A_VALID_START A_VALID_END
    ------ ---------- ------------------------- -------------------- --------------------
    IGPE 14 Los Angeles IntlBBH 26-feb-2014 00:00:00

    SQL>
    SQL>
    SQL> execute DBMS_WM.SetValidTime(to_date(:dt_1, 'dd.mm.yyyy hh24:mi:ss'), dbms_wm.MAX_TIME);

    PL/SQL procedure successfully completed.

    SQL>
    SQL>
    SQL>
    SQL>
    SQL> SELECT a.stuff_id,
    2           a.PLACE,
    3           a.name,
    4           TO_CHAR (a.wm_valid.VALIDFROM, 'dd-mon-yyyy hh24:mi:ss') a_valid_start,
    5           TO_CHAR (a.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') a_valid_end
    6           from BBH_STUFF a
    7           WHERE a.stuff_ID = 'IGPE';
    STUFF_ PLACE NAME A_VALID_START A_VALID_END
    ------ ---------- ------------------------- -------------------- --------------------
    IGPE 14 Los Angeles IntlBBH 26-feb-2014 00:00:00

    SQL>
    SQL> execute DBMS_WM.SetValidTime(to_date(:dt_1, 'dd.mm.yyyy hh24:mi:ss')+1, dbms_wm.MAX_TIME);

    PL/SQL procedure successfully completed.

    SQL>
    SQL> select TO_CHAR (to_date(:dt_1, 'dd.mm.yyyy hh24:mi:ss')+1, 'dd-mon-yyyy hh24:mi:ss') from dual;
    TO_CHAR(TO_DATE(:DT_
    --------------------
    07-nov-2012 10:21:08

    SQL>
    SQL> SELECT a.stuff_id,
    2           a.PLACE,
    3           a.name,
    4           TO_CHAR (a.wm_valid.VALIDFROM, 'dd-mon-yyyy hh24:mi:ss') a_valid_start,
    5           TO_CHAR (a.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') a_valid_end
    6           from BBH_STUFF a
    7           WHERE a.stuff_ID = 'IGPE';
    STUFF_ PLACE NAME A_VALID_START A_VALID_END
    ------ ---------- ------------------------- -------------------- --------------------
    IGPE 14 Los Angeles IntlBBH 26-feb-2014 00:00:00

    SQL>
    SQL>
    SQL>
    SQL> execute DBMS_WM.SetValidTime(to_date(:dt_1, 'dd.mm.yyyy hh24:mi:ss')+(1/86400), null);

    PL/SQL procedure successfully completed.

    SQL>
    SQL> select TO_CHAR (to_date(:dt_1, 'dd.mm.yyyy hh24:mi:ss')+(1/86400), 'dd-mon-yyyy hh24:mi:ss') from dual;
    TO_CHAR(TO_DATE(:DT_
    --------------------
    06-nov-2012 10:21:09

    SQL>
    SQL> SELECT a.stuff_id,
    2           a.PLACE,
    3           a.name,
    4           TO_CHAR (a.wm_valid.VALIDFROM, 'dd-mon-yyyy hh24:mi:ss') a_valid_start,
    5           TO_CHAR (a.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') a_valid_end
    6           from BBH_STUFF a
    7           WHERE a.stuff_ID = 'IGPE';
    STUFF_ PLACE NAME A_VALID_START A_VALID_END
    ------ ---------- ------------------------- -------------------- --------------------
    IGPE 14 Los Angeles IntlBBH 26-feb-2014 00:00:00

    SQL>
    SQL> rollback;

    Rollback complete.

    SQL> @report_test3o_sh
    SQL> set echo off
    SQL>
    SQL>
    SQL> SELECT DBMS_WM.GetWorkspace() my_Workspace,
    2      TO_CHAR (sysdate, 'dd-mon-yyyy hh24:mi:ss') cur_sysdate,
    3      TO_CHAR(DBMS_WM.GetValidFrom, 'dd-mon-yyyy hh24:mi:ss') GetValidFrom,
    4      TO_CHAR(DBMS_WM.GetValidTill, 'dd-mon-yyyy hh24:mi:ss') GetValidTill FROM DUAL;
    MY_WORKSPACE CUR_SYSDATE GETVALIDFROM GETVALIDTILL
    -------------------- -------------------- -------------------- --------------------
    LIVE 06-nov-2012 10:21:08 06-nov-2012 10:21:09

    SQL>
    SQL>
    SQL> set echo on
    SQL> SELECT a.stuff_id,
    2           a.PLACE "current",
    3           a.name,
    4           TO_CHAR (a.wm_valid.VALIDFROM, 'dd-mon-yyyy hh24:mi:ss') a_valid_start,
    5           TO_CHAR (a.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') a_valid_end
    6           from BBH_STUFF a
    7           WHERE a.stuff_ID = 'IGPE';
    STUFF_ current NAME A_VALID_START A_VALID_END
    ------ ---------- ------------------------- -------------------- --------------------
    IGPE 32.3088 Los Angeles Intl 01-jan-1900 00:00:00 01-jan-9999 00:00:00

    SQL>
    SQL>
    SQL> SELECT a.stuff_id,
    2           a.PLACE "current",
    3           h.PLACE History,
    4           a.name,
    5           h.name,
    6           h.wm_version,
    7           h.wm_optype,
    8           TO_CHAR (a.wm_valid.VALIDFROM, 'dd-mon-yyyy hh24:mi:ss') a_valid_start,
    9           TO_CHAR (a.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') a_valid_end,
    10           TO_CHAR (h.wm_valid.VALIDFROM,'dd-mon-yyyy hh24:mi:ss') h_valid_start,
    11           TO_CHAR (h.wm_valid.VALIDTILL, 'dd-mon-yyyy hh24:mi:ss') h_valid_end,
    12           TO_CHAR (h.wm_createtime, 'dd-mon-yyyy hh24:mi:ss') tranaction_start,
    13           TO_CHAR (h.wm_retiretime, 'dd-mon-yyyy hh24:mi:ss') transaction_end
    14           from BBH_STUFF a,BBH_STUFF_hist h
    15           WHERE a.stuff_ID = 'IGPE' AND
    16           h.stuff_sys_id(+) = a.stuff_sys_id;
    STUFF_ current HISTORY NAME NAME WM_VERSION W A_VALID_START A_VALID_END H_VALID_START H_VALID_END TRANACTION_START TRANSACTION_END
    ------ ---------- ---------- ------------------------- ------------------------- ---------- - -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
    IGPE 32.3088 32.3088 Los Angeles Intl Los Angeles Intl 0 I 01-jan-1900 00:00:00 01-jan-9999 00:00:00 01-jan-1900 00:00:00 01-jan-9999 00:00:00 06-nov-2012 09:33:57
  • 3. Re: Oracle Workspace Mgr as documented and are not getting the expected results
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    The spooled log is somewhat difficult to read, but it does appear to be working as expected. The 3 updates that you do are non-sequenced updates. This type of update is done by explicitly specifying the wm_valid column in an update statement. This will result in the wm_valid column being updated to the specified value, but without any corresponding dmls for the non-overlapping time range(s). You can read more about sequenced vs non-sequenced updates in the documentation.

    So, in your example you have done the following:
    T1:
    update ... set wm_valid=WMSYS.WM_PERIOD(TO_DATE('10-17-1998', 'MM-DD-YYYY'), DBMS_WM.UNTIL_CHANGED)
    T2:
    update ... set wm_valid=WMSYS.WM_PERIOD(to_date(:dt_1, 'dd.mm.yyyy hh24:mi:ss'), DBMS_WM.UNTIL_CHANGED)
    T3:
    update ... set wm_valid=WMSYS.WM_PERIOD(TO_DATE('02-26-2014', 'MM-DD-YYYY'), DBMS_WM.UNTIL_CHANGED)
    T4:

    The T values are used to signify different transaction times. After each of the updates, the view of the data will only return a single row.

    Executing GotoDate(T1) will return 0 rows, GotoDate(T2) will return the [1998, null) row, GotoDate(T3) will return the [2012,null) row, and GotoDate(T4, or later) or any query based on the LATEST savepoint will return the [2014, null) row. All of these GotoDate time periods have a single row since the update is non-sequenced. Without executing GotoDate, the only row that is visible to be returned by a query on the table is the [2014, null) row which is what you were seeing in your example. Contrast this to the sequenced case:

    T1:
    update ... set wm_valid=WMSYS.WM_PERIOD(TO_DATE('10-17-1998', 'MM-DD-YYYY'), DBMS_WM.UNTIL_CHANGED)
    T2
    exec dbms_wm.setValidTime(to_date(:dt_1, 'dd.mm.yyyy hh24:mi:ss'), DBMS_WM.UNTIL_CHANGED) ;
    update ... set name=name ...
    T3
    exec dbms_wm.setValidTime(TO_DATE('02-26-2014', 'MM-DD-YYYY'), DBMS_WM.UNTIL_CHANGED) ;
    update ... set name=name ...
    T4

    I kept the non-sequenced update for the first update as it is the simpliest way to get a valid time range to have a date prior than the current date. In this case GotoDate(T1) will still return 0 rows, GotoDate(T2) will still return the [1998, null) row, GotoDate(T3) will now return 2 rows: [1998, 2012) and [2012,null), and GotoDate(T4, or later) or any query based on the LATEST savepoint will now return the 3 rows: [1998, 2012), [2012, 2014) and [2014, null). Using SetValidTime/SetValidTimeFilterON you can now set the valid time range to get a subset of these rows as needed.

    Regards,
    Ben
  • 4. Re: Oracle Workspace Mgr as documented and are not getting the expected results
    user8922650 Newbie
    Currently Being Moderated
    Hi Ben,

    Thank you very much for your posts! I appreciate it!

    Yes, I need to do transactions that are not sequenced(or sorted by valid time before applying to db).
    In my test whenever I execute this select SELECT a.PLACE||','||a.name place_name from BBH_STUFF a WHERE a.stuff_ID = 'IGPE';* I only get a single record. I never got more than one record so the dbms_wm.SetValidTimeFilterON; has nothing to filter.

    Thank you!

    Here is a condensed version of my test log:
    Insert into BBH_STUFF(STUFF_SYS_ID, STUFF_UNQ_SYS_ID, STUFF_ID, NAME, PLACE)
    Values (10000005087, 594476, 'IGPE', 'Los Angeles Intl', 32.3088);

    COMMIT;

    begin
    DBMS_WM.EnableVersioning (
    table_name=>'BBH_STUFF',
    hist=>'VIEW_WO_OVERWRITE',
    isTopology=>FALSE,
    validTime=>TRUE,
    validTimeRange=>WM_PERIOD(TO_DATE('01-01-1900', 'MM-DD-YYYY'), TO_DATE('01-01-9999', 'MM-DD-YYYY'))
    );
    end;
    /

    SELECT a.PLACE||','||a.name place_name from BBH_STUFF a WHERE a.stuff_ID = 'IGPE';
    place_name
    ==================
    32.3088,Los Angeles Intl

    UPDATE BBH_STUFF SET PLACE=15, wm_valid=WMSYS.WM_PERIOD(TO_DATE('10-17-1998', 'MM-DD-YYYY'), DBMS_WM.UNTIL_CHANGED) WHERE stuff_ID = 'IGPE';

    UPDATE BBH_STUFF SET name=name||'BBH', wm_valid=WMSYS.WM_PERIOD(to_date(:dt_1, 'dd.mm.yyyy hh24:mi:ss'), DBMS_WM.UNTIL_CHANGED) WHERE stuff_ID = 'IGPE';

    UPDATE BBH_STUFF SET PLACE=14, wm_valid=WMSYS.WM_PERIOD(TO_DATE('02-26-2014', 'MM-DD-YYYY'), DBMS_WM.UNTIL_CHANGED) WHERE stuff_ID = 'IGPE';

         SELECT a.PLACE||','||a.name place_name from BBH_STUFF a WHERE a.stuff_ID = 'IGPE';
         place_name
         ==================
         14,Los Angeles IntlBBH

    execute DBMS_WM.SetValidTime(TO_DATE('01-01-1998', 'MM-DD-YYYY'), null);
         SELECT a.PLACE||','||a.name place_name from BBH_STUFF a WHERE a.stuff_ID = 'IGPE';
         place_name
         ==================
         14,Los Angeles IntlBBH

    execute DBMS_WM.SetValidTime(TO_DATE('01-01-1998', 'MM-DD-YYYY'), dbms_wm.MAX_TIME);
         SELECT a.PLACE||','||a.name place_name from BBH_STUFF a WHERE a.stuff_ID = 'IGPE';
         place_name
         ==================
         14,Los Angeles IntlBBH
    execute dbms_wm.SetValidTimeFilterON;
         SELECT a.PLACE||','||a.name place_name from BBH_STUFF a WHERE a.stuff_ID = 'IGPE';
         no rows selected
         
    execute DBMS_WM.SetValidTime(TO_DATE('01-01-2014', 'MM-DD-YYYY'), dbms_wm.MAX_TIME);
         SELECT a.PLACE||','||a.name place_name from BBH_STUFF a WHERE a.stuff_ID = 'IGPE';
         place_name
         ==================
         14,Los Angeles IntlBBH
    execute DBMS_WM.SetValidTime(to_date(:dt_1, 'dd.mm.yyyy hh24:mi:ss'), dbms_wm.MAX_TIME);
         SELECT a.PLACE||','||a.name place_name from BBH_STUFF a WHERE a.stuff_ID = 'IGPE';
         place_name
         ==================
         14,Los Angeles IntlBBH

    rollback;

    SELECT a.PLACE||','||a.name place_name from BBH_STUFF a WHERE a.stuff_ID = 'IGPE';
    place_name
    ==================
    32.3088,Los Angeles Intl
  • 5. Re: Oracle Workspace Mgr as documented and are not getting the expected results
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    With non-sequenced updates, your log file is as expected. It keeps returning the same row because there is only one row and it overlaps with each of the time ranges your are specifying in SetValidTime.

    Regards,
    Ben
  • 6. Re: Oracle Workspace Mgr as documented and are not getting the expected results
    user8922650 Newbie
    Currently Being Moderated
    Hi Ben,

    Thank you very much for your posts! I appreciate it!

    Ok, per your last posted Nov 6, 2012 12:56 PM, I have setup my transaction's WM_PERIOD to not overlap.

    I have the following time periods(middle,end,begin):
    SET name=name||'BBH', wm_valid=WMSYS.WM_PERIOD(:dt_1,'02-25-2014')
    SET PLACE=14,wm_valid=WMSYS.WM_PERIOD('02-26-2014', DBMS_WM.UNTIL_CHANGED)
    SET PLACE=15,wm_valid=WMSYS.WM_PERIOD('10-17-1998', :dt_1 - (2/86400))

    I have the following problems/issues:
    1) With the changed nonoverlaped valid times, I can query DBMS_WM.SetValidTime('01-01-1998', dbms_wm.MAX_TIME) time frame and get a the correct place 15 but I get a incorrect name Los Angeles IntlBBH. I should have received the name Los Angeles Intl.
    2) Querying after the updates, I can not get a returned row for any other time other than SetValidTime('01-01-1998', dbms_wm.MAX_TIME) or SetValidTime('01-01-1998', null) (see below condensed log)
    3) If I change the order of apply the updates and first do the following update SET PLACE=15,wm_valid=WMSYS.WM_PERIOD('10-17-1998', :dt_1 - (2/86400))* both remaining updates(SET name=name||'BBH'... and SET PLACE=14...) update zero records.

    Thank you!

    Here is the condensed log:+
    var dt_1 varchar2(21)

    begin
    select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') into :dt_1 from dual;
    end;
    /

    Insert into BBH_STUFF(STUFF_SYS_ID, STUFF_UNQ_SYS_ID, STUFF_ID, NAME, PLACE)
    Values (10000005087, 594476, 'IGPE', 'Los Angeles Intl', 32.3088);
    1 row created.

    COMMIT;

    begin
    DBMS_WM.EnableVersioning (
    table_name=>'BBH_STUFF',
    hist=>'VIEW_WO_OVERWRITE',
    isTopology=>FALSE,
    validTime=>TRUE,
    validTimeRange=>WM_PERIOD(TO_DATE('01-01-1900', 'MM-DD-YYYY'), TO_DATE('01-01-9999', 'MM-DD-YYYY'))
    );
    end;
    /

    UPDATE BBH_STUFF SET name=name||'BBH', wm_valid=WMSYS.WM_PERIOD(to_date(:dt_1, 'dd.mm.yyyy hh24:mi:ss'), TO_DATE('02-25-2014', 'MM-DD-YYYY')) WHERE stuff_ID = 'IGPE';
    1 row updated.
    UPDATE BBH_STUFF SET PLACE=14,wm_valid=WMSYS.WM_PERIOD(TO_DATE('02-26-2014', 'MM-DD-YYYY'), DBMS_WM.UNTIL_CHANGED) WHERE stuff_ID = 'IGPE';
    1 row updated.
    UPDATE BBH_STUFF SET PLACE=15,wm_valid=WMSYS.WM_PERIOD(TO_DATE('10-17-1998', 'MM-DD-YYYY'), to_date(:dt_1, 'dd.mm.yyyy hh24:mi:ss')-(2/86400)) WHERE stuff_ID = 'IGPE';
    1 row updated.
         SELECT a.PLACE||','||a.name place_name from BBH_STUFF a WHERE a.stuff_ID = 'IGPE';
    no rows selected
    execute DBMS_WM.SetValidTime(TO_DATE('01-01-1998', 'MM-DD-YYYY'), null);

         SELECT a.PLACE||','||a.name place_name from BBH_STUFF a WHERE a.stuff_ID = 'IGPE';
    =======================
    15,Los Angeles IntlBBH

    execute DBMS_WM.SetValidTime(TO_DATE('01-01-1998', 'MM-DD-YYYY'), dbms_wm.MAX_TIME);

         SELECT a.PLACE||','||a.name place_name from BBH_STUFF a WHERE a.stuff_ID = 'IGPE';
    =======================
    15,Los Angeles IntlBBH

    execute dbms_wm.SetValidTimeFilterON;

         SELECT a.PLACE||','||a.name place_name from BBH_STUFF a WHERE a.stuff_ID = 'IGPE';
    no rows selected

    execute dbms_wm.SetValidTimeFilterOFF;
    execute DBMS_WM.SetValidTime(TO_DATE('01-01-2014', 'MM-DD-YYYY'), dbms_wm.MAX_TIME);

         SELECT a.PLACE||','||a.name place_name from BBH_STUFF a WHERE a.stuff_ID = 'IGPE';
    no rows selected

    execute DBMS_WM.SetValidTime(TO_DATE('02-27-2014', 'MM-DD-YYYY'), dbms_wm.MAX_TIME);

         SELECT a.PLACE||','||a.name place_name from BBH_STUFF a WHERE a.stuff_ID = 'IGPE';
    no rows selected
    execute DBMS_WM.SetValidTime(to_date(:dt_1, 'dd.mm.yyyy hh24:mi:ss'), dbms_wm.MAX_TIME);

         SELECT a.PLACE||','||a.name place_name from BBH_STUFF a WHERE a.stuff_ID = 'IGPE';
    no rows selected
    execute DBMS_WM.SetValidTime(to_date(:dt_1, 'dd.mm.yyyy hh24:mi:ss')+1, dbms_wm.MAX_TIME);

         select TO_CHAR (to_date(:dt_1, 'dd.mm.yyyy hh24:mi:ss')+1, 'dd-mon-yyyy hh24:mi:ss') from dual;

         SELECT a.PLACE||','||a.name place_name from BBH_STUFF a WHERE a.stuff_ID = 'IGPE';
    no rows selected
    execute DBMS_WM.SetValidTime(to_date(:dt_1, 'dd.mm.yyyy hh24:mi:ss')+(1/86400), null);

         select TO_CHAR (to_date(:dt_1, 'dd.mm.yyyy hh24:mi:ss')+(1/86400), 'dd-mon-yyyy hh24:mi:ss') from dual;

         SELECT a.PLACE||','||a.name place_name from BBH_STUFF a WHERE a.stuff_ID = 'IGPE';
    no rows selected
    rollback;

         SELECT a.PLACE||','||a.name place_name from BBH_STUFF a WHERE a.stuff_ID = 'IGPE';
    =======================
    32.3088,Los Angeles Intl
  • 7. Re: Oracle Workspace Mgr as documented and are not getting the expected results
    Ben Speckhard Pro
    Currently Being Moderated
    These results are also as expected.

    1. The first update modified the table with "name=name||'BBH'", so all of the rows after that would have the BBH since it was never removed. Each update is acting on the columns values from the previous update.

    2. After the 3 updates, the row that exists in LATEST is [1998, :dt_1-2seconds), so only queries with a valid time range that overlaps with that time range will be returned. When you executed execute dbms_wm.SetValidTimeFilterON, the default value for the filtertime parameter is the current date. Since the current row ends at :dt_1-2seconds, this row will not overlap.

    3. All updates are based on rows that can are visible within the view, which is dictated by the value specified in SetValTimeRange/SetValidTimeFilterON. Without changing the valid time range, the default range is the (systimestamp, dbms_wm.UNTIL_CHANGED). If you updated the wm_valid value to be (1998, :dt_1-2seconds) in the first update, the row would no longer be seen by the current time range, and so any further updates would not succeed until you changed the sessions' time range. A query would also return 0 rows.

    Have you tried sequenced updates? From the data you are attempting to query, it appears that you are looking to have multiple rows each with a different time range. This is what sequenced updates provides you.

    Regards,
    Ben
  • 8. Re: Oracle Workspace Mgr as documented and are not getting the expected results
    user8922650 Newbie
    Currently Being Moderated
    Hi Ben,

    Thank you very much for your posts! I appreciate it!

    Your last post recommending Sequenced updates and specifying SetValidTime date solved my problem! Thank you!

    Here is what I did:
    1)Converting to Sequenced updates(not specifying WM_VALID during update)
    2)Setting in the session DBMS_WM.SetValidTime(TO_DATE('01-01-1990', 'MM-DD-YYYY'), DBMS_WM.max_time);
    3) When query points in time using dbms_wm.SetValidTimeFilterON(+validTimeStartTime+)

    I think it also helped setting validTimeRange when executing DBMS_WM.EnableVersioning on a populated table.

    Here is the condensed log:+
    CREATE TABLE BBH_STUFF
    (
    STUFF_SYS_ID NUMBER(16,4),
    STUFF_UNQ_SYS_ID NUMBER(16),
    STUFF_ID VARCHAR2(6 BYTE),
    NAME VARCHAR2(50 BYTE),
    PLACE NUMBER(16,10)
    );

    Insert into BBH_STUFF(STUFF_SYS_ID, STUFF_UNQ_SYS_ID, STUFF_ID, NAME, PLACE)
    Values (10000005087, 594476, 'IGPE', 'Los Angeles Intl', 32.3088);

    COMMIT;


    begin
    DBMS_WM.EnableVersioning (
    table_name=>'BBH_STUFF',
    hist=>'VIEW_WO_OVERWRITE',
    isTopology=>FALSE,
    validTime=>TRUE,
    validTimeRange=>WM_PERIOD(TO_DATE('01-01-1900', 'MM-DD-YYYY'), TO_DATE('01-01-9999', 'MM-DD-YYYY'))
    );
    end;
    /

    var dt_1 varchar2(21)

    begin
    select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') into :dt_1 from dual;
    end;
    /

    EXECUTE DBMS_WM.SetValidTime(TO_DATE('10-17-1998', 'MM-DD-YYYY'), DBMS_WM.UNTIL_CHANGED);
         UPDATE BBH_STUFF SET PLACE=15 WHERE stuff_ID = 'IGPE';
    1 row updated.
    EXECUTE DBMS_WM.SetValidTime(to_date(:dt_1, 'dd.mm.yyyy hh24:mi:ss'), DBMS_WM.UNTIL_CHANGED);
         UPDATE BBH_STUFF SET name=name||'BBH' WHERE stuff_ID = 'IGPE';
    1 row updated.
    EXECUTE DBMS_WM.SetValidTime(TO_DATE('02-26-2014', 'MM-DD-YYYY'), DBMS_WM.UNTIL_CHANGED);
         UPDATE BBH_STUFF SET PLACE=14 WHERE stuff_ID = 'IGPE';
    1 row updated.
    EXECUTE DBMS_WM.SetValidTime(TO_DATE('01-01-1990', 'MM-DD-YYYY'), DBMS_WM.max_time);
         SELECT a.PLACE||','||a.name place_name from BBH_STUFF a WHERE a.stuff_ID = 'IGPE';
    place_name
    ==================
    14,Los Angeles IntlBBH
    32.3088,Los Angeles Intl
    15,Los Angeles Intl
    15,Los Angeles IntlBBH

    execute dbms_wm.SetValidTimeFilterON(TO_DATE('01-01-1998', 'MM-DD-YYYY'));
         SELECT a.PLACE||','||a.name place_name from BBH_STUFF a WHERE a.stuff_ID = 'IGPE';
    place_name
    ==================
    32.3088,Los Angeles Intl
    execute dbms_wm.SetValidTimeFilterON(TO_DATE('11-01-1998', 'MM-DD-YYYY'));
         SELECT a.PLACE||','||a.name place_name from BBH_STUFF a WHERE a.stuff_ID = 'IGPE';      
    place_name
    ==================
    15,Los Angeles Intl
    execute dbms_wm.SetValidTimeFilterON(TO_DATE('01-01-2014', 'MM-DD-YYYY'));
         SELECT a.PLACE||','||a.name place_name from BBH_STUFF a WHERE a.stuff_ID = 'IGPE';
    place_name
    ==================
    15,Los Angeles IntlBBH
    execute dbms_wm.SetValidTimeFilterON(TO_DATE('02-27-2014', 'MM-DD-YYYY'));
         SELECT a.PLACE||','||a.name place_name from BBH_STUFF a WHERE a.stuff_ID = 'IGPE';
    place_name
    ==================
    14,Los Angeles IntlBBH
    execute dbms_wm.SetValidTimeFilterON(to_date(:dt_1, 'dd.mm.yyyy hh24:mi:ss'));
         SELECT a.PLACE||','||a.name place_name from BBH_STUFF a WHERE a.stuff_ID = 'IGPE';
    place_name
    ==================
    15,Los Angeles IntlBBH
    execute dbms_wm.SetValidTimeFilterON(to_date(:dt_1, 'dd.mm.yyyy hh24:mi:ss')-(1/86400));
         SELECT a.PLACE||','||a.name place_name from BBH_STUFF a WHERE a.stuff_ID = 'IGPE';
    place_name
    ==================
    15,Los Angeles Intl

    Thank you!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points