context: // 
  // TestOracleClient.cs - Tests Sytem.Data.OracleClient
  //                       data provider in Mono.
  //  
  // Part of managed C#/.NET library System.Data.OracleClient.dll
  //
  // Part of the Mono class libraries at
  // mcs/class/System.Data.OracleClient/System.Data.OracleClient.OCI
  //
  // Tests:
  //     Assembly: System.Data.OracleClient.dll
  //     Namespace: System.Data.OracleClient
  // 
  // To Compile:
  // mcs TestOracleClient.cs /r:System.Data.dll /r:System.Data.OracleClient.dll /nowarn:0168
  //
  // Author: 
  //     Daniel Morgan <monodanmorg@yahoo.com>
  //         
  // Copyright (C) Daniel Morgan, 2002, 2004-2005, 2008
  // 
  
  using System;
  using System.IO;
  using System.Runtime.InteropServices;
  using System.Data;
  using System.Data.OracleClient;
  using System.Text;
  using System.Threading;
  
  namespace Test.OracleClient
  {
  	public class OracleTest
  	{
  		private static Thread t = null;
  		private static string conStr;
  		public static readonly int MAX_CONNECTIONS = 30; // max connections default to 100, but I will set to 30.
  
  		public OracleTest() 
  		{
  
  		}
  
  		static void MonoTest(OracleConnection con)  
  		{
  			Console.WriteLine ("  Drop table MONO_ORACLE_TEST ...");
  			try {
  				OracleCommand cmd2 = con.CreateCommand ();
  				cmd2.CommandText = "DROP TABLE MONO_ORACLE_TEST";
  				cmd2.ExecuteNonQuery ();
  			}
  			catch (OracleException) {
  				// ignore if table already exists
  			}
  
  			OracleCommand cmd = null;
  
  			Console.WriteLine("  Creating table MONO_ORACLE_TEST...");
  			cmd = new OracleCommand();
  			cmd.Connection = con;
  			cmd.CommandText = "CREATE TABLE MONO_ORACLE_TEST ( " +
  				" varchar2_value VarChar2(32),  " +
  				" long_value long, " +
  				" number_whole_value Number(18), " +
  				" number_scaled_value Number(18,2), " +
   				" number_integer_value Integer, " +
   				" float_value Float, " +
   				" date_value Date, " +
   				" char_value Char(32), " +
   				" clob_value Clob, " +
   				" blob_value Blob, " +
  				" clob_empty_value Clob, " +
  				" blob_empty_value Blob, " +
  				" varchar2_null_value VarChar2(32),  " +
  				" number_whole_null_value Number(18), " +
  				" number_scaled_null_value Number(18,2), " +
  				" number_integer_null_value Integer, " +
  				" float_null_value Float, " +
  				" date_null_value Date, " +
  				" char_null_value Char(32), " +
  				" clob_null_value Clob, " +
  				" blob_null_value Blob " +
  				")";
  
  			cmd.ExecuteNonQuery();
  
  			Console.WriteLine("  Begin Trans for table MONO_ORACLE_TEST...");
  			OracleTransaction trans = con.BeginTransaction ();
  
  			Console.WriteLine("  Inserting value into MONO_ORACLE_TEST...");
  			cmd = new OracleCommand();
  			cmd.Connection = con;
  			cmd.Transaction = trans;
  			cmd.CommandText = "INSERT INTO mono_oracle_test " +
   				" ( varchar2_value,  " +
  				"  long_value, " +
   				"  number_whole_value, " +
    				"  number_scaled_value, " +
    				"  number_integer_value, " +
    				"  float_value, " +
    				"  date_value, " +
    				"  char_value, " +
    				"  clob_value, " +
    				"  blob_value, " +
  				"  clob_empty_value, " +
  				"  blob_empty_value " +
  				") " +
   				" VALUES( " +
    				"  'Mono', " +
  				"  'This is a LONG column', " +
    				"  123, " +
    				"  456.78, " +
    				"  8765, " +
    				"  235.2, " +
    				"  TO_DATE( '2004-12-31', 'YYYY-MM-DD' ), " +
    				"  'US', " +
    				"  EMPTY_CLOB(), " +
    				"  EMPTY_BLOB()," +
  				"  EMPTY_CLOB(), " +
  				"  EMPTY_BLOB()" +
  				")";
  
  			cmd.ExecuteNonQuery();
  
  			Console.WriteLine("  Select/Update CLOB columns on table MONO_ORACLE_TEST...");
  
  			// update BLOB and CLOB columns
  			OracleCommand select = con.CreateCommand ();
  			select.Transaction = trans;
  			select.CommandText = "SELECT CLOB_VALUE, BLOB_VALUE FROM MONO_ORACLE_TEST FOR UPDATE";
  			OracleDataReader reader = select.ExecuteReader ();
  			if (!reader.Read ())
  				Console.WriteLine ("ERROR: RECORD NOT FOUND");
  			// update clob_value
  			Console.WriteLine("     Update CLOB column on table MONO_ORACLE_TEST...");
  			OracleLob clob = reader.GetOracleLob (0);
  			byte[] bytes = null;
  			UnicodeEncoding encoding = new UnicodeEncoding ();
  			bytes = encoding.GetBytes ("Mono is fun!");
  			clob.Write (bytes, 0, bytes.Length);
  			clob.Close ();
  			// update blob_value
  			Console.WriteLine("     Update BLOB column on table MONO_ORACLE_TEST...");
  			OracleLob blob = reader.GetOracleLob (1);
  			bytes = new byte[6] { 0x31, 0x32, 0x33, 0x34, 0x35, 0x036 };
  			blob.Write (bytes, 0, bytes.Length);
  			blob.Close ();
  			
  			Console.WriteLine("  Commit trans for table MONO_ORACLE_TEST...");
  			trans.Commit ();
  
  			// OracleCommand.ExecuteReader of MONO_ORACLE_TEST table
  			Console.WriteLine("  Read simple test for table MONO_ORACLE_TEST...");
  			ReadSimpleTest(con, "SELECT * FROM MONO_ORACLE_TEST");
  
  			// OracleCommand.ExecuteScalar
  			Console.WriteLine(" -ExecuteScalar tests...");
  			string varchar2_value = (string) ReadScalar (con,"SELECT MAX(varchar2_value) FROM MONO_ORACLE_TEST");
  			Console.WriteLine("     String Value: " + varchar2_value);
  
  			Console.WriteLine("  Read Scalar: number_whole_value");
  			decimal number_whole_value = (decimal) 
  			ReadScalar (con,"SELECT MAX(number_whole_value) FROM MONO_ORACLE_TEST");
  			Console.WriteLine("     Int32 Value: " + number_whole_value.ToString());
  
  			Console.WriteLine("  Read Scalar: number_scaled_value");
  			decimal number_scaled_value = (decimal) 
  			ReadScalar (con,"SELECT number_scaled_value FROM MONO_ORACLE_TEST");
  			Console.WriteLine("     Decimal Value: " + number_scaled_value.ToString());
  		
  			Console.WriteLine("  Read Scalar: date_value");
  			DateTime date_value = (DateTime) 
  			ReadScalar (con,"SELECT date_value FROM MONO_ORACLE_TEST");
  			Console.WriteLine("     DateTime Value: " + date_value.ToString());
  			
  			Console.WriteLine("  Read Scalar: clob_value");
  			string clob_value = (string) 
  			ReadScalar (con,"SELECT clob_value FROM MONO_ORACLE_TEST");
  			Console.WriteLine("     CLOB Value: " + clob_value);
  
  			Console.WriteLine("  Read Scalar: blob_value");
  			byte[] blob_value = (byte[]) 
  			ReadScalar (con,"SELECT blob_value FROM MONO_ORACLE_TEST");
  			string sblob_value = GetHexString (blob_value);
  			Console.WriteLine("     BLOB Value: " + sblob_value);
  			
  			// OracleCommand.ExecuteOracleScalar
  			Console.WriteLine(" -ExecuteOracleScalar tests...");
  			Console.WriteLine("  Read Oracle Scalar: varchar2_value");
  			ReadOracleScalar (con,"SELECT MAX(varchar2_value) FROM MONO_ORACLE_TEST");
  
  			Console.WriteLine("  Read Oracle Scalar: number_whole_value");
  			ReadOracleScalar (con,"SELECT MAX(number_whole_value) FROM MONO_ORACLE_TEST");
  
  			Console.WriteLine("  Read Oracle Scalar: number_scaled_value");
  			ReadOracleScalar (con,"SELECT number_scaled_value FROM MONO_ORACLE_TEST");
  		
  			Console.WriteLine("  Read Oracle Scalar: date_value");
  			ReadOracleScalar (con,"SELECT date_value FROM MONO_ORACLE_TEST");
  			
  			Console.WriteLine("  Read Oracle Scalar: clob_value");
  			ReadOracleScalar (con,"SELECT clob_value FROM MONO_ORACLE_TEST");
  
  			Console.WriteLine("  Read Oracle Scalar: blob_value");
  			ReadOracleScalar (con,"SELECT blob_value FROM MONO_ORACLE_TEST");
  		}
  
  		static object ReadScalar (OracleConnection con, string selectSql) 
  		{
  			OracleCommand cmd = null;
  			cmd = con.CreateCommand();
  			cmd.CommandText = selectSql;
  
  			object o = cmd.ExecuteScalar ();
  
  			string dataType = o.GetType ().ToString ();
  			Console.WriteLine ("       DataType: " + dataType);
  			return o;
  		}
  
  		static void ReadOracleScalar (OracleConnection con, string selectSql) 
  		{
  			OracleCommand cmd = null;
  			cmd = con.CreateCommand();
  			cmd.CommandText = selectSql;
  
  			object o = cmd.ExecuteOracleScalar ();
  
  			string dataType = o.GetType ().ToString ();
  			Console.WriteLine ("       DataType: " + dataType);
  			if (dataType.Equals("System.Data.OracleClient.OracleLob"))
  				o = ((OracleLob) o).Value;
  			if (o.GetType ().ToString ().Equals ("System.Byte[]"))
  				o = GetHexString ((byte[])o);
  			
  			Console.WriteLine ("          Value: " + o.ToString ());
  		}
  
  		static void ReadSimpleTest(OracleConnection con, string selectSql) 
  		{
  			OracleCommand cmd = null;
  			OracleDataReader reader = null;
  		
  			cmd = con.CreateCommand();
  			cmd.CommandText = selectSql;
  			reader = cmd.ExecuteReader();
  		
  			Console.WriteLine("  Results...");
  			Console.WriteLine("    Schema");
  			DataTable table;
  			table = reader.GetSchemaTable();
  			for(int c = 0; c < reader.FieldCount; c++) {
  				Console.WriteLine("  Column " + c.ToString());
  				DataRow row = table.Rows[c];
  			
  				string strColumnName = row["ColumnName"].ToString();
  				string strBaseColumnName = row["BaseColumnName"].ToString();
  				string strColumnSize = row["ColumnSize"].ToString();
  				string strNumericScale = row["NumericScale"].ToString();
  				string strNumericPrecision = row["NumericPrecision"].ToString();
  				string strDataType = row["DataType"].ToString();
  
  				Console.WriteLine("      ColumnName: " + strColumnName);
  				Console.WriteLine("      BaseColumnName: " + strBaseColumnName);
  				Console.WriteLine("      ColumnSize: " + strColumnSize);
  				Console.WriteLine("      NumericScale: " + strNumericScale);
  				Console.WriteLine("      NumericPrecision: " + strNumericPrecision);
  				Console.WriteLine("      DataType: " + strDataType);
  			}
  
  			int r = 0;
  			Console.WriteLine ("    Data");
  			while (reader.Read ()) {
  				r++;
  				Console.WriteLine ("       Row: " + r.ToString ());
  				for (int f = 0; f < reader.FieldCount; f++) {
  					string sname = "";
  					object ovalue = "";
  					string svalue = "";
  					string sDataType = "";
  					string sFieldType = "";
  					string sDataTypeName = "";
  					string sOraDataType = "";
  
  					sname = reader.GetName (f);
  
  					if (reader.IsDBNull (f)) {
  						ovalue = DBNull.Value;
  						svalue = "";
  						sDataType = "DBNull.Value";
  						sOraDataType = "DBNull.Value";
  					}
  					else {
  						//ovalue = reader.GetValue (f);
  						ovalue = reader.GetOracleValue (f);
  						object oravalue = null;
  					
  						sDataType = ovalue.GetType ().ToString ();
  						switch (sDataType) {
  						case "System.Data.OracleClient.OracleString":
  							oravalue = ((OracleString) ovalue).Value;
  							break;
  						case "System.Data.OracleClient.OracleNumber":
  							oravalue = ((OracleNumber) ovalue).Value;
  							break;
  						case "System.Data.OracleClient.OracleLob":
  							OracleLob lob = (OracleLob) ovalue;
  							oravalue = lob.Value;
  							lob.Close ();
  							break;
  						case "System.Data.OracleClient.OracleDateTime":
  							oravalue = ((OracleDateTime) ovalue).Value;
  							break;
  						case "System.Byte[]":
  							oravalue = GetHexString((byte[])ovalue);
  							break;
  						case "System.Decimal":
  							//Console.WriteLine("           *** Get Decimal, Int16, Int32, Int64, Float, Double, ...");
  							decimal dec = reader.GetDecimal (f);
  							//Console.WriteLine("             GetDecimal: " + dec.ToString ());
  
  							oravalue = (object) dec;
  /*
  							try {
  								reader.GetInt16 (f);
  							} catch (NotSupportedException e) {
  								Console.WriteLine ("            ** Expected exception caught for GetInt16: NotSupportedException: " + e.Message);
  							}
  
  							try {
  								long lng = reader.GetInt64 (f);
  								Console.WriteLine("           GetInt64: " + lng.ToString ());
  								int n = reader.GetInt32 (f);
  								Console.WriteLine("           GetInt32: " + n.ToString ());
  								float flt = reader.GetFloat (f);
  								Console.WriteLine("           GetFloat: " + flt.ToString ());
  								double dbl = reader.GetDouble (f);
  								Console.WriteLine("           GetDouble: " + dbl.ToString ());
  							} catch (OverflowException oe1) {
  								Console.WriteLine ("            ** Overflow exception for numbers to big or too small: " + oe1.Message);
  								}
  							*/
  							break;
  						default:
  							oravalue = ovalue.ToString ();
  
  							break;
  						}
  					
  						sOraDataType = oravalue.GetType ().ToString ();
  						if (sOraDataType.Equals ("System.Byte[]")) 
  							svalue = GetHexString ((byte[]) oravalue);
  						else
  							svalue = oravalue.ToString();
  						
  					}
  					sFieldType = reader.GetFieldType(f).ToString();
  					sDataTypeName = reader.GetDataTypeName(f);
  
  					Console.WriteLine("           Field: " + f.ToString());
  					Console.WriteLine("               Name: " + sname);
  					Console.WriteLine("               Value: " + svalue);
  					Console.WriteLine("               Oracle Data Type: " + sOraDataType);
  					Console.WriteLine("               Data Type: " + sDataType);
  					Console.WriteLine("               Field Type: " + sFieldType);
  					Console.WriteLine("               Data Type Name: " + sDataTypeName);
  				}
  			}
  			if(r == 0)
  				Console.WriteLine("  No data returned.");
  		}
  		
  		static void DataAdapterTest (OracleConnection connection)
  		{
  			Console.WriteLine("  Create select command...");
  			OracleCommand command = connection.CreateCommand ();
  			command.CommandText = "SELECT * FROM SCOTT.EMP";
  
  			Console.WriteLine("  Create data adapter...");
  			OracleDataAdapter adapter = new OracleDataAdapter (command);
  
  			Console.WriteLine("  Create DataSet...");
  			DataSet dataSet = new DataSet ("EMP");
  
  			Console.WriteLine("  Fill DataSet via data adapter...");
  			adapter.Fill (dataSet);
  
  			Console.WriteLine("  Get DataTable...");
  			DataTable table = dataSet.Tables [0];
  
  			Console.WriteLine("  Display each row...");
  			int rowCount = 0;
  			foreach (DataRow row in table.Rows) {
  				Console.WriteLine ("    row {0}", rowCount + 1);
  				for (int i = 0; i < table.Columns.Count; i += 1) {
  					Console.WriteLine ("      {0}: {1}", table.Columns [i].ColumnName, row [i]);
  				}
  				Console.WriteLine ();
  				rowCount += 1;
  			}
  		}
  
  		public static void DataAdapterTest2 (OracleConnection con) 
  		{
  			DataAdapterTest2_Setup (con);
  			ReadSimpleTest (con, "SELECT * FROM mono_adapter_test");
  		
  			GetMetaData (con, "SELECT * FROM mono_adapter_test");
  
  			DataAdapterTest2_Insert (con);
  			ReadSimpleTest (con, "SELECT * FROM mono_adapter_test");
  		
  			DataAdapterTest2_Update (con);
  			ReadSimpleTest (con, "SELECT * FROM mono_adapter_test");
  
  			DataAdapterTest2_Delete (con);
  			ReadSimpleTest (con, "SELECT * FROM mono_adapter_test");
  		}
  
  		public static void GetMetaData (OracleConnection con, string sql) 
  		{
  			OracleCommand cmd = null;
  			OracleDataReader rdr = null;
  		
  			cmd = con.CreateCommand();
  			cmd.CommandText = sql;
  
  			Console.WriteLine("Read Schema With KeyInfo");
  			rdr = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly);
  		
  			DataTable dt;
  			dt = rdr.GetSchemaTable();
  			foreach (DataRow schemaRow in dt.Rows) {
  				foreach (DataColumn schemaCol in dt.Columns) {
  					Console.WriteLine(schemaCol.ColumnName + 
  						" = " + 
  						schemaRow[schemaCol]);
  					Console.WriteLine("---Type: " + schemaRow[schemaCol].GetType ().ToString());
  				}
  				Console.WriteLine("");
  			}
  
  			Console.WriteLine("Read Schema with No KeyInfo");
  
  			rdr = cmd.ExecuteReader();
  
  
gt: 			dt = rdr.GetSchemaTable();