Dapper with Oracle give ORA-00936: missing expression error

Question

I am using Dapper, with Dapper.Extensions and Dapper.SimpleCRUD. The following code works fine when running against a MYSQL database. However when I run the same code with the same tables against oracle I get ORA-00936: missing expression error. I am not sure why I am getting this error because I am simply trying to retrieve all records from the table.

//MYSQL DDL
CREATE TABLE app_config(
    `app_config_id` int AUTO_INCREMENT  NOT NULL,
    `user_name` nvarchar(100) NULL,     
    `program_location` nvarchar(400) NULL,      
    CONSTRAINT PK_tk_app_config_id PRIMARY KEY (app_config_id)    );  

//ORACLE DDL
CREATE TABLE app_config(
    app_config_id number(10)  NOT NULL,
    user_name nvarchar2(100) NULL,      
    program_location nvarchar2(400) NULL,       
    CONSTRAINT PK_tk_app_config_id PRIMARY KEY (app_config_id));

CREATE SEQUENCE app_config_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER app_config_seq_tr
 BEFORE INSERT ON app_config FOR EACH ROW
 WHEN (NEW.app_config_id IS NULL)
BEGIN
 SELECT app_config_seq.NEXTVAL INTO :NEW.app_config_id FROM DUAL;
END;
/

//C# Code    
using System;
using System.Linq;
using System.Data.SqlClient;
using Dapper;

namespace RetrieveAll
{
    public class app_config
        {
            [Key]
            public int app_config_id { get; set; }
            public string user_name { get; set; }
            public string program_location { get; set; }


        }

 public static IDbConnection getDBConnection(string dbtype)
        {
            switch (dbtype)
            {
                default:                
                case "MYSQL":
                    return new MySqlConnection("userid=uid;password=pwd;server=localhost;database=test");
                case "ORACLE":
                    return new OracleConnection("Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=tempdb)));User ID=uid;Password=pwd;");


            }
        }

    class Program
    {
        static void Main(string[] args)
        {
              using (var connection = dbConnector.getDBConnection("ORACLE"))
            {

              var ac = connection.GetList<app_config>().ToList();
            }
        }
    }
}

Answer 1

I do not see anywhere in your code you instruct your ORM which RDBMS you are using.

The root cause of the error is explained here:

Cause
You tried to execute a SQL statement but you omitted a part of the syntax.

This might be caused because ORM is generating incorrect query. This may happen because you did not set the Dialect properly. Please refer to this and this for more details.

With Dapper Extensions, you need to set SqlDialect as below:

//Synchronous
DapperExtensions.DapperExtensions.SqlDialect = 
    new DapperExtensions.Sql.MySqlDialect();//or OracleDialect

//Asynchronous
DapperExtensions.DapperAsyncExtensions.SqlDialect = 
    new DapperExtensions.Sql.MySqlDialect();//or OracleDialect

Similarly, for Simple CRUD, you set the Dialect as below:

   SimpleCRUD.SetDialect(SimpleCRUD.Dialect.PostgreSQL);
    
   SimpleCRUD.SetDialect(SimpleCRUD.Dialect.MySQL);

This tells the ORM which RDBMS you are using. It generates the query accordingly.

I never used SimpleCRUD, but looking at supported dialects for it, Oracle is not supported:

//
// Summary:
//     Database server dialects
public enum Dialect
{
    SQLServer = 0,
    PostgreSQL = 1,
    SQLite = 2,
    MySQL = 3
}

There is separate repository on GitHub named Dapper.SimpleCRUD-with-Oracle-. You may need to use it for Oracle.