JBossDeveloper

> Is above feature only available inTeiid 8.5.?  if I am using  designer 8.2 then this feature is out of scope , is that correct?

No.  The native-query for functions/procedures is supported with Teiid 7.7+.  Setting a native-query for functions is supported in Teiid 8.5.   Teiid Designer version numbers are not directly correlated with Teiid.  They have their own release schedule.  Did you follow Barry's suggestion above?

> is there any document availabe on using 2nd freture from designer as mentioned below?

I don't believe so.  It is a somewhat advanced approach that involves an extra step when using from Designer - something like:

- enable the SupportsNativeQueries translator property on the target source schema.

- manually model a native procedure on the target source schema - it would take at least a string parameter for the sql

- use sql like was shown above to consume the procedure "select t.* from (CALL schema.native('SELECT "MSS4"."PKG_T1"."TEIID_USER_APP_ENT1"(''xxxx'',short_string_col(''xxxx'')) FROM DUAL')) as n, ARRAYTABLE(n.tuple COLUMNS col1 string, col2 string)) as t"

That is why it would be much simpler to just set the extension property if possible.

Amit,

Teiid 8.x Runtime work has been targeted at upgrading and leveraging the changes between JBoss AS 5/6 and the newer AS7.x/EAP 6.1.

Teiid Designer has been lagging Teiid 8.x  versions and our goal has been to add support for runtime changes as needed. Teiid 8.4 and Teiid Designer 8.2 are compatible versions and I would recommend that combination.

Barry

The addition of the native-query extension property was made in the 8.1 Teiid Designer release. As long as you build new model using 8.1, your procedure should include that property and be runnable within Teiid 8.2/AS 7.1.1

Amit,

Ah, so we get the scalar ref cursor back as the single value in the result set rather than the ref cursor as the result set. 

That leaves only two workarounds:

1. Have your stored function return a table type rather than just a cursor, then you could use the native sql:  SELECT * FROM TABLE("MSS4"."PKG_T1"."TEIID_USER_APP_ENT1"($1,short_string_col($2)))

2. Otherwise you'd have to add a (or change your) stored function to pass the short_string_col as a string/varchar and then handle the type conversion on the oracle side.

If those aren't acceptable we'll need an enhancement to cover one of:

1. Supporting non-literal parameters - which would be to more fully work https://issues.jboss.org/browse/TEIID-1323 then you could add a pushdown function short_string_col to properly represent the parameter expression.

2. Allow for the oracle return parameter result set handling to work even when using a native-query override

Thanks for working through this,

Steve

> all these stored fuctions are used in production environment by other existing application so there is no scope to modify them.

You can do the workarounds by adding new wrapping stored functions, but I understand that ideally you wouldn't want to do that.

Otherwise with [#TEIID-2644] Support retrieving result sets from any out parameter - JBoss Issue Tracker your scenario should work in several ways with Teiid 8.6, but Teiid Designer will also need to work [#TEIIDDES-1863] Add native type to procedure parameters - JBoss Issue Tracker to make this more seamless when using Teiid Designer.

Steve