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

[Rob Eddy]

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)
SELECT * FROM table (networknode_api.get_ftapp_networknode_hrchy('19-Feb-2013'));
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 -

public List<NetworkNodeGroupDTO> fetchNetworkNodeHierarchy(Date cobDate) {
	try {
		  InitialContext ic = new InitialContext();

		  //DataSource ds1 = (DataSource) ic.lookup("jndiDsFlex");
		  //Connection conn = ds1.getConnection();  

                                  /* Using the RmiDataSource class for performance optimizations */
		  weblogic.jdbc.common.internal.RmiDataSource ds = (weblogic.jdbc.common.internal.RmiDataSource) ic.lookup("jndiDsNex");      
				
		  Connection conn = ds.getConnection();
		  Statement stmt = conn.createStatement();
		  
		  boolean hasRows = stmt.execute("SELECT * FROM table (networknode_api.get_ftapp_networknode_hrchy('19-Feb-2013'))"); 
		  rs = stmt.getResultSet();
		  while(rs.next()) {
			System.out.println(rs.getString("parent_node"));
			System.out.println(rs.getString("child_node"));
                                                System.out.println(rs.getString("node_display_name"));			
		  }
		  stmt.close(); stmt = null;
		  conn.close(); conn = null;
	} catch(Exception ex) {
		ex.printStackTrace();
	}
	
	return null; // please ignore the return type for now
}

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-

public static void main(String[] args) throws Exception {
   
    try {
 
      //Class.forName("oracle.jdbc.driver.OracleDriver");
      //Class.forName("weblogic.jdbc.rmi.Driver");

      Driver myDriver = (Driver) Class.forName("weblogic.jdbc.jts.Driver").newInstance();

 
    } catch (ClassNotFoundException e) {
      System.out.println("Where is your Oracle JDBC Driver?");
      e.printStackTrace();
      return; 
    }
 
    Connection connection = null; 
    try {
      
      connection = DriverManager.getConnection(
                      "jdbc:oracle:thin:@//ssn20331020-rt.eu.prog.net:1522/NEXSERV", 
                      "sample",
                      "sample1234");
 
    } catch (SQLException e) {
 
      System.out.println("Connection Failed! Check output console");
      e.printStackTrace();
      return;
 
    }
    Statement stmt = connection.createStatement();
    stmt.execute("SELECT * FROM table (flex_compnode_api.get_ftapp_compnode_hrchy('19-Feb-2013'))");
    ResultSet rs = stmt.getResultSet();
    int i = 0;
    while(rs.next()) {
      String pNode = rs.getString("parent_node");
      String cNode = rs.getString("child_node");
      String dName = rs.getString("node_display_name");
      System.out.println("Hello: "+i++);
    }

  }

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]

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]

... and welcome to the Ranch, Rob!

[Rob Eddy]

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]

@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]

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.