// read the CLOB into file "cs-parser2.cs"
public static void ReadClob(OracleConnection connection)
{
OracleCommand rcmd = connection.CreateCommand();
rcmd.CommandText = "SELECT CLOB_COLUMN FROM CLOBTEST";
OracleDataReader reader2 = rcmd.ExecuteReader();
if (!reader2.Read())
{
Console.WriteLine("ERROR: RECORD NOT FOUND");
}
Console.WriteLine(" TESTING OracleLob OBJECT 2...");
OracleLob lob2 = reader2.GetOracleLob(0);
Console.WriteLine(" LENGTH: {0}", lob2.Length);
Console.WriteLine(" CHUNK SIZE: {0}", lob2.ChunkSize);
string lobvalue = (string)lob2.Value;
using (StreamWriter sw = new StreamWriter(outfilename)) {
sw.Write(lobvalue);
}
lob2.Close();
reader2.Close();
}
// read the BLOB into file "cs-parser2.cs"
public static void ReadBlob(OracleConnection connection)
{
if (File.Exists(outfilename) == true)
{
Console.WriteLine("Filename already exists: " + outfilename);
return;
}
OracleCommand rcmd = connection.CreateCommand();
rcmd.CommandText = "SELECT BLOB_COLUMN FROM BLOBTEST";
OracleDataReader reader2 = rcmd.ExecuteReader();
if (!reader2.Read())
{
Console.WriteLine("ERROR: RECORD NOT FOUND");
}
Console.WriteLine(" TESTING OracleLob OBJECT 2...");
OracleLob lob2 = reader2.GetOracleLob(0);
Console.WriteLine(" LENGTH: {0}", lob2.Length);
Console.WriteLine(" CHUNK SIZE: {0}", lob2.ChunkSize);
byte[] lobvalue = (byte[])lob2.Value;
if (ByteArrayCompare(bytes1, lobvalue) == true)
{
Console.WriteLine("bytes1 and bytes2 are equal: good");
}
else
{
Console.WriteLine("bytes1 and bytes2 are not equal: bad");
}
FileStream fs = new FileStream(outfilename, FileMode.CreateNew);
BinaryWriter w = new BinaryWriter(fs);
w.Write(lobvalue);
w.Close();
fs.Close();
lob2.Close();
reader2.Close();
}
public static void BLOBTest(OracleConnection connection)
{
Console.WriteLine(" BEGIN TRANSACTION ...");
OracleTransaction transaction = connection.BeginTransaction();
Console.WriteLine(" Drop table BLOBTEST ...");
try
{
OracleCommand cmd2 = connection.CreateCommand();
cmd2.Transaction = transaction;
cmd2.CommandText = "DROP TABLE BLOBTEST";
cmd2.ExecuteNonQuery();
}
catch (OracleException)
{
// ignore if table already exists
}
Console.WriteLine(" CREATE TABLE ...");
OracleCommand create = connection.CreateCommand();
create.Transaction = transaction;
create.CommandText = "CREATE TABLE BLOBTEST (BLOB_COLUMN BLOB)";
create.ExecuteNonQuery();
Console.WriteLine(" INSERT RECORD ...");
OracleCommand insert = connection.CreateCommand();
insert.Transaction = transaction;
insert.CommandText = "INSERT INTO BLOBTEST VALUES (EMPTY_BLOB())";
insert.ExecuteNonQuery();
OracleCommand select = connection.CreateCommand();
select.Transaction = transaction;
select.CommandText = "SELECT BLOB_COLUMN FROM BLOBTEST FOR UPDATE";
Console.WriteLine(" SELECTING A BLOB (Binary Large Object) VALUE FROM BLOBTEST");
OracleDataReader reader = select.ExecuteReader();
if (!reader.Read())
{
Console.WriteLine("ERROR: RECORD NOT FOUND");
}
Console.WriteLine(" TESTING OracleLob OBJECT ...");
OracleLob lob = reader.GetOracleLob(0);
Console.WriteLine(" LENGTH: {0}", lob.Length);
Console.WriteLine(" CHUNK SIZE: {0}", lob.ChunkSize);
//try {
if (File.Exists(infilename) == false)
{
Console.WriteLine("Filename does not exist: " + infilename);
return;
}
FileStream fs = new FileStream(infilename, FileMode.Open, FileAccess.Read);
BinaryReader r = new BinaryReader(fs);
byte[] bytes = null;
int bufferLen = 8192;
bytes = r.ReadBytes(bufferLen);
while (bytes.Length > 0)
{
Console.WriteLine("byte count: " + bytes.Length.ToString());
lob.Write(bytes, 0, bytes.Length);
bytes1 = ByteArrayCombine(bytes1, bytes);
if (bytes.Length < bufferLen)
{
break;
}
bytes = r.ReadBytes(bufferLen);
}
r.Close();
fs.Close();
//}
//catch (Exception e) {
// Console.WriteLine("The file could not be read:");
// Console.WriteLine(e.Message);
//}
lob.Close();
Console.WriteLine(" CLOSING READER...");
reader.Close();
transaction.Commit();
transaction = null;
lob = null;
reader.Dispose();
reader = null;
create = null;
insert = null;
select = null;
}
public static void Main(string[] args)
{
string connectionString =
"Data Source=testdb;" +
"User ID=scott;" +
"Password=tiger;";
OracleConnection connection = null;
connection = new OracleConnection(connectionString);
connection.Open();
Console.WriteLine("Setup test package and data...");
OracleCommand cmddrop = connection.CreateCommand();
cmddrop.CommandText = "DROP TABLE TESTTABLE";
try {
cmddrop.ExecuteNonQuery();
}
catch (OracleException e) {
Console.WriteLine("Ignore this error: " + e.Message);
}
cmddrop.Dispose();
cmddrop = null;
Console.WriteLine("Create table TESTTABLE...");
OracleCommand cmd = connection.CreateCommand();
// create table TESTTABLE
cmd.CommandText =
"create table TESTTABLE (\n" +
" col1 numeric(18,0),\n" +
" col2 varchar(32),\n" +
" col3 date, col4 blob)";
cmd.ExecuteNonQuery();
Console.WriteLine("Insert 3 rows...");
// insert some rows into TESTTABLE
cmd.CommandText =
"insert into TESTTABLE\n" +
"(col1, col2, col3, col4)\n" +
"values(45, 'Mono', sysdate, EMPTY_BLOB())";
cmd.ExecuteNonQuery();
cmd.CommandText =
"insert into TESTTABLE\n" +
"(col1, col2, col3, col4)\n" +
"values(136, 'Fun', sysdate, EMPTY_BLOB())";
cmd.ExecuteNonQuery();
cmd.CommandText =
"insert into TESTTABLE\n" +
"(col1, col2, col3, col4)\n" +
"values(526, 'System.Data.OracleClient', sysdate, EMPTY_BLOB())";
cmd.ExecuteNonQuery();
Console.WriteLine("commit...");
cmd.CommandText = "commit";
cmd.ExecuteNonQuery();
Console.WriteLine("Update blob...");
// update BLOB and CLOB columns
OracleCommand select = connection.CreateCommand();
select.Transaction = connection.BeginTransaction();
select.CommandText = "SELECT col1, col4 FROM testtable FOR UPDATE";
OracleDataReader readerz = select.ExecuteReader();
if (!readerz.Read())
{
Console.WriteLine("ERROR: RECORD NOT FOUND");
}
// update blob_value
Console.WriteLine(" Update BLOB column on table testtable...");
OracleLob blob = readerz.GetOracleLob(1);
byte[] bytes = new byte[6] {
0x31, 0x32, 0x33, 0x34, 0x35, 0x036
};
blob.Write(bytes, 0, bytes.Length);
blob.Close();
readerz.Close();
select.Transaction.Commit();
select.Dispose();
select = null;
cmd.CommandText = "commit";
cmd.ExecuteNonQuery();
Console.WriteLine("Create package...");
// create Oracle package TestTablePkg
cmd.CommandText =
"CREATE OR REPLACE PACKAGE TestTablePkg\n" +
"AS\n" +
" TYPE T_CURSOR IS REF CURSOR;\n"+
"\n" +
" PROCEDURE GetData(tableCursor OUT T_CURSOR);\n"+
"END TestTablePkg;";
cmd.ExecuteNonQuery();
// create Oracle package body for package TestTablePkg
cmd.CommandText =
"CREATE OR REPLACE PACKAGE BODY TestTablePkg AS\n" +
" PROCEDURE GetData(tableCursor OUT T_CURSOR)\n" +
" IS\n" +
" BEGIN\n" +
" OPEN tableCursor FOR\n" +
" SELECT *\n" +
" FROM TestTable;\n" +
" END GetData;\n" +
"END TestTablePkg;";
cmd.ExecuteNonQuery();
cmd.Dispose();
cmd = null;
Console.WriteLine("Set up command and parameters to call stored proc...");
OracleCommand command = new OracleCommand("TestTablePkg.GetData", connection);
command.CommandType = CommandType.StoredProcedure;
OracleParameter parameter = new OracleParameter("tableCursor", OracleType.Cursor);
parameter.Direction = ParameterDirection.Output;
command.Parameters.Add(parameter);
Console.WriteLine("Execute...");
command.ExecuteNonQuery();
Console.WriteLine("Get OracleDataReader for cursor output parameter...");
OracleDataReader reader = (OracleDataReader)parameter.Value;
Console.WriteLine("Read data***...");
int r = 0;
while (reader.Read())
{
Console.WriteLine("Row {0}", r);
for (int f = 0; f < reader.FieldCount; f++)
{
Console.WriteLine("FieldType: " + reader.GetFieldType(f).ToString());
object val = "";
if (f == 3)
{
Console.WriteLine("blob");
//OracleLob lob = reader.GetOracleLob (f);
//val = lob.Value;
val = reader.GetValue(f);
if (((byte[])val).Length == 0)
{
val = "Empty Blob (Not Null)";
}
else
{
val = BitConverter.ToString((byte[])val);
}
}
else
{
val = reader.GetOracleValue(f);
}
Console.WriteLine(" Field {0} Value: {1}", f, val);
}
r++;
}
Console.WriteLine("Rows retrieved: {0}", r);
Console.WriteLine("Clean up...");
reader.Close();
reader = null;
command.Dispose();
command = null;
connection.Close();
connection = null;
}
public static void CLOBTest(OracleConnection connection)
{
Console.WriteLine(" BEGIN TRANSACTION ...");
OracleTransaction transaction = connection.BeginTransaction();
Console.WriteLine(" Drop table CLOBTEST ...");
try {
OracleCommand cmd2 = connection.CreateCommand();
cmd2.Transaction = transaction;
cmd2.CommandText = "DROP TABLE CLOBTEST";
cmd2.ExecuteNonQuery();
}
catch (OracleException oe1) {
// ignore if table already exists
}
Console.WriteLine(" CREATE TABLE ...");
OracleCommand create = connection.CreateCommand();
create.Transaction = transaction;
create.CommandText = "CREATE TABLE CLOBTEST (CLOB_COLUMN CLOB)";
create.ExecuteNonQuery();
Console.WriteLine(" INSERT RECORD ...");
OracleCommand insert = connection.CreateCommand();
insert.Transaction = transaction;
insert.CommandText = "INSERT INTO CLOBTEST VALUES (EMPTY_CLOB())";
insert.ExecuteNonQuery();
OracleCommand select = connection.CreateCommand();
select.Transaction = transaction;
select.CommandText = "SELECT CLOB_COLUMN FROM CLOBTEST FOR UPDATE";
Console.WriteLine(" SELECTING A CLOB (CHARACTER) VALUE FROM CLOBTEST");
OracleDataReader reader = select.ExecuteReader();
if (!reader.Read())
{
Console.WriteLine("ERROR: RECORD NOT FOUND");
}
Console.WriteLine(" TESTING OracleLob OBJECT ...");
OracleLob lob = reader.GetOracleLob(0);
Console.WriteLine(" LENGTH: {0}", lob.Length);
Console.WriteLine(" CHUNK SIZE: {0}", lob.ChunkSize);
UnicodeEncoding encoding = new UnicodeEncoding();
try {
// read file "cs-parser.cs" into the oracle clob
using (StreamReader sr = new StreamReader(infilename)) {
string sbuff = sr.ReadToEnd();
byte[] evalue = encoding.GetBytes(sbuff);
lob.Write(evalue, 0, evalue.Length);
}
}
catch (Exception e) {
Console.WriteLine("The file could not be read:");
Console.WriteLine(e.Message);
}
lob.Close();
Console.WriteLine(" CLOSING READER...");
reader.Close();
transaction.Commit();
}
