calling oracle procedure with return type ref_cursor

[gadeyne.bram]

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

[shawkins]

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.

[gadeyne.bram]

Hi Steven,

I've send you a reply in a PM.

I've attached the configuration image here.

[shawkins]

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.

[gadeyne.bram]

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

[shawkins]

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

[gadeyne.bram]

Hi Steven,

Changing the datasource to a xa-datasource worked!

Thanks!