This discussion is archived
1 Reply Latest reply: Feb 7, 2013 4:15 AM by Tridus RSS

Application went to hung state when retriving clob data

374586 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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;
        }

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points