Example: read and write LONG RAW with Oracle managed driver

This is a demonstration how a long raw can be written (insert) and read (select) from/to a table with the ODP.NET managed driver.

The example consists of two C# files: insert-from-file.cs and select-to-file.cs.

insert-from-file.cs reads the content of a binary file and inserts it into the table. The name of the binary file is hard coded to Snake_River_(5mb).jpg. I have chosen this file because I wanted a file that is larger than 4 Mega Bytes.

write-to-file.cs reads the content of the written long raw and writes it into the file extracted.bin. Again, this name is hard coded.

create table table_with_long_raw (
    id   integer primary key,
    bin  long raw
);

//
// csc  -r:c:\oracle\18c\ODP.NET\managed\common\Oracle.ManagedDataAccess.dll insert-from-file.cs
//

using System;
using System.Data;
using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;
using System.IO;

class Prg {

   private static void Main() {

      OracleConnection ora = new OracleConnection("user id=rene;password=rene;data source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORA18)))");
      ora.Open();

   //
   // Read file content into byte array:
   //
      FileStream fs = new FileStream("Snake_River_(5mb).jpg", FileMode.Open, FileAccess.Read);
      Console.WriteLine("File size: {0}", fs.Length);
      byte[] data = new byte[fs.Length];
      fs.Read(data,0,System.Convert.ToInt32(fs.Length));
      fs.Close();

   //
   // Bind parameters:
   //
      OracleCommand stmt = new OracleCommand();
      stmt.CommandText = "insert into table_with_long_raw values(:id, :bin)";
      stmt.Connection  =  ora;
      stmt.CommandType =  CommandType.Text;

      stmt.Parameters.Add("id" , OracleDbType.Int32  ).Value = 42;
      stmt.Parameters.Add("bin", OracleDbType.LongRaw).Value = data;

      stmt.ExecuteNonQuery();
   }
}

//
// csc  -r:c:\oracle\18c\ODP.NET\managed\common\Oracle.ManagedDataAccess.dll select-to-file.cs
//

using System;
using System.Data;
using System.IO;
using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;

class Prg {

   static void Main() {

      OracleConnection ora = new OracleConnection("user id=rene;password=rene;data source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORA18)))");
      ora.Open();

      OracleCommand    stmt = new OracleCommand("select bin from table_with_long_raw where id = :id", ora);
      stmt.CommandType      = CommandType.Text;

      stmt.Parameters.Add("id", OracleDbType.Int32).Value = 42;

   //
   // Following line is kind of crucial: it specifies that
   // the entire length of the long raw needs to be read:
   //
      stmt.InitialLONGFetchSize  = -1;

      OracleDataReader rdr = stmt.ExecuteReader();

      rdr.Read();
      OracleBinary bin = rdr.GetOracleBinary(0);
      File.WriteAllBytes("extracted.bin", bin.Value);
   }
}