0 Replies Latest reply on Mar 29, 2013 9:15 AM by rayliner

    Getting an oracle error 01867 intermittently

    rayliner Level 1

      we have been having an issue with a query in a cfquery tag that gets info from our tables and links to a table in a remote database.... something like:  select x,y,z from mytable ours, theirtable@remotelinkname theirs where ours.orderNum = theirs.orderNum.  Sometimes the query works, and other times it would fail.  We simplified the query to that it is static and then we dumped the cfcatch structure.

       

      the simple query on a cfm page  "select 'text' as mytext from theirtable@remotelinkname" and the error "ORA 01867: the interval is invalid" is returned in the cfcatch structure.  We can run the exact same query in either Toad or Sql-developer, and no matter how many times we press RUN, it will always work.  On the coldfusion screen, when it works, the results come back immediately.  when it fails, it takes about 20 seconds before the error is reported.

      This is currently on a CF8.01 server, and the db is Oracle.

       

      The info in cfdump comes back - sorry the formatting looks so bad....

       

      struct Cause struct ErrorCode 1867  Message [Macromedia][Oracle JDBC Driver][Oracle]ORA-01867: the interval is invalid  SQLState HY000  StackTrace java.sql.SQLException: [Macromedia][Oracle JDBC Driver][Oracle]ORA-01867: the interval is invalid at macromedia.jdbc.base.BaseExceptions.createException(Unknown Source) at macromedia.jdbc.base.BaseExceptions.getException(Unknown Source) at macromedia.jdbc.oracle.OracleImplStatement.execute(Unknown Source) at macromedia.jdbc.base.BaseStatement.commonExecute(Unknown Source) at macromedia.jdbc.base.BaseStatement.executeInternal(Unknown Source) at macromedia.jdbc.base.BaseStatement.execute(Unknown Source) at coldfusion.server.j2ee.sql.JRunStatement.execute(JRunStatement.java:348) at coldfusion.sql.Executive.executeQuery(Executive.java:1224) at coldfusion.sql.Executive.executeQuery(Executive.java:1009) at coldfusion.sql.Executive.executeQuery(Executive.java:940) at coldfusion.sql.SqlImpl.execute(SqlImpl.java:325) at coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:838) at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:528) at cfebc_dblinks2ecfm1338204498._factor0(C:\Program Files\dt\dir2\x3\ebc_dblinks.cfm:106) at cfebc_dblinks2ecfm1338204498._factor9(C:\Program Files\dt\dir2\x3\ebc_dblinks.cfm:75) at cfebc_dblinks2ecfm1338204498.runPage(C:\Program Files\dt\dir2\x3\ebc_dblinks.cfm:1) at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:196) at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:483) at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65) at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:288) at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:48) at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40) at coldfusion.filter.PathFilter.invoke(PathFilter.java:86) at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:70) at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:2 8) at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38) at coldfusion.filter.NoCacheFilter.invoke(NoCacheFilter.java:46) at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38) at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22) at coldfusion.CfmServlet.service(CfmServlet.java:198) at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:89) at jrun.servlet.FilterChain.doFilter(FilterChain.java:86) at coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:42 ) at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:46) at jrun.servlet.FilterChain.doFilter(FilterChain.java:94) at jrun.servlet.FilterChain.service(FilterChain.java:101) at jrun.servlet.ServletInvoker.invoke(ServletInvoker.java:106) at jrun.servlet.JRunInvokerChain.invokeNext(JRunInvokerChain.java:42) at jrun.servlet.JRunRequestDispatcher.invoke(JRunRequestDispatcher.java:286) at jrun.servlet.ServletEngineService.dispatch(ServletEngineService.java:543) at jrun.servlet.jrpp.JRunProxyService.invokeRunnable(JRunProxyService.java:203) at jrunx.scheduler.ThreadPool$DownstreamMetrics.invokeRunnable(ThreadPool.java:320) at jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRunnable(ThreadPool.java:428) at jrunx.scheduler.ThreadPool$UpstreamMetrics.invokeRunnable(ThreadPool.java:266) at jrunx.scheduler.WorkerThread.run(WorkerThread.java:66)  TagContext array  1 struct COLUMN 0  ID CFQUERY  LINE 106  RAW_TRACE at cfebc_dblinks2ecfm1338204498._factor0(C:\Program Files\dt\dir2\x3\ebc_dblinks.cfm:106)  TEMPLATE C:\Program Files\dt\dir2\x3\ebc_dblinks.cfm  TYPE CFML  2 struct COLUMN 0  ID CF_EBC_DBLINKS  LINE 75  RAW_TRACE at cfebc_dblinks2ecfm1338204498._factor9(C:\Program Files\dt\dir2\x3\ebc_dblinks.cfm:75)  TEMPLATE C:\Program Files\dt\dir2\x3\ebc_dblinks.cfm  TYPE CFML  3 struct COLUMN 0  ID CF_EBC_DBLINKS  LINE 1  RAW_TRACE at cfebc_dblinks2ecfm1338204498.runPage(C:\Program Files\dt\dir2\x3\ebc_dblinks.cfm:1)  TEMPLATE C:\Program Files\dt\dir2\x3\ebc_dblinks.cfm  TYPE CFML  Type java.sql.SQLException  DataSource EBC  Detail [Macromedia][Oracle JDBC Driver][Oracle]ORA-01867: the interval is invalid  ErrorCode HY000  Message Error Executing Database Query.  NativeErrorCode 1867