Humble Trader

Wednesday, February 01, 2006

Time / Date PL/SQL Library Test Script

Paste this code into a .sql script and run it in SQL*PLUS by the owner of the target schema.

SET SERVEROUTPUT ON SIZE 1000000;

-- Test lib_time:
DECLARE
  l_date          DATE;
  l_week          NUMBER;
  l_month         NUMBER;
  l_qtr           NUMBER;
  l_year          NUMBER;
  l_passed        BOOLEAN := TRUE;
BEGIN
  dbms_output.put_line('-');
  dbms_output.put_line('FUNCTION calendar_week test start:');
  dbms_output.put_line('-');

  dbms_output.put_line
     ('Test  1: Passing 12-JAN-2005. 1st Jan is a Saturday.');
  dbms_output.put_line
     ('Expected result: Returns 3');

  l_week := lib_time.calendar_week('12-JAN-2005');

  dbms_output.put_line('Result = ' || l_week);

  IF l_week = 3
  THEN
     dbms_output.put_line('---PASSED---');
  ELSE
     dbms_output.put_line('---FAILED---');
     l_passed := FALSE;
  END IF;

  dbms_output.put_line('-');

  dbms_output.put_line
     ('Test  2: Passing 11-JAN-2006. 1st Jan is a Sunday.');
  dbms_output.put_line
     ('Expected result: Returns 3');

  l_week := lib_time.calendar_week('11-JAN-2006');

  dbms_output.put_line('Result = ' || l_week);

  IF l_week = 3
  THEN
     dbms_output.put_line('---PASSED---');
  ELSE
     dbms_output.put_line('---FAILED---');
     l_passed := FALSE;
  END IF;

  dbms_output.put_line('-');

  dbms_output.put_line
     ('Test  3: Passing 10-JAN-2007. 1st Jan is a Monday.');
  dbms_output.put_line
     ('Expected result: Returns 2');

  l_week := lib_time.calendar_week('10-JAN-2007');

  dbms_output.put_line('Result = ' || l_week);

  IF l_week = 2
  THEN
     dbms_output.put_line('---PASSED---');
  ELSE
     dbms_output.put_line('---FAILED---');
     l_passed := FALSE;
  END IF;

  dbms_output.put_line('-');

  dbms_output.put_line
     ('Test  4: Passing 09-JAN-2008. 1st Jan is a Tuesday.');
  dbms_output.put_line
     ('Expected result: Returns 2');

  l_week := lib_time.calendar_week('09-JAN-2008');

  dbms_output.put_line('Result = ' || l_week);

  IF l_week = 2
  THEN
     dbms_output.put_line('---PASSED---');
  ELSE
     dbms_output.put_line('---FAILED---');
     l_passed := FALSE;
  END IF;

  dbms_output.put_line('-');

  dbms_output.put_line('FUNCTION calendar_week test end:');
  dbms_output.put_line('-');

  dbms_output.put_line('FUNCTION calendar_qtr test start:');
  dbms_output.put_line('-');

  dbms_output.put_line
     ('Test  1: Passing 12-JAN-2005.');
  dbms_output.put_line
     ('Expected result: Returns 1');

  l_qtr := lib_time.calendar_qtr('12-JAN-2005');

  dbms_output.put_line('Result = ' || l_qtr);

  IF l_qtr = 1
  THEN
     dbms_output.put_line('---PASSED---');
  ELSE
     dbms_output.put_line('---FAILED---');
     l_passed := FALSE;
  END IF;

  dbms_output.put_line('-');

  dbms_output.put_line
     ('Test  2: Passing 12-OCT-2005.');
  dbms_output.put_line
     ('Expected result: Returns 4');

  l_qtr := lib_time.calendar_qtr('12-OCT-2005');

  dbms_output.put_line('Result = ' || l_qtr);

  IF l_qtr = 4
  THEN
     dbms_output.put_line('---PASSED---');
  ELSE
     dbms_output.put_line('---FAILED---');
     l_passed := FALSE;
  END IF;

  dbms_output.put_line('-');

  dbms_output.put_line('FUNCTION calendar_qtr test end:');
  dbms_output.put_line('-');

  dbms_output.put_line('FUNCTION financial_week test start:');
  dbms_output.put_line('-');

  dbms_output.put_line
     ('Test  1: Passing 12-JUL-2006. 1st Jul is a Saturday.');
  dbms_output.put_line
     ('Expected result: Returns 3');

  l_week := lib_time.financial_week('12-JUL-2006');

  dbms_output.put_line('Result = ' || l_week);

  IF l_week = 3
  THEN
     dbms_output.put_line('---PASSED---');
  ELSE
     dbms_output.put_line('---FAILED---');
     l_passed := FALSE;
  END IF;

  dbms_output.put_line('-');

  dbms_output.put_line
     ('Test  2: Passing 11-JUL-2007. 1st Jul is a Sunday.');
  dbms_output.put_line
     ('Expected result: Returns 3');

  l_week := lib_time.financial_week('11-JUL-2007');

  dbms_output.put_line('Result = ' || l_week);

  IF l_week = 3
  THEN
     dbms_output.put_line('---PASSED---');
  ELSE
     dbms_output.put_line('---FAILED---');
     l_passed := FALSE;
  END IF;

  dbms_output.put_line('-');

  dbms_output.put_line
     ('Test  3: Passing 10-JUL-2013. 1st Jul is a Monday.');
  dbms_output.put_line
     ('Expected result: Returns 2');

  l_week := lib_time.financial_week('10-JUL-2013');

  dbms_output.put_line('Result = ' || l_week);

  IF l_week = 2
  THEN
     dbms_output.put_line('---PASSED---');
  ELSE
     dbms_output.put_line('---FAILED---');
     l_passed := FALSE;
  END IF;

  dbms_output.put_line('-');

  dbms_output.put_line
     ('Test  4: Passing 09-JUL-2014. 1st Jul is a Tuesday.');
  dbms_output.put_line
     ('Expected result: Returns 2');

  l_week := lib_time.financial_week('09-JUL-2014');

  dbms_output.put_line('Result = ' || l_week);

  IF l_week = 2
  THEN
     dbms_output.put_line('---PASSED---');
  ELSE
     dbms_output.put_line('---FAILED---');
     l_passed := FALSE;
  END IF;

  dbms_output.put_line('-');

  dbms_output.put_line
     ('Test  5: Passing 22-JUN-2006. 1st Jul in previous calendar year.');
  dbms_output.put_line
     ('Expected result: Returns 52');

  l_week := lib_time.financial_week('22-JUN-2006');

  dbms_output.put_line('Result = ' || l_week);

  IF l_week = 52
  THEN
     dbms_output.put_line('---PASSED---');
  ELSE
     dbms_output.put_line('---FAILED---');
     l_passed := FALSE;
  END IF;

  dbms_output.put_line('-');

  dbms_output.put_line('FUNCTION financial_week test end:');
  dbms_output.put_line('-');

  dbms_output.put_line('FUNCTION financial_month test start:');
  dbms_output.put_line('-');

  dbms_output.put_line
     ('Test  1: Passing 12-APR-2006. In first half of calendar year.');
  dbms_output.put_line
     ('Expected result: Returns 10');

  l_month := lib_time.financial_month('12-APR-2006');

  dbms_output.put_line('Result = ' || l_month);

  IF l_month = 10
  THEN
     dbms_output.put_line('---PASSED---');
  ELSE
     dbms_output.put_line('---FAILED---');
     l_passed := FALSE;
  END IF;

  dbms_output.put_line('-');

  dbms_output.put_line
     ('Test  2: Passing 12-SEP-2006. In second half of calendar year.');
  dbms_output.put_line
     ('Expected result: Returns 3');

  l_month := lib_time.financial_month('12-SEP-2006');

  dbms_output.put_line('Result = ' || l_month);

  IF l_month = 3
  THEN
     dbms_output.put_line('---PASSED---');
  ELSE
     dbms_output.put_line('---FAILED---');
     l_passed := FALSE;
  END IF;

  dbms_output.put_line('-');

  dbms_output.put_line('FUNCTION financial_month test end:');
  dbms_output.put_line('-');

  dbms_output.put_line('FUNCTION financial_qtr test start:');
  dbms_output.put_line('-');

  dbms_output.put_line
     ('Test  1: Passing 12-JAN-2005.');
  dbms_output.put_line
     ('Expected result: Returns 3');

  l_qtr := lib_time.financial_qtr('12-JAN-2005');

  dbms_output.put_line('Result = ' || l_qtr);

  IF l_qtr = 3
  THEN
     dbms_output.put_line('---PASSED---');
  ELSE
     dbms_output.put_line('---FAILED---');
     l_passed := FALSE;
  END IF;

  dbms_output.put_line('-');

  dbms_output.put_line
     ('Test  2: Passing 12-OCT-2005.');
  dbms_output.put_line
     ('Expected result: Returns 2');

  l_qtr := lib_time.financial_qtr('12-OCT-2005');

  dbms_output.put_line('Result = ' || l_qtr);

  IF l_qtr = 2
  THEN
     dbms_output.put_line('---PASSED---');
  ELSE
     dbms_output.put_line('---FAILED---');
     l_passed := FALSE;
  END IF;

  dbms_output.put_line('-');

  dbms_output.put_line('FUNCTION financial_qtr test end:');
  dbms_output.put_line('-');

  dbms_output.put_line('FUNCTION financial_year test start:');
  dbms_output.put_line('-');

  dbms_output.put_line
     ('Test  1: Passing 12-JAN-2005.');
  dbms_output.put_line
     ('Expected result: Returns 2005');

  l_year := lib_time.financial_year('12-JAN-2005');

  dbms_output.put_line('Result = ' || l_year);

  IF l_year = 2005
  THEN
     dbms_output.put_line('---PASSED---');
  ELSE
     dbms_output.put_line('---FAILED---');
     l_passed := FALSE;
  END IF;

  dbms_output.put_line('-');

  dbms_output.put_line
     ('Test  2: Passing 12-OCT-2005.');
  dbms_output.put_line
     ('Expected result: Returns 2006');

  l_year := lib_time.financial_year('12-OCT-2005');

  dbms_output.put_line('Result = ' || l_year);

  IF l_year = 2006
  THEN
     dbms_output.put_line('---PASSED---');
  ELSE
     dbms_output.put_line('---FAILED---');
     l_passed := FALSE;
  END IF;

  dbms_output.put_line('-');

  dbms_output.put_line('FUNCTION financial_qtr test end:');
  dbms_output.put_line('-');
  IF l_passed =TRUE
  THEN
     dbms_output.put_line('---PASSED---');
  ELSE
     dbms_output.put_line('---FAILED---');
  END IF;

  dbms_output.put_line('-');
END;
/

posted by Cat Play Crew at 8:37 pm

0 Comments:

Post a Comment

<< Home