Problem with calling a function from Oracle

[rkishore999]

How can I call a function from Oracle? I have a very simple function with the following definition.

CREATE OR REPLACE FUNCTION NSGT.teiid_test(xyz IN VARCHAR2) RETURN VARCHAR2 IS

BEGIN

 RETURN 'Yes, Maestro function call is working.';
 EXCEPTION
 WHEN NO_DATA_FOUND THEN
 NULL;
 WHEN OTHERS THEN
 -- Consider logging the error and then re-raise
 RAISE;
END maestro_test;
/

I imported this into teiid designer and am trying to execute the following query.

select teiid_test("xxx") from <imported_table> limit 1

I get an error saying "please check that the function name and no of arguments are correct". I see the function imported and its metadata. As part of it's metadata I see there was a first argument imported "PARAMWITHNONAME1" before the actual argument.

Regards

Ram

[rareddy]

Ram,

The problem here is Teiid handles the procedures different than the Functions. However there couple different options to solve this issue.

1) Depending upon the Oracle Driver you are using (specially with Data Direct) drivers, you can import functions as stored procedures and use them. However, when you use them in the "select" queries they need to be defined as the "sub-queries" that gets evaluated as a separate query than the main query, so the push down will not be supported along with the main query.

2) You can use what Teiid calls as "user defined functions" or UDFs. You can read more on this in Teiid documentation. Essentially, you would model a "UDF" function called "teiid_test" in the designer and mark it as the "must pushdown". What this does is, it establishes a Teiid level system function, but marked as "pushdown" to the source-connectors. In Designer this will produce a model file called "FunctionDefinitions.xmi". Check out Designer documentation for details. Once you are done you need to copy "FunctionDefinition.xmi" to "extensions" folder in the Teiid runtime.

Now, the respective connector needs to specify in its capabilities that this defined function is supported in order for engine to push this function to the connector. You can do this by editing the connector binding xml (ConfigurationInfo.def file inside the vdb under the respective "connector/properties" section), and provide a additional property called "getSupportedFunctions" and list all the supported functions by this connector. The problem here is that this is override function, so you would have to list all the functions that are in "OracleSQLTranslator" in addition to your own to function to correctly support all the functions. Otherwise you need to edit the "OracleSQLTranslator" and add your function support in there and patch the Teiid runtime with your changes.

I know the last part is ugly, we have several JIRA to make this process easy.

Let us know if this works and/or if you have any suggestions.

Thanks.

Ramesh..

[rkishore999]

Ramesh,

Thanks for your prompt response. Does option 1 look something like this.

select * from ( select TEIID_TEST('xxx') 'abc' from CAP_CASE_MV) a

I tried this. It didn't work.

I will try to work with option 2 and let you know. Meanwhile, can you please give me an example query of option 1 you were talking about?

Regards

Ram

[shawkins]

Assuming NSGT.teiid_test is the fully qualified procedure name. Then if you just want a single invocation, you can use the exec syntax (optionally using named parameters):

exec NSGT.teiid_test('xxx')

or from a CallableStatement you can use the call syntax

{? = call NSGT.teiid_test(?)}

If you want multiple results it could be called as a correlated subquery:

select (exec NSGT.teiid_test(x.column)) from <some_table> as x

or it can even be invoked as a table

select * NSGT.teiid_test where param = <some value>

See also http://docs.jboss.org/teiid/6.2/reference/en-US/html/sql_support.html#exec_command

[rkishore999]

Steve,

This is not working either. I have to qualify the function with schema as well as vdb something like "exec vdb_name.NSGT.teiid('XXX')" and then I get the following error:

