Toad 10.0GA, Oracle 9208 client and Timestamp with timezone

Toad for Oracle
1

I’ve got a sort of inkling that I’ve got a client problem here, but
looking at DBA_SCHEDULER_JOBS, amongst others is causing an AV in Toad
10GA.

There is a TIMESTAMP WITH TZ data type in all the views that are
crashing out and when I look at options->data Types I don’t see
TIMESTAMP, let alone TIMEZONES added on. I suspect this is where I’m
falling down and I probably need a new client.

Can someone please confirm the above for me. Thanks. The AV is in
oranls9.dll.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

2

Replying to myself.

It works fine when I manipulate my local XE client (10g) to connect to
the database. No AVs at all. It’s a 9i client thing then. I still don’t
see TIMESTAMP% in the data types though. Should I worry?

Cheers,
Norm [TeamT].

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

3

It’s well documented (look on www.asktoad.com for example) that when using
new data types you MUST use newer Oracle client versions – as SQL*Net
layer that Toad has to talk to does not handle new data types …

4

Hi Bert,

It’s well documented (look on www.asktoad.com
for example) that when using new
data types you MUST use newer Oracle client versions – as
SQL*Net layer that Toad has to talk to does not handle new
data types …

I found this as the most relevant:

TOAD 7.5 and up can show TIMESTAMP and INTERVAL data in its data grids if you are using a 9.0.1 or higher Oracle client. TOAD 9.0.1 supports TIMESTAMP and INTERVAL data in its "run as script" function. TOAD versions 7.4 and previous do not support TIMESTAMP and INTERVAL data due to a limitation in their data layer.

To see timestamp/intervals in the "data type" drop down in the Create/Alter Table window, and in the "Add Column" window, go to options → datatypes, and make sure "Include Timestamp/Interval Types" is checked.

I'm using a 9i client (9208) and Toad 10.0.0.41. Timestamps (with and without timezones) are a 9i introduction, so I'm a tad puzzled as to how I'm barfing over them - unless, of course, there's something else. Hmmm, I feel aninvestigation coming on!

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

5

It gets interesting!

In a 10g database with 9208 client, I’ve narrowed the AV down to a data
type of TIMESTAMP(n) WITH TIME ZONE. But this is a 9i data type. Hmm.

Testing again in a 9i database with a 9i client, I can create a table
with TIMESTAMP, TIMESTAMP WITH TIME ZONE and TIMESTAMP(n) WITH TIME
ZONE, insert data, commit and all is well until I try and select any of
the TIMESTAMP columns. I get the AV in Toad again.

SQL*Plus (9i client to 9i or 10g database) works fine - no AV.

Google and Metalink/MOS were not much help. The AV is always in
oranls9.dll.

There’s more to this than a client mismatch methinks.

Anyway, it’s Friday, I’m off home to take my wife out for a meal. Have a
great weekend everyone.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

6

This seems to be one of those things that don’t work with the OCI when it
is Unicode mode.

I’d suggest to just use an Oracle 10 client.

7

Morning John,

This seems to be one of those things that don't work with
the OCI when it is Unicode mode.
Yes, there are plenty of hits on "OCI +TIMEZONE +utf16" or "OCI
+TIMEZONE +Unicode" on Google. Most of which say something along the
lines of "Oracle severely broke OCI when they added a Unicode (utf16)
ability. Especially at 9i. So it looks like I'm up that famous creek
without a canoe.

I'd suggest to just use an Oracle 10 client.
Well, My XE one does work. I'm puzzled at SQL*Plus manages to work (on
9i with 9i client) but Toad still falls over - possibly a different
manner of using OCI under the covers.

Not to worry & thanks.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

8

Im going to jump into this thread as I have an Oracle DB being updated by
multiple Browser clients
The program has a requirement needs to update the timestamp although I’m using
java SimpleDateFormat to achieve the update of the timestamp
At present i am experiencing subsecond response for each query but I know that
once this product is in the field the program may experience a bit more than a
slight performance degradation from these 3 table operations
Table LOCK
Table Insert
if Table Insert returns PK (Primary Key) SQLException
Table Update

My initial experience is that Browser clients generally work fine with a thin
type4 JDBC client but i remember the old days of implementing OCI Type2 Driver
to achieve
1)optimal caching of connection handles
2)optimal caching of statement handles
3)Ability for client to rewrite queries …(although i remember jumping thru
multiple hoops to turn this on for OCI client)

My question (after the initial question for the timestamp update is answered!)
How do i request the client to use OCI?
How do I achieve replacing JDBC Type 4 driver with OCI driver?

Many Thanks,
Martin Gainty
The followup question has to do with Gordon promising wireless access to every
household in the UK
…but …that discussion can be offlined as it is definitely OffTopic

…Standard Caveats apply…

9

Morning Martin,

The program has a requirement needs to update the timestamp
although I'm using java SimpleDateFormat to achieve the
update of the timestamp
I'm not a Java developer, honest, so what is the format/resolution of
one of these SimpleDateFormat variables? A timestamp in the Oracle
database has usecond resolution - ie, down to 1 millionth of a second.
(I presume though that this is dependent on the server being able to
give such a resolution of course!)

At present i am experiencing subsecond response for each
query but I know that once this product is in the field the
program may experience a bit more than a slight
performance degradation from these 3 table operations
Table LOCK
Oh dear....

Table Insert
if Table Insert returns PK (Primary Key) SQLException
Table Update
If you are locking the table at the start of each transaction, then you
will be sitting on a pile of enqueue waits at any time when more than
one user is connected.

I'm puzzled too by the sequence, are you saying that you:

  • try to insert
  • if you get a duplicate PK error, you do an update.

My initial experience is that Browser clients generally work
fine with a thin type4 JDBC client ...
remember jumping thru multiple hoops to turn this on for OCI client)
I suspect that you will find that the Thin Java client is built on OCI
anyway. Most Oracle drivers are as OCI is the lowest level of access -
it's the Oracle equivalent of Assembly Language - it's why you see
things like "cannot load oci.dll" and such like when you try to run
Toad, for example, without a client.

My question (after the initial question for the timestamp
update is answered!)
How do i request the client to use OCI?
Pass. It's probably already using OCI underneath.

How do I achieve replacing JDBC Type 4 driver with OCI driver?
Stop coding in Java?

As I mentioned above, I may be wrong, but I truly suspect that the Java
driver is calling out to the oci sub-system anyway. Why not try it out
on your development box - rename oci.dll (I'm assuming your web server
runs on windows) and try to make a connection. You'll get some error
about not being able to load the dll.

And another thing, as I found out yesterday, up to Oracle 9i, only data
could be retrieved in Unicode from Oracle, the metadata etc was still in
non-unicode character set. From 9i onwards, the meta data (and internal
SQL etc) was also in unicode. (Utf16.) It was at this point that huge
numbers of bugs were introduced to the OCI subsystem - hence my problems
with Toad/9i/9i client and any retrieval of a TIMESTAMP data type.
(Unless I convert to_char() first!)

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk