# Toad 10.0GA, Oracle 9208 client and Timestamp with timezone **URL:** \ **Category:** Toad for Oracle & DevOps Toolkit\ **Created:** [March 19, 2010, 8:31am UTC](https://forums.toadworld.com/t/toad-10-0ga-oracle-9208-client-and-timestamp-with-timezone/9363 "2010-03-19T08:31:40Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![NormTeamT](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/normteamt/32/53_2.png) [@NormTeamT](https://forums.toadworld.com/u/NormTeamT)\ **Post date:** [March 19, 2010, 8:31am UTC](https://forums.toadworld.com/t/toad-10-0ga-oracle-9208-client-and-timestamp-with-timezone/9363/1 "2010-03-19T08:31:40Z")
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](http://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](http://www.environment-agency.gov.uk) ---
**Author:** ![NormTeamT](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/normteamt/32/53_2.png) [@NormTeamT](https://forums.toadworld.com/u/NormTeamT)\ **Post date:** [March 19, 2010, 8:44am UTC](https://forums.toadworld.com/t/toad-10-0ga-oracle-9208-client-and-timestamp-with-timezone/9363/2 "2010-03-19T08:44:57Z")
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](http://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](http://www.environment-agency.gov.uk) ---
**Author:** ![Bert\_Scalzo](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/bert_scalzo/32/274_2.png) [@Bert\_Scalzo](https://forums.toadworld.com/u/Bert_Scalzo)\ **Post date:** [March 19, 2010, 8:59am UTC](https://forums.toadworld.com/t/toad-10-0ga-oracle-9208-client-and-timestamp-with-timezone/9363/3 "2010-03-19T08:59:13Z")
It’s well documented (look on [www.asktoad.com](http://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 … ---
**Author:** ![NormTeamT](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/normteamt/32/53_2.png) [@NormTeamT](https://forums.toadworld.com/u/NormTeamT)\ **Post date:** [March 19, 2010, 10:14am UTC](https://forums.toadworld.com/t/toad-10-0ga-oracle-9208-client-and-timestamp-with-timezone/9363/4 "2010-03-19T10:14:49Z")
Hi Bert, > > It’s well documented (look on [www.asktoad.com](http://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](http://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](http://www.environment-agency.gov.uk) ---
**Author:** ![NormTeamT](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/normteamt/32/53_2.png) [@NormTeamT](https://forums.toadworld.com/u/NormTeamT)\ **Post date:** [March 19, 2010, 11:41am UTC](https://forums.toadworld.com/t/toad-10-0ga-oracle-9208-client-and-timestamp-with-timezone/9363/5 "2010-03-19T11:41:00Z")
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](http://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](http://www.environment-agency.gov.uk) ---
**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\ **Post date:** [March 19, 2010, 9:12pm UTC](https://forums.toadworld.com/t/toad-10-0ga-oracle-9208-client-and-timestamp-with-timezone/9363/6 "2010-03-19T21:12:09Z")
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. ---
**Author:** ![NormTeamT](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/normteamt/32/53_2.png) [@NormTeamT](https://forums.toadworld.com/u/NormTeamT)\ **Post date:** [March 22, 2010, 5:15am UTC](https://forums.toadworld.com/t/toad-10-0ga-oracle-9208-client-and-timestamp-with-timezone/9363/7 "2010-03-22T05:15:17Z")
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](http://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](http://www.environment-agency.gov.uk) ---
**Author:** ![MGainty](https://avatars.discourse-cdn.com/v4/letter/m/a5b964/32.png) [@MGainty](https://forums.toadworld.com/u/MGainty)\ **Post date:** [March 22, 2010, 3:54pm UTC](https://forums.toadworld.com/t/toad-10-0ga-oracle-9208-client-and-timestamp-with-timezone/9363/8 "2010-03-22T15:54:28Z")
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… ---
**Author:** ![NormTeamT](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/normteamt/32/53_2.png) [@NormTeamT](https://forums.toadworld.com/u/NormTeamT)\ **Post date:** [March 23, 2010, 3:18am UTC](https://forums.toadworld.com/t/toad-10-0ga-oracle-9208-client-and-timestamp-with-timezone/9363/9 "2010-03-23T03:18:29Z")
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](http://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](http://www.environment-agency.gov.uk)