!ENTRY org.teiid.designer.dqp.ui 4 0 2009-09-25 14:32:28.484
!MESSAGE Connector worker process failed for atomic-request=3.30.2.187
!STACK 0
java.lang.AbstractMethodError: oracle.jdbc.driver.OracleCallableStatement.getParameterMetaData()Ljava/sql/ParameterMetaData;
 at org.teiid.connector.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:85)
 at org.teiid.dqp.internal.datamgr.impl.ConnectorWorkItem.processNewRequest(ConnectorWorkItem.java:277)
 at org.teiid.dqp.internal.datamgr.impl.ConnectorWorkItem.process(ConnectorWorkItem.java:157)
 at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:44)
 at org.teiid.dqp.internal.datamgr.impl.SynchConnectorWorkItem.run(SynchConnectorWorkItem.java:69)
 at com.metamatrix.common.queue.WorkerPoolFactory$StatsCapturingSharedThreadPoolExecutor$1.run(WorkerPoolFactory.java:211)
 at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
 at java.lang.Thread.run(Unknown Source)

!ENTRY org.teiid.designer.dqp.ui 4 0 2009-09-25 14:32:28.500
!MESSAGE Unexpected exception for request 3.30
!STACK 0
[MetaMatrixComponentException]oracle.jdbc.driver.OracleCallableStatement.getParameterMetaData()Ljava/sql/ParameterMetaData;
1 [AbstractMethodError]oracle.jdbc.driver.OracleCallableStatement.getParameterMetaData()Ljava/sql/ParameterMetaData;
 at org.teiid.dqp.internal.process.DataTierTupleSource.switchBatch(DataTierTupleSource.java:128)
 at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:89)
 at com.metamatrix.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:132)
 at com.metamatrix.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:261)
 at com.metamatrix.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:165)
 at com.metamatrix.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:261)
 at com.metamatrix.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:110)
 at com.metamatrix.query.processor.QueryProcessor.process(QueryProcessor.java:160)
 at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:261)
 at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:207)
 at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:44)
 at com.metamatrix.common.queue.WorkerPoolFactory$StatsCapturingSharedThreadPoolExecutor$1.run(WorkerPoolFactory.java:211)
 at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
 at java.lang.Thread.run(Unknown Source)
Caused by: java.lang.AbstractMethodError: oracle.jdbc.driver.OracleCallableStatement.getParameterMetaData()Ljava/sql/ParameterMetaData;
 at org.teiid.connector.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:85)
 at org.teiid.dqp.internal.datamgr.impl.ConnectorWorkItem.processNewRequest(ConnectorWorkItem.java:277)
 at org.teiid.dqp.internal.datamgr.impl.ConnectorWorkItem.process(ConnectorWorkItem.java:157)
 at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:44)
 at org.teiid.dqp.internal.datamgr.impl.SynchConnectorWorkItem.run(SynchConnectorWorkItem.java:69)
 ... 4 more

!ENTRY net.sourceforge.sqlexplorer 4 4 2009-09-25 14:32:28.531
!MESSAGE Error processing query
!STACK 0
com.metamatrix.jdbc.MMSQLException: oracle.jdbc.driver.OracleCallableStatement.getParameterMetaData()Ljava/sql/ParameterMetaData;
 at com.metamatrix.jdbc.MMSQLException.create(MMSQLException.java:123)
 at com.metamatrix.jdbc.MMSQLException.create(MMSQLException.java:71)
 at com.metamatrix.jdbc.MMStatement.executeSql(MMStatement.java:433)
 at com.metamatrix.jdbc.MMStatement.execute(MMStatement.java:328)
 at net.sourceforge.sqlexplorer.sqlpanel.SqlExecProgress.processQuery(SqlExecProgress.java:192)
 at net.sourceforge.sqlexplorer.sqlpanel.SqlExecProgress.run(SqlExecProgress.java:121)
 at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:121)
Caused by: [MetaMatrixComponentException]oracle.jdbc.driver.OracleCallableStatement.getParameterMetaData()Ljava/sql/ParameterMetaData;
1 [AbstractMethodError]oracle.jdbc.driver.OracleCallableStatement.getParameterMetaData()Ljava/sql/ParameterMetaData;
 at org.teiid.dqp.internal.process.DataTierTupleSource.switchBatch(DataTierTupleSource.java:128)
 at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:89)
 at com.metamatrix.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:132)
 at com.metamatrix.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:261)
 at com.metamatrix.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:165)
 at com.metamatrix.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:261)
 at com.metamatrix.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:110)
 at com.metamatrix.query.processor.QueryProcessor.process(QueryProcessor.java:160)
 at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:261)
 at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:207)
 at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:44)
 at com.metamatrix.common.queue.WorkerPoolFactory$StatsCapturingSharedThreadPoolExecutor$1.run(WorkerPoolFactory.java:211)
 at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
 at java.lang.Thread.run(Unknown Source)
