Re: Oracle and EOF with Timestamps
Hi Don Thanks for sharing and appreciate. I recall now the last time using ojdbc14 version for a small project though it works for JDK1.5 as standard SQL statements are being used. It is always safe to do so though less 'efficient and adventurous'. Cheers Cheong Hee - Original Message - From: Don Lindsay pccd...@mac.com To: Cheong Hee (Gmail) chn...@gmail.com Cc: Andrew Lindesay a...@lindesay.co.nz; WebObjectsDev Development webobjects-dev@lists.apple.com Sent: Thursday, December 17, 2009 1:23 PM Subject: Re: Oracle and EOF with Timestamps The Files depend on the version of the JDK you are using Classes12 is meant for JDK 1.2 and 1.3 ojdbc14 is meant for JDK 1.4 ojdbc5 is meant for use with JDK 1.5 and 1.6 Now for each of these jar files there are 5 or 6 different versions (9.2.0.4,10.1,10.2.0.4,11.1.0,11.2, ...) Now each of these versions has multiple versions of the JDBC drivers. The safest place to get your JDBC drivers is at: http://otn.oracle.com sign up for a OTN account, it is free, download the latest driver for the JDK you are currently running. JDBC drivers are backwards compatible with previous versions of Oracle. If you get the 11.1.0 version of ODJBC14.jar it is compatible with all versions of 10g and 9i. Confusing I know, the best working JDBC drivers are probably going to be 11. I have had good experience with the 10.2.0.4 drivers. The 11 Drivers have a few bugs, the 10.2.0.3 and 10.2.0.1 drivers have some issues with security. Avoid the 9.2.0.* drivers and 8i drivers too buggy. Thanks, hope this helps. Don On Dec 15, 2009, at 10:26 PM, Cheong Hee (Gmail) wrote: Hi Don Just curious, which version of ojdbc.jar needs to be avoided. Is odbc14.jar good for use? Cheers Cheong Hee - Original Message - From: Don Lindsay pccd...@mac.com To: Andrew Lindesay a...@lindesay.co.nz Cc: WebObjectsDev Development webobjects-dev@lists.apple.com Sent: Wednesday, December 16, 2009 8:55 AM Subject: Re: Oracle and EOF with Timestamps Andrew; Your JDBC file. Where did you get it from? We need to know the version of the file. There are 6 or 8 versions of the OJDBC5.jar file floating around. Some work and some do not. :) Don On Dec 15, 2009, at 4:25 AM, Andrew Lindesay wrote: Hello Don; select * from v$version; Oracle Database 11g Release 11.2.0.1.0 - 64bit Production What version is your jdbc file? ojdbc5 -- JDBC for 1.5 I take it you are using the thin driver and not the OCI driver correct? jdbc:oracle:thin:. cheers. ___ Andrew Lindesay www.lindesay.co.nz ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/chng34%40gmail.com This email sent to chn...@gmail.com ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
Re: Oracle and EOF with Timestamps
Hello Don; select * from v$version; Oracle Database 11g Release 11.2.0.1.0 - 64bit Production What version is your jdbc file? ojdbc5 -- JDBC for 1.5 I take it you are using the thin driver and not the OCI driver correct? jdbc:oracle:thin:. cheers. ___ Andrew Lindesay www.lindesay.co.nz ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
Re: Oracle and EOF with Timestamps
Hi Don; It seems that if I intercept the Oracle connection as it is created and force the timezone to GMT+0 in there, the problem is resolved. Thanks for looking anyway. cheers. ___ Andrew Lindesay www.lindesay.co.nz ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
Re: Oracle and EOF with Timestamps
Andrew; Your JDBC file. Where did you get it from? We need to know the version of the file. There are 6 or 8 versions of the OJDBC5.jar file floating around. Some work and some do not. :) Don On Dec 15, 2009, at 4:25 AM, Andrew Lindesay wrote: Hello Don; select * from v$version; Oracle Database 11g Release 11.2.0.1.0 - 64bit Production What version is your jdbc file? ojdbc5 -- JDBC for 1.5 I take it you are using the thin driver and not the OCI driver correct? jdbc:oracle:thin:. cheers. ___ Andrew Lindesay www.lindesay.co.nz ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
Re: Oracle and EOF with Timestamps
Glad you found the error. Do you have Oracle Metalink? Don On Dec 15, 2009, at 6:35 PM, Andrew Lindesay wrote: Hi Don; It seems that if I intercept the Oracle connection as it is created and force the timezone to GMT+0 in there, the problem is resolved. Thanks for looking anyway. cheers. ___ Andrew Lindesay www.lindesay.co.nz ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
Re: Oracle and EOF with Timestamps
Hi there Don; Somebody near me says I do. :) cheers. Do you have Oracle Metalink? ___ Andrew Lindesay www.lindesay.co.nz ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
Re: Oracle and EOF with Timestamps
Hi Don Just curious, which version of ojdbc.jar needs to be avoided. Is odbc14.jar good for use? Cheers Cheong Hee - Original Message - From: Don Lindsay pccd...@mac.com To: Andrew Lindesay a...@lindesay.co.nz Cc: WebObjectsDev Development webobjects-dev@lists.apple.com Sent: Wednesday, December 16, 2009 8:55 AM Subject: Re: Oracle and EOF with Timestamps Andrew; Your JDBC file. Where did you get it from? We need to know the version of the file. There are 6 or 8 versions of the OJDBC5.jar file floating around. Some work and some do not. :) Don On Dec 15, 2009, at 4:25 AM, Andrew Lindesay wrote: Hello Don; select * from v$version; Oracle Database 11g Release 11.2.0.1.0 - 64bit Production What version is your jdbc file? ojdbc5 -- JDBC for 1.5 I take it you are using the thin driver and not the OCI driver correct? jdbc:oracle:thin:. cheers. ___ Andrew Lindesay www.lindesay.co.nz ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/chng34%40gmail.com This email sent to chn...@gmail.com ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
Re: Oracle and EOF with Timestamps
Just guesses as I am not currently using Oracle... Check the data type of the relevant column in the JDBC2INFO. It might have some definition that is causing Oracle to do this and a different data type (or value type!) might avoid this. Could it be a driver version / Oracle version mismatch? Chuck On Dec 14, 2009, at 2:23 AM, Andrew Lindesay wrote: Hello; I'm communicating with an Oracle database through an EOF model. It is working well except that I get this trace logging coming through when timestamp attributes are being SELECT'ed on; SQLState(65000) vendor code(6502) java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error ORA-06512: at SYS.DBMS_UTILITY, line 102 ORA-06512: at line 1 at oracle .jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70) at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java: 132) at oracle .jdbc.driver.DatabaseError.createSqlException(DatabaseError.java:185) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:438) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521) at oracle .jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java: 202) at oracle .jdbc .driver .T4CCallableStatement.executeForRows(T4CCallableStatement.java:1005) at oracle .jdbc .driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java: 1307) at oracle .jdbc .driver .OraclePreparedStatement .executeInternal(OraclePreparedStatement.java:3449) at oracle .jdbc .driver.OraclePreparedStatement.execute(OraclePreparedStatement.java: 3550) at oracle .jdbc .driver.OracleCallableStatement.execute(OracleCallableStatement.java: 4693) at oracle .jdbc .driver .OraclePreparedStatementWrapper .execute(OraclePreparedStatementWrapper.java:1086) at oracle.sql.TRANSDUMP.getTransitions(TRANSDUMP.java:79) at oracle.sql.TIMEZONETAB.updateTable(TIMEZONETAB.java:548) at oracle .jdbc.driver.OracleStatement.getDefaultTimeZone(OracleStatement.java: 5831) at oracle .jdbc .driver.DateCommonBinder.setOracleCYMD(OraclePreparedStatement.java: 17474) at oracle.jdbc.driver.TimestampBinder.bind(OraclePreparedStatement.java: 17951) at oracle .jdbc .driver .OraclePreparedStatement .setupBindBuffers(OraclePreparedStatement.java:3014) at oracle .jdbc .driver .OraclePreparedStatement .processCompletedBindRow(OraclePreparedStatement.java:2247) at oracle .jdbc .driver .OraclePreparedStatement .executeInternal(OraclePreparedStatement.java:3444) at oracle .jdbc .driver .OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java: 3530) at oracle .jdbc .driver .OraclePreparedStatementWrapper .executeUpdate(OraclePreparedStatementWrapper.java:1062) at com .webobjects .jdbcadaptor .JDBCChannel ._bindInputVariablesWithBindingsAndExecute(JDBCChannel.java:263) at com .webobjects .jdbcadaptor.JDBCChannel._evaluateExpression(JDBCChannel.java:337) ... The fetch still works fine, but this problem is consuming some capacity and I don't know why. It seems to be invoking a stored- procedure to fiddle timezones. If I create a simple java program to fetch some data, I don't see this. Has anybody else seen this one? cheers. ___ Andrew Lindesay www.lindesay.co.nz ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/chill%40global-village.net This email sent to ch...@global-village.net -- Chuck Hill Senior Consultant / VP Development Practical WebObjects - for developers who want to increase their overall knowledge of WebObjects or who are trying to solve specific problems. http://www.global-village.net/products/practical_webobjects ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
Re: Oracle and EOF with Timestamps
I use Oracle with WebObjects. I have not seen this type of error. What are you trying to do specifically? Do you have any code? Send me the datatype and field name. How are you trying to manage the timezone. Thanks Don On Dec 14, 2009, at 5:23 AM, Andrew Lindesay wrote: Hello; I'm communicating with an Oracle database through an EOF model. It is working well except that I get this trace logging coming through when timestamp attributes are being SELECT'ed on; SQLState(65000) vendor code(6502) java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error ORA-06512: at SYS.DBMS_UTILITY, line 102 ORA-06512: at line 1 at oracle .jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70) at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java: 132) at oracle .jdbc.driver.DatabaseError.createSqlException(DatabaseError.java:185) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:438) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521) at oracle .jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java: 202) at oracle .jdbc .driver .T4CCallableStatement.executeForRows(T4CCallableStatement.java:1005) at oracle .jdbc .driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java: 1307) at oracle .jdbc .driver .OraclePreparedStatement .executeInternal(OraclePreparedStatement.java:3449) at oracle .jdbc .driver.OraclePreparedStatement.execute(OraclePreparedStatement.java: 3550) at oracle .jdbc .driver.OracleCallableStatement.execute(OracleCallableStatement.java: 4693) at oracle .jdbc .driver .OraclePreparedStatementWrapper .execute(OraclePreparedStatementWrapper.java:1086) at oracle.sql.TRANSDUMP.getTransitions(TRANSDUMP.java:79) at oracle.sql.TIMEZONETAB.updateTable(TIMEZONETAB.java:548) at oracle .jdbc.driver.OracleStatement.getDefaultTimeZone(OracleStatement.java: 5831) at oracle .jdbc .driver.DateCommonBinder.setOracleCYMD(OraclePreparedStatement.java: 17474) at oracle.jdbc.driver.TimestampBinder.bind(OraclePreparedStatement.java: 17951) at oracle .jdbc .driver .OraclePreparedStatement .setupBindBuffers(OraclePreparedStatement.java:3014) at oracle .jdbc .driver .OraclePreparedStatement .processCompletedBindRow(OraclePreparedStatement.java:2247) at oracle .jdbc .driver .OraclePreparedStatement .executeInternal(OraclePreparedStatement.java:3444) at oracle .jdbc .driver .OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java: 3530) at oracle .jdbc .driver .OraclePreparedStatementWrapper .executeUpdate(OraclePreparedStatementWrapper.java:1062) at com .webobjects .jdbcadaptor .JDBCChannel ._bindInputVariablesWithBindingsAndExecute(JDBCChannel.java:263) at com .webobjects .jdbcadaptor.JDBCChannel._evaluateExpression(JDBCChannel.java:337) ... The fetch still works fine, but this problem is consuming some capacity and I don't know why. It seems to be invoking a stored- procedure to fiddle timezones. If I create a simple java program to fetch some data, I don't see this. Has anybody else seen this one? cheers. ___ Andrew Lindesay www.lindesay.co.nz ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/pccdonl %40mac.com This email sent to pccd...@mac.com ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
Re: Oracle and EOF with Timestamps
Hello Don Chuck; I use Oracle with WebObjects. I have not seen this type of error. What are you trying to do specifically? Do you have any code? Send me the datatype and field name. How are you trying to manage the timezone. We're setting the Oracle host to GMT+0 and the WO server's JVM to GMT+0 so it should be GMT+0 clean right through. The timestamp type we're using in the model is TIMESTAMP. I wonder if using TIMESTAMP WITH TIMEZONE type might resolve the issue by not trying to do any timezone fiddling. The timestamps are being transformed for the user in WOComponents using a subclass of java.text.Format and all processing, web services and IO inside the WebObjects application is all GMT+0 relative. When faults fire in EOF, we seem to get the PL/SQL function get_tz_transitions fired (which looks to be a performance constraint) and it fails with that exception I posted. This appears to go to stdout and yet the fault is resolved OK from EOF's perspective. I can only presume it is something going on inside the Oracle JDBC driver rather than EOF. Check the data type of the relevant column in the JDBC2INFO. It might have some definition that is causing Oracle to do this and a different data type (or value type!) might avoid this. I deleted that from the model some years ago (after you correctly suggested it as a remedy to another problem) and it hasn't resurfaced! Could it be a driver version / Oracle version mismatch? I think this is ok. cheers. ___ Andrew Lindesay www.lindesay.co.nz ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
Re: Oracle and EOF with Timestamps
On Dec 14, 2009, at 12:54 PM, Andrew Lindesay wrote: Hello Don Chuck; I use Oracle with WebObjects. I have not seen this type of error. What are you trying to do specifically? Do you have any code? Send me the datatype and field name. How are you trying to manage the timezone. We're setting the Oracle host to GMT+0 and the WO server's JVM to GMT +0 so it should be GMT+0 clean right through. The timestamp type we're using in the model is TIMESTAMP. I wonder if using TIMESTAMP WITH TIMEZONE type might resolve the issue by not trying to do any timezone fiddling. Worth a shot. The timestamps are being transformed for the user in WOComponents using a subclass of java.text.Format and all processing, web services and IO inside the WebObjects application is all GMT+0 relative. When faults fire in EOF, we seem to get the PL/SQL function get_tz_transitions fired (which looks to be a performance constraint) and it fails with that exception I posted. This appears to go to stdout and yet the fault is resolved OK from EOF's perspective. I can only presume it is something going on inside the Oracle JDBC driver rather than EOF. Check the data type of the relevant column in the JDBC2INFO. It might have some definition that is causing Oracle to do this and a different data type (or value type!) might avoid this. I deleted that from the model some years ago (after you correctly suggested it as a remedy to another problem) and it hasn't resurfaced! EOF fetches it when the first db connection is attempted. Not having it in the model means it is not cached, not that it is not used. You will need to dig into the JDBCAdaptor level to get at it. Chuck Could it be a driver version / Oracle version mismatch? I think this is ok. cheers. ___ Andrew Lindesay www.lindesay.co.nz -- Chuck Hill Senior Consultant / VP Development Practical WebObjects - for developers who want to increase their overall knowledge of WebObjects or who are trying to solve specific problems. http://www.global-village.net/products/practical_webobjects ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
Re: Oracle and EOF with Timestamps
Hello Andrew; Can you run the following query and provide the results? select * from v$version; What version is your jdbc file? I take it you are using the thin driver and not the OCI driver correct? Don On Dec 14, 2009, at 3:54 PM, Andrew Lindesay wrote: Hello Don Chuck; I use Oracle with WebObjects. I have not seen this type of error. What are you trying to do specifically? Do you have any code? Send me the datatype and field name. How are you trying to manage the timezone. We're setting the Oracle host to GMT+0 and the WO server's JVM to GMT +0 so it should be GMT+0 clean right through. The timestamp type we're using in the model is TIMESTAMP. I wonder if using TIMESTAMP WITH TIMEZONE type might resolve the issue by not trying to do any timezone fiddling. The timestamps are being transformed for the user in WOComponents using a subclass of java.text.Format and all processing, web services and IO inside the WebObjects application is all GMT+0 relative. When faults fire in EOF, we seem to get the PL/SQL function get_tz_transitions fired (which looks to be a performance constraint) and it fails with that exception I posted. This appears to go to stdout and yet the fault is resolved OK from EOF's perspective. I can only presume it is something going on inside the Oracle JDBC driver rather than EOF. Check the data type of the relevant column in the JDBC2INFO. It might have some definition that is causing Oracle to do this and a different data type (or value type!) might avoid this. I deleted that from the model some years ago (after you correctly suggested it as a remedy to another problem) and it hasn't resurfaced! Could it be a driver version / Oracle version mismatch? I think this is ok. cheers. ___ Andrew Lindesay www.lindesay.co.nz ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
