none
system.data.oracleClient no reply when select a large number of colunms RRS feed

  • Question

  • hi all

         i have a table with 240 columns , the table name eir_detail.

         when i use system.data.OracleClient to query data from this talbe with "select * from eir_detail where id = 1254 ",

         it have no any reply.  i look the query session in the oracle, the session is inactive, and wait for the client message,

         but the client seem to wait the oracle reply, so the client seem to death...

         if i replace the sql string with "select  id  from eir_detail where id = 1254 ", i can receive the reply quickly.

         and i use odp.net substitute for system.data.oracleClient , and use the same sql string "select * from ......",  it run well, and receive the oracle reply quickly.

         connection string is like this:data source = testing;user id = my; password = my;

         help me!!

        thanks !!

    
    
    
    
    
    
    Thursday, May 31, 2012 2:26 PM

All replies

  • Hi Huiny,

    Welcome to the MSDN Forum.

    This is more related to Oracle database.

    Please check the table, is it very large? Does this table have indexes?

    For more information, please try Oracle forum: https://forums.oracle.com/forums/main.jspa?categoryID=84 

    Thank you for your understanding and support.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, June 1, 2012 8:57 AM
    Moderator
  • Hi Huiny,

    which version has the database?

    regards Ellen


    Ich benutze/ I'm using VB2008 & VB2010

    Friday, June 1, 2012 3:53 PM
  • From your explanation, looks like you have a big database from which you are running query, When you use * it fetches whole data, but when you use only id in select statement result set is confined only to the id column from the database, which would have been small in size, It's always advisable to fetch only the fields required.

    Mark Answered, if it solves your question
    Rohit Arora

    Monday, June 4, 2012 7:33 AM
  • below is my test code,

    /*
                 *  1: use factory to create ODP.NET oracle connection 
                 *   when select *  it run block
                 *   when select id it run well
                 */
                //DataTable dtDbClasses = DbProviderFactories.GetFactoryClasses();
                //DbConnection m_connect = null;
                //foreach (DataRow dr in dtDbClasses.Rows)
                //{
                //    if (dr["InvariantName"].ToString() == "Oracle.DataAccess.Client")
                //    {
                //        DbProviderFactory dbFactory = DbProviderFactories.GetFactory(dr);
                //        string cnnstr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1}))(CONNECT_DATA=(SERVICE_NAME={2})));User Id={3};Password={4};";
                //        cnnstr = string.Format(cnnstr, "192.168.5.32", "1521", "testing", "depot", "depot");
                //        m_connect = dbFactory.CreateConnection();
                //        m_connect.ConnectionString = cnnstr;
                //        if (m_connect.State == System.Data.ConnectionState.Closed)
                //        {
                //            m_connect.Open();
                //        }
                //        break;
                //    }
                //}
    
                /*
                 *  2: use factory to create MS oracle connection 
                 *   when select *  it run well
                 *   when select id it run well
                 */
                DataTable dtDbClasses = DbProviderFactories.GetFactoryClasses();
                DbConnection m_connect = null;
                foreach (DataRow dr in dtDbClasses.Rows)
                {
                    if (dr["InvariantName"].ToString() == "System.Data.OracleClient")
                    {
                        DbProviderFactory dbFactory = DbProviderFactories.GetFactory(dr);
                        string cnnstr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1}))(CONNECT_DATA=(SERVICE_NAME={2})));User Id={3};Password={4};";
                        cnnstr = string.Format(cnnstr, "192.168.5.32", "1521", "testing", "depot", "depot");
                        m_connect = dbFactory.CreateConnection();
                        m_connect.ConnectionString = cnnstr;
                        if (m_connect.State == System.Data.ConnectionState.Closed)
                        {
                            m_connect.Open();
                        }
                        break;
                    }
                }
    
                /*
                 * 3. use MS Enterprise Library 5.0 to create connection
                 *  config like this:
                 *  <connectionStrings>
                      <add name="DataAccessDIKsys" 
                           connectionString="Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.5.32)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=testing)));User Id=depot;Password=depot;" 
                           providerName="System.Data.OracleClient"/>
    
                     </connectionStrings>
                 *  when select *  it run block
                 *  when select id it run well
                 */
                //DbConnection m_connect = GetConnection("DataAccessDIKsys");
    
    
    
                /*
                 * 4. create a connection direct 
                 * MS oracle connection and ODP.net oracle connetion both run well
                 */ 
                //string cnnstr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1}))(CONNECT_DATA=(SERVICE_NAME={2})));User Id={3};Password={4};";
                //cnnstr = string.Format(cnnstr, "192.168.5.32", "1521", "testing", "depot", "depot");
                //DbConnection m_connect = new MSOC.OracleConnection(cnnstr);
                //m_connect.Open();
    
                
    
                // select single row, 240 columns
                string SQLString = " select * from eir_detail where id = 1254  ";
    
                DbCommand dbCommand = m_connect.CreateCommand();
                dbCommand.CommandText = SQLString;
                IDataReader reader = dbCommand.ExecuteReader();
    
    
    
                while (reader.Read())
                {
                    
                }
    
                reader.Close();
                m_connect.Close();

    Tuesday, June 5, 2012 9:19 AM
  • Hi Huiny,

    You have installed Oracle.DataAccess.dll on Your system. You can make a test with my sample:

    http://code.msdn.microsoft.com/Oracle-Dataprovider-for-736ad5af

    Make the project specific changes in tnsnames.ora and the connection string.

    I'm unsure whether Oracle has a limit in selecting columns. I have no database with 240 cols and cannot try this.

    I hope this helps

    regards Ellen


    Ich benutze/ I'm using VB2008 & VB2010

    Tuesday, June 5, 2012 10:44 AM
  •  thank you Ellen Ramcke

           i found that the problem is not on the oracle Database,

           i try substitute * with all column name , but is also can run block,

         then, i  reduce the columns one by one, then columns left 64, it run well,

         first, i doubt some columns case the problem, but i remove othe some columns only left 64, it run well too,

         if i add any more one column it run block.

         then, i make the sql : select  1,1,1,1,1,1...... /* 64 number of 1 */ from dual  ,    it run well

            but, when i add the columns to 76 : select  1,1,1,1,1,1...... /* 76 number of 1 */ from dual ,   it run block

       so, i think the problem on the connection object, 

       but, if the connetion object has the property to set the column number ?

    Wednesday, June 6, 2012 8:00 AM
  •  thank you Ellen Ramcke

           i found that the problem is not on the oracle Database,

           i try substitute * with all column name , but is also can run block,

         then, i  reduce the columns one by one, then columns left 64, it run well,

         first, i doubt some columns case the problem, but i remove othe some columns only left 64, it run well too,

         if i add any more one column it run block.

         then, i make the sql : select  1,1,1,1,1,1...... /* 64 number of 1 */ from dual  ,    it run well

            but, when i add the columns to 76 : select  1,1,1,1,1,1...... /* 76 number of 1 */ from dual ,   it run block

       so, i think the problem on the connection object, 

       but, if the connetion object has the property to set the column number ?

    But that is strange. You retrieve no Ora Server message? I see, that You do not use try catch. Try this:

    Dim reader As OracleDataReader 
            Try 
                reader = cmd.ExecuteReader 
            Catch ex As OracleException 
                MsgBox("execute dbAccess2: " & ex.Message & Environment.NewLine & "Aufruf: " & strSQL) 
                Return Nothing 
            End Try 

    Another reason may be, that the server is busy with 240 col and it needs a long time (infinity) for a response.

    Ellen


    Ich benutze/ I'm using VB2008 & VB2010

    Wednesday, June 6, 2012 9:09 AM