Caused by: java.lang.AbstractMethodError: oracle.jdbc.driver.OracleCallableStatement.getParameterMetaData()Ljava/sql/ParameterMetaData;
 at org.teiid.connector.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:85)
 at org.teiid.dqp.internal.datamgr.impl.ConnectorWorkItem.processNewRequest(ConnectorWorkItem.java:277)
 at org.teiid.dqp.internal.datamgr.impl.ConnectorWorkItem.process(ConnectorWorkItem.java:157)
 at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:44)
 at org.teiid.dqp.internal.datamgr.impl.SynchConnectorWorkItem.run(SynchConnectorWorkItem.java:69)
 ... 4 more

[rareddy]

What version of the OracleDriver are you using? The "getParameterMetaData()" method introduced in JDK 1.4. The exception indicating that this method is not available on the driver you are using.

Yes, you do have to fully qualify, but I do not think you need the VDB name.

Ramesh..

[rkishore999]

"rareddy" wrote:


2) You can use what Teiid calls as "user defined functions" or UDFs. You can read more on this in Teiid documentation. Essentially, you would model a "UDF" function called "teiid_test" in the designer and mark it as the "must pushdown". What this does is, it establishes a Teiid level system function, but marked as "pushdown" to the source-connectors. In Designer this will produce a model file called "FunctionDefinitions.xmi". Check out Designer documentation for details. Once you are done you need to copy "FunctionDefinition.xmi" to "extensions" folder in the Teiid runtime.

Now, the respective connector needs to specify in its capabilities that this defined function is supported in order for engine to push this function to the connector. You can do this by editing the connector binding xml (ConfigurationInfo.def file inside the vdb under the respective "connector/properties" section), and provide a additional property called "getSupportedFunctions" and list all the supported functions by this connector. The problem here is that this is override function, so you would have to list all the functions that are in "OracleSQLTranslator" in addition to your own to function to correctly support all the functions. Otherwise you need to edit the "OracleSQLTranslator" and add your function support in there and patch the Teiid runtime with your changes.

I know the last part is ugly, we have several JIRA to make this process easy.

Let us know if this works and/or if you have any suggestions.

Thanks.

Ramesh..

Ramesh,

I couldn't get first one going yet. Mean while Can you please give me some pointers about how to go with the 2nd option? What should I change in the Oracle SQLTranslator?

Regards

Ram

[rkishore999]

"rareddy" wrote:
What version of the OracleDriver are you using? The "getParameterMetaData()" method introduced in JDK 1.4. The exception indicating that this method is not available on the driver you are using.

Yes, you do have to fully qualify, but I do not think you need the VDB name.

Ramesh..

Ramesh,

I'm using ojdbc14.jar. I infact tried with both classes12 and ojdbc14 jar files.

Regards

Ram

[rkishore999]

"rkishore999" wrote:

"rareddy" wrote:
What version of the OracleDriver are you using? The "getParameterMetaData()" method introduced in JDK 1.4. The exception indicating that this method is not available on the driver you are using.

Yes, you do have to fully qualify, but I do not think you need the VDB name.

Ramesh..

Ramesh,

I'm using ojdbc14.jar. I infact tried with both classes12 and ojdbc14 jar files.

Regards

Ram

Ramesh,

It's not really the vdb name that it's looking to be qualified with. But it's model name. I tried running designer created vdb through the teiid server with no luck. I'm getting the following error when I try it out in the server.

URL: mmfile:/C:/teiid/extensions/ojdbc14.jar
com.metamatrix.jdbc.MMSQLException: Error Code:17023 Message:Error Code:17023 Message:Error occurred on connector NSGT Connector<8> - Error Code:17023 Message:{ ?= call TEIID_TEST(?)}

