I ran into a strange error yesterday: when I ran my test-suite from IDE I suddenly got a number of failed tests, but they were completely okay when I ran it from utPLSQL-cli.
I panicked a bit because that’s one of the worst possible situations you can imagine for a testing framework.
After some investigation, I found the problem.
Look at the following example-test suite which assures that the current date and time is set for “arrival” when a new entry is inserted into the “starport_flights”-table:
/* Simple table which contains starship-flights */ create table starport_flights ( id integer not null primary key, ship_id integer not null, arrival date default sysdate, departure date ); create or replace package ut_starport as -- %suite(Starport functionality) -- %test(Ship gets Arrival date on insert) procedure ship_gets_default_arrival; end; create or replace package body ut_starport as procedure ship_gets_default_arrival as /* Expected arrival is the current date */ l_expected_arrival date := current_date; l_actual_arrival date; begin /* Act */ insert into starport_flights ( id, ship_id ) values ( -1, -1 ); /* Assert: Actual arrival should be within 5 seconds more or less than the expected arrival */ select arrival into l_actual_arrival from starport_flights where id = -1; ut.expect(l_actual_arrival) .to_be_between( /* Assert with a bit of inaccuracy */ l_expected_arrival - interval '5' second, l_expected_arrival + interval '5' second ); end; end; / call ut.run('ut_starport');
Starport functionality Ship gets Arrival date on insert [,312 sec] (FAILED - 1) Failures: 1) ship_gets_default_arrival Actual: 2018-12-18T19:47:25 (date) was expected to be between: 2018-12-18T21:47:20 and 2018-12-18T21:47:30 at "SITHDB.UT_STARPORT.SHIP_GETS_DEFAULT_ARRIVAL", line 16 ut.expect(l_actual_arrival) Finished in ,312538 seconds 1 tests, 1 failed, 0 errored, 0 disabled, 0 warning(s)
Looks like the session in my IDE had a different timezone set than my database, while the fresh session in utPLSQL-cli had the same timezone as the database.
Depending on what you want to achieve, you should use SYSDATE or CURRENT_DATE consistently.
The following little function shows how those two differ:
/* Simple procedure to output sysdate and current_date Difference is CURRENT_DATE - SYSDATE */ create or replace procedure output_dates as l_sysdate date := sysdate; l_dbtimezone varchar2(16) := dbtimezone; l_curdate date := current_date; l_sessiontimezone varchar2(16) := sessiontimezone; begin dbms_output.PUT_LINE( 'Sysdate (' || l_dbtimezone || '): ' || to_char(l_sysdate, 'HH24:MI') || ', Current_Date (' || l_sessiontimezone || '): ' || to_char(l_curdate, 'HH24:MI') || ', Difference (in hours): ' || to_char((l_curdate-l_sysdate)*24)); end; / alter session set time_zone = '-6:00'; call output_dates(); alter session set time_zone = '+2:00'; call output_dates();
Sysdate (+00:00): 19:46, Current_Date (-06:00): 13:46, Difference (in hours): -6 Sysdate (+00:00): 19:46, Current_Date (+02:00): 21:46, Difference (in hours): 2
You can run this on LiveSQL.
The full example is available on GitHub.