Issues with ORA-12571 and DISTINCT

[aportela]

Hi,

In my company we purchased oracle dev art component for .net and today we found some issues with a select query.

I could attach the query but I think that without the big table struct /data and code example this won't help. I will try to resume in a big query for a report, that returns ~5 rows with ~25 cols. The query has a DISTINCT for all the colums.

When we execute the query in sqldeveloper, the query works fine and returns the expected data, but when running in net framework (v2) we catch an exception from Oracle (ORA-12571) and next queries return
an oracle data access general error. If we remove the DISTINCT from the query, works fine in both enviroments (sqdeveloper and net framework)

Any idea ?

[Shalex]

I cannot reproduce the problem at the moment. Please give us the following information:
1) the version of your dotConnect for Oracle (x.xx.xxx). You can find it via the Tools > Oracle > About menu of Visual Studio;
2) the version of your Oracle Server;
3) the mode of dotConnect for Oracle (Direct or OCI). If you are using Oracle Client, tell us its version;
4) please send us or post here your test code with the DDL/DML script to reproduce the issue.

Also please check the SQL that is sent to your database by our provider. You can do that with dbMonitor.
Download link: http://www.devart.com/dbmonitor/dbmon3.exe
Documentation: http://www.devart.com/dotconnect/oracle ... nitor.html

[aportela]

- dotConnect version is 5.20.33.0
- Oracle server is v10g
- We are using direct connection

Our code is splitted in a lot of classes, but I try to explain the normal flow:

We have a class for database abastraction (open connection, execute queries...) and we instance an object that is shared for all objects that need to use the database.

Init of this object is done in a few steps:

1.- A webservice is requesting some data, so we identify database provider (firebird, sqlserver, oracle), next get the connection string.
2.- Get the required objects for this provider and initialize with the help of a framework factory:

Code: Select all

        public DbProviderFactory ProviderFactory
        {
            get
            {
                if (this._ProviderFactory == null)
                {
                    string ProviderInvariantName = null;
                    if (this._ProviderType == DataProvider.DPMsSqlServer)
                    {
                        ProviderInvariantName = "System.Data.SqlClient";
                    }
                    else if (this._ProviderType == DataProvider.DPOracle)
                    {
                        ProviderInvariantName = "Oracle.DataAccess.Client";
                    }
                    else if (this._ProviderType == DataProvider.DPOracleNet)
                    {
                        ProviderInvariantName = string.Empty;
                    }
                    else if (this._ProviderType == DataProvider.DPFirebird)
                    {
                        ProviderInvariantName = "FirebirdSql.Data.FirebirdClient";
                    }
                    else if (this._ProviderType == DataProvider.DPDevartOracle)
                    {
                        ProviderInvariantName = "Devart.Data.Oracle";
                    }
                    if (!string.IsNullOrEmpty (ProviderInvariantName))
                    {
                        this._ProviderFactory = System.Data.Common.DbProviderFactories.GetFactory (ProviderInvariantName);
                    }
                    
                }
                return (this._ProviderFactory);
            }
        }

        private DbConnection GetFactoryConnection ()
        {
            if (UseNetFrameworkFactory == true)
            {
                return (ProviderFactory.CreateConnection ());
            }
            else
            {
                if (this._ProviderType == DataProvider.DPNone)
                {
                    return (null);
                }
#if ORACLE
                else if (this._ProviderType == DataProvider.DPDevartOracle)
                {
                    return (new Devart.Data.Oracle.OracleConnection ());
                }
                else if (this._ProviderType == DataProvider.DPOracle)
                {
                    return (null);
                }
                else if (this._ProviderType == DataProvider.DPOracleNet)
                {
                    return (null);
                }
#elif FIREBIRD
                else if (this._ProviderType == DataProvider.DPFirebird)
                {
                    return (new FirebirdSql.Data.FirebirdClient.FbConnection ());
                }
#elif SQLSERVER
                else if (this._ProviderType == DataProvider.DPMsSqlServer)
                {
                    return (new System.Data.SqlClient.SqlConnection ());
                }
#endif
                else if (this._ProviderType == DataProvider.DPODBC)
                {
                    return (new System.Data.Odbc.OdbcConnection ());
                }
                else if (this._ProviderType == DataProvider.DPOleDB)
                {
                    return (new System.Data.OleDb.OleDbConnection ());
                }
                else
                {
                    return (null);
                }
            }
        }

.. same for Devart.Data.Oracle.OracleCommand, transaction and others...

3.- With all required database objects created (connection, transaction, command...), connection is opened.

Them, we follow the webservice call and go a business layer to a data acess layer. A sql query is generated and executed as:

Code: Select all

cmd.CommandType = CommandType.Text;
cmd.CommandText = Sql;
cmd.ExecuteReader(CommandBehavior.Default);

cmd is created previously from Devart.Data.Oracle.OracleCommand on main init object (step 2)

Bug is catched on cmd.ExecuteReader(CommandBehavior.Default);

We can't post here the DDL script (we are talking of a big closed-source application with ~110 tables)

In the next days, we will try to test the queries sent with dbmonitor and if we found relevant data we contact again...

[Shalex]

Please try to localize the issue, create a small separate project, send it to us with the DDL/DML script so that we can reproduce the problem in our evironment.

[Shalex]

Also please try the latest version of dotConnect for Oracle (5.70.180). Does the problem persist with this version? I can reproduce the issue using a simple DISTINCT query neither with 5.70.180, nor with 5.20.33.