to_char produces different outputs depending on where the procedure is called

We're using a stored procedure provided by a remote system. For testing purposes, I call this procedure from my development machine. Now the problem is if I call the procedure from Toad, everything is OK. But when I call it using SQL Developer an error happens.

I debugged and debugged and found out this: In the procedure, an expire date is generated and passed to a web service (don't ask me why).

Here are the lines responsible for generating the date:

vt_User.EXPDATE := TO_DATE('01.01.2025', 'dd.mm.yyyy');
vs_Value := to_char(vt_User.EXPDATE, 'YYYY-MM-DD"T"HH24:MI:SSTZR');

vs_Value, when called from Toad is generated like:

2025-01-01T00:00:00+02:00

But if I call from SQL Developer, it's like:

2025-01-01T00:00:00EUROPE/ATHENS

Everything except these lines are exactly the same. I tried many different approach, trying to set NLS_LANG, altering the session etc but to no result.

I need to solve this because the same thing happens when I call the procedure from Java code also and that's the main issue.

I connect to the remote database using TNS for Toad and SQL Developer, thin driver for the Java code.

Answers


In Oracle DB you basically have a TIME_ZONE definition for the DB, but you can change it for a session. In this case the DB time_zone is set in the Absolute offset from UTC format which is what you want. Probably the SQL Developer opened the session in Time zone region name format, as could be seen with:

select sessiontimezone, dbtimezone from dual;

So, altering the session to be as dbtimezone may help.

ALTER SESSION SET TIME_ZONE=dbtimezone;

have another solution using tzh and tzm:

select to_char(current_timestamp, 'yyyy-mm-dd"T"hh24:mi:sstzh:tzm') from dual

Need Your Help

Any possible best alternative to this messy explode?

php explode

I have a thumb generator script that need me to use define to indicate the path of the folder ... I'm picking a it from a mySQL fild in the form of /images/galleries/name/imagem.jpg

Exceptions & Interrupts

exception assembly terminology interrupt interrupted-exception

When I was searching for a distinction between Exceptions and Interrupts,