/*
    name
        setup.sql
    version
        1.0, 2018-10-31
    author
        MalachiasTS

    purpose
        Build a table and two packages in the current schema 
        which are needed to demonstrate a problem with the 
        comparison timestamp columns in sys_refcursors.

    creates
        tables
            TMP_NLS_TEST
        packages
            P_NLS_TEST
            P_NLS_TEST_UT
*/

--drop table tmp_nls_test;

create table tmp_nls_test (
    id number,
	ats3 timestamp (3),
	ats6 timestamp (6),
	ats9 timestamp (9)
);

insert into tmp_nls_test (id, ats3, ats6, ats9) 
values (1, systimestamp, systimestamp, systimestamp);
commit;

--drop package p_nls_test;

create or replace package p_nls_test
is
    procedure insert_into_nls_test (
        i_id         tmp_nls_test.id%type,
        i_timestamp3 tmp_nls_test.ats3%type,
        i_timestamp6 tmp_nls_test.ats6%type,
        i_timestamp9 tmp_nls_test.ats9%type
    );
end p_nls_test;
/

create or replace package body p_nls_test
as
    procedure insert_into_nls_test (
        i_id         tmp_nls_test.id%type,
        i_timestamp3 tmp_nls_test.ats3%type,
        i_timestamp6 tmp_nls_test.ats6%type,
        i_timestamp9 tmp_nls_test.ats9%type
    )
    is
    begin
        insert into tmp_nls_test (id, ats3, ats6, ats9) 
            values (i_id, i_timestamp3, i_timestamp6, i_timestamp9);

        --commit;
    end insert_into_nls_test;
end p_nls_test;
/

--drop package p_nls_test_ut;

create or replace package p_nls_test_ut
is
    -- %suite(nls_test)
    -- %suitepath(nls_test)

    -- %test(check that inserts into tmp_nls_test work correctly)
    procedure ut_insert_into_nls_test;
end p_nls_test_ut;
/

create or replace package body p_nls_test_ut
is
    procedure ut_insert_into_nls_test
    is
        lv_new_id number;
        lv_ts timestamp(9);

        lc_exp sys_refcursor;
        lc_act sys_refcursor;
    begin
        -- get new id
        select max(id)+1
        into lv_new_id
        from tmp_nls_test;

        -- get a valid timestamp with timezone
        select systimestamp
        into lv_ts
        from dual;

        -- insert a record with these values
        p_nls_test.insert_into_nls_test (lv_new_id, null, lv_ts, null);


        -- build two cursors
        ut.set_nls();

        -- fill cursor with expected values by selecting from dual
        open lc_exp for
            select
                lv_new_id           as id, 
                to_timestamp (null) as ats3, 
                lv_ts               as ats6,  
                to_timestamp (null) as ats9
            from dual;

        -- fill cursor with actual table values.
        open lc_act for
            select id, ats3, ats6, ats9
            from tmp_nls_test
            where id = 2;
            
        ut.reset_nls();

        -- compare variable with table values
        ut.expect (lc_act).to_equal (lc_exp);
        
    end ut_insert_into_nls_test;
end p_nls_test_ut;
/

/*
select * from NLS_SESSION_PARAMETERS;

PARAMETER               VALUE
----------------------------------------------------------
NLS_LANGUAGE            AMERICAN
NLS_TERRITORY           AMERICA
NLS_CURRENCY            $
NLS_ISO_CURRENCY        AMERICA
NLS_NUMERIC_CHARACTERS  .,
NLS_CALENDAR            GREGORIAN
NLS_DATE_FORMAT         yyyy-mm-dd hh24:mi:ss
NLS_DATE_LANGUAGE       AMERICAN
NLS_SORT                BINARY
NLS_TIME_FORMAT         HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT    yyyy-mm-dd hh24:mi:ssxff
NLS_TIME_TZ_FORMAT      HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT yyyy-mm-dd hh24:mi:ssxff tzh:tzm
NLS_DUAL_CURRENCY       $
NLS_COMP                BINARY
NLS_LENGTH_SEMANTICS	BYTE
NLS_NCHAR_CONV_EXCP     FALSE
*/

begin
    ut.run ('p_nls_test_ut');
end;
/

/*
DBMS OUTPUT:

nls_test
  nls_test
    check that inserts into tmp_nls_test work correctly [.149 sec] (FAILED - 1)
 
Failures:
 
  1) ut_insert_into_nls_test
      Actual: refcursor [ count = 1 ] was expected to equal: refcursor [ count = 1 ]
      Diff:
      Rows: [ 1 differences ]
        Row No. 1 - Actual:   <ATS6>2018-10-31T17:11:51.150893</ATS6>
        Row No. 1 - Expected: <ATS6>2018-10-31T17:11:51.150893000</ATS6>
      at "<schema>.P_NLS_TEST_UT.UT_INSERT_INTO_NLS_TEST", line 46 ut.expect (lc_act).to_equal (lc_exp);
      
       
Finished in .15082 seconds
1 tests, 1 failed, 0 errored, 0 disabled, 0 warning(s)
 */

    gc_timestamp_format         constant varchar2(100) := 'yyyy-mm-dd"T"hh24:mi:ssxff';
->: gc_timestamp_format         constant varchar2(100) := 'yyyy-mm-dd"T"hh24:mi:ssxff6';    -- patched

    gc_timestamp_tz_format      constant varchar2(100) := 'yyyy-mm-dd"T"hh24:mi:ssxff tzh:tzm';
->: gc_timestamp_tz_format      constant varchar2(100) := 'yyyy-mm-dd"T"hh24:mi:ssxff6 tzh:tzm';  -- patched