Oracle error ORA-12571 encountered for Select Statement

1k Views Asked by At

I'm trying to read table data from ORACLE with Parameter binding in c#. While executing the dataAdapter I'm getting ORA-12571 exception.

Below is my code for reading multiple data at the same time.

public DataTable SelectFromServer(string qualifiedDBName, DataTable dataTable)        {
            try
            {
                if (this.Con.State == ConnectionState.Closed)
                {
                    this.OpenConnection();
                }
                DataTable resultTable = new DataTable();
                dataTable.TableName = qualifiedDBName;
                DbProviderFactory factory = DbProviderFactories.GetFactory(this.Con);
                using (DbCommand command = factory.CreateCommand())
                {
                   
                    command.Connection = this.Con;
                    command.CommandText = this.GenerateSqlToSelect(factory,command,dataTable);
                    DbDataAdapter adapter = factory.CreateDataAdapter();
                    adapter.SelectCommand = command;
                    adapter.Fill(resultTable);
                    return resultTable;
                }
            }
            catch (Exception exc)
            {
                throw exc;
            }
        }

Below is the method where I used to generate Select Query

  private string GenerateSqlToSelect(DbProviderFactory factory, DbCommand command, DataTable table)
        {

            //var values1 = new List<string>();
            var SelectQuery = new StringBuilder();
            var data = table.ToArray();
            var syntax = new OracleSyntax();

            command.GetType().GetProperty("ArrayBindCount").SetValue(command, table.Rows.Count, null);
         

            for (var i = 0; i < table.Columns.Count; i++)
            {
                var names = new StringBuilder();
                var values = new StringBuilder();
                var column = table.Columns[i];

                OracleParameter parameter = new OracleParameter();
                parameter.ParameterName = column.ColumnName;
                parameter.Direction = ParameterDirection.Input;
                parameter.DbType = column.DataType.GetDbType();
                parameter.Value = data[i];
                parameter.ArrayBindSize = GetDataLength(data[i]);
                if (SelectQuery.Length > 0)
                {
                    SelectQuery.Append(" and ");
                }
                names.AppendFormat("{0}", column.ColumnName);
                values.AppendFormat("{0}{1}", syntax.ParameterPrefix, column.ColumnName);
                SelectQuery.AppendFormat("{0} = {1}", names, values);
                command.Parameters.Add(parameter);
            }

            string operationString = "SELECT * FROM";
            string sqlQuery = string.Format("{0} {1} WHERE {2}", operationString,  this.FormatByQuote(syntax,table.TableName), SelectQuery);
            return sqlQuery;
        }

private int[] GetDataLength(object[] objs)
        {
            List<int> dataLengthIterator = new List<int>();
            foreach (object obj in objs)
            {
                dataLengthIterator.Add(obj.ToString().Length);
            }
            return dataLengthIterator.ToArray();
        }

this.FormatByQuote() method is nothing but getting a quoted TableName.

Output of GenerateSqlToSelect() method is

SELECT * FROM "CUSTOMER_MASTER" WHERE USER_ID= :USER_ID and LINE_NUMBER = :LINE_NUMBER

The Query is generating based upon the DataTable input

Tried multiple workarounds but could not get the reason why the exception is occurring.

0

There are 0 best solutions below