2

We have a query that will be executed on a monthly basis and returns data of size 1GB.

Query used here is just a select query with inner joins, no cursor involved.

Currently they are executing this query in Toad and exporting the data from output window as .dat file.

Please note that doing this manually using Toad takes 2 hrs of time.

After that they are changing the header text in .dat file to have meaningful names to share it with our clients.

I want to automate this process by creating an exe that will do this process.

Code snapshot looks like the below

using (OracleConnection conn = new OracleConnection())
{
    conn.ConnectionString = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
    conn.Open();

    using (OracleCommand cmd = new OracleCommand(commandText))
    {
        cmd.Connection = conn;

        using (OracleDataReader dtReader = cmd.ExecuteReader())
        {
            outputContent = new StringBuilder();

            while (dtReader != null && dtReader.Read())
            {
                for (int i = 0; i < dtReader.FieldCount; i++)
                {
                    outputContent.Append(dtReader[i]);
                    outputContent.Append(delimiter);
                }

                outputContent = outputContent.Replace(delimiter, Environment.NewLine, outputContent.Length - 1, 1);
            }
        }
    }
}

outputPath = string.Format(ConfigurationManager.AppSettings["OutputPath"], DateTime.Now.Ticks);
outputStream = new StreamWriter(outputPath, true);

//Export
outputStream.Write(outputContent.ToString());
outputStream.Close();

From the log, it got ot know that, execute reader statement is completed within seconds.

But reading the data from datareader throws "Exception message is ORA-03113: end-of-file on communication channel at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)" after 8 hours of time.

Could anyone please let me know the above approach is good to handle the data of 1GB size? or

Is there any other better way of doing this?

Thanks, Gayathri

PVitt
  • 11,500
  • 5
  • 51
  • 85
Gayathri
  • 21
  • 5

2 Answers2

1

May be you can try

CommandBehavior = SequentialAccess

from MSDN

Use SequentialAccess to retrieve large values and binary data

A sample how to use it

Surjit Samra
  • 4,614
  • 1
  • 26
  • 36
0

You can export data directly from PL/SQL procedure and have a shell file (instead of an exe) that launches it from SqlPlus.

See this question on SO on what to put in the procedure to export data.

Community
  • 1
  • 1
Andrea Colleoni
  • 5,919
  • 3
  • 30
  • 49
  • Thanks for your suggestion.Query has some month value to be replaced in the where condition according to the current month.I am planning to change cmd text accordingly for every month thro code.Header needs to be changed in the output content.All these might need manual interruption with above suggestion. – Gayathri Nov 28 '11 at 09:42
  • Remember you can pass parameters to PlSql from shell (see http://www.dbforums.com/oracle/1214698-procedure-call-through-shell-script.html for an example in unix) and/or run shell or PlSql code from C#. – Andrea Colleoni Nov 28 '11 at 09:53