Naming strategy with ORACLE

[fairking]

SQLKata 2.2.0
Oracle 18xe
Compiler only with no execution.

I have an issue.
Having names in my app like "snake_case" the SQLKata creates the following query:
select "my_column" from "USER1"."snake_case"
which causes the sql error "table or view not found".
The right solution is to have a query without quotes like: select my_column from USER1.snake_case.

Along with sqlkata I am using NHibernate with ImprovedNamingStrategy. NHibernate is working fine with those names and produces the queries with no quotes (eg. running QueryOver).
Also my app is using SQLServer and Postgres. Both of them are working with no issues.

Is it possible to tell SQLKata to not wrap names into quotes? Or maybe there is another solution for that. Thanks for any help.

[ahmad-moussawi]

You can create your own Oracle compiler and override the default Identifiers, and use it instead of the default Oracle Compiler

    public class MyOracleCompiler : OracleCompiler
    {
        MyOracleCompiler()
        {
            OpeningIdentifier = "";
            ClosingIdentifier = "";
        }
    }

I think this what you need in your case

[fairking]

Tried what you suggested. But I am ending up with the following error:

System.ArgumentException: String cannot be of zero length. (Parameter 'oldValue')
   at System.String.Replace(String oldValue, String newValue)
   at SqlKata.Compilers.Compiler.WrapValue(String value) in C:\Projects\querybuilder\QueryBuilder\Compilers\Compiler.cs:line 837
   at SqlKata.Compilers.Compiler.CompileColumns(SqlResult ctx) in C:\Projects\querybuilder\QueryBuilder\Compilers\Compiler.cs:line 508
   at SqlKata.Compilers.Compiler.CompileSelectQuery(Query query) in C:\Projects\querybuilder\QueryBuilder\Compilers\Compiler.cs:line 190
   at SqlKata.Compilers.OracleCompiler.CompileSelectQuery(Query query) in C:\Projects\querybuilder\QueryBuilder\Compilers\OracleCompiler.cs:line 24
   at SqlKata.Compilers.Compiler.CompileRaw(Query query) in C:\Projects\querybuilder\QueryBuilder\Compilers\Compiler.cs:line 120
   at SqlKata.Compilers.Compiler.Compile(Query query) in C:\Projects\querybuilder\QueryBuilder\Compilers\Compiler.cs:line 153

[ahmad-moussawi]

Oops, I think overriding the WrapValue method would help in this case.

[fairking]

Yes I did it and encountered another issue. Not sure if it's related to the SQLKata. In lots of places I got the following error: Object of type 'System.Int64' cannot be converted to type 'System.Int32'. or Unable to cast object of type 'System.Decimal' to type 'System.Int32'.
Looking at the problem...

public class ImprovedOracleCompiler : OracleCompiler
    {
        public ImprovedOracleCompiler()
        {
            OpeningIdentifier = "";
            ClosingIdentifier = "";
        }

        public override string WrapValue(string value)
        {
            return value;
        }
    }

[ahmad-moussawi]

I don't think it's related, could you share reproducible steps so we can investigate more

[fairking]

I fixed the issue by converting decimal and int64 into the int32 in my AliasToBeanResultTransformer.
It is not related to the SQLKata.
ImprovedOracleCompiler solved my primary problem in this post. But you could add a small fix into the Compiler if you feel it's necessery.

Thanks guys I love SQLKata so much. 🥇

Feel free to close the ticket.

[ahmad-moussawi]

Happy to hear that!, I will close it for now.
Thanks for your feedback.