Executing statement:

 {1}
 at com.metamatrix.jdbc.MMSQLException.create(MMSQLException.java:123)
 at com.metamatrix.jdbc.MMSQLException.create(MMSQLException.java:71)
 at com.metamatrix.jdbc.MMStatement.executeSql(MMStatement.java:442)
 at com.metamatrix.jdbc.MMStatement.executeQuery(MMStatement.java:348)
 at dk.client.JDBCClient.execute(JDBCClient.java:270)
 at dk.client.JDBCClient.main(JDBCClient.java:229)
Caused by: [MetaMatrixComponentException] 17023: Error Code:17023 Message:Error Code:17023 Message:Error occurred on connector NSGT Connector<8> - Error Code:17023 Message:{ ?= call TEIID_TEST(?)}

Executing statement:

 {1}
1 [ConnectorException]Error Code:17023 Message:Error occurred on connector NSGT Connector<8> - Error Code:17023 Message:{ ?= call TEIID_TEST(?)}

Executing statement:

 {1}
2 [ConnectorException]Error Code:17023 Message:{ ?= call TEIID_TEST(?)}

Executing statement:

 {1}
3 [SQLException]Unsupported feature
 at org.teiid.dqp.internal.process.DataTierTupleSource.switchBatch(DataTierTupleSource.java:128)
 at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:89)
 at com.metamatrix.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:132)
 at com.metamatrix.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:261)
 at com.metamatrix.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:165)
 at com.metamatrix.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:261)
 at com.metamatrix.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:110)
 at com.metamatrix.query.processor.QueryProcessor.process(QueryProcessor.java:160)
 at com.metamatrix.query.processor.relational.SubqueryAwareEvaluator.evaluateSubquery(SubqueryAwareEvaluator.java:129)
 at com.metamatrix.query.eval.Evaluator.evaluate(Evaluator.java:651)
 at com.metamatrix.query.eval.Evaluator.internalEvaluate(Evaluator.java:556)
 at com.metamatrix.query.eval.Evaluator.evaluate(Evaluator.java:514)
 at com.metamatrix.query.processor.relational.ProjectNode.updateTuple(ProjectNode.java:232)
 at com.metamatrix.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:192)
 at com.metamatrix.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:261)
 at com.metamatrix.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:110)
 at com.metamatrix.query.processor.QueryProcessor.process(QueryProcessor.java:160)
 at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:261)
 at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:207)
 at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:44)
 at com.metamatrix.common.queue.WorkerPoolFactory$StatsCapturingSharedThreadPoolExecutor$1.run(WorkerPoolFactory.java:211)
 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:619)
Caused by: org.teiid.connector.api.ConnectorException: Error Code:17023 Message:Error occurred on connector NSGT Connector<8> - Error Code:17023 Message:{ ?= call TEIID_TEST(?)}

Executing statement:

 {1}
 at org.teiid.dqp.internal.datamgr.impl.ConnectorWorkItem.handleError(ConnectorWorkItem.java:235)
 at org.teiid.dqp.internal.datamgr.impl.ConnectorWorkItem.process(ConnectorWorkItem.java:174)
 at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:44)
 at org.teiid.dqp.internal.datamgr.impl.SynchConnectorWorkItem.run(SynchConnectorWorkItem.java:69)
 ... 4 more
Caused by: org.teiid.connector.api.ConnectorException: Error Code:17023 Message:{ ?= call TEIID_TEST(?)}

Executing statement:

 {1}
 at org.teiid.connector.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:92)
 at org.teiid.dqp.internal.datamgr.impl.ConnectorWorkItem.processNewRequest(ConnectorWorkItem.java:277)
 at org.teiid.dqp.internal.datamgr.impl.ConnectorWorkItem.process(ConnectorWorkItem.java:157)
 ... 6 more
Caused by: java.sql.SQLException: Unsupported feature
 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:227)
 at oracle.jdbc.driver.DatabaseError.throwUnsupportedFeatureSqlException(DatabaseError.java:537)
 at oracle.jdbc.driver.OraclePreparedStatement.getParameterMetaData(OraclePreparedStatement.java:9086)
 at org.teiid.connector.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:85)
 ... 8 more
