-
15. Re: calling an oracle funtion part of the package from teiid returns error.
I will get back to you on binding a custom type using JDBC tomorrow.
I treid running using the standard syntax by making the column type as object but got below error.
TEIID30020 Processing exception 'TEIID30504 MYSSDEVORACLE_table_type: TEIID11004 Error executing statement(s): { ?= call "MSS4"."PKG_T1"."TEIID_USER_APP_ENT1"(?,?)}' for request L2Lf1OWwgBPO.5. Exception type org.teiid.core.TeiidProcessingException thrown from oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112).: org.teiid.core.TeiidProcessingException: TEIID30504 MYSSDEVORACLE_table_type: TEIID11004 Error executing statement(s): { ?= call "MSS4"."PKG_T1"."TEIID_USER_APP_ENT1"(?,?)}
at org.teiid.dqp.internal.process.DataTierTupleSource.exceptionOccurred(DataTierTupleSource.java:534) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:321) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:280) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:279) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:279) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:149) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:144) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:110) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:153) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:384) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:288) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:216) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:250) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:123) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:298) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895) [rt.jar:1.6.0_45]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918) [rt.jar:1.6.0_45]
at java.lang.Thread.run(Thread.java:662) [rt.jar:1.6.0_45]
Caused by: org.teiid.translator.TranslatorException: TEIID11004 Error executing statement(s): { ?= call "MSS4"."PKG_T1"."TEIID_USER_APP_ENT1"(?,?)}
at org.teiid.translator.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:70)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:257) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:457) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:177) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:174) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303) [rt.jar:1.6.0_45]
at java.util.concurrent.FutureTask.run(FutureTask.java:138) [rt.jar:1.6.0_45]
at org.teiid.dqp.internal.process.DQPCore$FutureWork.run(DQPCore.java:118) [teiid-engine-8.2.0.Final.jar:8.2.0.Final]
... 6 more
Caused by: java.sql.SQLException: Invalid column type
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:9231)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8812)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:9292)
at oracle.jdbc.driver.OracleCallableStatement.setObject(OracleCallableStatement.java:4899)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.setObject(WrappedPreparedStatement.java:957)
at org.teiid.translator.jdbc.JDBCExecutionFactory.bindValue(JDBCExecutionFactory.java:837)
at org.teiid.translator.jdbc.oracle.OracleExecutionFactory.bindValue(OracleExecutionFactory.java:477)
at org.teiid.translator.jdbc.JDBCExecutionFactory.executeStoredProcedure(JDBCExecutionFactory.java:746)
at org.teiid.translator.jdbc.oracle.OracleExecutionFactory.executeStoredProcedure(OracleExecutionFactory.java:742)
at org.teiid.translator.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:67)
... 13 more
-
16. Re: calling an oracle funtion part of the package from teiid returns error.
So we can conclude that oracle doesn't want these values to be bound as VARCHAR or JAVA_OBJECT. It may be somewhat oracle specific to bind these values properly going by http://docs.oracle.com/cd/B10501_01/java.920/a96654/oraoot.htm
Can you confirm if oracle is fine using a string literal as the parameter value? In SQuirreL you could issue: SELECT "MSS4"."PKG_T1"."TEIID_USER_APP_ENT1"('xxxx','xxxx')" FROM DUAL
-
17. Re: calling an oracle funtion part of the package from teiid returns error.
Hi Steve,
when I run you select statment from TOAD , I get below error.
[Error] Execution (3: 9): ORA-06553: PLS-306: wrong number or types of arguments in call to 'TEIID_USER_APP_ENT1'.
However, the below statment runs fine without any issue.
SELECT "MSS4"."PKG_T1"."TEIID_USER_APP_ENT1"('xxxx',short_string_col('xxxx')) FROM DUAL
it meant that i have to speicify the object name "short_string_col" in the select query.
-
18. Re: calling an oracle funtion part of the package from teiid returns error.
Try changing the Teiid type for the parameter to string and on the procedure set the extension property native-query to:
SELECT "MSS4"."PKG_T1"."TEIID_USER_APP_ENT1"($1,short_string_col($2)) FROM DUAL
We will substitute our generated call with that query instead.
-
19. Re: calling an oracle funtion part of the package from teiid returns error.
I just see relational:Non-Prepared property under extension category in designer. where will I see the "native-query" property?
-
20. Re: calling an oracle funtion part of the package from teiid returns error.
It should be there as well. What version of Designer are you using?
-
21. Re: Re: calling an oracle funtion part of the package from teiid returns error.
I am using Teiid 8.2.0 version. please see the attached screen print of my Teiid procedure.
-
22. Re: Re: calling an oracle funtion part of the package from teiid returns error.
Here it matters more what version of Teiid Designer you're using as it's in charge of associating the extension properties. An issue would need to be opened against Designer if this has not already been corrected and as far I know they don't provide you with a way to simply add an arbitrary property.
-
23. Re: calling an oracle funtion part of the package from teiid returns error.
Hi Steve,
would you be able to check with the Teiid designer team? is there any other way to manually change any of the Teiid file to get the native query working?
-
24. Re: calling an oracle funtion part of the package from teiid returns error.
I've opened the thread Simple Extension Properties related to this on the Designer forums. On that thread to help them track things down can you post what version of Teiid Designer are you using?
> is there any other way to manually change any of the Teiid file to get the native query working?
You can specify the procedure metadata via DDL in the vdb.xml, or starting with 8.3 you can just include an alter statement to set the property.
-
25. Re: calling an oracle funtion part of the package from teiid returns error.
I read the reference guide docuemnt but not able to exactly figure out what will be the entry for my situation? I can see references to various properties like
SupportsNativeQueries,NativeQueryProcedureName and teiid_rel:native-query but not sure how to construc the translator to enter in the vdb. xml file.
-
26. Re: calling an oracle funtion part of the package from teiid returns error.
Amit,
To enable "native queries", if you are using Designer, create a translator override, and set "SupportsNativeQueries" to "true". And on the view's extension property "native-query" provide query you want to execute.
For JDBC see this JDBC Translator for example query.
Ramesh..
-
27. Re: calling an oracle funtion part of the package from teiid returns error.
A model created via Teiid Designer 8.1/8.2 will contain the most recent Model Extension Definition which includes native-query property. The model you are using was probably created prior to 8.1.
Creating a new relational model in 8.2 and copy/pasting your relational tables/procedures into the new model should inject the latest ext. properties.
Barry
-
28. Re: calling an oracle funtion part of the package from teiid returns error.
> To enable "native queries", if you are using Designer, create a translator override, and set "SupportsNativeQueries" to "true". And on the view's extension property "native-query" provide query you want to execute.
Just to clarify there are two native features that are getting a little intermixed.
The first feature is the native-query extension metadata - this allows you on a view, procedure, or function (as of Teiid 8.5) to override the SQL that would have been generated by Teiid. There are no additional translator settings that are required to use this feature.
The other feature is to expose a native query procedure that allows you to execute any sql that you like, but you have to call it in a special procedure that is enabled by the SupportsNativeQueries property. For example:
CALL schema.native('SELECT "MSS4"."PKG_T1"."TEIID_USER_APP_ENT1"(?,short_string_col(?)) FROM DUAL', 'xxxx', 'xxxx');
However the result set that is returned is will have each row as an array and you'll then need to use array_get or arraytable to unpack the results.
-
29. Re: calling an oracle funtion part of the package from teiid returns error.
The first feature is the native-query extension metadata :
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?
is there any document availabe on using 2nd freture from designer as mentioned below?
The other feature is to expose a native query procedure that allows you to execute any sql that you like, but you have to call it in a special procedure that is enabled by the SupportsNativeQueries property