HELP !

[yangxiaochen <yx...@163.com>]

Error: 
No match found for function signature TO_CHAR(<CHARACTER>)

BACKGROUND：
	I want to implement a function that can suffice for parsing and validating complex Oracle SQL. But I encountered some Oracle functions and keywords that are not supported during development, I don't know how to do it! What configuration do I need to do in Calcite to support all Oracle functions except custom functions? thank you very much

This is My Code :

public class Example1 {
    public static void main(String[] args) throws Exception {
        String sql = "SELECT a.ID, to_char(b.AGE ) FROM HDC_ODS.DEMO a LEFT JOIN HDC_ODS.DEMO1 b ON a.ID = b.ID";
        runProjectQueryWithLex(Lex.ORACLE, sql);
    }

    private static void runProjectQueryWithLex(Lex lex, String sql)
            throws SqlParseException, ValidationException, RelConversionException {
        SqlParser.Config javaLex = SqlParser.configBuilder().setLex(lex).build();
        Planner planner = getPlanner(null, javaLex, Programs.ofRules(Programs.RULE_SET));
        SqlNode sqlNode = planner.parse(sql);
        SqlNode validate = planner.validate(sqlNode);
        System.out.println(validate);
    }

    private static Planner getPlanner(List<RelTraitDef> traitDefs,
                                      SqlParser.Config parserConfig, Program... programs) {
        CalciteSchema calciteSchema = CalciteSchema.createRootSchema(true, true);
        SchemaPlus rootSchema = calciteSchema.plus();

        //final SchemaPlus rootSchema = Frameworks.createRootSchema(true);
        DataSource dataSource = JdbcSchema.dataSource("jdbc:oracle:thin:@//XXX:1521/ORCL?fun=oracle",
                "oracle.jdbc.driver.OracleDriver", “XXX", “XXX");
        final JdbcCatalogSchema schema =
                JdbcCatalogSchema.create(null, "", dataSource, "PUBLIC");
        final CalciteSchema rootSchema0 =
                CalciteSchema.createRootSchema(false, false, "", schema);

        final val schemaMap = rootSchema0.getSubSchemaMap();

        schemaMap.forEach((key, value) -> {
            rootSchema.add(key, value.schema);
        });

//        SqlOperatorTable opTab =
//                SqlLibraryOperatorTableFactory.INSTANCE.getOperatorTable(
//                        EnumSet.of(SqlLibrary.ORACLE,SqlLibrary.STANDARD,SqlLibrary.SPATIAL));


        final FrameworkConfig config = Frameworks.newConfigBuilder()
                .parserConfig(parserConfig)
                .defaultSchema(rootSchema)
                .traitDefs(traitDefs)
                .programs(programs)
                .operatorTable(SqlStdOperatorTable.instance())
                .build();
        return Frameworks.getPlanner(config);
    }
}



This is My error:

Caused by: org.apache.calcite.sql.validate.SqlValidatorException: No match found for function signature TO_CHAR(<NUMERIC>)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:505)
	at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:599)
	... 24 more

Re: HELP !

[Jiajun Xie <ji...@gmail.com>]

Hi, xiaochen~
  As mentioned in calcite-5225, you can add it to SqlLibraryOperators. If
you have many functions to add, create your SqlOperatorTable and use
ChainedSqlOperatorTable is a more suitable way. For example,
https://github.com/apache/calcite/blob/0e57722208d842a1192f38f28c2a379ae88dcdf0/testkit/src/main/java/org/apache/calcite/test/MockSqlOperatorTable.java#L87



  In addition, I noticed that you use core SQL parser instead of Babel SQL
parser. Babel SQL parser is used to accept common dialects such as Oracle,
MySQL, PostgreSQL, BigQuery.
  Here is babel parser config:
```
SqlParser.Config c = SqlParser.config()
.withConformance(SqlConformanceEnum.BABEL)
            .withParserFactory(SqlBabelParserImpl.FACTORY);
```