5 Replies Latest reply: Feb 22, 2013 11:13 AM by Joe Weinstein-Oracle RSS

    Error executing a Oracle query when connection is fetched from Datasource

    992632

      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)

      view plaincopy to clipboardprint?
      Note: Text content in the code blocks is automatically word-wrapped
      01.SELECT * FROM table (networknode_api.get_ftapp_networknode_hrchy('19-Feb-2013'));
      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

        • 1. Re: Error executing a Oracle query when connection is fetched from Datasource
          Joe Weinstein-Oracle
          what version of the oracle driver are you using when you run your standalone program
          to connect directly? Put this in every code:

          System.out.println("The driver is " + conn.getMetaData().getDriverVersion() );
          System.out.println("The DBMS is " + conn.getMetaData().getDatabaseProductVersion() );
          • 2. Re: Error executing a Oracle query when connection is fetched from Datasource
            rp0428
            >
            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.
            >
            That is probably a PIPELINED function. They are used to return result sets just as you said 'like any query over a table'.

            Here is some sample code you can use in the SCOTT schema if you need to be able to do your own testing
            -- type to match emp record
            create or replace type emp_scalar_type as object
              (EMPNO NUMBER(4) ,
               ENAME VARCHAR2(10),
               JOB VARCHAR2(9),
               MGR NUMBER(4),
               HIREDATE DATE,
               SAL NUMBER(7, 2),
               COMM NUMBER(7, 2),
               DEPTNO NUMBER(2)
              )
              /
             
            -- table of emp records
            create or replace type emp_table_type as table of emp_scalar_type
              /
            
            -- pipelined function 
            
             
            create or replace function get_emp( p_deptno in number )
              return emp_table_type
              PIPELINED
              as
               TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
                emp_cv EmpCurTyp;
                l_rec  emp%rowtype;
              begin
                open emp_cv for select * from emp where deptno = p_deptno;
                loop
                  fetch emp_cv into l_rec;
                  exit when (emp_cv%notfound);
                  pipe row( emp_scalar_type( l_rec.empno, LOWER(l_rec.ename),
                      l_rec.job, l_rec.mgr, l_rec.hiredate, l_rec.sal, l_rec.comm, l_rec.deptno ) );
                end loop;
                return;
              end;
              /
              
            select * from table(get_emp(20))
            
            EMPNO     ENAME     JOB     MGR     HIREDATE     SAL     COMM     DEPTNO
            7369     smith     CLERK     7902     12/17/1980     800          20
            7566     jones     MANAGER     7839     4/2/1981     2975          20
            7788     scott     ANALYST     7566     4/19/1987     3000     999     20
            7876     adams     CLERK     7788     5/23/1987     1100     888     20
            7902     ford     ANALYST     7566     12/3/1981     3000     777     20
            Note that the return type of the function is a TABLE but the projection is based on the EMP table row (the emp_scalar_type object).
            • 3. Re: Error executing a Oracle query when connection is fetched from Datasource
              992632
              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)>
              • 4. Re: Error executing a Oracle query when connection is fetched from Datasource
                992632
                Also just to add - 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 wonder why its not and issue when using connection from DriverManager.getConnection
                • 5. Re: Error executing a Oracle query when connection is fetched from Datasource
                  Joe Weinstein-Oracle
                  Hmmmm... Before opening an official support case, try one more thing.
                  Please configure your WebLogic datasource to have a zero statement
                  cache size, reboot and let me know if this helps. Can you run your
                  weblogic-accessing code in the WebLogic JVM, such as in a JSP or servlet?