aspose file tools*
The moose likes JDBC and the fly likes Error executing a Oracle specific query when connection is fetched from Datasource Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Error executing a Oracle specific query when connection is fetched from Datasource" Watch "Error executing a Oracle specific query when connection is fetched from Datasource" New topic
Forums: JDBC Oracle/OAS
Author

Error executing a Oracle specific query when connection is fetched from Datasource

Rob Eddy
Greenhorn

Joined: Feb 21, 2013
Posts: 3
Hi Folks,

I am struggling with a rather wierd issue. The DBA of my application has exposed some function based view(not exactly sure what that means) which returns results like any query over a table. I am having a tough time consuming it in my java application. The name of the view is - `networknode_api.get_ftapp_networknode_hrchy` The SQL for it is(syntax might be a bit strange as there is actual word `table` in the query)

The query returns a hierarchical result data, provided below is a sample output (ignore the ouput if the formatting is too werid)

Parent_NODE | Child_NODE | NODE_DISPLAY_NAME | BEGIN_DATE | END_COB_DATE | LVL


null | CCN | Connent Node | 01/20/2013 | 10/19/2013 | 1
CCN | AVT | Avg Vol Turn | 01/20/2013 | 10/19/2013 | 2
AVT | L:2 | L:2 | 01/20/2013 | 10/19/2013 | 3
AVT | L:6 | L:6 | 01/20/2013 | 10/19/2013 | 3
CCN | NUT | Nap Up Turn | 01/20/2013 | 10/19/2013 | 2
.
.
.

I have the following method in my DAO -



Exception is thrown at line - stmt.execute(....)
java.sql.SQLSyntaxErrorException: ORA-00904: : invalid identifier

I debugged through the code and noted the driver and connection details that the connection object has embedded. Using that I wrote a sample java jdbc code-


The above code works perfectly fine. Also tried the above code using normal Oracle JDBC driver and too works fine. I am baffled; what goes wrong when using the connection from a DataSource object. Does the driver treat SQL statements differently based on how the connection object is obtained? Appreciate your inputs/comments for same.


The oracle db version is - Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
Application Server - Weblogic 10.3
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3433
    
  47

The way you obtain the connection should not be affecting the issue.

Can you post full stack trace?

I assume that you've obtained the sample output by running the query in the database. Did you try to run the query in SQL client using the credentials the app server is actually using? It might be an "insufficient privileges" thing.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3433
    
  47

... and welcome to the Ranch, Rob!
Rob Eddy
Greenhorn

Joined: Feb 21, 2013
Posts: 3
Hey Martin,

I actually ran the query using SQL client - Oracle SQL Developer with same credentials given in `Driver.getConnection` method. I don't have direct access to DB and can access using client only.

Below is the stacktrace and the driver details which are same for both the standalone basic Jdbc program and one where connection is fetched through datasource.

Driver name: Oracle JDBC driver
Driver version : 11.2.0.2.0
Driver major ver: 11
Driver minor ver: 2

