14 Replies Latest reply on Sep 28, 2009 11:54 AM by Ramesh Reddy

    Problem with calling a function from Oracle

    Ram Yelamanchilli Newbie

      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

        • 1. Re: Problem with calling a function from Oracle
          Ramesh Reddy Master

          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..

          • 2. Re: Problem with calling a function from Oracle
            Ram Yelamanchilli Newbie

            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

            • 3. Re: Problem with calling a function from Oracle
              Steven Hawkins Master

              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

              • 4. Re: Problem with calling a function from Oracle
                Ram Yelamanchilli Newbie

                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
                
                
                


                • 5. Re: Problem with calling a function from Oracle
                  Ramesh Reddy Master

                  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..

                  • 6. Re: Problem with calling a function from Oracle
                    Ram Yelamanchilli Newbie

                     

                    "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

                    • 7. Re: Problem with calling a function from Oracle
                      Ram Yelamanchilli Newbie

                       

                      "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

                      • 8. Re: Problem with calling a function from Oracle
                        Ram Yelamanchilli Newbie

                         

                        "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

                        • 9. Re: Problem with calling a function from Oracle
                          Steven Hawkins Master

                          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,

                          • 10. Re: Problem with calling a function from Oracle
                            Ram Yelamanchilli Newbie

                            Teve,

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

                            Regards

                            Ram

                            • 11. Re: Problem with calling a function from Oracle
                              Ram Yelamanchilli Newbie

                              Hi Steve,

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

                              Regards

                              Ram

                              • 12. Re: Problem with calling a function from Oracle
                                Ram Yelamanchilli Newbie

                                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

                                • 13. Re: Problem with calling a function from Oracle
                                  Ramesh Reddy Master

                                  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.

                                  • 14. Re: Problem with calling a function from Oracle
                                    Ramesh Reddy Master

                                    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.