[teiid-issues] [JBoss JIRA] (TEIID-1786) Cannot invoke stored function that returns resultset on Oracle

Cannot invoke stored function that returns resultset on Oracle --------------------------------------------------------------- Key: TEIID-1786 URL: https://issues.jboss.org/browse/TEIID-1786 Project: Teiid Issue Type: Bug Components: JDBC Connector Affects Versions: 7.4.1 Reporter: Filip Nguyen Assignee: Steven Hawkins Priority: Blocker My goal was to return result set from stored procedure using Teiid on Oracle. This should be only possible by stored function. I created stored function in the DB {code:title=The stored function|borderStyle=solid} create or replace FUNCTION getEmployeeSurnames RETURN sys_refcursor AS resultset sys_refcursor; BEGIN OPEN RESULTSET FOR SELECT surname FROM employees; RETURN RESULTSET; END; {code} In Teiid designer I imported the function (VDB in attachment). After deploying the VDB I tried to call the function {code:title=JDBC query|borderStyle=solid} List<String> surnameList = new ArrayList<String>(); String[] connectionProps = useCache ? new String[] {"resultSetCacheMode=true"} : new String[] {}; String query = "{CALL getEmployeeSurnames()}"; Connection conn = getTeiidConnection(VDBNAME, connectionProps); Statement cs = conn.createStatement(); cs.execute(query); ResultSet rs = cs.executeQuery(query); while (rs.next()) { surnameList.add(rs.getString(1)); } conn.close(); {code} The exception on the server is {code:title=server.log|borderStyle=solid} 10:57:57,713 WARN [org.teiid.CONNECTOR] Connector worker process failed for atomic-request=YI3UxN+ZHXZU.0.2.79 [TranslatorException] 65000: Error Code:65000 Message:'{ call GETEMPLOYEESURNAMES()}' error executing statement(s): {1} 1 [SQLException]ORA-06550: line 1, column 7: PLS-00221: 'GETEMPLOYEESURNAMES' is not a procedure or is undefined 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:264) at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:338) at org.teiid.dqp.internal.process.DataTierTupleSource.access$000(DataTierTupleSource.java:80) at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:138) at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:135) 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:121) at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:194) at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:118) at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:288) 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:662) Caused by: java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00221: 'GETEMPLOYEESURNAMES' is not a procedure or is undefined ORA-06550: line 1, column 7: PL/SQL: Statement ignored at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523) at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:204) at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1007) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576) at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3677) at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4714) at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374) at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:299) at org.teiid.translator.jdbc.JDBCExecutionFactory.executeStoredProcedure(JDBCExecutionFactory.java:774) at org.teiid.translator.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:67) ... 14 more {code} This is solely problem on Oracle. I tried tested stored procedures/functions that return resultsets on other DB engines without problems (Postgres, MSSQL, MySQL, DB2). Additional info. In SOA platform there is native oracle driver: {code:title=ojdbc6.jar MANIFEST.MF|borderStyle=solid} Manifest-Version: 1.0 Ant-Version: Apache Ant 1.6.5 Created-By: 1.5.0_24-rev-b08 (Sun Microsystems Inc.) Implementation-Vendor: Oracle Corporation Implementation-Title: JDBC Implementation-Version: 11.2.0.2.0 Repository-Id: JAVAVM_11.2.0.2.0_LINUX_100812.1 Specification-Vendor: Sun Microsystems Inc. Specification-Title: JDBC Specification-Version: 4.0 Main-Class: oracle.jdbc.OracleDriver sealed: true Name: oracle/sql/converter/ Sealed: false Name: oracle/sql/ Sealed: false Name: oracle/sql/converter_xcharset/ Sealed: false {code}