Workaround Description: To workaround this issue, either use DataDirect drivers or create a stored procedure wrapper to call the stored function.
project_key: SOA

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}