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

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

Postby 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
welton
 
Posts: 4
Joined: Thu 08 Jul 2010 18:13

Postby 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.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Postby 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

Postby 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

Postby 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.
dschipper
 
Posts: 3
Joined: Thu 23 Sep 2010 18:05
Location: United States


Return to dotConnect for Oracle