Toad timezone error

[Roshan]

Hi Erman,

When selecting from a table “cb_pos_transactions” on production db, I was getting an error “ORA-01882: timezone region %s not found” . I searched on google, maybe some bug on Toad. On sqlplus, error does not occur.

Please advise.

Regards,
Roshan

[ErmanArslansOracleBlog]

Are the Oracle Homes(Oracle Client) that are used by Toad and Sqlplus same?
I think a higher version of Oracle Home (Oracle Client) is required for Toad.
For example:
if you use a Oracle10 database in which a UTC time zone is already stored, this can not be retrieved from a Oracle9 client.

Ensure you are using the same Oracle Client for both Toad and sqlplus. I think sqlplus uses a newer version of Oracle client. If so, choose the same for Toad.(in the initial connection page, in the oracle home editor)

[Roshan]

Thanks a lot. It's working

[ErmanArslansOracleBlog]

Did you take the relevant actions as I said above?

[Roshan]

Yes.

One thing I find weird is that whenever I query a table which is on DB 12.2.0 with client 11g, I am not getting 'timezone' error.

But when I connect to a DB 12.1.0.2 with client 11g and query same table on that database, I am getting 'timezone' error.

I noticed that timezone was not set on DB 12.1.0.2
SQL>  SELECT DBTIMEZONE, SESSIONTIMEZONE from dual;

DBTIME
------
SESSIONTIMEZONE
---------------------------------------------------------------------------
+00:00
+00:00

Please advise.

Regards,

Roshan

[ErmanArslansOracleBlog]

Probably you have a mismatch between Toad's timezone and DB timezone.

You have a "ORA-01882 Timezone Region Not Found" error right?

We need to get a detailed info first....

1)Send me the screenshot of the error
2)Send me the query.
3)Are you using the same Oracle Client from connecting to DB 12.1.0.2 and 12.2.0
4)What is your timezone?
5)What is stored in the table that you query?  What kind of data? What types of columns (Timestamp etc) ?
6)What is the TOAD version?