1 Reply Latest reply: Feb 7, 2013 6:15 AM by Tridus RSS

    Application went to hung state when retriving clob data

    kn*374586*74
      Hi

      I have written a sample to read data from oracle database and write to a file. I am able to execute fine in my development environment. But in my customer environment it reads few records and goes to hung state.

      The only difference between two environments was oracle was in different OS. my oracle was in windows & customer was in Linux. Would this make any difference.

      Could someone please help to run the sample in customer environment.

      Here is the sample code :


      using System;
      using System.Collections.Generic;
      using System.Text;
      using System.Data.SqlClient;
      using System.Runtime.InteropServices;
      using System.Data.OleDb;
      using System.IO;
      using System.Xml;
      namespace ADOSample
      {
      class Program
      {
      static void Main(string[] args)
      {
      String ConnectionString = @"Provider=OraOLEDB.Oracle;Data Source=ORCL;User Id=scott;Password=tiger;";
      String TableName= "Dummy";
      OleDbConnection oleDBConn = new OleDbConnection(ConnectionString);
      int counter = 0;
      counter = counter + 1;
      string extPath = Path.Combine(Environment.CurrentDirectory, "Test_" + ".txt");
      StreamWriter sw = new StreamWriter(extPath);
      sw.AutoFlush = true;
      OleDbCommand cmd = new OleDbCommand();
      try
      {
      oleDBConn.Open();
      cmd = new OleDbCommand(TableName, oleDBConn);
      OleDbDataReader oleDataReader= cmd.ExecuteReader();
      while (oleDataReader.Read())
      {
      for (int i = 0; i < oleDataReader.FieldCount; i++)
      {
      if (oleDataReader.GetFieldType(i).Name.ToLower().ToString() == "decimal")
      {
      sw.Write(oleDataReader.GetValue(i).ToString());
      sw.Flush();

      if (sw.BaseStream.Length >= ExtractFileSize) //104857600 - 100 MB
      {
      sw.Close();
      counter = counter + 1;
      extPath = Path.Combine(Environment.CurrentDirectory, "Test_" + ".txt");
      sw = new StreamWriter(extPath);
      sw.AutoFlush = true;
      }
      }
      else
      {

      int bufLen = 4096;
      byte[] buffer = new byte[4096];

      if (oleDataReader.GetValue(i).ToString().Length > 4096)
      {
      bufLen = 4096;
      }
      else
      {
      bufLen = oleDataReader.GetValue(i).ToString().Length;
      }
      int startidx = 0;
      //StringBuilder sb = new StringBuilder(bufLen);
      char[] buf = new Char[bufLen];
      long clob = oleDataReader.GetChars(i, startidx, buf, 0, bufLen);
      if (clob < 4096)
      {
      sw.Write(buf);
      sw.Flush();
      buf = null;
      if (sw.BaseStream.Length >= ExtractFileSize) //104857600 - 100 MB
      {
      sw.Close();
      counter = counter + 1;
      extPath = Path.Combine(Environment.CurrentDirectory, "Test_" + ".txt");

      sw = new StreamWriter(extPath);
      sw.AutoFlush = true;
      }
      }
      while (clob == 4096)
      {
      sw.Write(buf);
      sw.Flush();
      buf = null;
      if (sw.BaseStream.Length >= ExtractFileSize) //104857600 - 100 MB
      {
      sw.Close();
      counter = counter + 1;
      extPath = Path.Combine(Environment.CurrentDirectory, "Test_" + ".txt");
      sw = new StreamWriter(extPath);
      sw.AutoFlush = true;
      }
      // Reposition the startindex
      startidx += bufLen;
      buf = new Char[bufLen];
      // Exception here, because the DataReader seems to have progressed to the next column.
      clob = oleDataReader.GetChars(i, startidx, buf, 0, bufLen);
      if (clob != 4096)
      {
      sw.Write(buf);
      sw.Flush();
      buf = null;
      if (sw.BaseStream.Length >= ExtractFileSize) //104857600 - 100 MB
      {
      sw.Close();
      counter = counter + 1;
      extPath = Path.Combine(Environment.CurrentDirectory, "Test_" + ".txt");
      sw = new StreamWriter(extPath);
      sw.AutoFlush = true;
      }
      }
      }
      }
      if (i < oleDataReader.FieldCount -1)
      {
      sw.Write(",");
      }
      }
      sw.WriteLine("");
      }
      sw.Close();
      oleDataReader.Close();
      cmd.Dispose();
      oleDBConn.Close();
      }
      catch (Exception ex)
      {
      Console.WriteLine("Can not open connection ! ");
      }
      }
      }
      }

      Edited by: kn**** on Feb 7, 2013 3:14 AM
        • 1. Re: Application went to hung state when retriving clob data
          Tridus
          Hi. This looks like C# code, so is there a reason you need to use OLE DB instead of the .net provided classes in ODP.net?

          I found this in the samples section of the ODP documentation. If you add the text file writes, it should do largely the same thing that your code is doing now, only with the .net classes. I expect it'll work a bit more smoothly for you.
           /// <summary>
              /// The main entry point for the application.
              /// </summary>
              static void Main(string[] args)
              {
                // Connect
                string constr = "User Id=scott;Password=tiger;Data Source=oracle";
                OracleConnection con = Connect(constr);
           
                // Set the command
                OracleCommand cmd = new OracleCommand(
                  "select story from multimedia_tab where thekey = 1");
                cmd.Connection = con;
                cmd.CommandType = CommandType.Text; 
          
                OracleDataReader reader;
                try
                {
                  // Create DataReader
                  reader = cmd.ExecuteReader();
          
                  // Read the first row 
                  while(reader.Read())
                  {
                    // Set the OracleClob object to the CLOB selected
                    OracleClob clob = reader.GetOracleClob(0);
          
                    // Read data all data
                    Byte [] clob_data = new Byte[120];
                    Int64 amountRead = 0;
                    int readSize = 8;
                    Int64 totalRead = 0;
          
                    do
                    {
                      amountRead = clob.Read(clob_data, (int)totalRead, readSize);
                      Console.WriteLine("Actual read: {0} bytes", amountRead);
                      totalRead += amountRead;
                    } while(amountRead > 0);
                      
                    Console.WriteLine("Total number of bytes read: {0}", totalRead);
          
                    // Dispose OracleClob object
                    clob.Dispose();
                  }
                }
                catch (Exception e)
                {
                  Console.WriteLine("Exception:" + e.Message);
                }
                finally
                {
                  // Dispose OracleCommand object
                  cmd.Dispose();
          
                  // Close and Dispose OracleConnection object
                  con.Close();
                  con.Dispose();
                }
                    }
          
              /// <summary>
              /// Wrapper for Opening a new Connection
              /// </summary>
              /// <param name="connectStr"></param>
              /// <returns></returns>
              public static OracleConnection Connect(string connectStr)
              {
                OracleConnection con = new OracleConnection(connectStr);
                try
                {
                  con.Open();
                }
                catch (Exception e)
                {
                  Console.WriteLine("Error: {0}", e.Message);
                }
                return con;
              }