Devart Forums

Posted: **Tue 21 Sep 2010 19:09**

I am trying to run the following query:

"SELECT version FROM sys.v_$instance"

When executed, it throws a System.Data.Entity.SqlException with the following message:

The query syntax is not valid., line 1, column 27

Below is a code snippet that causes the error:

Code: Select all

DbConnection conn = (System.Data.EntityClient.EntityConnection)context.Connection;

ConnectionState initialState = conn.State;
try
{
    if (initialState != ConnectionState.Open)
        conn.Open();  // open connection if not already open 

    try
    {
        using (DbCommand cmd = conn.CreateCommand())
        {
            // todo: fix the v_$ error
            cmd.CommandText = "SELECT edition FROM sys.v_$instance";
            r = cmd.ExecuteReader();

            if (r.HasRows)
            {
                r.Read();

                _databaseEdition = r.GetValue(0).ToString();
            }
        }
    }
    catch (Exception ex)
    {
        // eat the error
    }
}
finally
{
    if (initialState != ConnectionState.Open)
        conn.Close(); // only close connection if not initially open 
}

I have tried escaping the query expression, e.g.

Code: Select all

@"SELECT version FROM sys.v_\$instance", "SELECT version FROM sys.v_\$instance", "SELECT version FROM sys.v_\u0024instance"

, but it still throws the same exception.

Is threre any way to query the Oracle system objects that contain a dollar sign?

Thanks,
Welton

Posted: **Wed 22 Sep 2010 14:35**

Please try this code:

Code: Select all

DbConnection conn = 
((EntityConnection)(context.Connection)).StoreConnection; //line modified

ConnectionState initialState = conn.State;
try
{
    if (initialState != ConnectionState.Open)
        conn.Open();  // open connection if not already open

    try
    {
        using (DbCommand cmd = conn.CreateCommand())
        {
            // todo: fix the v_$ error
            cmd.CommandText = "SELECT version FROM sys.v_$instance"; //line modified
            r = cmd.ExecuteReader();

            if (r.HasRows)
            {
                r.Read();

                _databaseEdition = r.GetValue(0).ToString();
            }
        }
    }
    catch (Exception ex)
    {
        // eat the error
    }
}
finally
{
    if (initialState != ConnectionState.Open)
        conn.Close(); // only close connection if not initially open
}

I have checked this code with the 5.70.152 version of dotConnect for Oracle.
If the problem persits, we recommend you to check the query that is sent to the database with the dbMonitor tool that performs per-component tracing of database events such as commit, rollback, SQL statement execute etc.
Download link: http://www.devart.com/dbmonitor/dbmon3.exe
Documentation: http://www.devart.com/dotconnect/oracle ... nitor.html

Please notify us about the results.

Posted: **Fri 24 Sep 2010 18:16**

It is not just the $. Select * from dual gives a similar systax error "Message = "The query syntax is not valid. Near term '*', line 1, column 9.""

Posted: **Fri 24 Sep 2010 18:17**

These results were obtained with the latest version of the software.

Posted: **Mon 27 Sep 2010 12:25**

Please ignore this last response - I forgot to reimplement the change:

DbConnection conn =
((EntityConnection)(context.Connection)).StoreConnection; //line modified

It works as expected.

Devart Forums

Query Oracle system object containing dollar sign ($)...

Query Oracle system object containing dollar sign ($)...