[teiid-issues] [JBoss JIRA] (TEIID-1786) Cannot invoke stored function that returns resultset on Oracle
Steven Hawkins (Resolved) (JIRA)
jira-events at lists.jboss.org
Tue Oct 18 10:25:45 EDT 2011
[ https://issues.jboss.org/browse/TEIID-1786?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Steven Hawkins resolved TEIID-1786.
-----------------------------------
Resolution: Duplicate Issue
This is a dup of TEIID-832. This has always been the case with Oracle native (datadirect supports this case without any special effort). This is a KI and I don't believe should be a blocker.
> 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}
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
More information about the teiid-issues
mailing list