Toad World® Forums

Formatter issue ? with :.AT TIME ZONE (in_to_tz) or AT TIME ZONE in_to_tz


#1

Hi there,

using the sample code (on WinXP, Oracle 10.2.0.3.0)
http://apex.oracle.com/pls/otn/f?p=31811:16:1837267364212031::NO
I wanted to build a function inside my XP_DT-Package (contains some units for DATE related stuff)
However when I tried to make it run with parameters the execution failed with an ORA-00907.
This is an anonymous block for explanation:
DECLARE
in_date DATE := SYSDATE;
in_from_tz VARCHAR2 (64) := ‘Europe/Berlin’;
in_to_tz VARCHAR2 (64) := ‘Australia/Melbourne’;

lv_retval DATE;
BEGIN
SELECT TO_DATE (TO_CHAR (FROM_TZ (TO_TIMESTAMP (TO_CHAR (in_date, ‘DD.MM.YYYY HH24:MI:SS’), ‘DD.MM.YYYY HH24:MI:SS’),
in_from_tz
) AT TIME ZONE (in_to_tz) /‘Australia/Melbourne’ in_to_tz/, ‘DD.MM.YYYY HH24:MI:SS’),
‘DD.MM.YYYY HH24:MI:SS’
)
INTO lv_retval
FROM DUAL;

xp_p.l (lv_retval);
END;

Why do I post this here:
As you can see there are round brackets around the parameter in_to_tz.
Well, these round brackets break the formatter somehow.
If on the other hand I leave these round brackets out the execution fails as noted above.

This is working (and formatting) without any complains. However with the lack of one parameter in_from_tz:
SELECT TO_DATE (TO_CHAR (FROM_TZ (TO_TIMESTAMP (TO_CHAR (in_date, ‘DD.MM.YYYY HH24:MI:SS’), ‘DD.MM.YYYY HH24:MI:SS’),
in_from_tz
) AT TIME ZONE ‘Australia/Melbourne’ , ‘DD.MM.YYYY HH24:MI:SS’),
‘DD.MM.YYYY HH24:MI:SS’
)
INTO lv_retval
FROM DUAL;

This is formatting but not working, since there is the parameter in_from_tz without round brackets:
SELECT TO_DATE (TO_CHAR (FROM_TZ (TO_TIMESTAMP (TO_CHAR (in_date, ‘DD.MM.YYYY HH24:MI:SS’), ‘DD.MM.YYYY HH24:MI:SS’),
in_from_tz
) AT TIME ZONE in_to_tz , ‘DD.MM.YYYY HH24:MI:SS’),
‘DD.MM.YYYY HH24:MI:SS’
)
INTO lv_retval
FROM DUAL;

This is working but not formatting, since there is the parameter in_from_tz with round brackets:
SELECT TO_DATE (TO_CHAR (FROM_TZ (TO_TIMESTAMP (TO_CHAR (in_date, ‘DD.MM.YYYY HH24:MI:SS’), ‘DD.MM.YYYY HH24:MI:SS’),
in_from_tz
) AT TIME ZONE (in_to_tz) , ‘DD.MM.YYYY HH24:MI:SS’),
‘DD.MM.YYYY HH24:MI:SS’
)
INTO lv_retval
FROM DUAL;

To be absolutely honest and to make a long story short:
I have no idea what exactly is right and whats wrong here.
Is the need of round brackets around the parameter an oracle bug somehow or does the formatter have a problem with this special syntax?

Please let me know.

Warm Regards
Andre


#2

Hi Andre,

I believe Oracle has its reason to require the brackets around the variable in_to_tz. I have created a CR to format the script with brackets. I will let you know once this is done.

Thanks,
Vincent


#3

Hello Vicent,

thank you for your info.

But this question remains:
Why does it require the bracket only for a call with a parameter
like AT TIME ZONE (in_to_tz)
and for a call with a hard coded string
like AT TIME ZONE ‘Australia/Melbourne’ ?

I really don’t understand this.

Andre


#4

Hi Andre,

I don’t have any idea how Oracle makes the rule for this either. Looking for people who can give some hints.

Thanks,
Vincent