java.sql.SQLSyntaxErrorException: ORA-00904: : invalid identifier

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:852)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1890)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1855)
at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:304)
at weblogic.jdbc.wrapper.Statement.execute(Statement.java:458)
at com.rfs.gs.ggl.nex.persistence.jpa.FlagDao.fetchCompositeNodeHierarchy(FlagDao.java:258)
at com.rfs.gs.ggl.nex.service.mars.GSServiceBean.searchCompositeNode(GSServiceBean.java:192)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.bea.core.repackaged.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:310)
at com.bea.core.repackaged.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
at com.bea.core.repackaged.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
at com.bea.core.repackaged.springframework.jee.intercept.MethodInvocationInvocationContext.proceed(MethodInvocationInvocationContext.java:104)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.bea.core.repackaged.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:310)
at com.bea.core.repackaged.springframework.jee.intercept.JeeInterceptorInterceptor.invoke(JeeInterceptorInterceptor.java:69)
at com.bea.core.repackaged.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at com.bea.core.repackaged.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131)
at com.bea.core.repackaged.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119)
at com.bea.core.repackaged.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at com.bea.core.repackaged.springframework.jee.spi.MethodInvocationVisitorImpl.visit(MethodInvocationVisitorImpl.java:37)
at weblogic.ejb.container.injection.EnvironmentInterceptorCallbackImpl.callback(EnvironmentInterceptorCallbackImpl.java:55)
at com.bea.core.repackaged.springframework.jee.spi.EnvironmentInterceptor.invoke(EnvironmentInterceptor.java:50)
at com.bea.core.repackaged.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at com.bea.core.repackaged.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
at com.bea.core.repackaged.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at com.bea.core.repackaged.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131)
at com.bea.core.repackaged.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119)
at com.bea.core.repackaged.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at com.bea.core.repackaged.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy119.searchCompositeNode(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at weblogic.ejb.container.internal.RemoteBusinessIntfProxy.invoke(RemoteBusinessIntfProxy.java:73)
at $Proxy92.searchCompositeNode(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at weblogic.wsee.jaxws.WLSInstanceResolver$WLSInvoker.invoke(WLSInstanceResolver.java:101)
at weblogic.wsee.jaxws.WLSInstanceResolver$WLSInvoker.invoke(WLSInstanceResolver.java:83)
at com.sun.xml.ws.server.InvokerTube$2.invoke(InvokerTube.java:152)
at com.sun.xml.ws.server.sei.EndpointMethodHandler.invoke(EndpointMethodHandler.java:264)
at com.sun.xml.ws.server.sei.SEIInvokerTube.processRequest(SEIInvokerTube.java:93)
at weblogic.wsee.jaxws.tubeline.FlowControlTube$FlowControlAwareTube.processRequest(FlowControlTube.java:148)
at weblogic.wsee.jaxws.tubeline.FlowControlTube.processRequest(FlowControlTube.java:99)
at com.sun.xml.ws.api.pipe.Fiber.__doRun(Fiber.java:604)
at com.sun.xml.ws.api.pipe.Fiber._doRun(Fiber.java:563)
at com.sun.xml.ws.api.pipe.Fiber.doRun(Fiber.java:548)
at com.sun.xml.ws.api.pipe.Fiber.runSync(Fiber.java:445)
at com.sun.xml.ws.server.WSEndpointImpl$2.process(WSEndpointImpl.java:275)
at com.sun.xml.ws.transport.http.HttpAdapter$HttpToolkit.handle(HttpAdapter.java:454)
at com.sun.xml.ws.transport.http.HttpAdapter.handle(HttpAdapter.java:250)
at com.sun.xml.ws.transport.http.servlet.ServletAdapter.handle(ServletAdapter.java:140)
at weblogic.wsee.jaxws.HttpServletAdapter$AuthorizedInvoke.run(HttpServletAdapter.java:319)
at weblogic.wsee.jaxws.HttpServletAdapter.post(HttpServletAdapter.java:232)
at weblogic.wsee.jaxws.JAXWSServlet.doPost(JAXWSServlet.java:310)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at weblogic.wsee.jaxws.JAXWSServlet.service(JAXWSServlet.java:87)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)
at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:292)
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:175)
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3594)
at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:121)
at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2202)
at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2108)
at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1432)
at weblogic.work.ExecuteThread.execute(ExecuteThread.java:201)
at weblogic.work.ExecuteThread.run(ExecuteThread.java:173)
<Timestamp Fri Feb 22 11:41:19 EST 2013 (1361551279781)>

Rob Eddy
Greenhorn

Joined: Feb 21, 2013
Posts: 3
@Martin,

Not a privileges issue because I can execute a normal simple select query using the connection obtained from datasource. So I wonder if the oracle specifice query syntax is causing some issue and why don't I encounter the issue when using connection from DriverManager.getConnection
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3433
    
  47

ORA-00904 is an "invalid identifier" error. It would be expected if you misspelled the package or function name (that is, referencing a non-existent function), or if the function did exist, but the current user would not be granted the EXECUTE right on it (this is why I thought it could be related to privileges).

If you're able to run the query from SQL Developer using the same exact credentials, then it is certainly not related to privileges.

The strange thing is that the identifier name should be part of the error message, but it is not there.

Select from a pipelined function is a standard thing Oracle, even the syntax might look exotic at first. If it was me, given that there aren't any clues, I'd be trying to wrap the function somehow to see whether something gets through. Eg, create a view, a stored procedure returning a ref cursor, an anonymous PL/SQL block, etc. A stored proc might be best since you could easily pass in the parameter, and get the resultset out in a ref cursor. It would hide the fact that there is a pipelined function from the JDBC driver, though I'm really not sure this is the error.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Error executing a Oracle specific query when connection is fetched from Datasource
 
Similar Threads
A challenge while formatting date value from the database
basic sql query doubt
unable to connect to sql server 2000 thru weblogic
How do you find out if a table exists (via JDBC)
Webapp hang when calling stored procedure