select (exec rowcol.NSGT.maestro_test('xyz')) ZZZ from CSP_CASE limit 1

Regards

Ram

[shawkins]

Hi Ram,

getParameterMetaData works fine with the ojdbc6.jar (11.2.0.1.0) client jar. From a quick search, it seems like this was an Oracle problem until some release of ojdbc5.jar.

Thanks,

[rkishore999]

Teve,

I have used ojdbc6.jar and still see the same error.

Regards

Ram

[rkishore999]

Hi Steve,

I have used ojdbc6.jar. But I'm getting the same error still.

Regards

Ram

[rkishore999]

Steve/Ramesh,

I got it working but as non-pushdown function. I have created a FunctionDefinitions.xmi with non-pushdown function and created the function method in a class which in turn called the source function from Oracle.

I think this is not the right way to do. But I can keep going with this work around. I still want to keep working with the original issue. Thanks a lot for your valuable cooperation.

Regards

Ram

[rareddy]

You could try building a test program which uses "getParameterMetaData()" call and use with both the driver versions. Be sure to test both function as well as procedure and see the results. That will surely tell you what is supported by your Oracle driver.

[rareddy]

For make your function "pushdown" do the following

1) In the Designer, in the "FunctionDefinitions.xmi" model, select your function, and in the properties tab, change the property under "Misc/Push Down" to "REQUIRED".

2) Save the model, and copy to the "FunctionDefinitions.xmi" file to "extensions" folder.

3) If you want to take "non" coding approach, edit the "ConfigurationInfo.def" file in your VDB, and add following property.

<Connector Name="My Connector" ComponentType="Oracle Connector">
 <Properties>
 <Property Name="getSupportedFunctions">+,-,*,/,ABS,ACOS,ASIN,ATAN,ATAN2,COS,EXP,FLOOR,CEILING,LOG,LOG10,MOD,POWER,SIGN,SIN,SQRT,TAN,ASCII,CHAR,CHR,CONCAT,||,INITCAP,LCASE,LENGTH,LEFT,LOCATE,LOWER,LPAD,LTRIM,REPLACE,RPAD,RIGHT,RTRIM,SUBSTRING,TRANSLATE,UCASE,UPPER,HOUR,MONTH,MONTHNAME,YEAR,DAY,DAYNAME,DAYOFMONTH,DAYOFWEEK,DAYOFYEAR,QUARTER,MINUTE,SECOND,QUARTER,WEEK,CAST,CONVERT,IFNULL,NVL,COALESCE,sdo_relate,sdo_nn,sdo_filter,sdo_nn_distance,sdo_within_distance,test_teiid</Property>

 Other Properties..
 </Properties>
</Connector>

4) Notice the your function at the end.

If you want to do programatically,

1) Extend "org.teiid.connector.jdbc.oracle.Oraclepabilities" class, overload the "getSupportedFunctions()" method, for example the new class name is "MyOracleCapabilities"

@Override
public List<String> getSupportedFunctions() {
 List<String> supportedFunctions = new ArrayList<String>();
 supportedFunctions.addAll(super.getSupportedFunctions());
 supportedFunctions.add("teiid_test");
 return supportedFunctions;
}

2) Now, you need to configure the Teiid to use the "MyOracleCapabilities" class, instead of default "OracleCapabilities" class, to do that edit the "ConfigurationInfo.def" file and following property to the connector binding.

<Connector Name="My Connector" ComponentType="Oracle Connector">
 <Properties>
 <Property Name="ExtensionCapabilityClass">MyOracleCapabilities</Property>
 <Property Name="ConnectorClassPath">extensionjar:patch.jar;extensionjar:ojdbc6.jar</Property>
 Other Properties..
 </Properties>
</Connector>

3) Provide the new class in a jar file (patch.jar) in "extensions" directory, and update the "ConfigurationInfo.def" as shown above with the "ConnectorClassPath" property.

4) restart.

above both techniques should work for your scenario.