6 Replies Latest reply on Mar 4, 2015 3:42 AM by gadeyne.bram

    calling oracle procedure with return type ref_cursor

    gadeyne.bram Master

      Hi,

       

      I'm using Teiid 8.9.1.

       

      I'm trying to execute an oracle procedure that returns a REF_CURSOR.

       

      When I do this, I get the following exception:java.sql.SQLException: Invalid column type: 2000

       

      Do I have to configure something else to make this work? I'm not really sure what column type 2000 is...

       

      15:50:04,076 WARN  [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue11) nwKUiogkfDYt Connector worker process failed for atomic-request=nwKUiogkfDYt.5.1.0: org.teiid.translator.TranslatorException: TEIID11004 Error executing statement(s): {?= call TARSIER.PKG_IZ_VERBLIJVEN.UitvoerenCur(?,?)}

              at org.teiid.translator.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:70) [translator-jdbc-8.9.1.jar:8.9.1]

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

              at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_25]

              at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_25]

              at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_25]

              at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_25]

              at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:209)

              at com.sun.proxy.$Proxy47.execute(Unknown Source)

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

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

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

              at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334) [rt.jar:1.7.0_25]

              at java.util.concurrent.FutureTask.run(FutureTask.java:166) [rt.jar:1.7.0_25]

              at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58)

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

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

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

              at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_25]

              at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_25]

              at java.lang.Thread.run(Thread.java:724) [rt.jar:1.7.0_25]

      Caused by: java.sql.SQLException: Invalid column type: 2000

              at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:4369)

              at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:137)

              at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:335)

              at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:583)

              at oracle.jdbc.driver.OracleCallableStatementWrapper.registerOutParameter(OracleCallableStatementWrapper.java:1585)

              at org.jboss.jca.adapters.jdbc.WrappedCallableStatement.registerOutParameter(WrappedCallableStatement.java:807)

              at org.teiid.translator.jdbc.JDBCExecutionFactory.registerSpecificTypeOfOutParameter(JDBCExecutionFactory.java:828) [translator-jdbc-8.9.1.jar:8.9.1]

              at org.teiid.translator.jdbc.oracle.OracleExecutionFactory.registerSpecificTypeOfOutParameter(OracleExecutionFactory.java:841) [translator-jdbc-8.9.1.jar:8.9.1]

              at org.teiid.translator.jdbc.JDBCExecutionFactory.executeStoredProcedure(JDBCExecutionFactory.java:788) [translator-jdbc-8.9.1.jar:8.9.1]

              at org.teiid.translator.jdbc.oracle.OracleExecutionFactory.executeStoredProcedure(OracleExecutionFactory.java:847) [translator-jdbc-8.9.1.jar:8.9.1]

              at org.teiid.translator.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:67) [translator-jdbc-8.9.1.jar:8.9.1]

              ... 19 more

        • 1. Re: calling oracle procedure with return type ref_cursor
          Steven Hawkins Master

          What is the exact definition of the source procedure?  And how was the Teiid metadata created - designer or a dynamic vdb?

           

          Since [TEIID-2644] Support retrieving result sets from any out parameter - JBoss Issue Tracker at least for a dynamic vdb we should be able to support most of Oracle procedure results scenarios.

          • 2. Re: Re: calling oracle procedure with return type ref_cursor
            gadeyne.bram Master

            Hi Steven,

             

            I've send you a reply in a PM.

             

            I've attached the configuration image here.

            • 3. Re: Re: calling oracle procedure with return type ref_cursor
              Steven Hawkins Master

              Unfortunately you'll have to teak the designer metadata for this to work.  For either the return or out parameter form, you'll need to add the a result set to the procedure based upon the cursor columns.

               

              For the stored function that returns the cursor, either remove the return parameter that is currently object type or change it's native type to REF CURSOR.  Teiid will then either assume or know that the cursor is coming from a return parameter.

               

              For the procedure with the out parameter, there should be no return parameter.  Just set the native type of the appropriate out parameter to REF CURSOR.

               

              This can also be logged as an issue for Teiid Designer.

              • 4. Re: calling oracle procedure with return type ref_cursor
                gadeyne.bram Master

                Hi Steven,

                 

                Indeed it works when I add a definition for the result set to the procedure and remove the additional parameter.

                 

                However I have an additional problem now.

                 

                When I execute this it works fine:

                 

                call OAZISP.TARSIER.UITVOERENCUR('someid', 'someid')

                 

                When I use the procedure call as a table join I get a weird error.

                 

                select *

                from prod_PV_PatientInfo pi

                join OAZISP.TARSIER.UITVOERENCUR uc on

                    uc.PATID_IN = replace(pi.PatientCode, ' ','') and

                    uc.VISITID_IN = pi.PatientSSN

                where pi.DisTime is not null

                 

                This statement resolves in an error message:

                 

                Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 OAZISP: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@2ccffa20[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@30db21a1 connection handles=0 lastUse=1425379693035 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@56ec4ba0 pool internal context=SemaphoreArrayListManagedConnectionPool@23156082[pool=oazisp] xaResource=LocalXAResourceImpl@3046b257[connectionListener=2ccffa20 connectionManager=71668683 warned=false currentXid=null productName=Oracle productVersion=Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

                 

                When I check the teiid logs I see this stack trace:

                 

                11:48:13,034 WARN  [com.arjuna.ats.arjuna] (Worker269_QueryProcessorQueue173302) qJodiSWyUu4O ARJUNA012140: Adding multiple last resources is disallowed. Trying to add LastResourceRecord(XAOnePhaseResource(LocalXAResourceImpl@3046b257[connectionListener=2ccffa20 connectionManager=71668683 warned=false currentXid=< formatId=131077, gtrid_length=29, bqual_length=36, tx_uid=0:ffff7f000101:-6ff0c392:54f08404:4fbf, node_name=1, branch_uid=0:ffff7f000101:-6ff0c392:54f08404:4fc2, subordinatenodename=null, eis_name=java:/oazisp > productName=Oracle productVersion=Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

                With the Partitioning, Real Application Clusters, OLAP, Data Mining

                and Real Application Testing options jndiName=java:/oazisp])), but already have LastResourceRecord(XAOnePhaseResource(LocalXAResourceImpl@231a0430[connectionListener=3217ccff connectionManager=2093c9f warned=false currentXid=< formatId=131077, gtrid_length=29, bqual_length=36, tx_uid=0:ffff7f000101:-6ff0c392:54f08404:4fbf, node_name=1, branch_uid=0:ffff7f000101:-6ff0c392:54f08404:4fc1, subordinatenodename=null, eis_name=java:/izisprod > productName=ASE productVersion=15.50 jndiName=java:/izisprod]))

                11:48:13,035 DEBUG [org.teiid.COMMAND_LOG] (Worker269_QueryProcessorQueue173302) qJodiSWyUu4O   ERROR SRC COMMAND:      endTime=2015-03-03 11:48:13.035 requestID=qJodiSWyUu4O.24       sourceCommandID=7       executionID=44904       txID=TransactionImple < ac, BasicAction: 0:ffff7f000101:-6ff0c392:54f08404:4fbf status: ActionStatus.ABORT_ONLY >       modelName=OAZISP        translatorName=oracle   sessionID=qJodiSWyUu4O  principal=bram@teiid-security   finalRowCount=null

                11:48:13,035 WARN  [org.teiid.CONNECTOR] (Worker269_QueryProcessorQueue173302) qJodiSWyUu4O Connector worker process failed for atomic-request=qJodiSWyUu4O.24.7.44904: org.teiid.translator.TranslatorException: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@2ccffa20[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@30db21a1 connection handles=0 lastUse=1425379693035 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@56ec4ba0 pool internal context=SemaphoreArrayListManagedConnectionPool@23156082[pool=oazisp] xaResource=LocalXAResourceImpl@3046b257[connectionListener=2ccffa20 connectionManager=71668683 warned=false currentXid=null productName=Oracle productVersion=Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

                With the Partitioning, Real Application Clusters, OLAP, Data Mining

                and Real Application Testing options jndiName=java:/oazisp] txSync=null]

                        at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:285) [translator-jdbc-8.9.1.jar:8.9.1]

                        at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:57) [translator-jdbc-8.9.1.jar:8.9.1]

                        at org.teiid.translator.ExecutionFactory.getConnection(ExecutionFactory.java:194) [teiid-api-8.9.1.jar:8.9.1]

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

                        at sun.reflect.GeneratedMethodAccessor99.invoke(Unknown Source) [:1.7.0_25]

                        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_25]

                        at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_25]

                        at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:209)

                        at com.sun.proxy.$Proxy47.execute(Unknown Source)

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

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

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

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

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

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

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

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

                        at org.teiid.query.processor.BatchIterator.finalRow(BatchIterator.java:69)

                        at org.teiid.common.buffer.AbstractTupleSource.getCurrentTuple(AbstractTupleSource.java:70)

                        at org.teiid.query.processor.BatchIterator.getCurrentTuple(BatchIterator.java:84)

                        at org.teiid.common.buffer.AbstractTupleSource.nextTuple(AbstractTupleSource.java:48)

                        at org.teiid.query.processor.relational.EnhancedSortMergeJoinStrategy.process(EnhancedSortMergeJoinStrategy.java:431)

                        at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:211)

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

                        at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:102)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

                        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_25]

                        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_25]

                        at java.lang.Thread.run(Thread.java:724) [rt.jar:1.7.0_25]

                Caused by: java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@2ccffa20[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@30db21a1 connection handles=0 lastUse=1425379693035 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@56ec4ba0 pool internal context=SemaphoreArrayListManagedConnectionPool@23156082[pool=oazisp] xaResource=LocalXAResourceImpl@3046b257[connectionListener=2ccffa20 connectionManager=71668683 warned=false currentXid=null productName=Oracle productVersion=Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

                With the Partitioning, Real Application Clusters, OLAP, Data Mining

                and Real Application Testing options jndiName=java:/oazisp] txSync=null]

                        at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:147)

                        at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:281) [translator-jdbc-8.9.1.jar:8.9.1]

                        ... 42 more

                Caused by: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@2ccffa20[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@30db21a1 connection handles=0 lastUse=1425379693035 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@56ec4ba0 pool internal context=SemaphoreArrayListManagedConnectionPool@23156082[pool=oazisp] xaResource=LocalXAResourceImpl@3046b257[connectionListener=2ccffa20 connectionManager=71668683 warned=false currentXid=null productName=Oracle productVersion=Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

                With the Partitioning, Real Application Clusters, OLAP, Data Mining

                and Real Application Testing options jndiName=java:/oazisp] txSync=null]

                        at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.reconnectManagedConnection(AbstractConnectionManager.java:628)

                        at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:491)

                        at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:139)

                        ... 43 more

                Caused by: javax.resource.ResourceException: IJ000461: Could not enlist in transaction on entering meta-aware object

                        at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.managedConnectionReconnected(TxConnectionManagerImpl.java:474)

                        at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.reconnectManagedConnection(AbstractConnectionManager.java:623)

                        ... 45 more

                Caused by: javax.transaction.SystemException: IJ000356: Failed to enlist: java.lang.Throwable: Unabled to enlist resource, see the previous warnings. tx=TransactionImple < ac, BasicAction: 0:ffff7f000101:-6ff0c392:54f08404:4fbf status: ActionStatus.ABORT_ONLY >

                        at org.jboss.jca.core.connectionmanager.listener.TxConnectionListener$TransactionSynchronization.checkEnlisted(TxConnectionListener.java:653)

                        at org.jboss.jca.core.connectionmanager.listener.TxConnectionListener.enlist(TxConnectionListener.java:356)

                        at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.managedConnectionReconnected(TxConnectionManagerImpl.java:467)

                        ... 46 more

                • 5. Re: calling oracle procedure with return type ref_cursor
                  Steven Hawkins Master

                  Either you are already working under a transaction or Teiid has started one for you.  Then you are accessing more than one local transaction data source.  JBoss Transactions will error at that point because it cannot guarantee 2 phase commit.  If a transaction is desirable here, then it would be best to make the sources involved XA data sources instead.  Otherwise we'd need to see if the client or Teiid is starting a transaction and see what could be done to workaround that.

                   

                  Steve

                  1 of 1 people found this helpful
                  • 6. Re: calling oracle procedure with return type ref_cursor
                    gadeyne.bram Master

                    Hi Steven,

                     

                    Changing the datasource to a xa-datasource worked!

                     

                    Thanks!