[teiid-issues] [JBoss JIRA] (TEIID-994) Unable to execute Oracle pipelined table function as procedure

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-994?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12950440#comment-12950440 ] 

Steven Hawkins commented on TEIID-994:
--------------------------------------

Basic support for this should be available based upon TEIID-2644

If the pipelined function is modeled as a procedure with a native-query property of the form "SELECT * FROM TABLE(func(params...))", that will work for a single call - although the parameter substitution does not yet work for varags.

The follow-ons to the basic support are:
Is pushdown support needed so that the pipelined function can be pushed as part of a larger query - this is possible, but it does require some additional metadata and translator supports.
Is chaining of pipelined functions needed? This is not very easy for us to implement yet as it requires the notion of a cursor type that is not yet exposed in our type system.


                
> Unable to execute Oracle pipelined table function as procedure
> --------------------------------------------------------------
>
>                 Key: TEIID-994
>                 URL: https://issues.jboss.org/browse/TEIID-994
>             Project: Teiid
>          Issue Type: Feature Request
>          Components: Misc. Connectors, Query Engine
>            Reporter: Steven Hawkins
>            Assignee: Steven Hawkins
>             Fix For: 8.7, Open To Community
>
>
> Oracle pipelined table functions (can be used in Oracle queries as TABLE(...)) that can be put in the FROM clause and used as if they were tables although they are really procedures.  We found that we imported this table function, although didn't get it quite right - the result set was wrong, which may just be a deficiency of the JDBC import metadata.  We were able to map the proc to a table and execute but when we did we got a PL/SQL error from the db.
> Because this is a proc, we are executing it as a CallableStatement down in the JDBC connector which apparently does not work with DataDirect.  I'm not sure how we can detect and do something different but seems like we need to have some metadata (procedure name in source?) that can tell us that this proc needs to be run differently and have the Oracle JDBC Connector deal with this properly.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira