1 2 Previous Next 20 Replies Latest reply on Mar 28, 2013 8:35 AM by shawkins

    Did teiid7.7 supports procedures with OUT parameter? If it supports means what are the procedures to call sp through callable statement with teiid

    raghu85
    raghu85 Mar 21, 2013 3:15 AM

      I am trying to call a oracle stored procedure with 3 OUT parameter. For that i imported that procedure in teiid designer and converted to VDB file and deployed to JBoss and created a data source for this vdb also.

       

      When i tried to call stored procedures using callable statement i am getting teiid exception. Please suggest me where i am doing wrong inorder to get this exception.

       

      Using the below steps i am invoking the sp.

       

      try {

                  getHibernateSession().doWork(new Work() {

       

                      public void execute(Connection connection) throws SQLException {

                          cstmt = connection

                                  .prepareCall("{call INSERT_SCHOOL_DETAIL(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");

       

                          cstmt.setString(1, dataRequest.getSchoolName());

                          cstmt.setString(2, dataRequest.getRO());

                          cstmt.setString(3, dataRequest.getSR());

                          cstmt.setString(4, dataRequest.getCountry());

                          cstmt.setString(5, dataRequest.getState());

                          cstmt.setNull(6, Types.VARCHAR);

                          cstmt.setString(7, dataRequest.getUserName());

                          cstmt.setString(8, dataRequest.getRoleName());

                          cstmt.registerOutParameter(9, Types.VARCHAR);

                          cstmt.registerOutParameter(10, Types.VARCHAR);

                          cstmt.registerOutParameter(11, Types.VARCHAR);

                          cstmt.execute();

                          pCreateSchoolResponse.setStatus(cstmt.getString(9));

                          pCreateSchoolResponse.setError_text(cstmt.getString(10));

                          pCreateSchoolResponse.setSchool_code(cstmt.getString(11));

                      }

                  });

              } catch (HibernateException e) {

                  e.printStackTrace();

              }

       

      org.teiid.translator.TranslatorException: Error Code:65000 Message:'{ ?= call "PLUSER"."INSERT_SCHOOL_DETAIL"(?,?,?,?,?,?,?,?,?,?,?)}' error executing statement(s): {1}

          at org.teiid.translator.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:70)

          at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:276)

          at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:354)

          at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:143)

          at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:140)

          at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)

          at java.util.concurrent.FutureTask.run(FutureTask.java:138)

          at org.teiid.dqp.internal.process.DQPCore$FutureWork.run(DQPCore.java:120)

          at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:240)

          at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:122)

          at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:292)

          at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)

          at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)

          at java.lang.Thread.run(Thread.java:619)

      Caused by: java.sql.SQLException: ORA-06550: line 1, column 13:

      PLS-00201: identifier 'PLUSER.INSERT_SCHOOL_DETAIL' must be declared

      ORA-06550: line 1, column 7:

      PL/SQL: Statement ignored

       

          at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)

          at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)

          at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)

          at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)

          at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:215)

          at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:954)

          at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)

          at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3316)

          at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3422)

          at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4394)

          at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:299)

          at org.teiid.translator.jdbc.JDBCExecutionFactory.executeStoredProcedure(JDBCExecutionFactory.java:753)

          at org.teiid.translator.jdbc.oracle.OracleExecutionFactory.executeStoredProcedure(OracleExecutionFactory.java:720)

          at org.teiid.translator.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:67)

          ... 13 more

      12:40:00,150 DEBUG (http-0.0.0.0-8080-1) [PROCESSOR] Request Thread 3Jgs87FsMImm.3 with state PROCESSING

      12:40:00,150 DEBUG (http-0.0.0.0-8080-1) [PROCESSOR] QueryProcessor: closing processor

      12:40:00,150 DEBUG (http-0.0.0.0-8080-1) [PROCESSOR] Request Thread 3Jgs87FsMImm.3 - error occurred

      org.teiid.core.TeiidProcessingException: Error Code:65000 Message:ibis_proc_poc: Error Code:65000 Message:'{ ?= call "PLUSER"."INSERT_SCHOOL_DETAIL"(?,?,?,?,?,?,?,?,?,?,?)}' error executing statement(s): {1}

          at org.teiid.dqp.internal.process.DataTierTupleSource.exceptionOccurred(DataTierTupleSource.java:430)

          at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:250)

          at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:271)

          at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:280)

          at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:155)

          at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:280)

          at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:176)

          at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:139)

          at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:105)

          at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:147)

          at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:375)

          at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:288)

          at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49)

          at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:216)

          at org.teiid.dqp.internal.process.DQPCore.executeRequest(DQPCore.java:390)

          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 org.teiid.logging.LogManager$LoggingProxy.invoke(LogManager.java:121)

          at org.teiid.jboss.deployers.RuntimeEngineDeployer$3.invoke(RuntimeEngineDeployer.java:397)

          at $Proxy453.executeRequest(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 org.teiid.transport.LocalServerConnection$1$1.call(LocalServerConnection.java:130)

          at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)

          at java.util.concurrent.FutureTask.run(FutureTask.java:138)

          at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:240)

          at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:224)

          at org.teiid.transport.LocalServerConnection$1.invoke(LocalServerConnection.java:128)

          at $Proxy453.executeRequest(Unknown Source)

          at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:611)

          at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:541)

          at org.teiid.jdbc.PreparedStatementImpl.execute(PreparedStatementImpl.java:211)

          at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:299)

          at org.ibo.globalapi.dao.school.api.impl.SchoolDAOImpl$1.execute(SchoolDAOImpl.java:172)

          at org.hibernate.impl.SessionImpl.doWork(SessionImpl.java:1901)

          at org.ibo.globalapi.dao.school.api.impl.SchoolDAOImpl.createSchool(SchoolDAOImpl.java:155)

          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 org.jboss.aop.joinpoint.MethodInvocation.invokeTarget(MethodInvocation.java:122)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:111)

          at org.jboss.ejb3.interceptors.container.ContainerMethodInvocationWrapper.invokeNext(ContainerMethodInvocationWrapper.java:72)

          at org.jboss.ejb3.interceptors.aop.InterceptorSequencer.invoke(InterceptorSequencer.java:76)

          at org.jboss.ejb3.interceptors.aop.InterceptorSequencer.aroundInvoke(InterceptorSequencer.java:62)

          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 org.jboss.aop.advice.PerJoinpointAdvice.invoke(PerJoinpointAdvice.java:174)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.ejb3.interceptors.aop.InvocationContextInterceptor.fillMethod(InvocationContextInterceptor.java:72)

          at org.jboss.aop.advice.org.jboss.ejb3.interceptors.aop.InvocationContextInterceptor_z_fillMethod_17943221.invoke(InvocationContextInterceptor_z_fillMethod_17943221.java)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.ejb3.interceptors.aop.InvocationContextInterceptor.setup(InvocationContextInterceptor.java:88)

          at org.jboss.aop.advice.org.jboss.ejb3.interceptors.aop.InvocationContextInterceptor_z_setup_17943221.invoke(InvocationContextInterceptor_z_setup_17943221.java)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.ejb3.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:62)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.ejb3.entity.TransactionScopedEntityManagerInterceptor.invoke(TransactionScopedEntityManagerInterceptor.java:56)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.ejb3.AllowedOperationsInterceptor.invoke(AllowedOperationsInterceptor.java:47)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.ejb3.tx.NullInterceptor.invoke(NullInterceptor.java:42)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.ejb3.stateless.StatelessInstanceInterceptor.invoke(StatelessInstanceInterceptor.java:68)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.aspects.tx.TxPolicy.invokeInCallerTx(TxPolicy.java:126)

          at org.jboss.aspects.tx.TxInterceptor$Supports.invoke(TxInterceptor.java:147)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.aspects.tx.TxPropagationInterceptor.invoke(TxPropagationInterceptor.java:76)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.ejb3.tx.NullInterceptor.invoke(NullInterceptor.java:42)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.ejb3.security.RoleBasedAuthorizationInterceptorv2.invoke(RoleBasedAuthorizationInterceptorv2.java:201)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.ejb3.security.Ejb3AuthenticationInterceptorv2.invoke(Ejb3AuthenticationInterceptorv2.java:182)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.ejb3.ENCPropagationInterceptor.invoke(ENCPropagationInterceptor.java:41)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.ejb3.BlockContainerShutdownInterceptor.invoke(BlockContainerShutdownInterceptor.java:67)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.ejb3.core.context.CurrentInvocationContextInterceptor.invoke(CurrentInvocationContextInterceptor.java:47)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.aspects.currentinvocation.CurrentInvocationInterceptor.invoke(CurrentInvocationInterceptor.java:67)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.ejb3.interceptor.EJB3TCCLInterceptor.invoke(EJB3TCCLInterceptor.java:86)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.ejb3.session.SessionSpecContainer.invoke(SessionSpecContainer.java:193)

          at org.jboss.ejb3.session.SessionSpecContainer.invoke(SessionSpecContainer.java:250)

          at org.jboss.ejb3.proxy.impl.handler.session.SessionProxyInvocationHandlerBase.invoke(SessionProxyInvocationHandlerBase.java:188)

          at $Proxy783.createSchool(Unknown Source)

          at org.ibo.globalapi.service.school.api.impl.SchoolServiceImpl.createSchoolData(SchoolServiceImpl.java:219)

          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 org.jboss.aop.joinpoint.MethodInvocation.invokeTarget(MethodInvocation.java:122)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:111)

          at org.jboss.ejb3.interceptors.container.ContainerMethodInvocationWrapper.invokeNext(ContainerMethodInvocationWrapper.java:72)

          at org.jboss.ejb3.interceptors.aop.InterceptorSequencer.invoke(InterceptorSequencer.java:76)

          at org.jboss.ejb3.interceptors.aop.InterceptorSequencer.aroundInvoke(InterceptorSequencer.java:62)

          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 org.jboss.aop.advice.PerJoinpointAdvice.invoke(PerJoinpointAdvice.java:174)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.ejb3.interceptors.aop.InvocationContextInterceptor.fillMethod(InvocationContextInterceptor.java:72)

          at org.jboss.aop.advice.org.jboss.ejb3.interceptors.aop.InvocationContextInterceptor_z_fillMethod_17943221.invoke(InvocationContextInterceptor_z_fillMethod_17943221.java)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.ejb3.interceptors.aop.InvocationContextInterceptor.setup(InvocationContextInterceptor.java:88)

          at org.jboss.aop.advice.org.jboss.ejb3.interceptors.aop.InvocationContextInterceptor_z_setup_17943221.invoke(InvocationContextInterceptor_z_setup_17943221.java)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.ejb3.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:62)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.ejb3.entity.TransactionScopedEntityManagerInterceptor.invoke(TransactionScopedEntityManagerInterceptor.java:56)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.ejb3.AllowedOperationsInterceptor.invoke(AllowedOperationsInterceptor.java:47)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.ejb3.tx.NullInterceptor.invoke(NullInterceptor.java:42)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.ejb3.stateless.StatelessInstanceInterceptor.invoke(StatelessInstanceInterceptor.java:68)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.aspects.tx.TxPolicy.invokeInOurTx(TxPolicy.java:79)

          at org.jboss.aspects.tx.TxInterceptor$Required.invoke(TxInterceptor.java:190)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.aspects.tx.TxPropagationInterceptor.invoke(TxPropagationInterceptor.java:76)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.ejb3.tx.NullInterceptor.invoke(NullInterceptor.java:42)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.ejb3.security.RoleBasedAuthorizationInterceptorv2.invoke(RoleBasedAuthorizationInterceptorv2.java:201)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.ejb3.security.Ejb3AuthenticationInterceptorv2.invoke(Ejb3AuthenticationInterceptorv2.java:182)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.ejb3.ENCPropagationInterceptor.invoke(ENCPropagationInterceptor.java:41)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.ejb3.BlockContainerShutdownInterceptor.invoke(BlockContainerShutdownInterceptor.java:67)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.ejb3.core.context.CurrentInvocationContextInterceptor.invoke(CurrentInvocationContextInterceptor.java:47)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.aspects.currentinvocation.CurrentInvocationInterceptor.invoke(CurrentInvocationInterceptor.java:67)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.ejb3.interceptor.EJB3TCCLInterceptor.invoke(EJB3TCCLInterceptor.java:86)

          at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

          at org.jboss.ejb3.session.SessionSpecContainer.invoke(SessionSpecContainer.java:193)

          at org.jboss.ejb3.session.SessionSpecContainer.invoke(SessionSpecContainer.java:250)

          at org.jboss.ejb3.proxy.impl.handler.session.SessionProxyInvocationHandlerBase.invoke(SessionProxyInvocationHandlerBase.java:188)

          at $Proxy799.createSchoolData(Unknown Source)

          at org.ibo.globalapi.esb.webservice.webservice.SchoolServiceWS.createSchoolData(SchoolServiceWS.java:62)

          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 org.jboss.wsf.container.jboss50.invocation.InvocationHandlerJSE.invoke(InvocationHandlerJSE.java:117)

          at org.jboss.wsf.stack.cxf.AbstractInvoker._invokeInternal(AbstractInvoker.java:154)

          at org.jboss.wsf.stack.cxf.AbstractInvoker.invoke(AbstractInvoker.java:104)

          at org.apache.cxf.interceptor.ServiceInvokerInterceptor$1.run(ServiceInvokerInterceptor.java:58)

          at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)

          at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)

          at java.util.concurrent.FutureTask.run(FutureTask.java:138)

          at org.apache.cxf.workqueue.SynchronousExecutor.execute(SynchronousExecutor.java:37)

          at org.apache.cxf.interceptor.ServiceInvokerInterceptor.handleMessage(ServiceInvokerInterceptor.java:106)

          at org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:243)

          at org.apache.cxf.transport.ChainInitiationObserver.onMessage(ChainInitiationObserver.java:111)

          at org.apache.cxf.transport.servlet.ServletDestination.invoke(ServletDestination.java:99)

          at org.apache.cxf.transport.servlet.ServletController.invokeDestination(ServletController.java:431)

          at org.jboss.wsf.stack.cxf.ServletControllerExt.invoke(ServletControllerExt.java:173)

          at org.jboss.wsf.stack.cxf.RequestHandlerImpl.handleHttpRequest(RequestHandlerImpl.java:61)

          at org.jboss.wsf.stack.cxf.CXFServletExt.invoke(CXFServletExt.java:185)

          at org.apache.cxf.transport.servlet.AbstractHTTPServlet.handleRequest(AbstractHTTPServlet.java:179)

          at org.apache.cxf.transport.servlet.AbstractHTTPServlet.doPost(AbstractHTTPServlet.java:103)

          at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)

          at org.apache.cxf.transport.servlet.AbstractHTTPServlet.service(AbstractHTTPServlet.java:159)

          at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)

          at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)

          at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)

          at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)

          at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)

          at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:235)

          at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)

          at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:183)

          at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:95)

          at org.jboss.web.tomcat.security.SecurityContextEstablishmentValve.process(SecurityContextEstablishmentValve.java:126)

          at org.jboss.web.tomcat.security.SecurityContextEstablishmentValve.invoke(SecurityContextEstablishmentValve.java:70)

          at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)

          at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)

          at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:158)

          at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)

          at org.jboss.web.tomcat.service.request.ActiveRequestResponseCacheValve.internalProcess(ActiveRequestResponseCacheValve.java:74)

          at org.jboss.web.tomcat.service.request.ActiveRequestResponseCacheValve.invoke(ActiveRequestResponseCacheValve.java:47)

          at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:330)

          at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:829)

          at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:599)

          at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:451)

          at java.lang.Thread.run(Thread.java:619)

      Caused by: org.teiid.translator.TranslatorException: Error Code:65000 Message:'{ ?= call "PLUSER"."INSERT_SCHOOL_DETAIL"(?,?,?,?,?,?,?,?,?,?,?)}' error executing statement(s): {1}

          at org.teiid.translator.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:70)

          at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:276)

          at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:354)

          at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:143)

          at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:140)

          at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)

          at java.util.concurrent.FutureTask.run(FutureTask.java:138)

          at org.teiid.dqp.internal.process.DQPCore$FutureWork.run(DQPCore.java:120)

          at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:240)

          at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:122)

          at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:292)

          at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)

          at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)

          ... 1 more

      Caused by: java.sql.SQLException: ORA-06550: line 1, column 13:

      PLS-00201: identifier 'PLUSER.INSERT_SCHOOL_DETAIL' must be declared

      ORA-06550: line 1, column 7:

      PL/SQL: Statement ignored

      • 1748 Views
      • Tags:
        • 2. Re: Did teiid7.7 supports procedures with OUT parameter? If it supports means what are the procedures to call sp through callable statement with teiid
          raghu85
          raghu85 Mar 25, 2013 5:55 AM (in response to rareddy)

          We need some more inputs on calling stored procedure via teiid.

          Below is the process we followed.

          1. Imported a oracle stored procedure through teiid designer and we made a vdb file and deployed to jboss. We are using that vdb file as a datasoruce for our java program which calls stored procedure.

          2. From java class i am calling those imported procedure using the below code

              

               cstmt = connection.prepareCall("{call INSERT_SCHOOL_DETAIL(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");

           

          While calling we are getting the exception that i wrote in the above thread...

           

          we are not sure where we went wrong...find the attached screenshot

          properties.jpg

            • Actions
          • 3. Re: Did teiid7.7 supports procedures with OUT parameter? If it supports means what are the procedures to call sp through callable statement with teiid
            rareddy
            rareddy Mar 25, 2013 8:47 AM (in response to raghu85)

            Raghu,

             

            Usally Teiid requires to stored procedures fully qualified. I see as "{call INSERT_SCHOOL_DETAIL(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}", which should be "{call modelName.INSERT_SCHOOL_DETAIL(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?")}

             

            But I do not think that is the issue. What the above error is saying is, there is no procedure called "{ ?= call "PLUSER"."INSERT_SCHOOL_DETAIL"(?,?,?,?,?,?,?,?,?,?,?)}" in Oracle DB. I guess this is some kind of name scoping issue. IMO, use of "PLUSER" infront of the procedure name *may* be causing the issue. Click on the procedure in the Designer, find the the property field called "NameInSource" and edit that field to just say "INSERT_SCHOOL_DETAIL", then save, rebuild, deploy the vdb and test again. I think that will work.

             

            Now, if it did work, is that procedure created in a user scope or global scope where all the users have access to execution of procedure? If all users have access, then metadata reported may be wrong, or somehow needs to be tweaked in Designer.

             

            If it did not work, try executing the source procedure directly in Oracle (no Teiid involved) from any Java program, if it did not work, figure out why?

             

            Ramesh..

              • Actions
            • 4. Re: Did teiid7.7 supports procedures with OUT parameter? If it supports means what are the procedures to call sp through callable statement with teiid
              shawkins
              shawkins Mar 25, 2013 8:59 AM (in response to rareddy)

              > Usally Teiid requires to stored procedures fully qualified. I see as "{call INSERT_SCHOOL_DETAIL(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}", which should be "{call modelName.INSERT_SCHOOL_DETAIL(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?")}

               

              That was addressed by https://issues.jboss.org/browse/TEIID-46 so you should be able to call procedures unqualified via Teiid.  Teiid will then use the name in source to call any source procedures.

               

              > Now, if it did work, is that procedure created in a user scope or global scope where all the users have access to execution of procedure?

               

              This may be similar to:  http://stackoverflow.com/questions/970875/oracle-identifier-myschema-mytable-must-be-declared

                • Actions
              • 5. Re: Did teiid7.7 supports procedures with OUT parameter? If it supports means what are the procedures to call sp through callable statement with teiid
                jaigates
                jaigates Mar 25, 2013 11:07 AM (in response to rareddy)

                The procedure is packed under school.

                so if i execute as call "PLUSER"."SCHOOLS"."INSERT_SCHOOL_DATA"('school1','asdfasdfas','tye','asdfas','asdfasd','sdfasdf'); in oracle it works fine.

                 

                but teiid designed tool imports tables without packaging and while i execute the sql is called as call "PLUSER"."INSERT_SCHOOL_DATA"('school1','asdfasdfas','tye','asdfas','asdfasd','sdfasdf');

                 

                when i followed above by altering xmi file with NameInSource from "PLUSER"."INSERT_SCHOOL_DATA" to "PLUSER"."SCHOOLS"."INSERT_SCHOOL_DATA" and recompile vdb file, i get some scopping issue with below error. Let me know how to solve this.

                 

                 

                20:35:31,163 WARN  (Worker25_QueryProcessorQueue225)  [org.teiid.CONNECTOR] Connector worker process failed for atomic-request=FDvP2EXLVxub.10.1.9

                org.teiid.translator.TranslatorException: Error Code:65000 Message:'{ ?= call "PLUSER"."SCHOOLS"."INSERT_SCHOOL_DATA"(?,?,?,?,?,?,?,?)}' error executing statement(s): {1}

                    at org.teiid.translator.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:70)

                    at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:276)

                    at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:354)

                    at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:143)

                    at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:140)

                    at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)

                    at java.util.concurrent.FutureTask.run(FutureTask.java:166)

                    at org.teiid.dqp.internal.process.DQPCore$FutureWork.run(DQPCore.java:120)

                    at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:240)

                    at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:122)

                    at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:292)

                    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)

                    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)

                    at java.lang.Thread.run(Thread.java:722)

                Caused by: java.sql.SQLException: ORA-06550: line 1, column 13:

                PLS-00222: no function with name 'INSERT_SCHOOL_DATA' exists in this scope

                ORA-06550: line 1, column 7:

                PL/SQL: Statement ignored

                 

                    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)

                    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)

                    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)

                    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)

                    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)

                    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)

                    at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:204)

                    at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1034)

                    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329

                  • Actions
                • 6. Re: Did teiid7.7 supports procedures with OUT parameter? If it supports means what are the procedures to call sp through callable statement with teiid
                  shawkins
                  shawkins Mar 25, 2013 11:57 AM (in response to jaigates)

                  Teiid is calling the procedure with a return parameter.  What does this procedure return (cursor, scalar, nothing)?  And what is in the Teiid metadata?  Using a return parameter in appropriately I believe can cause this error.

                   

                  Steve

                    • Actions
                  • 7. Re: Did teiid7.7 supports procedures with OUT parameter? If it supports means what are the procedures to call sp through callable statement with teiid
                    jaigates
                    jaigates Mar 26, 2013 2:09 AM (in response to shawkins)

                    the procedure is under user "pluser" and under package "schools" and returns values as OUT parameter. I know calling this way i should get SQL Error: ORA-06553: PLS-306: wrong number or types of arguments in call to 'INSERT_SCHOOL_DATA, i am testing here how teiid translates the sql and call the original oralcle database. And i see scope issue here. Meanwhile i will try to execute this query using callable statement, but if you could give me idea on the above "PLS-00222: no function with name 'INSERT_SCHOOL_DATA' exists in this scope" error, it would be great.

                     

                    Store Procedure create statement:

                    PROCEDURE insert_school_data (v_school_code IN school_data.SCHOOL_CODE%TYPE,
                                                    v_programme IN school_data.PROGRAMME%TYPE,
                                                  v_type IN school_data.TYPE%TYPE,
                                                  v_value IN school_data.VALUE%TYPE,
                                                  v_person_code IN person.PERSON_CODE%TYPE,
                                                  v_role IN person_role.ROLE%TYPE,
                                                  v_status OUT VARCHAR2,
                                                  v_errortext OUT VARCHAR2);

                      • Actions
                    • 8. Re: Did teiid7.7 supports procedures with OUT parameter? If it supports means what are the procedures to call sp through callable statement with teiid
                      shawkins
                      shawkins Mar 26, 2013 7:59 AM (in response to jaigates)

                      > Meanwhile i will try to execute this query using callable statement, but if you could give me idea on the above "PLS-00222: no function with name 'INSERT_SCHOOL_DATA' exists in this scope" error, it would be great.

                       

                      That was what my last post was getting at.  The Teiid source sql shows "? = call ..." However the procedure definition above does not show a return/result parameter, which means that Oracle will likely complain that it is not a function (which should return a value) if invoked as "? = call ..."

                       

                      So what does the Teiid Designer metadata for your procedure look like?  Does it have a result set / return parameter defined? 

                       

                      > when i followed above by altering xmi file

                       

                      I should add that generally you want to avoid directly editing the xmi files.  You can get to the name in source from the properties panel when you have selected the relevant object.

                        • Actions
                      • 9. Re: Did teiid7.7 supports procedures with OUT parameter? If it supports means what are the procedures to call sp through callable statement with teiid
                        raghu85
                        raghu85 Mar 26, 2013 8:46 AM (in response to shawkins)

                        Steve..Thanks for you reply...

                         

                        > That was what my last post was getting at.  The Teiid source sql shows "? = call ..." However the procedure definition above does not show a return/result parameter, which means that Oracle will likely complain that it is not a function (which should return a value) if invoked as "? = call ..."

                         

                            plz tell us how to call the procedure defined in teiid vdb file, if the above used statement(CallableStatement cstmt = connection.prepareCall("{call INSERT_SCHOOL_DETAIL(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");) is wrong. What type of statements have to use for calling Procedures with return paramter.

                         

                        >So what does the Teiid Designer metadata for your procedure look like?  Does it have a result set / return parameter defined?

                             We are defining return paramter. Please find the attached xmi file for your reference.

                          • Actions
                        • 10. Re: Did teiid7.7 supports procedures with OUT parameter? If it supports means what are the procedures to call sp through callable statement with teiid
                          shawkins
                          shawkins Mar 26, 2013 9:14 AM (in response to raghu85)

                          Just to be clear you'll want to separate out whatever is happening with INSERT_SCHOOL_DATA and INSERT_SCHOOL_DETAIL as they seem to be two different scenarios.  My response below will be about INSERT_SCHOOL_DETAIL.

                           

                          > plz tell us how to call the procedure defined in teiid vdb file, if the above used statement(CallableStatement cstmt = connection.prepareCall("{call INSERT_SCHOOL_DETAIL(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");) is wrong.

                           

                          Can you be more specific about what is not working?

                           

                          > What type of statements have to use for calling Procedures with return paramter.

                           

                          If you have a return parameter (which in neither case do you appear to have one), then you would use {? = call ... } such that the return parameter must be parameter 1 - see http://docs.oracle.com/javase/6/docs/api/java/sql/CallableStatement.html

                           

                          > We are defining return paramter. Please find the attached xmi file for your reference.

                           

                          INSERT_SCHOOL_DETAIL does not have a return parameter in this xmi.

                            • Actions
                          • 11. Re: Did teiid7.7 supports procedures with OUT parameter? If it supports means what are the procedures to call sp through callable statement with teiid
                            raghu85
                            raghu85 Mar 26, 2013 10:05 AM (in response to shawkins)

                            Sorry for the confusion..INSERT_SCHOOL_DETAIL and INSERT_SCHOOL_DATA are the two procedures that we are working now. For this discussion we will go with INSERT_SCHOOL_DETAIL as you said.

                             

                            Sorry i mean to say that we are defining the paramter as Dierction--> OUT in xmi, we are not defining any return parameter.

                             

                            TO be more specific...

                             

                            All Oracle procedures that we are working are having parameters defined as IN and OUT and we are not having any return paramter defined in procedures.

                                     

                                      CREATE PROCEDURE INSERT_SCHOOL_DETAIL (

                                    IN V_SCHOOL_NAME String(240),

                                    IN V_REGION_OFFICE String(18),

                                    IN V_SUB_OFFICE String(18),

                                    IN V_COUNTRY_CODE String(9),

                                    IN V_REGION_STATE String(9),

                                    IN V_CURRENCY_CODE String(4000),

                                    IN V_PERSON_CODE String(18),

                                    IN V_ROLE String(105),

                                    OUT V_STATUS String(4000),

                                    OUT V_ERRORTEXT String(4000),

                                    OUT V_NEW_SCHOOL_CODE String(4000))

                             

                            We tried to call this stored procedure through callable statement(using the below statements) and it worked well for Oracle data source that we configured locally.

                                     

                                               cstmt = connection.prepareCall("{call INSERT_SCHOOL_DETAIL(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");

                                                cstmt.setString(1, dataRequest.getSchoolName());

                                                cstmt.setString(2, dataRequest.getRO());

                                                cstmt.setString(3, dataRequest.getSR());

                                                cstmt.setString(4, dataRequest.getCountry());

                                                cstmt.setString(5, dataRequest.getState());

                                                cstmt.setNull(6, Types.VARCHAR);

                                                cstmt.setString(7, dataRequest.getUserName());

                                                cstmt.setString(8, dataRequest.getRoleName());

                                                cstmt.registerOutParameter(9, Types.VARCHAR);

                                                cstmt.registerOutParameter(10, Types.VARCHAR);

                                                cstmt.registerOutParameter(11, Types.VARCHAR);

                                                cstmt.execute();

                            Then we tried to do the same thing via Teiid. So for that we imported oracle procedures via Teiid designer and made xmi and with that xmi we defined vdb and deployed to JBoss and created one datasouce for Teiid vdb that we deployed.

                             

                            Now we tried to call  INSERT_SCHOOL_DETAIL procedure that we defined in xmi using the Teiid datasource that we created. We used the same code that mentioned above to call procedures. But Teiid throwing exception(see the full exception trace).

                             

                            On digging the exception trace element it shows that Teiid converting the callable statement that we wrote in java code into

                            '{ ?= call SCHOOLS.INSERT_SCHOOL_DETAIL(?,?,?,?,?,?,?,?,?,?,

                            ?)}' i.e.,

                            interms of calling the procedure with return paramater but we havent use any return paramter in xmi or in oracle procedure. The real problem is here.

                            We want inputs on how to make configuration in xmi file inorder to call procedure that is not having return parameter.

                                

                             

                            org.teiid.jdbc.TeiidSQLException: Error Code:65000 Message:ibis_proc_noqn: Error Code:65000 Message:'{ ?= call SCHOOLS.INSERT_SCHOOL_DETAIL(?,?,?,?,?,?,?,?,?,?,

                            ?)}' error executing statement(s): {1}

                                at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:113)

                                at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:70)

                                at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:624)

                                at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:72)

                                at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:539)

                                at org.teiid.client.util.ResultsFuture.done(ResultsFuture.java:130)

                                at org.teiid.client.util.ResultsFuture.access$200(ResultsFuture.java:37)

                                at org.teiid.client.util.ResultsFuture$1.receiveResults(ResultsFuture.java:75)

                                at org.teiid.net.socket.SocketServerInstanceImpl.receivedMessage(SocketServerInstanceImpl.java:220)

                                at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:255)

                                at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

                                at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

                                at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

                                at java.lang.reflect.Method.invoke(Unknown Source)

                                at org.teiid.net.socket.SocketServerConnectionFactory$ShutdownHandler.invoke(SocketServerConnectionFactory.java:110)

                                at $Proxy1.read(Unknown Source)

                                at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(SocketServerInstanceImpl.java:349)

                                at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:548)

                                at org.teiid.jdbc.PreparedStatementImpl.execute(PreparedStatementImpl.java:211)

                                at com.jai.TeiidCalableStatementTEST.insertSchool(TeiidCalableStatementTEST.java:71)

                                at com.jai.TeiidCalableStatementTEST.main(TeiidCalableStatementTEST.java:37)

                            Caused by: [TeiidProcessingException] 65000: Error Code:65000 Message:ibis_proc_noqn: Error Code:65000 Message:'{ ?= call SCHOOLS.INSERT_SCHOOL_DETAIL(?,?,?,?,?

                            ,?,?,?,?,?,?)}' error executing statement(s): {1}

                            1 [TranslatorException] 65000: Error Code:65000 Message:'{ ?= call SCHOOLS.INSERT_SCHOOL_DETAIL(?,?,?,?,?,?,?,?,?,?,?)}' error executing statement(s): {1}

                            2 [SQLException]ORA-06550: line 1, column 13:

                            PLS-00222: no function with name 'INSERT_SCHOOL_DETAIL' exists in this scope

                            ORA-06550: line 1, column 7:

                            PL/SQL: Statement ignored

                             

                                at org.teiid.dqp.internal.process.DataTierTupleSource.exceptionOccurred(DataTierTupleSource.java:430)

                                at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:250)

                                at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:271)

                                at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:280)

                                at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:155)

                                at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:280)

                                at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:176)

                                at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:139)

                                at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:105)

                                at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:147)

                                at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:375)

                                at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:288)

                                at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49)

                                at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:216)

                                at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:240)

                                at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:122)

                                at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:292)

                                at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)

                                at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)

                                at java.lang.Thread.run(Thread.java:619)

                            Caused by: [TranslatorException] 65000: Error Code:65000 Message:'{ ?= call SCHOOLS.INSERT_SCHOOL_DETAIL(?,?,?,?,?,?,?,?,?,?,?)}' error executing statement(s):

                            {1}

                            1 [SQLException]ORA-06550: line 1, column 13:

                            PLS-00222: no function with name 'INSERT_SCHOOL_DETAIL' exists in this scope

                            ORA-06550: line 1, column 7:

                            PL/SQL: Statement ignored

                             

                                at org.teiid.translator.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:70)

                                at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:276)

                                at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:354)

                                at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:143)

                                at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:140)

                                at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)

                                at java.util.concurrent.FutureTask.run(FutureTask.java:138)

                                at org.teiid.dqp.internal.process.DQPCore$FutureWork.run(DQPCore.java:120)

                                ... 6 more

                            Caused by: java.sql.SQLException: ORA-06550: line 1, column 13:

                            PLS-00222: no function with name 'INSERT_SCHOOL_DETAIL' exists in this scope

                            ORA-06550: line 1, column 7:

                            PL/SQL: Statement ignored

                             

                                at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)

                                at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)

                                at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)

                                at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)

                                at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:215)

                                at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:954)

                                at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)

                                at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3316)

                                at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3422)

                                at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4394)

                                at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:299)

                                at org.teiid.translator.jdbc.JDBCExecutionFactory.executeStoredProcedure(JDBCExecutionFactory.java:753)

                                at org.teiid.translator.jdbc.oracle.OracleExecutionFactory.executeStoredProcedure(OracleExecutionFactory.java:720)

                                at org.teiid.translator.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:67)

                                ... 13 more

                              • Actions
                            • 12. Re: Did teiid7.7 supports procedures with OUT parameter? If it supports means what are the procedures to call sp through callable statement with teiid
                              shawkins
                              shawkins Mar 26, 2013 10:32 AM (in response to raghu85)

                              > On digging the exception trace element it shows that Teiid converting the callable statement that we wrote in java code into

                              '{ ?= call SCHOOLS.INSERT_SCHOOL_DETAIL(?,?,?,?,?,?,?,?,?,?,

                              ?)}' i.e.,

                              interms of calling the procedure with return paramater but we havent use any return paramter in xmi or in oracle procedure. The real problem is here.

                              We want inputs on how to make configuration in xmi file inorder to call procedure that is not having return parameter.

                               

                              Yes, we are on the same page I just needed to make sure we are seeing the same thing.  The xmi file that you sent did not contain either a result set or a return parameter for INSERT_SCHOOL_DETAIL, so the issue lies with the Oracle translator.  For Teiid to return refcursors with the oracle driver we add ?=  - however we are still adding it even when a result set is not expected.  Can you log an issue?  Assuming that none of your procedures provide result sets, you can workaround this by adding the translator property oracleSuppliedDriver=false. - i.e. in the vdb.xml:

                               

                              ...

                              <model name="x">

                                      <source name="oracle" translator-name="my-oracle" connection-jndi-name="...">

                              </model>

                              ... then after the model definitions ...

                              <translator name="my-oracle" type="oracle" />

                                     <property name="oracleSuppliedDriver" value="false" />

                                ...

                              </translator>

                              ...

                                • Actions
                              • 14. Re: Did teiid7.7 supports procedures with OUT parameter? If it supports means what are the procedures to call sp through callable statement with teiid
                                raghu85
                                raghu85 Mar 27, 2013 1:34 AM (in response to shawkins)

                                Thanks Steve for your inputs. This will help us to move furthure...

                                 

                                As per my understanding this vdb.xml file is only used while importing vdb dyanamically(correct me if i am wrong), but we are using teiid designer which will create static vdb. So whether we have to follow dyanamic vdb approach to solve this issue instead of static vdb?

                                  • Actions
                                1 2 Previous Next