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

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
welton
Posts: 4
Joined: Thu 08 Jul 2010 18:13

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

Post by welton » Tue 21 Sep 2010 19:09

I am trying to run the following query:

Code: Select all

"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

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » 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.

dschipper
Posts: 3
Joined: Thu 23 Sep 2010 18:05
Location: United States

Post by dschipper » 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.""

dschipper
Posts: 3
Joined: Thu 23 Sep 2010 18:05
Location: United States

Post by dschipper » Fri 24 Sep 2010 18:17

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

dschipper
Posts: 3
Joined: Thu 23 Sep 2010 18:05
Location: United States

Post by dschipper » 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.

Post Reply