Re: TIMESTAMP in Object Type Bug

Björn Wächter wrote:
> Hello,
>
> I have a problem with a timestamp with timezone in an object type.
> What I do is. Create a type like this:
>
> CREATE OR REPLACE
> TYPE OBJ_TIMESTAMP AS OBJECT (
> TEST_TS TIMESTAMP(3) WITH LOCAL TIME ZONE
> );
>
> Create a function returning such an object:
>
> CREATE OR REPLACE FUNCTION OBJ_CURRENT_TIME
> RETURN OBJ_TIMESTAMP
> IS
> v_return OBJ_TIMESTAMP;
> BEGIN
>
> v_return := OBJ_TIMESTAMP(CURRENT_TIMESTAMP);
>
> RETURN v_return;
>
> END OBJ_CURRENT_TIME;
>
>
> If my session timezone is:
>
> SELECT sessiontimezone FROM DUAL;
>
> SESSIONTIMEZONE
> ---------------
> +02:00
>
> Everything works just fine like expected:
>
> SELECT OBJ_CURRENT_TIME().TEST_TS from dual;
>
> OBJ_CURRENT_TIME().TEST_TS
> -------------------------------
> 13-SEP-06 01.53.30.693845 PM
>
>
> But if a change to CET:
>
> ALTER SESSION SET TIME_ZONE = 'CET';
>
> SELECT sessiontimezone FROM DUAL;
>
> SESSIONTIMEZONE
> ----------------
> CET
>
> and execute the same again:
>
> SELECT OBJ_CURRENT_TIME().TEST_TS from dual;
>
> OBJ_CURRENT_TIME().TEST_TS
> ---------------------------
> 11--48 05.02.01.086455 PM
>
>
> I'm facing this problem in 10.1.0.4 and 10.2.0.1
>
> Am I doing something wrong with the object or is
> it a bug?
>
>
>
> Thanks Björn

Looks like a bug: on my 10.1.0.5 Solaris 64-bit instance I managed to get ORA-600 with your sample code. :) While session time zone is not changed, everything works as expected. Change it to anything, and boom - I get ORA-600 [kopp2pic661]. The only published note on this particular OERI doesn't seem to be related as it involves dblinks, besides it is claimed to be fixed in 10.1.0.5... If you have support contract, I'd suggest that you open a SR with Oracle Support, or I can open it with your test case and my trace files, which clearly show this is erratic server behavior.

Regards,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Wed Sep 13 2006 - 13:08:27 CDT