mcs/class/System.Data.OracleClient/Test/TestOracleClient.cs

   1 //
   2 // TestOracleClient.cs - Tests Sytem.Data.OracleClient
   3 //                       data provider in Mono.
   4 //
   5 // Part of managed C#/.NET library System.Data.OracleClient.dll
   6 //
   7 // Part of the Mono class libraries at
   8 // mcs/class/System.Data.OracleClient/System.Data.OracleClient.OCI
   9 //
  10 // Tests:
  11 //     Assembly: System.Data.OracleClient.dll
  12 //     Namespace: System.Data.OracleClient
  13 //
  14 // To Compile:
  15 // mcs TestOracleClient.cs /r:System.Data.dll /r:System.Data.OracleClient.dll /nowarn:0168
  16 //
  17 // Author:
  18 //     Daniel Morgan <monodanmorg@yahoo.com>
  19 //
  20 // Copyright (C) Daniel Morgan, 2002, 2004-2005, 2008
  21 //
  22
  23 using System;
  24 using System.IO;
  25 using System.Runtime.InteropServices;
  26 using System.Data;
  27 using System.Data.OracleClient;
  28 using System.Text;
  29 using System.Threading;
  30
  31 namespace Test.OracleClient
  32 {
  33         public class OracleTest
  34         {
  35                 private static Thread t = null;\r
  36                 private static string conStr;\r
  37                 public static readonly int MAX_CONNECTIONS = 30; // max connections default to 100, but I will set to 30.\r
  38
  39                 public OracleTest()
  40                 {
  41
  42                 }
  43
  44                 static void MonoTest(OracleConnection con)
  45                 {
  46                         Console.WriteLine ("  Drop table MONO_ORACLE_TEST ...");
  47                         try {
  48                                 OracleCommand cmd2 = con.CreateCommand ();
  49                                 cmd2.CommandText = "DROP TABLE MONO_ORACLE_TEST";
  50                                 cmd2.ExecuteNonQuery ();
  51                         }
  52                         catch (OracleException) {
  53                                 // ignore if table already exists
  54                         }
  55
  56                         OracleCommand cmd = null;
  57
  58                         Console.WriteLine("  Creating table MONO_ORACLE_TEST...");
  59                         cmd = new OracleCommand();
  60                         cmd.Connection = con;
  61                         cmd.CommandText = "CREATE TABLE MONO_ORACLE_TEST ( " +
  62                                 " varchar2_value VarChar2(32),  " +
  63                                 " long_value long, " +
  64                                 " number_whole_value Number(18), " +
  65                                 " number_scaled_value Number(18,2), " +
  66                                 " number_integer_value Integer, " +
  67                                 " float_value Float, " +
  68                                 " date_value Date, " +
  69                                 " char_value Char(32), " +
  70                                 " clob_value Clob, " +
  71                                 " blob_value Blob, " +
  72                                 " clob_empty_value Clob, " +
  73                                 " blob_empty_value Blob, " +
  74                                 " varchar2_null_value VarChar2(32),  " +
  75                                 " number_whole_null_value Number(18), " +
  76                                 " number_scaled_null_value Number(18,2), " +
  77                                 " number_integer_null_value Integer, " +
  78                                 " float_null_value Float, " +
  79                                 " date_null_value Date, " +
  80                                 " char_null_value Char(32), " +
  81                                 " clob_null_value Clob, " +
  82                                 " blob_null_value Blob " +
  83                                 ")";
  84
  85                         cmd.ExecuteNonQuery();
  86
  87                         Console.WriteLine("  Begin Trans for table MONO_ORACLE_TEST...");
  88                         OracleTransaction trans = con.BeginTransaction ();
  89
  90                         Console.WriteLine("  Inserting value into MONO_ORACLE_TEST...");
  91                         cmd = new OracleCommand();
  92                         cmd.Connection = con;
  93                         cmd.Transaction = trans;
  94                         cmd.CommandText = "INSERT INTO mono_oracle_test " +
  95                                 " ( varchar2_value,  " +
  96                                 "  long_value, " +
  97                                 "  number_whole_value, " +
  98                                 "  number_scaled_value, " +
  99                                 "  number_integer_value, " +
 100                                 "  float_value, " +
 101                                 "  date_value, " +
 102                                 "  char_value, " +
 103                                 "  clob_value, " +
 104                                 "  blob_value, " +
 105                                 "  clob_empty_value, " +
 106                                 "  blob_empty_value " +
 107                                 ") " +
 108                                 " VALUES( " +
 109                                 "  'Mono', " +
 110                                 "  'This is a LONG column', " +
 111                                 "  123, " +
 112                                 "  456.78, " +
 113                                 "  8765, " +
 114                                 "  235.2, " +
 115                                 "  TO_DATE( '2004-12-31', 'YYYY-MM-DD' ), " +
 116                                 "  'US', " +
 117                                 "  EMPTY_CLOB(), " +
 118                                 "  EMPTY_BLOB()," +
 119                                 "  EMPTY_CLOB(), " +
 120                                 "  EMPTY_BLOB()" +
 121                                 ")";
 122
 123                         cmd.ExecuteNonQuery();
 124
 125                         Console.WriteLine("  Select/Update CLOB columns on table MONO_ORACLE_TEST...");
 126
 127                         // update BLOB and CLOB columns
 128                         OracleCommand select = con.CreateCommand ();
 129                         select.Transaction = trans;
 130                         select.CommandText = "SELECT CLOB_VALUE, BLOB_VALUE FROM MONO_ORACLE_TEST FOR UPDATE";
 131                         OracleDataReader reader = select.ExecuteReader ();
 132                         if (!reader.Read ())
 133                                 Console.WriteLine ("ERROR: RECORD NOT FOUND");
 134                         // update clob_value
 135                         Console.WriteLine("     Update CLOB column on table MONO_ORACLE_TEST...");
 136                         OracleLob clob = reader.GetOracleLob (0);
 137                         byte[] bytes = null;
 138                         UnicodeEncoding encoding = new UnicodeEncoding ();
 139                         bytes = encoding.GetBytes ("Mono is fun!");
 140                         clob.Write (bytes, 0, bytes.Length);
 141                         clob.Close ();
 142                         // update blob_value
 143                         Console.WriteLine("     Update BLOB column on table MONO_ORACLE_TEST...");
 144                         OracleLob blob = reader.GetOracleLob (1);
 145                         bytes = new byte[6] { 0x31, 0x32, 0x33, 0x34, 0x35, 0x036 };
 146                         blob.Write (bytes, 0, bytes.Length);
 147                         blob.Close ();
 148
 149                         Console.WriteLine("  Commit trans for table MONO_ORACLE_TEST...");
 150                         trans.Commit ();
 151
 152                         // OracleCommand.ExecuteReader of MONO_ORACLE_TEST table
 153                         Console.WriteLine("  Read simple test for table MONO_ORACLE_TEST...");
 154                         ReadSimpleTest(con, "SELECT * FROM MONO_ORACLE_TEST");
 155
 156                         // OracleCommand.ExecuteScalar
 157                         Console.WriteLine(" -ExecuteScalar tests...");
 158                         string varchar2_value = (string) ReadScalar (con,"SELECT MAX(varchar2_value) FROM MONO_ORACLE_TEST");
 159                         Console.WriteLine("     String Value: " + varchar2_value);
 160
 161                         Console.WriteLine("  Read Scalar: number_whole_value");
 162                         decimal number_whole_value = (decimal)
 163                         ReadScalar (con,"SELECT MAX(number_whole_value) FROM MONO_ORACLE_TEST");
 164                         Console.WriteLine("     Int32 Value: " + number_whole_value.ToString());
 165
 166                         Console.WriteLine("  Read Scalar: number_scaled_value");
 167                         decimal number_scaled_value = (decimal)
 168                         ReadScalar (con,"SELECT number_scaled_value FROM MONO_ORACLE_TEST");
 169                         Console.WriteLine("     Decimal Value: " + number_scaled_value.ToString());
 170
 171                         Console.WriteLine("  Read Scalar: date_value");
 172                         DateTime date_value = (DateTime)
 173                         ReadScalar (con,"SELECT date_value FROM MONO_ORACLE_TEST");
 174                         Console.WriteLine("     DateTime Value: " + date_value.ToString());
 175
 176                         Console.WriteLine("  Read Scalar: clob_value");
 177                         string clob_value = (string)
 178                         ReadScalar (con,"SELECT clob_value FROM MONO_ORACLE_TEST");
 179                         Console.WriteLine("     CLOB Value: " + clob_value);
 180
 181                         Console.WriteLine("  Read Scalar: blob_value");
 182                         byte[] blob_value = (byte[])
 183                         ReadScalar (con,"SELECT blob_value FROM MONO_ORACLE_TEST");
 184                         string sblob_value = GetHexString (blob_value);
 185                         Console.WriteLine("     BLOB Value: " + sblob_value);
 186
 187                         // OracleCommand.ExecuteOracleScalar
 188                         Console.WriteLine(" -ExecuteOracleScalar tests...");
 189                         Console.WriteLine("  Read Oracle Scalar: varchar2_value");
 190                         ReadOracleScalar (con,"SELECT MAX(varchar2_value) FROM MONO_ORACLE_TEST");
 191
 192                         Console.WriteLine("  Read Oracle Scalar: number_whole_value");
 193                         ReadOracleScalar (con,"SELECT MAX(number_whole_value) FROM MONO_ORACLE_TEST");
 194
 195                         Console.WriteLine("  Read Oracle Scalar: number_scaled_value");
 196                         ReadOracleScalar (con,"SELECT number_scaled_value FROM MONO_ORACLE_TEST");
 197
 198                         Console.WriteLine("  Read Oracle Scalar: date_value");
 199                         ReadOracleScalar (con,"SELECT date_value FROM MONO_ORACLE_TEST");
 200
 201                         Console.WriteLine("  Read Oracle Scalar: clob_value");
 202                         ReadOracleScalar (con,"SELECT clob_value FROM MONO_ORACLE_TEST");
 203
 204                         Console.WriteLine("  Read Oracle Scalar: blob_value");
 205                         ReadOracleScalar (con,"SELECT blob_value FROM MONO_ORACLE_TEST");
 206                 }
 207
 208                 static object ReadScalar (OracleConnection con, string selectSql)
 209                 {
 210                         OracleCommand cmd = null;
 211                         cmd = con.CreateCommand();
 212                         cmd.CommandText = selectSql;
 213
 214                         object o = cmd.ExecuteScalar ();
 215
 216                         string dataType = o.GetType ().ToString ();
 217                         Console.WriteLine ("       DataType: " + dataType);
 218                         return o;
 219                 }
 220
 221                 static void ReadOracleScalar (OracleConnection con, string selectSql)
 222                 {
 223                         OracleCommand cmd = null;
 224                         cmd = con.CreateCommand();
 225                         cmd.CommandText = selectSql;
 226
 227                         object o = cmd.ExecuteOracleScalar ();
 228
 229                         string dataType = o.GetType ().ToString ();
 230                         Console.WriteLine ("       DataType: " + dataType);
 231                         if (dataType.Equals("System.Data.OracleClient.OracleLob"))
 232                                 o = ((OracleLob) o).Value;
 233                         if (o.GetType ().ToString ().Equals ("System.Byte[]"))
 234                                 o = GetHexString ((byte[])o);
 235
 236                         Console.WriteLine ("          Value: " + o.ToString ());
 237                 }
 238
 239                 static void ReadSimpleTest(OracleConnection con, string selectSql)
 240                 {
 241                         OracleCommand cmd = null;
 242                         OracleDataReader reader = null;
 243
 244                         cmd = con.CreateCommand();
 245                         cmd.CommandText = selectSql;
 246                         reader = cmd.ExecuteReader();
 247
 248                         Console.WriteLine("  Results...");
 249                         Console.WriteLine("    Schema");
 250                         DataTable table;
 251                         table = reader.GetSchemaTable();
 252                         for(int c = 0; c < reader.FieldCount; c++) {
 253                                 Console.WriteLine("  Column " + c.ToString());
 254                                 DataRow row = table.Rows[c];
 255
 256                                 string strColumnName = row["ColumnName"].ToString();
 257                                 string strBaseColumnName = row["BaseColumnName"].ToString();
 258                                 string strColumnSize = row["ColumnSize"].ToString();
 259                                 string strNumericScale = row["NumericScale"].ToString();
 260                                 string strNumericPrecision = row["NumericPrecision"].ToString();
 261                                 string strDataType = row["DataType"].ToString();
 262
 263                                 Console.WriteLine("      ColumnName: " + strColumnName);
 264                                 Console.WriteLine("      BaseColumnName: " + strBaseColumnName);
 265                                 Console.WriteLine("      ColumnSize: " + strColumnSize);
 266                                 Console.WriteLine("      NumericScale: " + strNumericScale);
 267                                 Console.WriteLine("      NumericPrecision: " + strNumericPrecision);
 268                                 Console.WriteLine("      DataType: " + strDataType);
 269                         }
 270
 271                         int r = 0;
 272                         Console.WriteLine ("    Data");
 273                         while (reader.Read ()) {
 274                                 r++;
 275                                 Console.WriteLine ("       Row: " + r.ToString ());
 276                                 for (int f = 0; f < reader.FieldCount; f++) {
 277                                         string sname = "";
 278                                         object ovalue = "";
 279                                         string svalue = "";
 280                                         string sDataType = "";
 281                                         string sFieldType = "";
 282                                         string sDataTypeName = "";
 283                                         string sOraDataType = "";
 284
 285                                         sname = reader.GetName (f);
 286
 287                                         if (reader.IsDBNull (f)) {
 288                                                 ovalue = DBNull.Value;
 289                                                 svalue = "";
 290                                                 sDataType = "DBNull.Value";
 291                                                 sOraDataType = "DBNull.Value";
 292                                         }
 293                                         else {
 294                                                 //ovalue = reader.GetValue (f);
 295                                                 ovalue = reader.GetOracleValue (f);
 296                                                 object oravalue = null;
 297
 298                                                 sDataType = ovalue.GetType ().ToString ();
 299                                                 switch (sDataType) {
 300                                                 case "System.Data.OracleClient.OracleString":
 301                                                         oravalue = ((OracleString) ovalue).Value;
 302                                                         break;
 303                                                 case "System.Data.OracleClient.OracleNumber":
 304                                                         oravalue = ((OracleNumber) ovalue).Value;
 305                                                         break;
 306                                                 case "System.Data.OracleClient.OracleLob":
 307                                                         OracleLob lob = (OracleLob) ovalue;
 308                                                         oravalue = lob.Value;
 309                                                         lob.Close ();
 310                                                         break;
 311                                                 case "System.Data.OracleClient.OracleDateTime":
 312                                                         oravalue = ((OracleDateTime) ovalue).Value;
 313                                                         break;
 314                                                 case "System.Byte[]":
 315                                                         oravalue = GetHexString((byte[])ovalue);
 316                                                         break;
 317                                                 case "System.Decimal":
 318                                                         //Console.WriteLine("           *** Get Decimal, Int16, Int32, Int64, Float, Double, ...");
 319                                                         decimal dec = reader.GetDecimal (f);
 320                                                         //Console.WriteLine("             GetDecimal: " + dec.ToString ());
 321
 322                                                         oravalue = (object) dec;
 323 /*
 324                                                         try {
 325                                                                 reader.GetInt16 (f);
 326                                                         } catch (NotSupportedException e) {
 327                                                                 Console.WriteLine ("            ** Expected exception caught for GetInt16: NotSupportedException: " + e.Message);
 328                                                         }
 329
 330                                                         try {
 331                                                                 long lng = reader.GetInt64 (f);
 332                                                                 Console.WriteLine("           GetInt64: " + lng.ToString ());
 333                                                                 int n = reader.GetInt32 (f);
 334                                                                 Console.WriteLine("           GetInt32: " + n.ToString ());
 335                                                                 float flt = reader.GetFloat (f);
 336                                                                 Console.WriteLine("           GetFloat: " + flt.ToString ());
 337                                                                 double dbl = reader.GetDouble (f);
 338                                                                 Console.WriteLine("           GetDouble: " + dbl.ToString ());
 339                                                         } catch (OverflowException oe1) {
 340                                                                 Console.WriteLine ("            ** Overflow exception for numbers to big or too small: " + oe1.Message);
 341                                                                 }
 342                                                         */
 343                                                         break;
 344                                                 default:
 345                                                         oravalue = ovalue.ToString ();
 346
 347                                                         break;
 348                                                 }
 349
 350                                                 sOraDataType = oravalue.GetType ().ToString ();
 351                                                 if (sOraDataType.Equals ("System.Byte[]"))
 352                                                         svalue = GetHexString ((byte[]) oravalue);
 353                                                 else
 354                                                         svalue = oravalue.ToString();
 355
 356                                         }
 357                                         sFieldType = reader.GetFieldType(f).ToString();
 358                                         sDataTypeName = reader.GetDataTypeName(f);
 359
 360                                         Console.WriteLine("           Field: " + f.ToString());
 361                                         Console.WriteLine("               Name: " + sname);
 362                                         Console.WriteLine("               Value: " + svalue);
 363                                         Console.WriteLine("               Oracle Data Type: " + sOraDataType);
 364                                         Console.WriteLine("               Data Type: " + sDataType);
 365                                         Console.WriteLine("               Field Type: " + sFieldType);
 366                                         Console.WriteLine("               Data Type Name: " + sDataTypeName);
 367                                 }
 368                         }
 369                         if(r == 0)
 370                                 Console.WriteLine("  No data returned.");
 371                 }
 372
 373                 static void DataAdapterTest (OracleConnection connection)
 374                 {
 375                         Console.WriteLine("  Create select command...");
 376                         OracleCommand command = connection.CreateCommand ();
 377                         command.CommandText = "SELECT * FROM SCOTT.EMP";
 378
 379                         Console.WriteLine("  Create data adapter...");
 380                         OracleDataAdapter adapter = new OracleDataAdapter (command);
 381
 382                         Console.WriteLine("  Create DataSet...");
 383                         DataSet dataSet = new DataSet ("EMP");
 384
 385                         Console.WriteLine("  Fill DataSet via data adapter...");
 386                         adapter.Fill (dataSet);
 387
 388                         Console.WriteLine("  Get DataTable...");
 389                         DataTable table = dataSet.Tables [0];
 390
 391                         Console.WriteLine("  Display each row...");
 392                         int rowCount = 0;
 393                         foreach (DataRow row in table.Rows) {
 394                                 Console.WriteLine ("    row {0}", rowCount + 1);
 395                                 for (int i = 0; i < table.Columns.Count; i += 1) {
 396                                         Console.WriteLine ("      {0}: {1}", table.Columns [i].ColumnName, row [i]);
 397                                 }
 398                                 Console.WriteLine ();
 399                                 rowCount += 1;
 400                         }
 401                 }
 402
 403                 public static void DataAdapterTest2 (OracleConnection con) \r
 404                 {\r
 405                         DataAdapterTest2_Setup (con);\r
 406                         ReadSimpleTest (con, "SELECT * FROM mono_adapter_test");\r
 407                 \r
 408                         GetMetaData (con, "SELECT * FROM mono_adapter_test");\r
 409 \r
 410                         DataAdapterTest2_Insert (con);\r
 411                         ReadSimpleTest (con, "SELECT * FROM mono_adapter_test");\r
 412                 \r
 413                         DataAdapterTest2_Update (con);\r
 414                         ReadSimpleTest (con, "SELECT * FROM mono_adapter_test");\r
 415 \r
 416                         DataAdapterTest2_Delete (con);\r
 417                         ReadSimpleTest (con, "SELECT * FROM mono_adapter_test");\r
 418                 }\r
 419 \r
 420                 public static void GetMetaData (OracleConnection con, string sql) \r
 421                 {\r
 422                         OracleCommand cmd = null;
 423                         OracleDataReader rdr = null;
 424
 425                         cmd = con.CreateCommand();
 426                         cmd.CommandText = sql;
 427
 428                         Console.WriteLine("Read Schema With KeyInfo");
 429                         rdr = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly);
 430                 \r
 431                         DataTable dt;\r
 432                         dt = rdr.GetSchemaTable();\r
 433                         foreach (DataRow schemaRow in dt.Rows) {\r
 434                                 foreach (DataColumn schemaCol in dt.Columns) {\r
 435                                         Console.WriteLine(schemaCol.ColumnName + \r
 436                                                 " = " + \r
 437                                                 schemaRow[schemaCol]);\r
 438                                         Console.WriteLine("---Type: " + schemaRow[schemaCol].GetType ().ToString());\r
 439                                 }\r
 440                                 Console.WriteLine("");\r
 441                         }\r
 442 \r
 443                         Console.WriteLine("Read Schema with No KeyInfo");\r
 444 \r
 445                         rdr = cmd.ExecuteReader();
 446 \r
 447                         dt = rdr.GetSchemaTable();\r
 448                         foreach (DataRow schemaRow in dt.Rows) {\r
 449                                 foreach (DataColumn schemaCol in dt.Columns) {\r
 450                                         Console.WriteLine(schemaCol.ColumnName + \r
 451                                                 " = " + \r
 452                                                 schemaRow[schemaCol]);\r
 453                                         Console.WriteLine("---Type: " + schemaRow[schemaCol].GetType ().ToString());\r
 454                                         Console.WriteLine();\r
 455                                 }\r
 456                         }\r
 457 \r
 458                 }\r
 459 \r
 460                 public static void DataAdapterTest2_Setup (OracleConnection con) \r
 461                 {\r
 462                         Console.WriteLine ("  Drop table mono_adapter_test ...");
 463                         try {
 464                                 OracleCommand cmd2 = con.CreateCommand ();
 465                                 cmd2.CommandText = "DROP TABLE mono_adapter_test";
 466                                 cmd2.ExecuteNonQuery ();
 467                         }
 468                         catch (OracleException) {
 469                                 // ignore if table already exists
 470                         }
 471
 472                         OracleCommand cmd = null;
 473
 474                         Console.WriteLine("  Creating table mono_adapter_test...");
 475                         cmd = new OracleCommand ();
 476                         cmd.Connection = con;
 477                         cmd.CommandText = "CREATE TABLE mono_adapter_test ( " +
 478                                 " varchar2_value VarChar2(32),  " +
 479                                 " number_whole_value Number(18) PRIMARY KEY, " +
 480                                 " number_scaled_value Number(18,2), " +
 481                                 " number_integer_value Integer, " +
 482                                 " float_value Float, " +
 483                                 " date_value Date, " +
 484                                 " clob_value Clob, " +
 485                                 " blob_value Blob ) ";
 486
 487                         // FIXME: char_value does not work
 488                         /*
 489                         cmd.CommandText = "CREATE TABLE mono_adapter_test ( " +
 490                                 " varchar2_value VarChar2(32),  " +
 491                                 " number_whole_value Number(18) PRIMARY KEY, " +
 492                                 " number_scaled_value Number(18,2), " +
 493                                 " number_integer_value Integer, " +
 494                                 " float_value Float, " +
 495                                 " date_value Date, " +
 496                                 " char_value Char(32), " +
 497                                 " clob_value Clob, " +
 498                                 " blob_value Blob ) ";
 499                         */
 500
 501                         cmd.ExecuteNonQuery();
 502
 503                         Console.WriteLine("  Begin Trans for table mono_adapter_test...");
 504                         OracleTransaction trans = con.BeginTransaction ();
 505
 506                         Console.WriteLine("  Inserting value into mono_adapter_test...");
 507                         cmd = new OracleCommand();
 508                         cmd.Connection = con;
 509                         cmd.Transaction = trans;
 510
 511                         cmd.CommandText = "INSERT INTO mono_adapter_test " +
 512                                 " ( varchar2_value,  " +
 513                                 "  number_whole_value, " +
 514                                 "  number_scaled_value, " +
 515                                 "  number_integer_value, " +
 516                                 "  float_value, " +
 517                                 "  date_value, " +
 518                                 "  clob_value, " +
 519                                 "  blob_value " +
 520                                 ") " +
 521                                 " VALUES( " +
 522                                 "  'Mono', " +
 523                                 "  11, " +
 524                                 "  456.78, " +
 525                                 "  8765, " +
 526                                 "  235.2, " +
 527                                 "  TO_DATE( '2004-12-31', 'YYYY-MM-DD' ), " +
 528                                 "  EMPTY_CLOB(), " +
 529                                 "  EMPTY_BLOB() " +
 530                                 ")";
 531
 532                         /*
 533                                         cmd.CommandText = "INSERT INTO mono_adapter_test " +
 534                                                 " ( varchar2_value,  " +
 535                                                 "  number_whole_value, " +
 536                                                 "  number_scaled_value, " +
 537                                                 "  number_integer_value, " +
 538                                                 "  float_value, " +
 539                                                 "  date_value, " +
 540                                                 "  char_value, " +
 541                                                 "  clob_value, " +
 542                                                 "  blob_value " +
 543                                                 ") " +
 544                                                 " VALUES( " +
 545                                                 "  'Mono', " +
 546                                                 "  11, " +
 547                                                 "  456.78, " +
 548                                                 "  8765, " +
 549                                                 "  235.2, " +
 550                                                 "  TO_DATE( '2004-12-31', 'YYYY-MM-DD' ), " +
 551                                                 "  'US', " +
 552                                                 "  EMPTY_CLOB(), " +
 553                                                 "  EMPTY_BLOB() " +
 554                                                 ")";
 555                         */
 556                         cmd.ExecuteNonQuery();\r
 557 \r
 558                         Console.WriteLine("  Select/Update CLOB columns on table mono_adapter_test...");
 559
 560                         // update BLOB and CLOB columns
 561                         OracleCommand select = con.CreateCommand ();
 562                         select.Transaction = trans;
 563                         select.CommandText = "SELECT CLOB_VALUE, BLOB_VALUE FROM mono_adapter_test FOR UPDATE";
 564                         OracleDataReader reader = select.ExecuteReader ();
 565                         if (!reader.Read ())
 566                                 Console.WriteLine ("ERROR: RECORD NOT FOUND");
 567
 568                         // update clob_value
 569                         Console.WriteLine("     Update CLOB column on table mono_adapter_test...");
 570                         OracleLob clob = reader.GetOracleLob (0);
 571                         byte[] bytes = null;
 572                         UnicodeEncoding encoding = new UnicodeEncoding ();
 573                         bytes = encoding.GetBytes ("Mono is fun!");
 574                         clob.Write (bytes, 0, bytes.Length);
 575                         clob.Close ();
 576
 577                         // update blob_value
 578                         Console.WriteLine("     Update BLOB column on table mono_adapter_test...");
 579                         OracleLob blob = reader.GetOracleLob (1);
 580                         bytes = new byte[6] { 0x31, 0x32, 0x33, 0x34, 0x35, 0x036 };
 581                         blob.Write (bytes, 0, bytes.Length);
 582                         blob.Close ();
 583
 584                         Console.WriteLine("  Commit trans for table mono_adapter_test...");
 585                         trans.Commit ();
 586
 587                         CommitCursor (con);\r
 588                 }\r
 589 \r
 590                 public static void DataAdapterTest2_Insert (OracleConnection con)
 591                 {
 592                         Console.WriteLine("================================");\r
 593                         Console.WriteLine("=== Adapter Insert =============");
 594                         Console.WriteLine("================================");\r
 595                         OracleTransaction transaction = con.BeginTransaction ();
 596                 \r
 597                         Console.WriteLine("   Create adapter...");\r
 598                         OracleDataAdapter da = new OracleDataAdapter("select * from mono_adapter_test", con);
 599                         da.SelectCommand.Transaction = transaction;
 600                 \r
 601                         Console.WriteLine("   Create command builder...");\r
 602                         OracleCommandBuilder mycb = new OracleCommandBuilder(da);
 603 \r
 604                         Console.WriteLine("   Create data set ...");\r
 605                         DataSet ds = new DataSet();\r
 606
 607                         Console.WriteLine("   Fill data set via adapter...");\r
 608                         da.Fill(ds, "mono_adapter_test");\r
 609 \r
 610                         Console.WriteLine("   New Row...");
 611                         DataRow myRow;\r
 612                         myRow = ds.Tables["mono_adapter_test"].NewRow();
 613 \r
 614                         byte[] bytes = new byte[] { 0x45,0x46,0x47,0x48,0x49,0x50 };\r
 615 \r
 616                         Console.WriteLine("   Set values in the new DataRow...");\r
 617                         myRow["varchar2_value"] = "OracleClient";\r
 618                         myRow["number_whole_value"] = 22;\r
 619                         myRow["number_scaled_value"] = 12.34;\r
 620                         myRow["number_integer_value"] = 456;\r
 621                         myRow["float_value"] = 98.76;\r
 622                         myRow["date_value"] = new DateTime(2001,07,09);\r
 623                         Console.WriteLine("   *** FIXME; char value not working");\r
 624                         //myRow["char_value"] = "Romeo";\r
 625                         myRow["clob_value"] = "clobtest";\r
 626                         myRow["blob_value"] = bytes;\r
 627                 \r
 628                         Console.WriteLine("    Add DataRow to DataTable...");           \r
 629                         ds.Tables["mono_adapter_test"].Rows.Add(myRow);
 630 \r
 631                         Console.WriteLine("da.Update(ds...");\r
 632                         da.Update(ds, "mono_adapter_test");\r
 633 \r
 634                         transaction.Commit();
 635
 636                         mycb.Dispose();
 637                         mycb = null;
 638                 }
 639 \r
 640                 public static void DataAdapterTest2_Update (OracleConnection con)
 641                 {
 642                         Console.WriteLine("================================");\r
 643                         Console.WriteLine("=== Adapter Update =============");
 644                         Console.WriteLine("================================");\r
 645 \r
 646                         OracleTransaction transaction = con.BeginTransaction ();
 647 \r
 648                         Console.WriteLine("   Create adapter...");\r
 649                         OracleCommand selectCmd = con.CreateCommand ();
 650                         selectCmd.Transaction = transaction;\r
 651                         selectCmd.CommandText = "SELECT * FROM mono_adapter_test";\r
 652                         OracleDataAdapter da = new OracleDataAdapter(selectCmd);\r
 653                         Console.WriteLine("   Create command builder...");\r
 654                         OracleCommandBuilder mycb = new OracleCommandBuilder(da);\r
 655                         Console.WriteLine("   Create data set ...");\r
 656                         DataSet ds = new DataSet();\r
 657 \r
 658                         Console.WriteLine("   Set missing schema action...");\r
 659                 \r
 660                         Console.WriteLine("  Fill data set via adapter...");\r
 661                         da.Fill(ds, "mono_adapter_test");\r
 662                         DataRow myRow;\r
 663 \r
 664                         Console.WriteLine("   New Row...");
 665                         myRow = ds.Tables["mono_adapter_test"].Rows[0];
 666
 667                         Console.WriteLine("Tables Count: " + ds.Tables.Count.ToString());
 668
 669                         DataTable table = ds.Tables["mono_adapter_test"];
 670                         DataRowCollection rows;
 671                         rows = table.Rows;
 672                         Console.WriteLine("   Row Count: " + rows.Count.ToString());
 673                         myRow = rows[0];\r
 674 \r
 675                         byte[] bytes = new byte[] { 0x62,0x63,0x64,0x65,0x66,0x67 };\r
 676 \r
 677                         Console.WriteLine("   Set values in the new DataRow...");\r
 678
 679                         myRow["varchar2_value"] = "Super Power!";\r
 680                 \r
 681                         myRow["number_scaled_value"] = 12.35;\r
 682                         myRow["number_integer_value"] = 457;\r
 683                         myRow["float_value"] = 198.76;\r
 684                         myRow["date_value"] = new DateTime(2002,08,09);\r
 685                         //myRow["char_value"] = "Juliet";\r
 686                         myRow["clob_value"] = "this is a clob";\r
 687                         myRow["blob_value"] = bytes;
 688 \r
 689                         Console.WriteLine("da.Update(ds...");\r
 690                         da.Update(ds, "mono_adapter_test");\r
 691 \r
 692                         transaction.Commit();
 693
 694                         mycb.Dispose();
 695                         mycb = null;
 696                 }\r
 697 \r
 698                 public static void DataAdapterTest2_Delete (OracleConnection con)
 699                 {
 700                         Console.WriteLine("================================");\r
 701                         Console.WriteLine("=== Adapter Delete =============");
 702                         Console.WriteLine("================================");\r
 703                         OracleTransaction transaction = con.BeginTransaction ();
 704                 \r
 705                         Console.WriteLine("   Create adapter...");\r
 706                         OracleDataAdapter da = new OracleDataAdapter("SELECT * FROM mono_adapter_test", con);
 707                         Console.WriteLine("   Create command builder...");
 708                         OracleCommandBuilder mycb = new OracleCommandBuilder(da);
 709                         Console.WriteLine("   set transr...");
 710                         da.SelectCommand.Transaction = transaction;
 711
 712                         Console.WriteLine("   Create data set ...");\r
 713                         DataSet ds = new DataSet();\r
 714                 \r
 715                         Console.WriteLine("Fill data set via adapter...");\r
 716                         da.Fill(ds, "mono_adapter_test");\r
 717 \r
 718                         Console.WriteLine("delete row...");
 719                         ds.Tables["mono_adapter_test"].Rows[0].Delete();
 720
 721                         Console.WriteLine("da.Update(table...");\r
 722                         da.Update(ds, "mono_adapter_test");\r
 723 \r
 724                         Console.WriteLine("Commit...");\r
 725                         transaction.Commit();
 726
 727                         mycb.Dispose();
 728                         mycb = null;\r
 729                 }
 730
 731                 static void TestNonQueryUsingExecuteReader(OracleConnection con) \r
 732                 {\r
 733                         OracleDataReader reader = null;\r
 734                         OracleTransaction trans = null;\r
 735 \r
 736                         Console.WriteLine("   drop table mono_adapter_test...");\r
 737                         OracleCommand cmd = con.CreateCommand();\r
 738 \r
 739                         cmd.CommandText = "DROP TABLE MONO_ADAPTER_TEST";\r
 740                         trans = con.BeginTransaction();\r
 741                         cmd.Transaction = trans;\r
 742                         try {\r
 743                                 reader = cmd.ExecuteReader();\r
 744                                 Console.WriteLine("   RowsAffected before read: " + reader.RecordsAffected.ToString());\r
 745                                 reader.Read();\r
 746                                 Console.WriteLine("   RowsAffected after read: " + reader.RecordsAffected.ToString());\r
 747                                 reader.Close();\r
 748                                 Console.WriteLine("   RowsAffected after close: " + reader.RecordsAffected.ToString());\r
 749                                 trans.Commit();\r
 750                         }\r
 751                         catch(OracleException e) {\r
 752                                 Console.WriteLine("   OracleException caught: " + e.Message);\r
 753                                 trans.Commit();\r
 754                         }\r
 755 \r
 756                         Console.WriteLine("   Create table mono_adapter_test...");\r
 757                         cmd.CommandText = "CREATE TABLE MONO_ADAPTER_TEST ( " +
 758                                 " varchar2_value VarChar2(32),  " +
 759                                 " number_whole_value Number(18,0) PRIMARY KEY ) ";\r
 760                         trans = con.BeginTransaction();\r
 761                         cmd.Transaction = trans;\r
 762                         reader = cmd.ExecuteReader();\r
 763                         Console.WriteLine("   RowsAffected before read: " + reader.RecordsAffected.ToString());\r
 764                         reader.Read();\r
 765                         Console.WriteLine("   RowsAffected after read: " + reader.RecordsAffected.ToString());\r
 766                         reader.Close();\r
 767                         Console.WriteLine("   RowsAffected after close: " + reader.RecordsAffected.ToString());\r
 768                         trans.Commit();\r
 769 \r
 770                         Console.WriteLine("Insert into table mono_adapter_test...");\r
 771                         \r
 772                         string sql =\r
 773                                 "INSERT INTO MONO_ADAPTER_TEST " +\r
 774                                 "(VARCHAR2_VALUE,NUMBER_WHOLE_VALUE) " +\r
 775                                 "VALUES(:p1,:p2)";\r
 776 \r
 777                         OracleCommand cmd2 = con.CreateCommand();\r
 778                         trans = con.BeginTransaction();\r
 779                         cmd2.Transaction = trans;\r
 780                         cmd2.CommandText = sql;\r
 781                         \r
 782                         OracleParameter myParameter1 = new OracleParameter("p1", OracleType.VarChar, 32);
 783                         myParameter1.Direction = ParameterDirection.Input;\r
 784                 \r
 785                         OracleParameter myParameter2 = new OracleParameter("p2", OracleType.Number);\r
 786                         myParameter2.Direction = ParameterDirection.Input;\r
 787 \r
 788                         myParameter2.Value = 182;\r
 789                         myParameter1.Value = "Mono";\r
 790 \r
 791                         cmd2.Parameters.Add (myParameter1);\r
 792                         cmd2.Parameters.Add (myParameter2);\r
 793                         \r
 794                         // insert 1 record\r
 795                         reader = cmd2.ExecuteReader();\r
 796                         Console.WriteLine("   RowsAffected before read: " + reader.RecordsAffected.ToString());\r
 797                         reader.Read();\r
 798                         Console.WriteLine("   RowsAffected after read: " + reader.RecordsAffected.ToString());\r
 799                         reader.Close();\r
 800                         Console.WriteLine("   RowsAffected after close: " + reader.RecordsAffected.ToString());\r
 801 \r
 802                         // insert another record\r
 803                         Console.WriteLine("   Insert another record...");\r
 804                         myParameter2.Value = 183;\r
 805                         myParameter1.Value = "Oracle";\r
 806                         reader = cmd2.ExecuteReader();\r
 807                         Console.WriteLine("   RowsAffected before read: " + reader.RecordsAffected.ToString());\r
 808                         reader.Read();\r
 809                         Console.WriteLine("   RowsAffected after read: " + reader.RecordsAffected.ToString());\r
 810                         reader.Close();\r
 811                         Console.WriteLine("   RowsAffected after close: " + reader.RecordsAffected.ToString());\r
 812 \r
 813                         trans.Commit();\r
 814                         trans = null;\r
 815                         \r
 816                         ReadSimpleTest(con, "SELECT * FROM MONO_ADAPTER_TEST");\r
 817                 }\r
 818
 819                 static void CommitCursor (OracleConnection con)
 820                 {
 821                         OracleCommand cmd = con.CreateCommand ();
 822                         cmd.CommandText = "COMMIT";
 823                         cmd.ExecuteNonQuery ();
 824                         cmd.Dispose ();
 825                         cmd = null;
 826                 }\r
 827
 828                 static void RollbackTest (OracleConnection connection)
 829                 {
 830                         OracleTransaction transaction = connection.BeginTransaction ();
 831
 832                         OracleCommand insert = connection.CreateCommand ();
 833                         insert.Transaction = transaction;
 834                         insert.CommandText = "INSERT INTO SCOTT.EMP (EMPNO, ENAME, JOB) VALUES (8787, 'T Coleman', 'Monoist')";
 835
 836                         Console.WriteLine ("  Inserting record ...");
 837
 838                         insert.ExecuteNonQuery ();
 839
 840                         OracleCommand select = connection.CreateCommand ();
 841                         select.CommandText = "SELECT COUNT(*) FROM SCOTT.EMP WHERE EMPNO = 8787";
 842                         select.Transaction = transaction;
 843                         OracleDataReader reader = select.ExecuteReader ();
 844                         reader.Read ();
 845
 846                         Console.WriteLine ("  Row count SHOULD BE 1, VALUE IS {0}", reader.GetValue (0));
 847                         reader.Close ();
 848
 849                         Console.WriteLine ("  Rolling back transaction ...");
 850
 851                         transaction.Rollback ();
 852
 853                         select = connection.CreateCommand ();
 854                         select.CommandText = "SELECT COUNT(*) FROM SCOTT.EMP WHERE EMPNO = 8787";
 855
 856                         reader = select.ExecuteReader ();
 857                         reader.Read ();
 858                         Console.WriteLine ("  Row count SHOULD BE 0, VALUE IS {0}", reader.GetValue (0));
 859                         reader.Close ();
 860                 }
 861
 862                 static void CommitTest (OracleConnection connection)
 863                 {
 864                         OracleTransaction transaction = connection.BeginTransaction ();
 865
 866                         OracleCommand insert = connection.CreateCommand ();
 867                         insert.Transaction = transaction;
 868                         insert.CommandText = "INSERT INTO SCOTT.EMP (EMPNO, ENAME, JOB) VALUES (8787, 'T Coleman', 'Monoist')";
 869
 870                         Console.WriteLine ("  Inserting record ...");
 871
 872                         insert.ExecuteNonQuery ();
 873
 874                         OracleCommand select = connection.CreateCommand ();
 875                         select.CommandText = "SELECT COUNT(*) FROM SCOTT.EMP WHERE EMPNO = 8787";
 876                         select.Transaction = transaction;
 877
 878                         Console.WriteLine ("  Row count SHOULD BE 1, VALUE IS {0}", select.ExecuteScalar ());
 879
 880                         Console.WriteLine ("  Committing transaction ...");
 881
 882                         transaction.Commit ();
 883
 884                         select = connection.CreateCommand ();
 885                         select.CommandText = "SELECT COUNT(*) FROM SCOTT.EMP WHERE EMPNO = 8787";
 886
 887                         Console.WriteLine ("Row count SHOULD BE 1, VALUE IS {0}", select.ExecuteScalar ());
 888                         transaction = connection.BeginTransaction ();
 889                         OracleCommand delete = connection.CreateCommand ();
 890                         delete.Transaction = transaction;
 891                         delete.CommandText = "DELETE FROM SCOTT.EMP WHERE EMPNO = 8787";
 892                         delete.ExecuteNonQuery ();
 893                         transaction.Commit ();
 894                 }
 895
 896                 public static void ParameterTest2 (OracleConnection connection)
 897                 {
 898                         Console.WriteLine("  Setting NLS_DATE_FORMAT...");\r
 899 \r
 900                         OracleCommand cmd2 = connection.CreateCommand();\r
 901                         cmd2.CommandText = "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'";\r
 902                 \r
 903                         cmd2.ExecuteNonQuery ();\r
 904 \r
 905                         Console.WriteLine("  Drop table MONO_TEST_TABLE2...");
 906                         try {
 907                                 cmd2.CommandText = "DROP TABLE MONO_TEST_TABLE7";
 908                                 cmd2.ExecuteNonQuery ();
 909                         }
 910                         catch(OracleException) {
 911                                 // ignore if table already exists
 912                         }
 913
 914                         Console.WriteLine("  Create table MONO_TEST_TABLE7...");
 915
 916                         cmd2.CommandText = "CREATE TABLE MONO_TEST_TABLE7(" +
 917                                 " COL1 VARCHAR2(8) NOT NULL, " +
 918                                 " COL2 VARCHAR2(32), " +
 919                                 " COL3 NUMBER(18,2), " +
 920                                 " COL4 NUMBER(18,2), " +
 921                                 " COL5 DATE NOT NULL, " +
 922                                 " COL6 DATE, " +
 923                                 " COL7 BLOB NOT NULL, " +
 924                                 " COL8 BLOB, " +
 925                                 " COL9 CLOB NOT NULL, " +
 926                                 " COL10 CLOB " +
 927                                 ")";
 928                         cmd2.ExecuteNonQuery ();
 929
 930                         Console.WriteLine("  COMMIT...");
 931                         cmd2.CommandText = "COMMIT";
 932                         cmd2.ExecuteNonQuery ();
 933
 934                         Console.WriteLine("  create insert command...");
 935 \r
 936                         OracleTransaction trans = connection.BeginTransaction ();\r
 937                         OracleCommand cmd = connection.CreateCommand ();\r
 938                         cmd.Transaction = trans;\r
 939
 940                         cmd.CommandText = "INSERT INTO MONO_TEST_TABLE7 " + \r
 941                                 "(COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10) " + \r
 942                                 "VALUES(:P1,:P2,:P3,:P4,:P5,:P6,:P7,:P8,:P9,:P10)";\r
 943                 \r
 944                         Console.WriteLine("  Add parameters...");\r
 945 \r
 946                         OracleParameter parm1 = cmd.Parameters.Add (":P1", OracleType.VarChar, 8);\r
 947                         OracleParameter parm2 = cmd.Parameters.Add (":P2", OracleType.VarChar, 32);\r
 948                 \r
 949                         OracleParameter parm3 = cmd.Parameters.Add (":P3", OracleType.Number);\r
 950                         OracleParameter parm4 = cmd.Parameters.Add (":P4", OracleType.Number);\r
 951                 \r
 952                         OracleParameter parm5 = cmd.Parameters.Add (":P5", OracleType.DateTime);\r
 953                         OracleParameter parm6 = cmd.Parameters.Add (":P6", OracleType.DateTime);\r
 954 \r
 955                         // FIXME: fix BLOBs and CLOBs in OracleParameter\r
 956 \r
 957                         OracleParameter parm7 = cmd.Parameters.Add (":P7", OracleType.Blob);\r
 958                         OracleParameter parm8 = cmd.Parameters.Add (":P8", OracleType.Blob);\r
 959 \r
 960                         OracleParameter parm9 = cmd.Parameters.Add (":P9", OracleType.Clob);\r
 961                         OracleParameter parm10 = cmd.Parameters.Add (":P10", OracleType.Clob);\r
 962 \r
 963                         // TODO: implement out, return, and ref parameters\r
 964 \r
 965                         string s = "Mono";\r
 966                         decimal d = 123456789012345.678M;\r
 967                         DateTime dt = DateTime.Now;\r
 968 \r
 969                         string clob = "Clob";\r
 970                         byte[] blob = new byte[] { 0x31, 0x32, 0x33, 0x34, 0x35 };\r
 971                 \r
 972                         Console.WriteLine("  Set Values...");\r
 973 \r
 974                         parm1.Value = s;\r
 975                         parm2.Value = DBNull.Value;\r
 976                 \r
 977                         parm3.Value = d;\r
 978                         parm4.Value = DBNull.Value;\r
 979                 \r
 980                         parm5.Value = dt;\r
 981                         parm6.Value = DBNull.Value;\r
 982                 \r
 983                         parm7.Value = blob;\r
 984                         parm8.Value = DBNull.Value;\r
 985 \r
 986                         parm9.Value = clob;\r
 987                         parm10.Value = DBNull.Value;\r
 988                 \r
 989                         Console.WriteLine("  ExecuteNonQuery...");\r
 990 \r
 991                         cmd.ExecuteNonQuery ();\r
 992                         trans.Commit();
 993                 }
 994
 995                 public static void ParameterTest (OracleConnection connection)
 996                 {
 997                         Console.WriteLine("  Setting NLS_DATE_FORMAT...");\r
 998 \r
 999                         OracleCommand cmd2 = connection.CreateCommand();\r
1000                         cmd2.CommandText = "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'";\r
1001                 \r
1002                         cmd2.ExecuteNonQuery ();\r
1003 \r
1004                         Console.WriteLine("  Drop table MONO_TEST_TABLE2...");
1005                         try {
1006                                 cmd2.CommandText = "DROP TABLE MONO_TEST_TABLE7";
1007                                 cmd2.ExecuteNonQuery ();
1008                         }
1009                         catch(OracleException) {
1010                                 // ignore if table already exists
1011                         }
1012
1013                         Console.WriteLine("  Create table MONO_TEST_TABLE7...");
1014
1015                         cmd2.CommandText = "CREATE TABLE MONO_TEST_TABLE7(" +
1016                                 " COL1 VARCHAR2(8) NOT NULL, " +
1017                                 " COL2 VARCHAR2(32), " +
1018                                 " COL3 NUMBER(18,2) NOT NULL, " +
1019                                 " COL4 NUMBER(18,2), " +
1020                                 " COL5 DATE NOT NULL, " +
1021                                 " COL6 DATE, " +
1022                                 " COL7 BLOB NOT NULL, " +
1023                                 " COL8 BLOB, " +
1024                                 " COL9 CLOB NOT NULL, " +
1025                                 " COL10 CLOB " +
1026                                 ")";
1027                         cmd2.ExecuteNonQuery ();
1028
1029                         Console.WriteLine("  COMMIT...");
1030                         cmd2.CommandText = "COMMIT";
1031                         cmd2.ExecuteNonQuery ();
1032
1033                         Console.WriteLine("  create insert command...");
1034 \r
1035                         OracleTransaction trans = connection.BeginTransaction ();\r
1036                         OracleCommand cmd = connection.CreateCommand ();\r
1037                         cmd.Transaction = trans;\r
1038
1039                         cmd.CommandText = "INSERT INTO MONO_TEST_TABLE7 " + \r
1040                                 "(COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10) " + \r
1041                                 "VALUES(:P1,:P2,:P3,:P4,:P5,:P6,:P7,:P8,:P9,:P10)";\r
1042                 \r
1043                         Console.WriteLine("  Add parameters...");\r
1044 \r
1045                         OracleParameter parm1 = cmd.Parameters.Add (":P1", OracleType.VarChar, 8);\r
1046                         OracleParameter parm2 = cmd.Parameters.Add (":P2", OracleType.VarChar, 32);\r
1047                 \r
1048                         OracleParameter parm3 = cmd.Parameters.Add (":P3", OracleType.Number);\r
1049                         OracleParameter parm4 = cmd.Parameters.Add (":P4", OracleType.Number);\r
1050                 \r
1051                         OracleParameter parm5 = cmd.Parameters.Add (":P5", OracleType.DateTime);\r
1052                         OracleParameter parm6 = cmd.Parameters.Add (":P6", OracleType.DateTime);\r
1053 \r
1054                         // FIXME: fix BLOBs and CLOBs in OracleParameter\r
1055 \r
1056                         OracleParameter parm7 = cmd.Parameters.Add (":P7", OracleType.Blob);\r
1057                         OracleParameter parm8 = cmd.Parameters.Add (":P8", OracleType.Blob);\r
1058 \r
1059                         OracleParameter parm9 = cmd.Parameters.Add (":P9", OracleType.Clob);\r
1060                         OracleParameter parm10 = cmd.Parameters.Add (":P10", OracleType.Clob);\r
1061 \r
1062                         // TODO: implement out, return, and ref parameters\r
1063 \r
1064                         string s = "Mono";\r
1065                         decimal d = 123456789012345.678M;\r
1066                         DateTime dt = DateTime.Now;\r
1067 \r
1068                         string clob = "Clob";\r
1069                         byte[] blob = new byte[] { 0x31, 0x32, 0x33, 0x34, 0x35 };\r
1070                 \r
1071                         Console.WriteLine("  Set Values...");\r
1072 \r
1073                         parm1.Value = s;\r
1074                         parm2.Value = DBNull.Value;\r
1075                 \r
1076                         parm3.Value = d;\r
1077                         parm4.Value = DBNull.Value;\r
1078                 \r
1079                         parm5.Value = dt;\r
1080                         parm6.Value = DBNull.Value;\r
1081                 \r
1082                         parm7.Value = blob;\r
1083                         parm8.Value = DBNull.Value;\r
1084 \r
1085                         parm9.Value = clob;\r
1086                         parm10.Value = DBNull.Value;\r
1087                 \r
1088                         Console.WriteLine("  ExecuteNonQuery...");\r
1089 \r
1090                         cmd.ExecuteNonQuery ();\r
1091                         trans.Commit();
1092                 }
1093
1094                 public static void CLOBTest (OracleConnection connection)
1095                 {
1096                         Console.WriteLine ("  BEGIN TRANSACTION ...");
1097
1098                         OracleTransaction transaction = connection.BeginTransaction ();
1099
1100                         Console.WriteLine ("  Drop table CLOBTEST ...");
1101                         try {
1102                                 OracleCommand cmd2 = connection.CreateCommand ();
1103                                 cmd2.Transaction = transaction;
1104                                 cmd2.CommandText = "DROP TABLE CLOBTEST";
1105                                 cmd2.ExecuteNonQuery ();
1106                         }
1107                         catch (OracleException) {
1108                                 // ignore if table already exists
1109                         }
1110
1111                         Console.WriteLine ("  CREATE TABLE ...");
1112
1113                         OracleCommand create = connection.CreateCommand ();
1114                         create.Transaction = transaction;
1115                         create.CommandText = "CREATE TABLE CLOBTEST (CLOB_COLUMN CLOB)";
1116                         create.ExecuteNonQuery ();
1117
1118                         Console.WriteLine ("  INSERT RECORD ...");
1119
1120                         OracleCommand insert = connection.CreateCommand ();
1121                         insert.Transaction = transaction;
1122                         insert.CommandText = "INSERT INTO CLOBTEST VALUES (EMPTY_CLOB())";
1123                         insert.ExecuteNonQuery ();
1124
1125                         OracleCommand select = connection.CreateCommand ();
1126                         select.Transaction = transaction;
1127                         select.CommandText = "SELECT CLOB_COLUMN FROM CLOBTEST FOR UPDATE";
1128                         Console.WriteLine ("  SELECTING A CLOB (CHARACTER) VALUE FROM CLOBTEST");
1129
1130                         OracleDataReader reader = select.ExecuteReader ();
1131                         if (!reader.Read ())
1132                                 Console.WriteLine ("ERROR: RECORD NOT FOUND");
1133
1134                         Console.WriteLine ("  TESTING OracleLob OBJECT ...");
1135                         OracleLob lob = reader.GetOracleLob (0);
1136                         Console.WriteLine ("  LENGTH: {0}", lob.Length);
1137                         Console.WriteLine ("  CHUNK SIZE: {0}", lob.ChunkSize);
1138
1139                         UnicodeEncoding encoding = new UnicodeEncoding ();
1140
1141                         byte[] value = new byte [lob.Length * 2];
1142
1143                         Console.WriteLine ("  CURRENT POSITION: {0}", lob.Position);
1144                         Console.WriteLine ("  UPDATING VALUE TO 'TEST ME!'");
1145                         value = encoding.GetBytes ("TEST ME!");
1146                         lob.Write (value, 0, value.Length);
1147
1148                         Console.WriteLine ("  CURRENT POSITION: {0}", lob.Position);
1149                         Console.WriteLine ("  RE-READ VALUE...");
1150                         lob.Seek (1, SeekOrigin.Begin);
1151
1152                         Console.WriteLine ("  CURRENT POSITION: {0}", lob.Position);
1153                         value = new byte [lob.Length * 2];
1154                         lob.Read (value, 0, value.Length);
1155                         Console.WriteLine ("  VALUE: {0}", encoding.GetString (value));
1156                         Console.WriteLine ("  CURRENT POSITION: {0}", lob.Position);
1157
1158                         Console.WriteLine ("  CLOSE OracleLob...");
1159                         lob.Close ();
1160
1161                         Console.WriteLine ("  CLOSING READER...");
1162
1163                         reader.Close ();
1164                         transaction.Commit ();
1165                 }
1166
1167                 public static void BLOBTest (OracleConnection connection)
1168                 {
1169                         Console.WriteLine ("  BEGIN TRANSACTION ...");
1170
1171                         OracleTransaction transaction = connection.BeginTransaction ();
1172
1173                         Console.WriteLine ("  Drop table BLOBTEST ...");
1174                         try {
1175                                 OracleCommand cmd2 = connection.CreateCommand ();
1176                                 cmd2.Transaction = transaction;
1177                                 cmd2.CommandText = "DROP TABLE BLOBTEST";
1178                                 cmd2.ExecuteNonQuery ();
1179                         }
1180                         catch (OracleException) {
1181                                 // ignore if table already exists
1182                         }
1183
1184                         Console.WriteLine ("  CREATE TABLE ...");
1185
1186                         OracleCommand create = connection.CreateCommand ();
1187                         create.Transaction = transaction;
1188                         create.CommandText = "CREATE TABLE BLOBTEST (BLOB_COLUMN BLOB)";
1189                         create.ExecuteNonQuery ();
1190
1191                         Console.WriteLine ("  INSERT RECORD ...");
1192
1193                         OracleCommand insert = connection.CreateCommand ();
1194                         insert.Transaction = transaction;
1195                         insert.CommandText = "INSERT INTO BLOBTEST VALUES (EMPTY_BLOB())";
1196                         insert.ExecuteNonQuery ();
1197
1198                         OracleCommand select = connection.CreateCommand ();
1199                         select.Transaction = transaction;
1200                         select.CommandText = "SELECT BLOB_COLUMN FROM BLOBTEST FOR UPDATE";
1201                         Console.WriteLine ("  SELECTING A BLOB (Binary) VALUE FROM BLOBTEST");
1202
1203                         OracleDataReader reader = select.ExecuteReader ();
1204                         if (!reader.Read ())
1205                                 Console.WriteLine ("ERROR: RECORD NOT FOUND");
1206
1207                         Console.WriteLine ("  TESTING OracleLob OBJECT ...");
1208                         OracleLob lob = reader.GetOracleLob (0);
1209
1210                         byte[] value = null;
1211                         string bvalue = "";
1212
1213                         Console.WriteLine ("  UPDATING VALUE");
1214
1215                         byte[] bytes = new byte[6];
1216                         bytes[0] = 0x31;
1217                         bytes[1] = 0x32;
1218                         bytes[2] = 0x33;
1219                         bytes[3] = 0x34;
1220                         bytes[4] = 0x35;
1221                         bytes[5] = 0x36;
1222
1223                         lob.Write (bytes, 0, bytes.Length);
1224
1225                         Console.WriteLine ("  CURRENT POSITION: {0}", lob.Position);
1226                         Console.WriteLine ("  RE-READ VALUE...");
1227                         lob.Seek (1, SeekOrigin.Begin);
1228
1229                         Console.WriteLine ("  CURRENT POSITION: {0}", lob.Position);
1230                         value = new byte [lob.Length];
1231                         lob.Read (value, 0, value.Length);
1232
1233                         bvalue = "";
1234                         if (value.GetType ().ToString ().Equals ("System.Byte[]"))
1235                                 bvalue = GetHexString (value);
1236                         Console.WriteLine ("  Bytes: " + bvalue);
1237
1238                         Console.WriteLine ("  CURRENT POSITION: {0}", lob.Position);
1239
1240                         Console.WriteLine ("  CLOSE OracleLob...");
1241                         lob.Close ();
1242
1243                         Console.WriteLine ("  CLOSING READER...");
1244
1245                         reader.Close ();
1246                         transaction.Commit ();
1247                 }
1248
1249                 static void Wait(string msg)
1250                 {
1251                         Console.WriteLine(msg);
1252                         if (msg.Equals(""))
1253                                 Console.WriteLine("Waiting...  Press Enter to continue...");
1254                         Console.ReadLine();
1255                 }
1256
1257                 // use this function to read a byte array into a string
1258                 // for easy display of binary data, such as, a BLOB value
1259                 public static string GetHexString (byte[] bytes)
1260                 {
1261                         string bvalue = "";
1262
1263                         StringBuilder sb2 = new StringBuilder();
1264                         for (int z = 0; z < bytes.Length; z++) {
1265                                 byte byt = bytes[z];
1266                                 if (byt < 0x10)
1267                                         sb2.Append ("0");
1268                                 sb2.Append (byt.ToString("x"));
1269                         }
1270                         if (sb2.Length > 0)
1271                                 bvalue = "0x" + sb2.ToString ();
1272
1273                         return bvalue;
1274                 }
1275
1276                 static void StoredProcedureTest1 (OracleConnection con)
1277                 {
1278                         // test stored procedure with no parameters
1279
1280
1281                         OracleCommand cmd2 = con.CreateCommand ();
1282
1283                         Console.WriteLine("  Drop table MONO_TEST_TABLE1...");
1284                         try {
1285                                 cmd2.CommandText = "DROP TABLE MONO_TEST_TABLE1";
1286                                 cmd2.ExecuteNonQuery ();
1287                         }
1288                         catch(OracleException) {
1289                                 // ignore if table did not exist
1290                         }
1291
1292                         Console.WriteLine("  Drop procedure SP_TEST1...");
1293                         try {
1294                                 cmd2.CommandText = "DROP PROCEDURE SP_TEST1";
1295                                 cmd2.ExecuteNonQuery ();
1296                         }
1297                         catch(OracleException) {
1298                                 // ignore if procedure did not exist
1299                         }
1300
1301                         Console.WriteLine("  Create table MONO_TEST_TABLE1...");
1302                         cmd2.CommandText = "CREATE TABLE MONO_TEST_TABLE1 (" +
1303                                         " COL1 VARCHAR2(8), "+
1304                                         " COL2 VARCHAR2(32))";
1305                         cmd2.ExecuteNonQuery ();
1306
1307                         Console.WriteLine("  Create stored procedure SP_TEST1...");
1308                         cmd2.CommandText = "CREATE PROCEDURE SP_TEST1 " +
1309                                 " IS " +
1310                                 " BEGIN " +
1311                                 "       INSERT INTO MONO_TEST_TABLE1 (COL1,COL2) VALUES ('aaa','bbbb');" +
1312                                 "       COMMIT;" +
1313                                 " END;";
1314                         cmd2.ExecuteNonQuery ();
1315
1316                         Console.WriteLine("COMMIT...");
1317                         cmd2.CommandText = "COMMIT";
1318                         cmd2.ExecuteNonQuery ();
1319
1320                         Console.WriteLine("  Call stored procedure sp_test1...");
1321                         OracleCommand cmd3 = con.CreateCommand ();\r
1322                         cmd3.CommandType = CommandType.StoredProcedure;\r
1323                         cmd3.CommandText = "sp_test1";\r
1324                         cmd3.ExecuteNonQuery ();
1325                 }
1326
1327                 static void StoredProcedureTest2 (OracleConnection con)
1328                 {
1329                         // test stored procedure with 2 parameters
1330
1331                         Console.WriteLine("  Drop table MONO_TEST_TABLE2...");
1332                         OracleCommand cmd2 = con.CreateCommand ();
1333
1334                         try {
1335                                 cmd2.CommandText = "DROP TABLE MONO_TEST_TABLE2";
1336                                 cmd2.ExecuteNonQuery ();
1337                         }
1338                         catch(OracleException) {
1339                                 // ignore if table already exists
1340                         }
1341
1342                         Console.WriteLine("  Drop procedure SP_TEST2...");
1343                         try {
1344                                 cmd2.CommandText = "DROP PROCEDURE SP_TEST2";
1345                                 cmd2.ExecuteNonQuery ();
1346                         }
1347                         catch(OracleException) {
1348                                 // ignore if procedure does not exists
1349                         }
1350
1351                         Console.WriteLine("  Create table MONO_TEST_TABLE2...");
1352
1353                         cmd2.CommandText = "CREATE TABLE MONO_TEST_TABLE2 (" +
1354                                 " COL1 VARCHAR2(8), "+
1355                                 " COL2 VARCHAR2(32))";
1356                         cmd2.ExecuteNonQuery ();
1357
1358                         Console.WriteLine("  Create stored procedure SP_TEST2...");
1359                         cmd2.CommandText = "CREATE PROCEDURE SP_TEST2(parm1 VARCHAR2,parm2 VARCHAR2) " +
1360                                 " IS " +
1361                                 " BEGIN " +
1362                                 "       INSERT INTO MONO_TEST_TABLE2 (COL1,COL2) VALUES (parm1,parm2);" +
1363                                 "       COMMIT;" +
1364                                 " END;";
1365                         cmd2.ExecuteNonQuery ();
1366
1367                         Console.WriteLine("  COMMIT...");
1368                         cmd2.CommandText = "COMMIT";
1369                         cmd2.ExecuteNonQuery ();
1370
1371                         Console.WriteLine("  Call stored procedure SP_TEST2 with two parameters...");
1372                         OracleCommand cmd3 = con.CreateCommand ();\r
1373                         cmd3.CommandType = CommandType.StoredProcedure;\r
1374                         cmd3.CommandText = "sp_test2";\r
1375 \r
1376                         OracleParameter myParameter1 = new OracleParameter("parm1", OracleType.VarChar);\r
1377                         myParameter1.Value = "yyy13";\r
1378                         myParameter1.Size = 8;\r
1379                         myParameter1.Direction = ParameterDirection.Input;\r
1380                 \r
1381                         OracleParameter myParameter2 = new OracleParameter("parm2", OracleType.VarChar);\r
1382                         myParameter2.Value = "iii13";\r
1383                         myParameter2.Size = 32;\r
1384                         myParameter2.Direction = ParameterDirection.Input;\r
1385 \r
1386                         cmd3.Parameters.Add (myParameter1);\r
1387                         cmd3.Parameters.Add (myParameter2);\r
1388
1389                         cmd3.ExecuteNonQuery ();
1390                 }
1391
1392                 static void OutParmTest1(OracleConnection con)
1393                 {
1394                     // test stored fuctions with 4 parameters
1395                     // 1. input varchar2
1396                     // 2. output varchar2
1397                     // 3. input output varchar2
1398                     // 4. return varchar2
1399
1400                     Console.WriteLine("  Create stored function SP_OUTPUTPARMTEST1 for testing VARCHAR2 Input, Output, InputOutput, Return parameters...");
1401
1402                     OracleCommand cmd2 = con.CreateCommand();
1403                     cmd2.CommandText =
1404                         "CREATE OR REPLACE FUNCTION SF_TESTOUTPARM1(parm1 IN VARCHAR2, parm2 OUT VARCHAR2, parm3 IN OUT VARCHAR2) RETURN VARCHAR2 " +
1405                         "IS " +
1406                         "    returnValue VARCHAR2(32) := 'Anywhere';" +
1407                         "BEGIN " +
1408                         "   IF parm1 IS NULL THEN " +
1409                         "        parm2 := 'parm1 is null'; " +
1410                         "   ELSE " +
1411                         "            parm2 := 'One' || parm1 || 'Three'; " +
1412                         "   END IF; " +
1413                         "   IF parm3 IS NOT NULL THEN " +
1414                         "       parm3 := parm2 || parm3 || 'Five'; " +
1415                         "   ELSE " +
1416                         "       parm3 := 'parm3 in was NULL'; " +
1417                         "   END IF; " +
1418                         "   IF parm1 IS NOT NULL THEN " +
1419                         "       IF parm1 = '999' THEN " +
1420                         "          parm2 := NULL; " +
1421                         "          parm3 := NULL; " +
1422                         "          returnValue := NULL; " +
1423                         "       END IF; " +
1424                         "   END IF; " +
1425                         "   RETURN returnValue; " +
1426                         "END;";
1427
1428                     cmd2.ExecuteNonQuery();
1429
1430                     Console.WriteLine("  COMMIT...");
1431                     cmd2.CommandText = "COMMIT";
1432                     cmd2.ExecuteNonQuery();
1433
1434                     Console.WriteLine("  Call stored function SF_TESTOUTPARM1 with 4 parameters...");
1435                     OracleCommand cmd3 = con.CreateCommand();
1436                     cmd3.CommandType = CommandType.Text;
1437                     cmd3.CommandText =
1438                         "BEGIN " +
1439                         "       :ReturnValue := SF_TESTOUTPARM1(:p1, :p2, :p3); " +
1440                         "END;";
1441                     OracleParameter myParameter1 = new OracleParameter("p1", OracleType.VarChar);
1442                     myParameter1.Value = "Two";
1443                     myParameter1.Size = 32;
1444                     myParameter1.Direction = ParameterDirection.Input;
1445
1446                     OracleParameter myParameter2 = new OracleParameter("p2", OracleType.VarChar);
1447                     myParameter2.Size = 32;
1448                     myParameter2.Direction = ParameterDirection.Output;
1449
1450                     OracleParameter myParameter3 = new OracleParameter("p3", OracleType.VarChar);
1451                     myParameter3.Value = "Four";
1452                     myParameter3.Size = 32;
1453                     myParameter3.Direction = ParameterDirection.InputOutput;
1454
1455                     OracleParameter myParameter4 = new OracleParameter("ReturnValue", OracleType.VarChar);
1456                     myParameter4.Size = 32;
1457                     myParameter4.Direction = ParameterDirection.ReturnValue;
1458
1459                     cmd3.Parameters.Add(myParameter1);
1460                     cmd3.Parameters.Add(myParameter2);
1461                     cmd3.Parameters.Add(myParameter3);
1462                     cmd3.Parameters.Add(myParameter4);
1463
1464                     cmd3.ExecuteNonQuery();
1465                     string outValue = (string)myParameter2.Value;
1466                     string inOutValue = (string)myParameter3.Value;
1467                     string returnValue = (string)myParameter4.Value;
1468                     Console.WriteLine("    1Out Value should be: OneTwoThree");
1469                     Console.WriteLine("    1Out Value: " + outValue);
1470                     Console.WriteLine("    1InOut Value should be: OneTwoThreeFourFive");
1471                     Console.WriteLine("    1InOut Value: " + inOutValue);
1472                     Console.WriteLine("    1Return Value should be: Anywhere");
1473                     Console.WriteLine("    1Return Value: " + returnValue);
1474                     Console.WriteLine();
1475
1476                     myParameter1.Value = DBNull.Value;
1477                     myParameter3.Value = "Hello";
1478                     cmd3.ExecuteNonQuery();
1479                     outValue = (string)myParameter2.Value;
1480                     inOutValue = (string)myParameter3.Value;
1481                     returnValue = (string)myParameter4.Value;
1482                     Console.WriteLine("    2Out Value should be: parm1 is null");
1483                     Console.WriteLine("    2Out Value: " + outValue);
1484                     Console.WriteLine("    2InOut Value should be: parm1 is nullHelloFive");
1485                     Console.WriteLine("    2InOut Value: " + inOutValue);
1486                     Console.WriteLine("    2Return Value should be: Anywhere");
1487                     Console.WriteLine("    2Return Value: " + returnValue);
1488                     Console.WriteLine();
1489
1490                     myParameter1.Value = "999";
1491                     myParameter3.Value = "Bye";
1492                     cmd3.ExecuteNonQuery();
1493                     if (myParameter2.Value == DBNull.Value)
1494                         outValue = "Value is DBNull.Value";
1495                     else
1496                         outValue = (string)myParameter2.Value;
1497                     if( myParameter3.Value == DBNull.Value)
1498                         inOutValue = "Value is DBNull.Value";
1499                     else
1500                         inOutValue = (string)myParameter3.Value;
1501                     if (myParameter4.Value == DBNull.Value)
1502                         returnValue = "Value is DBNull.Value";
1503                     else
1504                         returnValue = (string)myParameter4.Value;
1505                     Console.WriteLine("    3Out Value should be: Value is DBNull.Value");
1506                     Console.WriteLine("    3Out Value: " + outValue);
1507                     Console.WriteLine("    3InOut Value should be: Value is DBNull.Value");
1508                     Console.WriteLine("    3InOut Value: " + inOutValue);
1509                     Console.WriteLine("    3Return Value should be: Value is DBNull.Value");
1510                     Console.WriteLine("    3Return Value: " + returnValue);
1511                     Console.WriteLine();
1512
1513                     myParameter1.Value = "***";
1514                     myParameter3.Value = DBNull.Value;
1515                     cmd3.ExecuteNonQuery();
1516                     outValue = (string)myParameter2.Value;
1517                     inOutValue = (string)myParameter3.Value;
1518                     returnValue = (string)myParameter4.Value;
1519                     Console.WriteLine("    4Out Value should be: One***Three");
1520                     Console.WriteLine("    4Out Value: " + outValue);
1521                     Console.WriteLine("    4InOut Value should be: parm3 in was NULL");
1522                     Console.WriteLine("    4InOut Value: " + inOutValue);
1523                     Console.WriteLine("    4Return Value should be: Anywhere");
1524                     Console.WriteLine("    4Return Value: " + returnValue);
1525                     Console.WriteLine();
1526                 }
1527
1528                 static void OutParmTest2 (OracleConnection con)
1529                 {
1530                     // test stored function with 4 parameters
1531                     // 1. input number(18,2)
1532                     // 2. output number(18,2)
1533                     // 3. input output number (18,2)
1534                     // 4. return number (18,2)
1535
1536                     Console.WriteLine("  Create stored function SF_TESTOUTPARM2 to test NUMBER parameters...");
1537
1538                     // stored procedure addes two numbers
1539                     OracleCommand cmd2 = con.CreateCommand();
1540                     cmd2.CommandText =
1541                         "CREATE OR REPLACE FUNCTION SF_TESTOUTPARM2(parm1 IN NUMBER, parm2 OUT NUMBER, parm3 IN OUT NUMBER) RETURN NUMBER " +
1542                         "IS " +
1543                         "   returnValue NUMBER := 123.45; " +
1544                         "BEGIN " +
1545                         "   IF parm1 IS NULL THEN " +
1546                         "      parm2 := 18; " +
1547                         "          parm3 := parm3 + 8000; " +
1548                         "      returnValue := 78; " +
1549                         "   ELSIF parm1 = 999 THEN " +
1550                         "         parm2 := NULL;" +
1551                         "         parm3 := NULL;" +
1552                         "         returnValue := NULL;" +
1553                         "   ELSIF parm3 IS NULL THEN " +
1554                         "         parm2 := 0; " +
1555                         "         parm3 := 1234567890123.12345678; " +
1556                         "   ELSE " +
1557                         "          parm2 := parm1 + 3; " +
1558                         "      parm3 := parm3 + 70; " +
1559                         "   END IF;" +
1560                         "   RETURN returnValue;" +
1561                         "END;";
1562
1563                     cmd2.ExecuteNonQuery();
1564
1565                     Console.WriteLine("  COMMIT...");
1566                     cmd2.CommandText = "COMMIT";
1567                     cmd2.ExecuteNonQuery();
1568
1569                     Console.WriteLine("  Call stored function SP_TESTOUTPARM2 with 4 parameters...");
1570                     OracleCommand cmd3 = con.CreateCommand();
1571                     cmd3.CommandType = CommandType.Text;
1572                     cmd3.CommandText =
1573                         "BEGIN " +
1574                         "       :returnValue := SF_TESTOUTPARM2(:p1, :p2, :p3);" +
1575                         "END;";
1576                     OracleParameter myParameter1 = new OracleParameter("p1", OracleType.Number);
1577                     myParameter1.Value = 2.2;
1578                     myParameter1.Direction = ParameterDirection.Input;
1579
1580                     OracleParameter myParameter2 = new OracleParameter("p2", OracleType.Number);
1581                     myParameter2.Direction = ParameterDirection.Output;
1582
1583                     OracleParameter myParameter3 = new OracleParameter("p3", OracleType.Number);
1584                     myParameter3.Value = 33.4;
1585                     myParameter3.Direction = ParameterDirection.InputOutput;
1586
1587                     OracleParameter myParameter4 = new OracleParameter("returnValue", OracleType.Number);
1588                     myParameter4.Direction = ParameterDirection.ReturnValue;
1589
1590                     cmd3.Parameters.Add(myParameter1);
1591                     cmd3.Parameters.Add(myParameter2);
1592                     cmd3.Parameters.Add(myParameter3);
1593                     cmd3.Parameters.Add(myParameter4);
1594
1595                     cmd3.ExecuteNonQuery();
1596                     decimal outValue = (decimal)myParameter2.Value;
1597                     decimal inOutValue = (decimal)myParameter3.Value;
1598                     decimal returnValue = (decimal)myParameter4.Value;
1599                     Console.WriteLine("    1Out Value should be: 5.20");
1600                     Console.WriteLine("    1Out Value: {0}", outValue);
1601                     Console.WriteLine("    1InOut Value should be: 103.40");
1602                     Console.WriteLine("    1InOut Value: {0}", inOutValue);
1603                     Console.WriteLine("    1Return Value should be: 123.45");
1604                     Console.WriteLine("    1Return Value: {0}", returnValue);
1605                     Console.WriteLine();
1606
1607                     myParameter1.Value = DBNull.Value;
1608                     myParameter3.Value = 23;
1609                     cmd3.ExecuteNonQuery();
1610                     outValue = (decimal)myParameter2.Value;
1611                     inOutValue = (decimal)myParameter3.Value;
1612                     returnValue = (decimal)myParameter4.Value;
1613                     Console.WriteLine("    2Out Value should be: 18");
1614                     Console.WriteLine("    2Out Value: {0}", outValue);
1615                     Console.WriteLine("    2InOut Value should be: 8023");
1616                     Console.WriteLine("    2InOut Value: {0}", inOutValue);
1617                     Console.WriteLine("    2Return Value should be: 78");
1618                     Console.WriteLine("    2Return Value: {0}", returnValue);
1619                     Console.WriteLine();
1620
1621                     string soutValue = "";
1622                     string sinOutValue = "";
1623                     string sreturnValue = "";
1624                     myParameter1.Value = 999;
1625                     myParameter3.Value = 66;
1626                     cmd3.ExecuteNonQuery();
1627                     if (myParameter2.Value == DBNull.Value)
1628                         soutValue = "DBNull.Value";
1629                     else
1630                         soutValue = myParameter2.Value.ToString();
1631                     if (myParameter3.Value == DBNull.Value)
1632                         sinOutValue = "DBNull.Value";
1633                     else
1634                         sinOutValue = myParameter3.Value.ToString();
1635                     if (myParameter4.Value == DBNull.Value)
1636                         sreturnValue = "DBNull.Value";
1637                     else
1638                         sreturnValue = myParameter4.Value.ToString();
1639                     Console.WriteLine("    3Out Value should be: DBNull.Value");
1640                     Console.WriteLine("    3Out Value: {0}", soutValue);
1641                     Console.WriteLine("    3InOut Value should be: DBNull.Value");
1642                     Console.WriteLine("    3InOut Value: {0}", sinOutValue);
1643                     Console.WriteLine("    3Return Value should be: DBNull.Value");
1644                     Console.WriteLine("    3Return Value: {0}", sreturnValue);
1645                     Console.WriteLine();
1646
1647                     myParameter1.Value = 111;
1648                     myParameter3.Value = DBNull.Value;
1649                     cmd3.ExecuteNonQuery();
1650                     outValue = (decimal)myParameter2.Value;
1651                     inOutValue = (decimal)myParameter3.Value;
1652                     returnValue = (decimal)myParameter4.Value;
1653                     Console.WriteLine("    4Out Value should be: 0 (as in digit zero)");
1654                     Console.WriteLine("    4Out Value: {0}", outValue);
1655                     Console.WriteLine("    4InOut Value should be: 1234567890123.12345678");
1656                     Console.WriteLine("    4InOut Value: {0}", inOutValue);
1657                     Console.WriteLine("    4Return Value should be: 123.45");
1658                     Console.WriteLine("    4Return Value: {0}", returnValue);
1659                     Console.WriteLine();
1660
1661                 }
1662
1663                 static void OutParmTest3 (OracleConnection con)
1664                 {
1665                     // test stored function with 4 parameters
1666                     // 1. input date
1667                     // 2. output date
1668                     // 3. input output date
1669                     // 4. return dae
1670
1671                     // a DATE type in Oracle has Date and Time
1672
1673                     Console.WriteLine("  Create stored function SF_TESTOUTPARM3 to test Date parameters...");
1674
1675                     OracleCommand cmd2 = con.CreateCommand();
1676                     cmd2.CommandText =
1677                         "CREATE OR REPLACE FUNCTION SF_TESTOUTPARM3(parm1 IN DATE, parm2 OUT DATE, parm3 IN OUT DATE) RETURN DATE " +
1678                         "IS " +
1679                         "   returnValue DATE := TO_DATE('2001-07-01 15:32:52', 'YYYY-MM-DD HH24:MI:SS');" +
1680                         "BEGIN " +
1681                         "   IF parm1 IS NULL THEN " +
1682                         "      parm2 := TO_DATE('1900-12-31', 'YYYY-MM-DD'); " +
1683                         "      parm3 := TO_DATE('1900-12-31', 'YYYY-MM-DD'); " +
1684                         "   ELSIF parm1 = TO_DATE('1979-11-25','YYYY-MM-DD') THEN " +
1685                         "      parm2 := NULL;" +
1686                         "      parm3 := NULL;" +
1687                         "      returnValue := NULL;"+
1688                         "   ELSIF parm3 IS NULL THEN " +
1689                         "      parm2 := TO_DATE('2008-08-08', 'YYYY-MM-DD');" +
1690                         "      parm3 := TO_DATE('2000-01-01', 'YYYY-MM-DD');" +
1691                         "   ELSE " +
1692                         "      -- add 3 days to date\n " +
1693                         "          parm2 := parm1 + 3; " +
1694                         "      parm3 := parm3 + 5; " +
1695                         "   END IF; " +
1696                         "   RETURN returnValue;" +
1697                         "END;";
1698
1699                     cmd2.ExecuteNonQuery();
1700
1701                     Console.WriteLine("  COMMIT...");
1702                     cmd2.CommandText = "COMMIT";
1703                     cmd2.ExecuteNonQuery();
1704
1705                     Console.WriteLine("  Call stored function SF_TESTOUTPARM3 with 4 parameters...");
1706                     OracleCommand cmd3 = con.CreateCommand();
1707                     cmd3.CommandType = CommandType.Text;
1708                     cmd3.CommandText =
1709                         "BEGIN " +
1710                         "       :returnValue := SF_TESTOUTPARM3(:p1, :p2, :p3);" +
1711                         "END;";
1712                     OracleParameter myParameter1 = new OracleParameter("p1", OracleType.DateTime);
1713                     myParameter1.Value = new DateTime(2004, 12, 15);
1714                     myParameter1.Direction = ParameterDirection.Input;
1715
1716                     OracleParameter myParameter2 = new OracleParameter("p2", OracleType.DateTime);
1717                     myParameter2.Direction = ParameterDirection.Output;
1718
1719                     OracleParameter myParameter3 = new OracleParameter("p3", OracleType.DateTime);
1720                     myParameter3.Value = new DateTime(2008, 10, 14, 20, 21, 22);
1721                     myParameter3.Direction = ParameterDirection.InputOutput;
1722
1723                     OracleParameter myParameter4 = new OracleParameter("returnValue", OracleType.DateTime);
1724                     myParameter4.Direction = ParameterDirection.ReturnValue;
1725
1726                     cmd3.Parameters.Add(myParameter1);
1727                     cmd3.Parameters.Add(myParameter2);
1728                     cmd3.Parameters.Add(myParameter3);
1729                     cmd3.Parameters.Add(myParameter4);
1730
1731                     cmd3.ExecuteNonQuery();
1732                     DateTime outValue = (DateTime)myParameter2.Value;
1733                     DateTime inOutValue = (DateTime)myParameter3.Value;
1734                     DateTime returnValue = (DateTime)myParameter4.Value;
1735                     Console.WriteLine("    1Out Value should be: 2004-12-18 00:00:00");
1736                     Console.WriteLine("    1Out Value: {0}", outValue.ToString("yyyy-MM-dd HH:mm:ss"));
1737                     Console.WriteLine("    1InOut Value should be: 2008-10-19 20:21:22");
1738                     Console.WriteLine("    1InOut Value: {0}", inOutValue.ToString("yyyy-MM-dd HH:mm:ss"));
1739                     Console.WriteLine("    1Return Value should be: 2001-07-01 15:32:52");
1740                     Console.WriteLine("    1Return Value: {0}", returnValue.ToString("yyyy-MM-dd HH:mm:ss"));
1741                     Console.WriteLine();
1742
1743                     myParameter1.Value = DBNull.Value;
1744                     myParameter3.Value = new DateTime(1980, 11, 22);
1745                     cmd3.ExecuteNonQuery();
1746                     outValue = (DateTime)myParameter2.Value;
1747                     inOutValue = (DateTime)myParameter3.Value;
1748                     returnValue = (DateTime)myParameter4.Value;
1749                     Console.WriteLine("    2Out Value should be: 1900-12-31 00:00:00");
1750                     Console.WriteLine("    2Out Value: {0}", outValue.ToString("yyyy-MM-dd HH:mm:ss"));
1751                     Console.WriteLine("    2InOut Value should be: 1900-12-31 00:00:00");
1752                     Console.WriteLine("    2InOut Value: {0}", inOutValue.ToString("yyyy-MM-dd HH:mm:ss"));
1753                     Console.WriteLine("    2Return Value should be: 2001-07-01 15:32:52");
1754                     Console.WriteLine("    2Return Value: {0}", returnValue.ToString("yyyy-MM-dd HH:mm:ss"));
1755                     Console.WriteLine();
1756
1757                     myParameter1.Value = new DateTime(1979, 11, 25);
1758                     myParameter3.Value = new DateTime(1981, 12, 14);
1759                     cmd3.ExecuteNonQuery();
1760                     string soutValue = "";
1761                     string sinOutValue = "";
1762                     string sreturnValue = "";
1763                     if (myParameter2.Value == DBNull.Value)
1764                         soutValue = "DBNull.Value";
1765                     else {
1766                         outValue = (DateTime)myParameter2.Value;
1767                         soutValue = outValue.ToString("yyyy-MM-dd HH:mm:ss");
1768                     }
1769                     if (myParameter3.Value == DBNull.Value)
1770                         sinOutValue = "DBNull.Value";
1771                     else {
1772                         inOutValue = (DateTime)myParameter3.Value;
1773                         sinOutValue = inOutValue.ToString("yyyy-MM-dd HH:mm:ss");
1774                     }
1775                     if (myParameter4.Value == DBNull.Value)
1776                         sreturnValue = "DBNull.Value";
1777                     else {
1778                         returnValue = (DateTime)myParameter4.Value;
1779                         sreturnValue = returnValue.ToString("yyyy-MM-dd HH:mm:ss");
1780                     }
1781                     Console.WriteLine("    3Out Value should be: DBNull.Value");
1782                     Console.WriteLine("    3Out Value: {0}", soutValue);
1783                     Console.WriteLine("    3InOut Value should be: DBNull.Value");
1784                     Console.WriteLine("    3InOut Value: {0}", sinOutValue);
1785                     Console.WriteLine("    3Return Value should be: DBNull.Value");
1786                     Console.WriteLine("    3Return Value: {0}", sreturnValue);
1787                     Console.WriteLine();
1788
1789                     myParameter1.Value = new DateTime(1976, 7, 4);
1790                     myParameter3.Value = DBNull.Value;
1791                     cmd3.ExecuteNonQuery();
1792                     outValue = (DateTime)myParameter2.Value;
1793                     inOutValue = (DateTime)myParameter3.Value;
1794                     returnValue = (DateTime)myParameter4.Value;
1795                     Console.WriteLine("    4Out Value should be: 2008-08-08 00:00:00");
1796                     Console.WriteLine("    4Out Value: {0}", outValue.ToString("yyyy-MM-dd HH:mm:ss"));
1797                     Console.WriteLine("    4InOut Value should be: 2000-01-01 00:00:00");
1798                     Console.WriteLine("    4InOut Value: {0}", inOutValue.ToString("yyyy-MM-dd HH:mm:ss"));
1799                     Console.WriteLine("    4Return Value should be: 2001-07-01 15:32:52");
1800                     Console.WriteLine("    4Return Value: {0}", returnValue.ToString("yyyy-MM-dd HH:mm:ss"));
1801                     Console.WriteLine();
1802
1803                 }
1804
1805                 static void OutParmTest4(OracleConnection con)
1806                 {
1807                     // test stored fuctions with 4 parameters
1808                     // 1. input long
1809                     // 2. output long
1810                     // 3. input output long
1811                     // 4. return long
1812
1813                     Console.WriteLine("  Create stored function SP_OUTPUTPARMTEST4 for testing LONG VARCHAR Input, Output, InputOutput, Return parameters...");
1814
1815                     OracleCommand cmd2 = con.CreateCommand();
1816                     cmd2.CommandText =
1817                         "CREATE OR REPLACE FUNCTION SP_OUTPUTPARMTEST4(parm1 IN LONG, parm2 OUT LONG, parm3 IN OUT LONG) RETURN LONG " +
1818                         "IS " +
1819                         "    returnValue LONG := 'A very, very, very long value in a far away memory space.'; " +
1820                         "BEGIN " +
1821                         "   IF parm1 IS NULL THEN " +
1822                         "        parm2 := 'parm1 is null'; " +
1823                         "        returnValue := 'Another one bytes the dust.'; " +
1824                         "   ELSE " +
1825                         "            parm2 := 'One' || parm1 || 'Three'; " +
1826                         "   END IF; " +
1827                         "   IF parm3 IS NOT NULL THEN " +
1828                         "       parm3 := parm2 || parm3 || 'Five'; " +
1829                         "   ELSE " +
1830                         "       parm3 := 'parm3 in was NULL'; " +
1831                         "   END IF; " +
1832                         "   IF parm1 IS NOT NULL THEN " +
1833                         "       IF parm1 = '999' THEN " +
1834                         "          parm2 := NULL; " +
1835                         "          parm3 := NULL; " +
1836                         "          returnValue := NULL; " +
1837                         "       END IF; " +
1838                         "   END IF; " +
1839                         "   RETURN returnValue; " +
1840                         "END;";
1841
1842                     cmd2.ExecuteNonQuery();
1843
1844                     Console.WriteLine("  COMMIT...");
1845                     cmd2.CommandText = "COMMIT";
1846                     cmd2.ExecuteNonQuery();
1847
1848                     Console.WriteLine("  Call stored procedure SP_OUTPUTPARMTEST4 with 4 parameters...");
1849                     OracleCommand cmd3 = con.CreateCommand();
1850                     cmd3.CommandType = CommandType.Text;
1851                     cmd3.CommandText =
1852                         "BEGIN " +
1853                         "       :ReturnValue := SP_OUTPUTPARMTEST4(:p1, :p2, :p3); " +
1854                         "END;";
1855                     OracleParameter myParameter1 = new OracleParameter("p1", OracleType.LongVarChar);
1856                     myParameter1.Size = 1000;
1857                     myParameter1.Direction = ParameterDirection.Input;
1858                     myParameter1.Value = "Two";
1859
1860                     OracleParameter myParameter2 = new OracleParameter("p2", OracleType.LongVarChar);
1861                     myParameter2.Size = 1000;
1862                     myParameter2.Direction = ParameterDirection.Output;
1863
1864                     OracleParameter myParameter3 = new OracleParameter("p3", OracleType.LongVarChar);
1865                     myParameter3.Value = "Four";
1866                     myParameter3.Size = 1000;
1867                     myParameter3.Direction = ParameterDirection.InputOutput;
1868
1869                     OracleParameter myParameter4 = new OracleParameter("ReturnValue", OracleType.LongVarChar);
1870                     myParameter4.Size = 1000;
1871                     myParameter4.Direction = ParameterDirection.ReturnValue;
1872
1873                     cmd3.Parameters.Add(myParameter1);
1874                     cmd3.Parameters.Add(myParameter2);
1875                     cmd3.Parameters.Add(myParameter3);
1876                     cmd3.Parameters.Add(myParameter4);
1877
1878                     cmd3.ExecuteNonQuery();
1879                     string outValue = (string)myParameter2.Value;
1880                     string inOutValue = (string)myParameter3.Value;
1881                     string returnValue = (string)myParameter4.Value;
1882                     Console.WriteLine("    1Out Value should be: OneTwoThree");
1883                     Console.WriteLine("    1Out Value: " + outValue);
1884                     Console.WriteLine("    1InOut Value should be: OneTwoThreeFourFive");
1885                     Console.WriteLine("    1InOut Value: " + inOutValue);
1886                     Console.WriteLine("    1Return Value should be: A very, very, very long value in a far away memory space.");
1887                     Console.WriteLine("    1Return Value: " + returnValue);
1888                     Console.WriteLine();
1889
1890                     myParameter1.Value = DBNull.Value;
1891                     myParameter3.Value = "Hello";
1892                     cmd3.ExecuteNonQuery();
1893                     outValue = (string)myParameter2.Value;
1894                     inOutValue = (string)myParameter3.Value;
1895                     returnValue = (string)myParameter4.Value;
1896                     Console.WriteLine("    2Out Value should be: parm1 is null");
1897                     Console.WriteLine("    2Out Value: " + outValue);
1898                     Console.WriteLine("    2InOut Value should be: parm1 is nullHelloFive");
1899                     Console.WriteLine("    2InOut Value: " + inOutValue);
1900                     Console.WriteLine("    2Return Value should be: Another one bytes the dust.");
1901                     Console.WriteLine("    2Return Value: " + returnValue);
1902                     Console.WriteLine();
1903
1904                     myParameter1.Value = "999";
1905                     myParameter3.Value = "Bye";
1906                     cmd3.ExecuteNonQuery();
1907                     if (myParameter2.Value == DBNull.Value)
1908                         outValue = "Value is DBNull.Value";
1909                     else
1910                         outValue = (string)myParameter2.Value;
1911                     if (myParameter3.Value == DBNull.Value)
1912                         inOutValue = "Value is DBNullValue";
1913                     else
1914                         inOutValue = (string)myParameter3.Value;
1915                     if (myParameter4.Value == DBNull.Value)
1916                         returnValue = "Value is DBNull.Value";
1917                     else
1918                         returnValue = (string)myParameter4.Value;
1919                     Console.WriteLine("    3Out Value should be: Value is DBNull.Value");
1920                     Console.WriteLine("    3Out Value: " + outValue);
1921                     Console.WriteLine("    3InOut Value should be: Value is DBNull.Value");
1922                     Console.WriteLine("    3InOut Value: " + inOutValue);
1923                     Console.WriteLine("    3Return Value should be: Value is DBNull.Value");
1924                     Console.WriteLine("    3Return Value: " + returnValue);
1925                     Console.WriteLine();
1926
1927                     myParameter1.Value = "***";
1928                     myParameter3.Value = DBNull.Value;
1929                     cmd3.ExecuteNonQuery();
1930                     outValue = (string)myParameter2.Value;
1931                     inOutValue = (string)myParameter3.Value;
1932                     returnValue = (string)myParameter4.Value;
1933                     Console.WriteLine("    4Out Value should be: One***Three");
1934                     Console.WriteLine("    4Out Value: " + outValue);
1935                     Console.WriteLine("    4InOut Value should be: parm3 in was NULL");
1936                     Console.WriteLine("    4InOut Value: " + inOutValue);
1937                     Console.WriteLine("    4Return Value should be: A very, very, very long value in a far away memory space.");
1938                     Console.WriteLine("    4Return Value: " + returnValue);
1939                     Console.WriteLine();
1940                 }
1941
1942                 static void OutParmTest5(OracleConnection con)
1943                 {
1944                         // test stored fuctions with 4 parameters
1945                         // 1. input CLOB
1946                         // 2. output CLOB
1947                         // 3. input output CLOB
1948                         // 4. return CLOB
1949
1950                         Console.WriteLine("  Create stored function SP_OUTPUTPARMTEST5 for testing CLOB Input, Output, InputOutput, Return parameters...");
1951
1952                         OracleCommand cmd2 = con.CreateCommand();
1953                         cmd2.CommandText =
1954                             "CREATE OR REPLACE FUNCTION SP_OUTPUTPARMTEST5(parm1 IN CLOB, parm2 OUT CLOB, parm3 IN OUT CLOB) RETURN CLOB " +
1955                             " IS " +
1956                             "    returnValue CLOB := 'Clobber'; " +
1957                             " BEGIN " +
1958                             "   IF parm1 IS NULL THEN " +
1959                             "        parm2 := 'parm1 is null'; " +
1960                             "   ELSE " +
1961                             "        parm2 := 'One' || parm1 || 'Three'; " +
1962                             "   END IF; " +
1963                             "   IF parm3 IS NOT NULL THEN " +
1964                             "       parm3 := parm2 || parm3 || 'Five'; " +
1965                             "   ELSE " +
1966                             "       parm3 := 'parm3 in was NULL'; " +
1967                             "   END IF; " +
1968                             "   IF parm1 IS NOT NULL THEN " +
1969                             "       IF parm1 = '999' THEN " +
1970                             "          parm2 := NULL; " +
1971                             "          parm3 := NULL; " +
1972                             "          returnValue := NULL; " +
1973                             "       ELSIF LENGTH(parm1) = 0 THEN " +
1974                             "          parm2 := 'parm1 is zero length'; " +
1975                             "          IF LENGTH(parm3) = 0 THEN " +
1976                             "              parm3 := 'parm3 is zero length';" +
1977                             "          ELSE " +
1978                             "              parm3 := 'Uh oh, parm3 is not zero length like we thought'; " +
1979                             "          END IF; " +
1980                             "          returnValue := 'parm1 is zero length'; " +
1981                             "       ELSIF parm1 = '888' THEN " +
1982                             "          parm2 := EMPTY_CLOB(); " +
1983                             "          parm3 := EMPTY_CLOB(); " +
1984                             "          returnValue := EMPTY_CLOB(); " +
1985                             "       END IF; " +
1986                             "   END IF; " +
1987                             "   RETURN returnValue; " +
1988                             "END;";
1989
1990                         cmd2.ExecuteNonQuery();
1991
1992                         Console.WriteLine("  COMMIT...");
1993                         cmd2.CommandText = "COMMIT";
1994                         cmd2.ExecuteNonQuery();
1995
1996                         Console.WriteLine("  Call stored procedure SP_OUTPUTPARMTEST5 with 4 parameters...");
1997                         //OracleTransaction trans = con.BeginTransaction();
1998                         //OracleCommand cmd4 = con.CreateCommand();
1999                         //cmd4.Transaction = trans;
2000                         //OracleLob lob = CreateTemporaryLobLocator(cmd4, OracleType.Clob);
2001
2002                         OracleCommand cmd3 = con.CreateCommand();
2003                         //cmd3.Transaction = trans;
2004                         cmd3.CommandType = CommandType.Text;
2005                         cmd3.CommandText =
2006                             "DECLARE " +
2007                             "       tempP3 CLOB; " +
2008                             "BEGIN " +
2009                             "   tempP3 := :inp3; " +
2010                             "   :ReturnValue := SP_OUTPUTPARMTEST5(:p1, :p2, tempP3); " +
2011                             "   :outp3 := tempP3;" +
2012                             "END;";
2013                         OracleParameter myParameter1 = new OracleParameter("p1", OracleType.Clob);
2014                         myParameter1.Size = 1000;
2015                         myParameter1.Direction = ParameterDirection.Input;
2016                         myParameter1.Value = "Two";
2017
2018                         OracleParameter myParameter2 = new OracleParameter("p2", OracleType.Clob);
2019                         myParameter2.Size = 1000;
2020                         myParameter2.Direction = ParameterDirection.Output;
2021
2022                         // impossible to use one OracleParameter for an CLOB IN OUT parameter?
2023                         // I had to create two parameters for the 3rd parameter: in3 as input and out3 as output
2024                         // and in the anonymous PL/SQL block, get and set the 3rd parameter appropriately
2025
2026                         OracleParameter myParameterIn3 = new OracleParameter("inp3", OracleType.Clob);
2027                         myParameterIn3.Size = 1000;
2028                         myParameterIn3.Direction = ParameterDirection.Input;
2029                         string s = "Everything";
2030                         myParameterIn3.Value = s;
2031
2032                         OracleParameter myParameterOut3 = new OracleParameter("outp3", OracleType.Clob);
2033                         myParameterOut3.Size = 1000;
2034                         myParameterOut3.Direction = ParameterDirection.Output;
2035
2036                         OracleParameter myParameter4 = new OracleParameter("ReturnValue", OracleType.Clob);
2037                         myParameter4.Size = 1000;
2038                         myParameter4.Direction = ParameterDirection.ReturnValue;
2039
2040                         cmd3.Parameters.Add(myParameter1);
2041                         cmd3.Parameters.Add(myParameter2);
2042                         cmd3.Parameters.Add(myParameterIn3);
2043                         cmd3.Parameters.Add(myParameterOut3);
2044                         cmd3.Parameters.Add(myParameter4);
2045
2046                         cmd3.ExecuteNonQuery();
2047
2048                         string outValue = GetOracleClobValue(myParameter2);
2049                         string inOutValue = GetOracleClobValue(myParameterOut3);
2050                         string returnValue = GetOracleClobValue(myParameter4);
2051                         Console.WriteLine("    1Out Value should be: OneTwoThree");
2052                         Console.WriteLine("    1Out Value: " + outValue);
2053                         Console.WriteLine("    1InOut Value should be: OneTwoThreeEverythingFive");
2054                         Console.WriteLine("    1InOut Value: " + inOutValue);
2055                         Console.WriteLine("    1Return Value should be: Clobber");
2056                         Console.WriteLine("    1Return Value: " + returnValue);
2057                         Console.WriteLine();
2058
2059                         myParameter1.Value = DBNull.Value;
2060                         myParameterIn3.Value = "Hello";
2061                         cmd3.ExecuteNonQuery();
2062                         outValue = GetOracleClobValue(myParameter2);
2063                         inOutValue = GetOracleClobValue(myParameterOut3);
2064                         returnValue = GetOracleClobValue(myParameter4);
2065                         Console.WriteLine("    2Out Value should be: parm1 is null");
2066                         Console.WriteLine("    2Out Value: " + outValue);
2067                         Console.WriteLine("    2InOut Value should be: parm1 is nullHelloFive");
2068                         Console.WriteLine("    2InOut Value: " + inOutValue);
2069                         Console.WriteLine("    2Return Value should be: Clobber");
2070                         Console.WriteLine("    2Return Value: " + returnValue);
2071                         Console.WriteLine();
2072
2073                         myParameter1.Value = "999";
2074                         myParameterIn3.Value = "Bye";
2075                         cmd3.ExecuteNonQuery();
2076                         outValue = GetOracleClobValue(myParameter2);
2077                         inOutValue = GetOracleClobValue(myParameterOut3);
2078                         returnValue = GetOracleClobValue(myParameter4);
2079                         Console.WriteLine("    3Out Value should be: Value is DBNull.Value");
2080                         Console.WriteLine("    3Out Value: " + outValue);
2081                         Console.WriteLine("    3InOut Value should be: Value is DBNull.Value");
2082                         Console.WriteLine("    3InOut Value: " + inOutValue);
2083                         Console.WriteLine("    3Return Value should be: Value is DBNull.Value");
2084                         Console.WriteLine("    3Return Value: " + returnValue);
2085                         Console.WriteLine();
2086
2087                         myParameter1.Value = "***";
2088                         myParameterIn3.Value = DBNull.Value;
2089                         cmd3.ExecuteNonQuery();
2090                         outValue = GetOracleClobValue(myParameter2);
2091                         inOutValue = GetOracleClobValue(myParameterOut3);
2092                         returnValue = GetOracleClobValue(myParameter4);
2093                         Console.WriteLine("    4Out Value should be: One***Three");
2094                         Console.WriteLine("    4Out Value: " + outValue);
2095                         Console.WriteLine("    4InOut Value should be: parm3 in was NULL");
2096                         Console.WriteLine("    4InOut Value: " + inOutValue);
2097                         Console.WriteLine("    4Return Value should be: Clobber");
2098                         Console.WriteLine("    4Return Value: " + returnValue);
2099                         Console.WriteLine();
2100
2101                         myParameter1.Value = OracleLob.Null;
2102                         myParameterIn3.Value = "bass";
2103                         cmd3.ExecuteNonQuery();
2104                         outValue = GetOracleClobValue(myParameter2);
2105                         inOutValue = GetOracleClobValue(myParameterOut3);
2106                         returnValue = GetOracleClobValue(myParameter4);
2107                         Console.WriteLine("    5Out Value should be: parm1 is null");
2108                         Console.WriteLine("    5Out Value: " + outValue);
2109                         Console.WriteLine("    5InOut Value should be: parm1 is nullbassFive");
2110                         Console.WriteLine("    5InOut Value: " + inOutValue);
2111                         Console.WriteLine("    5Return Value should be: Clobber");
2112                         Console.WriteLine("    5Return Value: " + returnValue);
2113                         Console.WriteLine();
2114
2115                         myParameter1.Value = "888";
2116                         myParameterIn3.Value = "777";
2117                         cmd3.ExecuteNonQuery();
2118                         outValue = GetOracleClobValue(myParameter2);
2119                         inOutValue = GetOracleClobValue(myParameterOut3);
2120                         returnValue = GetOracleClobValue(myParameter4);
2121                         Console.WriteLine("    6Out Value should be: Zero Length");
2122                         Console.WriteLine("    6Out Value: " + outValue);
2123                         Console.WriteLine("    6InOut Value should be: Zero Length");
2124                         Console.WriteLine("    6InOut Value: " + inOutValue);
2125                         Console.WriteLine("    6Return Value should be: Zero Length");
2126                         Console.WriteLine("    6Return Value: " + returnValue);
2127                         Console.WriteLine();
2128                 }
2129
2130                 public static string GetOracleClobValue (OracleParameter parm)
2131                 {
2132                         if (parm.Value.Equals (DBNull.Value))
2133                                 return "Clob is DBNull.Value";
2134                         OracleLob lob = (OracleLob) parm.Value;
2135                         if (lob.Length == 0)
2136                                 return "Zero Length";
2137                         return lob.Value.ToString ();
2138                 }
2139
2140                 public static OracleLob CreateTemporaryLobLocator (OracleCommand cmd, OracleType lobType)
2141                 {
2142                         cmd.CommandText =
2143                                 "DECLARE TEMP_LOB " + lobType.ToString () + "; " +
2144                                 "   BEGIN " +
2145                                 "       SYS.DBMS_LOB.CREATETEMPORARY (TEMP_LOB, FALSE); " +
2146                                 "       :TempLobLocator := TEMP_LOB; " +
2147                                 " END;";
2148
2149                         OracleParameter parm = cmd.Parameters.Add ("TempLobLocator", lobType);
2150                         parm.Direction = ParameterDirection.Output;
2151
2152                         cmd.ExecuteNonQuery ();
2153
2154                         return (OracleLob)parm.Value;
2155                 }
2156
2157                 static void OutParmTest6 (OracleConnection con)
2158                 {
2159                     // test stored function with 4 parameters
2160                     // 1. input timestamp
2161                     // 2. output timestamp
2162                     // 3. input output timestamp
2163                     // 4. return timestamp
2164
2165                     // a TIMESTAMP type in Oracle has Date and Time
2166
2167                     Console.WriteLine("  Create stored function SF_TESTOUTPARM6 to test Date parameters...");
2168
2169                     OracleCommand cmd2 = con.CreateCommand();
2170                     cmd2.CommandText =
2171                         "CREATE OR REPLACE FUNCTION SF_TESTOUTPARM6(parm1 IN TIMESTAMP, parm2 OUT TIMESTAMP, parm3 IN OUT TIMESTAMP) RETURN TIMESTAMP " +
2172                         "IS " +
2173                         "   returnValue TIMESTAMP := TO_TIMESTAMP('2001-07-01 15:32:52', 'YYYY-MM-DD HH24:MI:SS');" +
2174                         "BEGIN " +
2175                         "   IF parm1 IS NULL THEN " +
2176                         "      parm2 := TO_TIMESTAMP('1900-12-31', 'YYYY-MM-DD'); " +
2177                         "      parm3 := TO_TIMESTAMP('1900-12-31', 'YYYY-MM-DD'); " +
2178                         "   ELSIF parm1 = TO_TIMESTAMP('1979-11-25','YYYY-MM-DD') THEN " +
2179                         "      parm2 := NULL;" +
2180                         "      parm3 := NULL;" +
2181                         "      returnValue := NULL;"+
2182                         "   ELSIF parm3 IS NULL THEN " +
2183                         "      parm2 := TO_TIMESTAMP('2008-08-08', 'YYYY-MM-DD');" +
2184                         "      parm3 := TO_TIMESTAMP('2000-01-01', 'YYYY-MM-DD');" +
2185                         "   ELSE " +
2186                         "      -- add 3 days to date\n " +
2187                         "          parm2 := parm1 + 3; " +
2188                         "      parm3 := parm3 + 5; " +
2189                         "   END IF; " +
2190                         "   RETURN returnValue;" +
2191                         "END;";
2192
2193                     cmd2.ExecuteNonQuery();
2194
2195                     Console.WriteLine("  COMMIT...");
2196                     cmd2.CommandText = "COMMIT";
2197                     cmd2.ExecuteNonQuery();
2198
2199                     Console.WriteLine("  Call stored function SF_TESTOUTPARM6 with 4 parameters...");
2200                     OracleCommand cmd3 = con.CreateCommand();
2201                     cmd3.CommandType = CommandType.Text;
2202                     cmd3.CommandText =
2203                         "BEGIN " +
2204                         "       :returnValue := SF_TESTOUTPARM6(:p1, :p2, :p3);" +
2205                         "END;";
2206                     OracleParameter myParameter1 = new OracleParameter("p1", OracleType.Timestamp);
2207                     myParameter1.Value = new DateTime(2004, 12, 15);
2208                     myParameter1.Direction = ParameterDirection.Input;
2209
2210                     OracleParameter myParameter2 = new OracleParameter("p2", OracleType.Timestamp);
2211                     myParameter2.Direction = ParameterDirection.Output;
2212
2213                     OracleParameter myParameter3 = new OracleParameter("p3", OracleType.Timestamp);
2214                     myParameter3.Value = new DateTime(2008, 10, 14, 20, 21, 22);
2215                     myParameter3.Direction = ParameterDirection.InputOutput;
2216
2217                     OracleParameter myParameter4 = new OracleParameter("returnValue", OracleType.Timestamp);
2218                     myParameter4.Direction = ParameterDirection.ReturnValue;
2219
2220                     cmd3.Parameters.Add(myParameter1);
2221                     cmd3.Parameters.Add(myParameter2);
2222                     cmd3.Parameters.Add(myParameter3);
2223                     cmd3.Parameters.Add(myParameter4);
2224
2225                     cmd3.ExecuteNonQuery();
2226                     DateTime outValue = (DateTime)myParameter2.Value;
2227                     DateTime inOutValue = (DateTime)myParameter3.Value;
2228                     DateTime returnValue = (DateTime)myParameter4.Value;
2229                     Console.WriteLine("    1Out Value should be: 2004-12-18 00:00:00");
2230                     Console.WriteLine("    1Out Value: {0}", outValue.ToString("yyyy-MM-dd HH:mm:ss"));
2231                     Console.WriteLine("    1InOut Value should be: 2008-10-19 20:21:22");
2232                     Console.WriteLine("    1InOut Value: {0}", inOutValue.ToString("yyyy-MM-dd HH:mm:ss"));
2233                     Console.WriteLine("    1Return Value should be: 2001-07-01 15:32:52");
2234                     Console.WriteLine("    1Return Value: {0}", returnValue.ToString("yyyy-MM-dd HH:mm:ss"));
2235                     Console.WriteLine();
2236
2237                     myParameter1.Value = DBNull.Value;
2238                     myParameter3.Value = new DateTime(1980, 11, 22);
2239                     cmd3.ExecuteNonQuery();
2240                     outValue = (DateTime)myParameter2.Value;
2241                     inOutValue = (DateTime)myParameter3.Value;
2242                     returnValue = (DateTime)myParameter4.Value;
2243                     Console.WriteLine("    2Out Value should be: 1900-12-31 00:00:00");
2244                     Console.WriteLine("    2Out Value: {0}", outValue.ToString("yyyy-MM-dd HH:mm:ss"));
2245                     Console.WriteLine("    2InOut Value should be: 1900-12-31 00:00:00");
2246                     Console.WriteLine("    2InOut Value: {0}", inOutValue.ToString("yyyy-MM-dd HH:mm:ss"));
2247                     Console.WriteLine("    2Return Value should be: 2001-07-01 15:32:52");
2248                     Console.WriteLine("    2Return Value: {0}", returnValue.ToString("yyyy-MM-dd HH:mm:ss"));
2249                     Console.WriteLine();
2250
2251                     myParameter1.Value = new DateTime(1979, 11, 25);
2252                     myParameter3.Value = new DateTime(1981, 12, 14);
2253                     cmd3.ExecuteNonQuery();
2254                     string soutValue = "";
2255                     string sinOutValue = "";
2256                     string sreturnValue = "";
2257                     if (myParameter2.Value == DBNull.Value)
2258                         soutValue = "DBNull.Value";
2259                     else {
2260                         outValue = (DateTime)myParameter2.Value;
2261                         soutValue = outValue.ToString("yyyy-MM-dd HH:mm:ss");
2262                     }
2263                     if (myParameter3.Value == DBNull.Value)
2264                         sinOutValue = "DBNull.Value";
2265                     else {
2266                         inOutValue = (DateTime)myParameter3.Value;
2267                         sinOutValue = inOutValue.ToString("yyyy-MM-dd HH:mm:ss");
2268                     }
2269                     if (myParameter4.Value == DBNull.Value)
2270                         sreturnValue = "DBNull.Value";
2271                     else {
2272                         returnValue = (DateTime)myParameter4.Value;
2273                         sreturnValue = returnValue.ToString("yyyy-MM-dd HH:mm:ss");
2274                     }
2275                     Console.WriteLine("    3Out Value should be: DBNull.Value");
2276                     Console.WriteLine("    3Out Value: {0}", soutValue);
2277                     Console.WriteLine("    3InOut Value should be: DBNull.Value");
2278                     Console.WriteLine("    3InOut Value: {0}", sinOutValue);
2279                     Console.WriteLine("    3Return Value should be: DBNull.Value");
2280                     Console.WriteLine("    3Return Value: {0}", sreturnValue);
2281                     Console.WriteLine();
2282
2283                     myParameter1.Value = new DateTime(1976, 7, 4);
2284                     myParameter3.Value = DBNull.Value;
2285                     cmd3.ExecuteNonQuery();
2286                     outValue = (DateTime)myParameter2.Value;
2287                     inOutValue = (DateTime)myParameter3.Value;
2288                     returnValue = (DateTime)myParameter4.Value;
2289                     Console.WriteLine("    4Out Value should be: 2008-08-08 00:00:00");
2290                     Console.WriteLine("    4Out Value: {0}", outValue.ToString("yyyy-MM-dd HH:mm:ss"));
2291                     Console.WriteLine("    4InOut Value should be: 2000-01-01 00:00:00");
2292                     Console.WriteLine("    4InOut Value: {0}", inOutValue.ToString("yyyy-MM-dd HH:mm:ss"));
2293                     Console.WriteLine("    4Return Value should be: 2001-07-01 15:32:52");
2294                     Console.WriteLine("    4Return Value: {0}", returnValue.ToString("yyyy-MM-dd HH:mm:ss"));
2295                     Console.WriteLine();
2296
2297                 }
2298
2299                 static void ShowConnectionProperties (OracleConnection con)
2300                 {
2301                         try {
2302                                 Console.WriteLine ("ServerVersion: " + con.ServerVersion);
2303                         } catch (System.InvalidOperationException ioe) {\r
2304                                 Console.WriteLine ("InvalidOperationException caught.");\r
2305                                 Console.WriteLine ("Message: " + ioe.Message);\r
2306                         }
2307
2308                         Console.WriteLine ("DataSource: " + con.DataSource);
2309                 }
2310
2311                 static void NullAggregateTest (OracleConnection con)
2312                 {
2313                         Console.WriteLine("  Drop table MONO_TEST_TABLE3...");
2314                         OracleCommand cmd2 = con.CreateCommand ();
2315
2316                         try {
2317                                 cmd2.CommandText = "DROP TABLE MONO_TEST_TABLE3";
2318                                 cmd2.ExecuteNonQuery ();
2319                         }
2320                         catch(OracleException) {
2321                                 // ignore if table already exists
2322                         }
2323
2324                         Console.WriteLine("  Create table MONO_TEST_TABLE3...");
2325
2326                         cmd2.CommandText = "CREATE TABLE MONO_TEST_TABLE3 (" +
2327                                 " COL1 VARCHAR2(8), "+
2328                                 " COL2 VARCHAR2(32))";
2329
2330                         cmd2.ExecuteNonQuery ();
2331
2332                         Console.WriteLine("  Insert some rows into table MONO_TEST_TABLE3...");
2333                         cmd2.CommandText = "INSERT INTO MONO_TEST_TABLE3 (COL1, COL2) VALUES ('1','one')";
2334                         cmd2.ExecuteNonQuery ();
2335
2336                         cmd2.CommandText = "INSERT INTO MONO_TEST_TABLE3 (COL1, COL2) VALUES ('1','uno')";
2337                         cmd2.ExecuteNonQuery ();
2338
2339                         cmd2.CommandText = "INSERT INTO MONO_TEST_TABLE3 (COL1, COL2) VALUES ('3','three')";
2340                         cmd2.ExecuteNonQuery ();
2341
2342                         cmd2.CommandText = "INSERT INTO MONO_TEST_TABLE3 (COL1, COL2) VALUES ('3', null)";
2343                         cmd2.ExecuteNonQuery ();
2344
2345                         cmd2.CommandText = "INSERT INTO MONO_TEST_TABLE3 (COL1, COL2) VALUES ('3','few')";
2346                         cmd2.ExecuteNonQuery ();
2347
2348                         Console.WriteLine("  ExecuteScalar...");
2349                         cmd2.CommandText = "SELECT COL1, COUNT(COL2) AS MAX_COL1 FROM MONO_TEST_TABLE3 GROUP BY COL1";
2350                         OracleDataReader reader = cmd2.ExecuteReader ();
2351                         Console.WriteLine (" Read...");
2352                         while (reader.Read ()) {
2353
2354                                 object obj0 = reader.GetValue (0);
2355                                 Console.WriteLine("Value 0: " + obj0.ToString ());
2356                                 object obj1 = reader.GetValue (1);
2357                                 Console.WriteLine("Value 1: " + obj1.ToString ());
2358
2359                                 Console.WriteLine (" Read...");
2360                         }
2361
2362                         Console.WriteLine (" No more records.");
2363                 }
2364
2365                 static void OnInfoMessage (object sender, OracleInfoMessageEventArgs e) \r
2366                 {\r
2367                         Console.WriteLine("InfoMessage Message: " + e.Message.ToString());\r
2368                         Console.WriteLine("InfoMessage Code: " + e.Code.ToString());\r
2369                         Console.WriteLine("InfoMessage Source: " + e.Source.ToString());\r
2370                 }
2371
2372                 static void OnStateChange (object sender, StateChangeEventArgs e) \r
2373                 {\r
2374                         Console.WriteLine("StateChange CurrentSate:" + e.CurrentState.ToString ());\r
2375                         Console.WriteLine("StateChange OriginalState:" + e.OriginalState.ToString ());\r
2376                 }
2377
2378                 static void RefCursorTests(OracleConnection con) \r
2379                 {\r
2380                         SetupRefCursorTests(con); // for ref cursor tests 1 thru 3\r
2381                         RefCursorTest1(con); // using BEGIN/END\r
2382                         RefCursorTest2(con); // using call\r
2383                         RefCursorTest3(con); // using CommandType.StoredProcedure\r
2384                 \r
2385                         RefCursorTest4(con);\r
2386                 }\r
2387 \r
2388                 static void SetupRefCursorTests(OracleConnection con) \r
2389                 {\r
2390                         Console.WriteLine("Setup Oracle package curspkg_join...");\r
2391                 \r
2392                         OracleCommand cmd = con.CreateCommand();\r
2393
2394                         Console.Error.WriteLine("    create or replace package curspkg_join...");\r
2395                         cmd.CommandText = \r
2396                                 "CREATE OR REPLACE PACKAGE curspkg_join AS\n" +\r
2397                                 "TYPE t_cursor IS REF CURSOR;\n" +\r
2398                                 "Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor);\n" +\r
2399                                 "END curspkg_join;";\r
2400                         cmd.ExecuteNonQuery();
2401 \r
2402                         Console.Error.WriteLine("    create or replace package body curspkg_join...");
2403                         cmd.CommandText = \r
2404                                 "CREATE OR REPLACE PACKAGE BODY curspkg_join AS\n" +\r
2405                                 "   Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor)\n" +\r
2406                                 "   IS\n" +\r
2407                                 "        v_cursor t_cursor;\n" +\r
2408                                 "   BEGIN\n" +\r
2409                                 "        IF n_EMPNO <> 0 THEN\n" +\r
2410                                 "             OPEN v_cursor FOR\n" +\r
2411                                 "             SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME\n" +\r
2412                                 "                  FROM SCOTT.EMP, SCOTT.DEPT\n" +\r
2413                                 "                  WHERE EMP.DEPTNO = DEPT.DEPTNO\n" +\r
2414                                 "                  AND EMP.EMPNO = n_EMPNO;\n" +\r
2415                                 "\n" +\r
2416                                 "        ELSE\n" +\r
2417                                 "             OPEN v_cursor FOR\n" +\r
2418                                 "             SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME\n" +\r
2419                                 "                  FROM SCOTT.EMP, SCOTT.DEPT\n" +\r
2420                                 "                  WHERE EMP.DEPTNO = DEPT.DEPTNO;\n" +\r
2421                                 "\n" +\r
2422                                 "        END IF;\n" +\r
2423                                 "        io_cursor := v_cursor;\n" +\r
2424                                 "   END open_join_cursor1;\n" +\r
2425                                 "END curspkg_join;";\r
2426                         cmd.ExecuteNonQuery();
2427
2428                         cmd.CommandText = "commit";\r
2429                         cmd.ExecuteNonQuery();\r
2430                 }\r
2431 \r
2432                 public static void RefCursorTest4(OracleConnection connection) \r
2433                 {\r
2434                         Console.WriteLine("Setup test package and data for RefCursorTest4...");\r
2435                         OracleCommand cmddrop = connection.CreateCommand();\r
2436 \r
2437                         cmddrop.CommandText = "DROP TABLE TESTTABLE";\r
2438                         try { \r
2439                                 cmddrop.ExecuteNonQuery(); \r
2440                         } \r
2441                         catch(OracleException e) {\r
2442                                 Console.WriteLine("Ignore this error: " + e.Message); \r
2443                         }\r
2444                         cmddrop.Dispose();\r
2445                         cmddrop = null;\r
2446 \r
2447                         OracleCommand cmd = connection.CreateCommand();\r
2448 \r
2449                         // create table TESTTABLE\r
2450                         cmd.CommandText = \r
2451                                 "create table TESTTABLE (\n" +\r
2452                                 " col1 numeric(18,0),\n" +\r
2453                                 " col2 char(32),\n" +\r
2454                                 " col3 date)";\r
2455                         cmd.ExecuteNonQuery();\r
2456 \r
2457                         // insert some rows into TESTTABLE\r
2458                         cmd.CommandText = \r
2459                                 "insert into TESTTABLE\n" +\r
2460                                 "(col1, col2, col3)\n" +\r
2461                                 "values(45, 'Mono', sysdate)";\r
2462                         cmd.ExecuteNonQuery();\r
2463 \r
2464                         cmd.CommandText = \r
2465                                 "insert into TESTTABLE\n" +\r
2466                                 "(col1, col2, col3)\n" +\r
2467                                 "values(136, 'Fun', sysdate)";\r
2468                         cmd.ExecuteNonQuery();\r
2469 \r
2470                         cmd.CommandText = \r
2471                                 "insert into TESTTABLE\n" +\r
2472                                 "(col1, col2, col3)\n" +\r
2473                                 "values(526, 'System.Data.OracleClient', sysdate)";\r
2474                         cmd.ExecuteNonQuery();\r
2475 \r
2476                         cmd.CommandText = "commit";\r
2477                         cmd.ExecuteNonQuery();\r
2478 \r
2479                         // create Oracle package TestTablePkg\r
2480                         cmd.CommandText = \r
2481                                 "CREATE OR REPLACE PACKAGE TestTablePkg\n" +\r
2482                                 "AS\n" +\r
2483                                 "       TYPE T_CURSOR IS REF CURSOR;\n" +\r
2484                                 "\n" +\r
2485                                 "       PROCEDURE GetData(tableCursor OUT T_CURSOR);\n" +\r
2486                                 "END TestTablePkg;";\r
2487                         cmd.ExecuteNonQuery();\r
2488 \r
2489                         // create Oracle package body for package TestTablePkg\r
2490                         cmd.CommandText = \r
2491                                 "CREATE OR REPLACE PACKAGE BODY TestTablePkg AS\n" +\r
2492                                 "  PROCEDURE GetData(tableCursor OUT T_CURSOR)\n" +\r
2493                                 "  IS\n" +\r
2494                                 "  BEGIN\n" +\r
2495                                 "    OPEN tableCursor FOR\n" +\r
2496                                 "    SELECT *\n" +\r
2497                                 "    FROM TestTable;\n" +\r
2498                                 "  END GetData;\n" +\r
2499                                 "END TestTablePkg;";\r
2500                         cmd.ExecuteNonQuery();\r
2501 \r
2502                         cmd.Dispose();\r
2503                         cmd = null;\r
2504 \r
2505                         Console.WriteLine("Set up command and parameters to call stored proc...");\r
2506                         OracleCommand command = new OracleCommand("TestTablePkg.GetData", connection);\r
2507                         command.CommandType = CommandType.StoredProcedure;\r
2508                         OracleParameter parameter = new OracleParameter("tableCursor", OracleType.Cursor);\r
2509                         parameter.Direction = ParameterDirection.Output;\r
2510                         command.Parameters.Add(parameter);\r
2511 \r
2512                         Console.WriteLine("Execute...");\r
2513                         command.ExecuteNonQuery();\r
2514 \r
2515                         Console.WriteLine("Get OracleDataReader for cursor output parameter...");\r
2516                         OracleDataReader reader = (OracleDataReader) parameter.Value;\r
2517                         \r
2518                         Console.WriteLine("Read data...");\r
2519                         int r = 0;\r
2520                         while (reader.Read()) {\r
2521                                 Console.WriteLine("Row {0}", r);\r
2522                                 for (int f = 0; f < reader.FieldCount; f ++) {\r
2523                                         object val = reader.GetValue(f);\r
2524                                         Console.WriteLine("    Field {0} Value: {1}", f, val.ToString());\r
2525                                 }\r
2526                                 r ++;\r
2527                         }\r
2528                         Console.WriteLine("Rows retrieved: {0}", r);\r
2529 \r
2530                         Console.WriteLine("Clean up...");\r
2531                         reader.Close();\r
2532                         reader = null;\r
2533                         command.Dispose();\r
2534                         command = null;\r
2535                 }\r
2536 \r
2537                 static void RefCursorTest1(OracleConnection con) \r
2538                 {\r
2539                         Console.WriteLine("Ref Cursor Test 1 - using BEGIN/END for proc - Begin...");\r
2540 \r
2541                         Console.WriteLine("Create command...");\r
2542                         OracleCommand cmd = new OracleCommand();\r
2543                         cmd.Connection = con;\r
2544 \r
2545                         cmd.CommandText = \r
2546                                 "BEGIN\n" +\r
2547                                 "       curspkg_join.open_join_cursor1(:n_Empno,:io_cursor);\n" +\r
2548                                 "END;";\r
2549                 \r
2550                         // PL/SQL definition of stored procedure in package curspkg_join\r
2551                         // open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor)\r
2552 \r
2553                         Console.WriteLine("Create parameters...");\r
2554 \r
2555                         OracleParameter parm1 = new OracleParameter("n_Empno", OracleType.Number);\r
2556                         parm1.Direction = ParameterDirection.Input;\r
2557                         parm1.Value = 7902;\r
2558 \r
2559                         OracleParameter parm2 = new OracleParameter("io_cursor", OracleType.Cursor);\r
2560                         parm2.Direction = ParameterDirection.Output;\r
2561 \r
2562                         cmd.Parameters.Add(parm1);\r
2563                         cmd.Parameters.Add(parm2);\r
2564 \r
2565                         // positional parm\r
2566                         //cmd.Parameters.Add(new OracleParameter("io_cursor", OracleType.Cursor)).Direction = ParameterDirection.Output;\r
2567                         // named parm\r
2568                         //cmd.Parameters.Add("n_Empno", OracleType.Number, 4).Value = 7902;\r
2569 \r
2570                         OracleDataReader reader;\r
2571                         Console.WriteLine("Execute Non Query...");\r
2572                         cmd.ExecuteNonQuery();\r
2573 \r
2574                         Console.WriteLine("Get data reader (ref cursor) from out parameter...");\r
2575                         reader = (OracleDataReader) cmd.Parameters["io_cursor"].Value;\r
2576 \r
2577                         int x, count;\r
2578                         count = 0;\r
2579 \r
2580                         Console.WriteLine("Get data from ref cursor...");\r
2581                         while (reader.Read()) {\r
2582                                 for (x = 0; x < reader.FieldCount; x++) \r
2583                                         Console.Write(reader[x] + " ");\r
2584                         \r
2585                                 Console.WriteLine();\r
2586                                 count += 1;\r
2587                         }\r
2588                         Console.WriteLine(count.ToString() + " Rows Returned.");\r
2589 \r
2590                         reader.Close();\r
2591                 }\r
2592 \r
2593                 static void RefCursorTest2(OracleConnection con) \r
2594                 {\r
2595                         Console.WriteLine("Ref Cursor Test 2 - using call - Begin...");\r
2596 \r
2597                         Console.WriteLine("Create command...");\r
2598                         OracleCommand cmd = new OracleCommand();\r
2599                         cmd.Connection = con;\r
2600                         cmd.CommandText = "call curspkg_join.open_join_cursor1(:n_Empno,:io_cursor)";\r
2601                 \r
2602                         // PL/SQL definition of stored procedure in package curspkg_join\r
2603                         // open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor)\r
2604 \r
2605                         Console.WriteLine("Create parameters...");\r
2606 \r
2607                         OracleParameter parm1 = new OracleParameter("n_Empno", OracleType.Number);\r
2608                         parm1.Direction = ParameterDirection.Input;\r
2609                         parm1.Value = 7902;\r
2610 \r
2611                         OracleParameter parm2 = new OracleParameter("io_cursor", OracleType.Cursor);\r
2612                         parm2.Direction = ParameterDirection.Output;\r
2613 \r
2614                         cmd.Parameters.Add(parm1);\r
2615                         cmd.Parameters.Add(parm2);\r
2616 \r
2617                         // positional parm\r
2618                         //cmd.Parameters.Add(new OracleParameter("io_cursor", OracleType.Cursor)).Direction = ParameterDirection.Output;\r
2619                         // named parm\r
2620                         //cmd.Parameters.Add("n_Empno", OracleType.Number, 4).Value = 7902;\r
2621 \r
2622                         OracleDataReader reader;\r
2623                         Console.WriteLine("Execute Non Query...");\r
2624                         cmd.ExecuteNonQuery();\r
2625 \r
2626                         Console.WriteLine("Get data reader (ref cursor) from out parameter...");\r
2627                         reader = (OracleDataReader) cmd.Parameters["io_cursor"].Value;\r
2628 \r
2629                         int x, count;\r
2630                         count = 0;\r
2631 \r
2632                         Console.WriteLine("Get data from ref cursor...");\r
2633                         while (reader.Read()) {\r
2634                                 for (x = 0; x < reader.FieldCount; x++) \r
2635                                         Console.Write(reader[x] + " ");\r
2636                         \r
2637                                 Console.WriteLine();\r
2638                                 count += 1;\r
2639                         }\r
2640                         Console.WriteLine(count.ToString() + " Rows Returned.");\r
2641 \r
2642                         reader.Close();\r
2643                 }\r
2644 \r
2645                 static void RefCursorTest3(OracleConnection con) \r
2646                 {\r
2647                         Console.WriteLine("Ref Cursor Test 3 - CommandType.StoredProcedure - Begin...");\r
2648 \r
2649                         Console.WriteLine("Create command...");\r
2650                         OracleCommand cmd = new OracleCommand();\r
2651                         cmd.Connection = con;\r
2652                         cmd.CommandText = "curspkg_join.open_join_cursor1";\r
2653                         cmd.CommandType = CommandType.StoredProcedure;\r
2654                 \r
2655                         // PL/SQL definition of stored procedure in package curspkg_join\r
2656                         // open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor)\r
2657 \r
2658                         Console.WriteLine("Create parameters...");\r
2659 \r
2660                         OracleParameter parm1 = new OracleParameter("n_Empno", OracleType.Number);\r
2661                         parm1.Direction = ParameterDirection.Input;\r
2662                         parm1.Value = 7902;\r
2663 \r
2664                         OracleParameter parm2 = new OracleParameter("io_cursor", OracleType.Cursor);\r
2665                         parm2.Direction = ParameterDirection.Output;\r
2666 \r
2667                         cmd.Parameters.Add(parm1);\r
2668                         cmd.Parameters.Add(parm2);\r
2669 \r
2670                         // positional parm\r
2671                         //cmd.Parameters.Add(new OracleParameter("io_cursor", OracleType.Cursor)).Direction = ParameterDirection.Output;\r
2672                         // named parm\r
2673                         //cmd.Parameters.Add("n_Empno", OracleType.Number, 4).Value = 7902;\r
2674 \r
2675                         OracleDataReader reader;\r
2676                         Console.WriteLine("Execute Non Query...");\r
2677                         cmd.ExecuteNonQuery();\r
2678 \r
2679                         Console.WriteLine("Get data reader (ref cursor) from out parameter...");\r
2680                         reader = (OracleDataReader) cmd.Parameters["io_cursor"].Value;\r
2681 \r
2682                         int x, count;\r
2683                         count = 0;\r
2684 \r
2685                         Console.WriteLine("Get data from ref cursor...");\r
2686                         while (reader.Read()) {\r
2687                                 for (x = 0; x < reader.FieldCount; x++) \r
2688                                         Console.Write(reader[x] + " ");\r
2689                         \r
2690                                 Console.WriteLine();\r
2691                                 count += 1;\r
2692                         }\r
2693                         Console.WriteLine(count.ToString() + " Rows Returned.");\r
2694 \r
2695                         reader.Close();\r
2696                 }\r
2697 \r
2698                 static void ExternalAuthenticationTest () \r
2699                 {\r
2700                         string user = Environment.UserName;\r
2701                         if (!Environment.UserDomainName.Equals(String.Empty))\r
2702                                 user = Environment.UserDomainName + "\\" + Environment.UserName;\r
2703                         Console.WriteLine("Environment UserDomainName and UserName: " + user);\r
2704                         Console.WriteLine("Open connection using external authentication...");\r
2705                         OracleConnection con = new OracleConnection("Data Source=palis;Integrated Security=true");\r
2706                         try {\r
2707                                 con.Open();\r
2708                                 OracleCommand cmd = con.CreateCommand();\r
2709                                 cmd.CommandText = "SELECT USER FROM DUAL";\r
2710                                 OracleDataReader reader = cmd.ExecuteReader();\r
2711                                 if (reader.Read())\r
2712                                         Console.WriteLine("User: " + reader.GetString(reader.GetOrdinal("USER")));\r
2713                                 con.Close();\r
2714                         }\r
2715                         catch (Exception e) {\r
2716                                 Console.WriteLine("Exception caught: " + e.Message);\r
2717                                 Console.WriteLine("Probably not setup for external authentication.");\r
2718                         }\r
2719                         con.Dispose();\r
2720                         con = null;\r
2721                 }\r
2722 \r
2723                 public static void TestPersistSucurityInfo1() \r
2724                 {\r
2725                         Console.WriteLine("\nTestPersistSucurityInfo1 - persist security info=false");\r
2726                         OracleConnection con = new OracleConnection("data source=palis;user id=scott;password=tiger;persist security info=false");\r
2727                         Console.WriteLine("ConnectionString before open: " + con.ConnectionString);\r
2728                         con.Open();\r
2729                         Console.WriteLine("ConnectionString after open: " + con.ConnectionString);\r
2730                         con.Close();\r
2731                         Console.WriteLine("ConnectionString after close: " + con.ConnectionString);\r
2732                         con = null;\r
2733                 }\r
2734 \r
2735                 public static void TestPersistSucurityInfo2() \r
2736                 {\r
2737                         Console.WriteLine("\nTestPersistSucurityInfo2 - persist security info=true");\r
2738                         OracleConnection con = new OracleConnection("data source=palis;user id=scott;password=tiger;persist security info=true");\r
2739                         Console.WriteLine("ConnectionString before open: " + con.ConnectionString);\r
2740                         con.Open();\r
2741                         Console.WriteLine("ConnectionString after open: " + con.ConnectionString);\r
2742                         con.Close();\r
2743                         Console.WriteLine("ConnectionString after close: " + con.ConnectionString);\r
2744                         con = null;\r
2745                 }\r
2746 \r
2747                 public static void TestPersistSucurityInfo3() \r
2748                 {\r
2749                         Console.WriteLine("\nTestPersistSucurityInfo3 - use default for persist security info which is false");\r
2750                         OracleConnection con = new OracleConnection("data source=palis;user id=scott;password=tiger");\r
2751                         Console.WriteLine("ConnectionString before open: " + con.ConnectionString);\r
2752                         con.Open();\r
2753                         Console.WriteLine("ConnectionString after open: " + con.ConnectionString);\r
2754                         con.Close();\r
2755                         Console.WriteLine("ConnectionString after close: " + con.ConnectionString);\r
2756                         con = null;\r
2757                 }\r
2758 \r
2759                 public static void TestPersistSucurityInfo4() \r
2760                 {\r
2761                         Console.WriteLine("\nTestPersistSucurityInfo4 - persist security info=false with password at front");\r
2762                         OracleConnection con = new OracleConnection(";password=tiger;data source=palis;user id=scott;persist security info=false");\r
2763                         Console.WriteLine("ConnectionString before open: " + con.ConnectionString);\r
2764                         con.Open();\r
2765                         Console.WriteLine("ConnectionString after open: " + con.ConnectionString);\r
2766                         con.Close();\r
2767                         Console.WriteLine("ConnectionString after close: " + con.ConnectionString);\r
2768                         con = null;\r
2769                 }\r
2770 \r
2771                 public static void TestPersistSucurityInfo5() \r
2772                 {\r
2773                         Console.WriteLine("\nTestPersistSucurityInfo5 - persist security info=false");\r
2774                         OracleConnection con = new OracleConnection("data source=palis;user id=scott;password=tiger;persist security info=false");\r
2775                         Console.WriteLine("ConnectionString before open: " + con.ConnectionString);\r
2776                         con.Open();\r
2777                         Console.WriteLine("ConnectionString after open: " + con.ConnectionString);\r
2778                         Console.WriteLine("ConnectionState for con: " + con.State.ToString() + "\n");\r
2779                 \r
2780                         Console.WriteLine("Clone OracleConnection...");\r
2781                         OracleConnection con2 = (OracleConnection) ((ICloneable) con).Clone();\r
2782                 \r
2783                         Console.WriteLine("ConnectionState for con2: " + con2.State.ToString());\r
2784                         Console.WriteLine("con2 ConnectionString before open: " + con2.ConnectionString);\r
2785                         con2.Open();\r
2786                         Console.WriteLine("con2 ConnectionString after open: " + con2.ConnectionString);\r
2787                         con2.Close();\r
2788                         Console.WriteLine("con2 ConnectionString after close: " + con2.ConnectionString);\r
2789                 \r
2790                         con.Close();\r
2791                 }\r
2792 \r
2793                 public static void TestPersistSucurityInfo6() \r
2794                 {\r
2795                         Console.WriteLine("\nTestPersistSucurityInfo6 - external auth using persist security info");\r
2796 \r
2797                         string user = Environment.UserName;\r
2798                         if (!Environment.UserDomainName.Equals(String.Empty))\r
2799                                 user = Environment.UserDomainName + "\\" + Environment.UserName;\r
2800                         Console.WriteLine("Environment UserDomainName and UserName: " + user);\r
2801                         Console.WriteLine("Open connection using external authentication...");\r
2802                         OracleConnection con = new OracleConnection("Data Source=palis;Integrated Security=true");\r
2803                         Console.WriteLine("ConnectionString before open: " + con.ConnectionString);\r
2804                         try {\r
2805                                 con.Open();\r
2806                                 OracleCommand cmd = con.CreateCommand();\r
2807                                 cmd.CommandText = "SELECT USER FROM DUAL";\r
2808                                 OracleDataReader reader = cmd.ExecuteReader();\r
2809                                 if (reader.Read())\r
2810                                         Console.WriteLine("User: " + reader.GetString(reader.GetOrdinal("USER")));\r
2811                                 con.Close();\r
2812                                 Console.WriteLine("ConnectionString after close: " + con.ConnectionString);\r
2813                         }\r
2814                         catch (Exception e) {\r
2815                                 Console.WriteLine("Exception caught: " + e.Message);\r
2816                                 Console.WriteLine("Probably not setup for external authentication. This is fine.");\r
2817                         }\r
2818                         con.Dispose();\r
2819                         Console.WriteLine("ConnectionString after dispose: " + con.ConnectionString);\r
2820                         con = null;\r
2821                         Console.WriteLine("\n\n");\r
2822                 }\r
2823
2824                 public static void ConnectionPoolingTest1 () \r
2825                 {\r
2826                         Console.WriteLine("Start Connection Pooling Test 1...");\r
2827                         OracleConnection[] connections = null;\r
2828                         int maxCon = MAX_CONNECTIONS + 1; // add 1 more over the max connections to cause it to wait for the next available connection\r
2829                         int i = 0;\r
2830 \r
2831                         try {\r
2832                                 connections = new OracleConnection[maxCon];                     \r
2833                 \r
2834                                 for (i = 0; i < maxCon; i++) {\r
2835                                         Console.WriteLine("   Open connection: {0}", i);\r
2836                                         connections[i] = new OracleConnection(conStr);\r
2837                                         connections[i].Open ();\r
2838                                 }\r
2839                         } catch (InvalidOperationException e) {\r
2840                                 Console.WriteLine("Expected exception InvalidOperationException caught.");\r
2841                                 Console.WriteLine(e);\r
2842                         }\r
2843 \r
2844                         for (i = 0; i < maxCon; i++) {\r
2845                                 if (connections[i] != null) {\r
2846                                         Console.WriteLine("   Close connection: {0}", i);\r
2847                                         if (connections[i].State == ConnectionState.Open)\r
2848                                                 connections[i].Close ();\r
2849                                         connections[i].Dispose ();\r
2850                                         connections[i] = null;\r
2851                                 }\r
2852                         }\r
2853 \r
2854                         connections = null;\r
2855 \r
2856                         Console.WriteLine("Done Connection Pooling Test 1.");\r
2857                 }\r
2858 \r
2859                 public static void ConnectionPoolingTest2 () \r
2860                 {\r
2861                         Console.WriteLine("Start Connection Pooling Test 2...");\r
2862                         OracleConnection[] connections = null;\r
2863                         int maxCon = MAX_CONNECTIONS;\r
2864                         int i = 0;\r
2865 \r
2866                         connections = new OracleConnection[maxCon];                     \r
2867                 \r
2868                         for (i = 0; i < maxCon; i++) {\r
2869                                 Console.WriteLine("   Open connection: {0}", i);\r
2870                                 connections[i] = new OracleConnection(conStr);\r
2871                                 connections[i].Open ();\r
2872                         }\r
2873                 \r
2874                         Console.WriteLine("Start another thread...");\r
2875                         t = new Thread(new ThreadStart(AnotherThreadProc));\r
2876                         t.Start ();\r
2877 \r
2878                         Console.WriteLine("Sleep...");\r
2879                         Thread.Sleep(100);\r
2880 \r
2881                         Console.WriteLine("Closing...");\r
2882                         for (i = 0; i < maxCon; i++) {\r
2883                                 if (connections[i] != null) {\r
2884                                         Console.WriteLine("   Close connection: {0}", i);\r
2885                                         if (connections[i].State == ConnectionState.Open)\r
2886                                                 connections[i].Close ();\r
2887                                         connections[i].Dispose ();\r
2888                                         connections[i] = null;\r
2889                                 }\r
2890                         }\r
2891 \r
2892                         connections = null;\r
2893                 }\r
2894 \r
2895                 private static void AnotherThreadProc () \r
2896                 {\r
2897                         Console.WriteLine("Open connection via another thread...");\r
2898                         OracleConnection[] connections = null;\r
2899                         int maxCon = MAX_CONNECTIONS; \r
2900                         int i = 0;\r
2901 \r
2902                         connections = new OracleConnection[maxCon];                     \r
2903                 \r
2904                         for (i = 0; i < maxCon; i++) {\r
2905                                 Console.WriteLine("   Open connection: {0}", i);\r
2906                                 connections[i] = new OracleConnection(conStr);\r
2907                                 connections[i].Open ();\r
2908                         }\r
2909 \r
2910                         Console.WriteLine("Done Connection Pooling Test 2.");\r
2911                         System.Environment.Exit (0);\r
2912                 }
2913
2914                 private static void SetParameterOracleType (OracleConnection con)
2915                 {
2916                         Console.WriteLine();
2917                         OracleParameter p = con.CreateCommand().CreateParameter();
2918                         Console.WriteLine("p.OracleType [VarChar]: " + p.OracleType.ToString());
2919                         p.OracleType = OracleType.Clob;
2920                         Console.WriteLine("p.OracleType [Clob]: " + p.OracleType.ToString());
2921                         p.Value = "SomeString";
2922                         Console.WriteLine("p.OracleType [Clob]: " + p.OracleType.ToString());
2923                         Console.WriteLine();
2924
2925                         OracleParameter p2 = con.CreateCommand().CreateParameter();
2926                         Console.WriteLine("p2.OracleType [VarChar]: " + p2.OracleType.ToString());
2927                         p2.Value = new byte[] { 0x01, 0x02, 0x03, 0x04 };
2928                         Console.WriteLine("p2.OracleType [VarChar]: " + p2.OracleType.ToString());
2929                         p2.OracleType = OracleType.Blob;
2930                         Console.WriteLine("p2.OracleType [Blob]: " + p2.OracleType.ToString());
2931                         Console.WriteLine();
2932
2933                         OracleParameter p3 = new OracleParameter("test", OracleType.Clob);
2934                         Console.WriteLine("p3.OracleType [Clob]: " + p3.OracleType.ToString());
2935                         p3.Value = "blah";
2936                         Console.WriteLine("p3.OracleType [Clob]: " + p3.OracleType.ToString());
2937                         Console.WriteLine();
2938
2939                         OracleParameter p4 = new OracleParameter("test", "blah");
2940                         Console.WriteLine("p4.OracleType [VarChar]: " + p4.OracleType.ToString());
2941                         p4.OracleType = OracleType.Clob;
2942                         Console.WriteLine("p4.OracleType [Clob]: " + p4.OracleType.ToString());
2943                         Console.WriteLine();
2944
2945                         OracleParameter p5 = new OracleParameter ((string) null, new DateTime (2005, 3, 8));
2946                         Console.WriteLine("p5.OracleType [DateTime]: " + p5.OracleType.ToString());
2947                 }
2948
2949                 public static void InsertBlobTest(OracleConnection con)
2950                 {
2951                         checkTNS();
2952                         SetupMyPackage(con);
2953                         InsertBlob(con);
2954                 }
2955
2956                 public static void checkTNS()
2957                 {
2958                         //string tnsAdmin = System.Environment.GetEnvironmentVariable("TNS_ADMIN");
2959                         //if ( (tnsAdmin == null)|| (string.Empty.Equals(tnsAdmin)) )
2960                         //{
2961                         //      System.Environment.SetEnvironmentVariable("TNS_ADMIN", "~/instantclient");
2962                         //}
2963                 }
2964
2965                 public static decimal InsertBlob(OracleConnection con)
2966                 {
2967                         byte[] ByteArray = new byte[2000]; // test Blob data
2968                         byte j = 0;
2969                         for (int i = 0; i < ByteArray.Length; i++) {
2970                                 ByteArray[i] = j;
2971                                 if (j > 255)
2972                                         j = 0;
2973                                 j++;
2974                         }
2975                         Console.WriteLine("Test Blob Data beginning: " + GetHexString (ByteArray));
2976
2977                         decimal retVal = -1;
2978
2979                         string sproc = "MyPackage" + ".InsertBlob";
2980
2981                         OracleCommand cmd = new OracleCommand();
2982                         cmd.CommandText = sproc;
2983                         cmd.CommandType = CommandType.StoredProcedure;
2984                         cmd.Connection = con;
2985                         //cmd.Connection.Open();
2986                         cmd.Transaction = cmd.Connection.BeginTransaction();
2987
2988                         try {
2989                                 OracleParameter p1 = new OracleParameter("i_Sig_File", OracleType.Blob);
2990                                 p1.Direction = ParameterDirection.Input;
2991
2992                                 //EXCEPTION thrown here
2993                                 //p1.Value = GetOracleLob(cmd.Transaction, ByteArray);
2994                                 OracleLob lob2 = GetOracleLob(cmd.Transaction, ByteArray);
2995                                 byte[] b2 = (byte[]) lob2.Value;
2996                                 Console.WriteLine("Test Blob Data here: " + GetHexString (b2));
2997                                 p1.Value = lob2.Value;
2998                                 //p1.Value = ByteArray;
2999
3000                                 cmd.Parameters.Add(p1);
3001
3002                                 cmd.ExecuteNonQuery();
3003
3004                                 cmd.Transaction.Commit();
3005
3006                                 OracleCommand select = con.CreateCommand ();
3007                                 //select.Transaction = transaction;
3008                                 select.CommandText = "SELECT BLOB_COLUMN FROM BLOBTEST2";
3009                                 Console.WriteLine ("  SELECTING A BLOB (Binary) VALUE FROM BLOBTEST2");
3010
3011                                 OracleDataReader reader = select.ExecuteReader ();
3012                                 if (!reader.Read ())
3013                                         Console.WriteLine ("ERROR: RECORD NOT FOUND");
3014
3015                                 Console.WriteLine ("  TESTING OracleLob OBJECT ...");
3016                                 if (reader.IsDBNull(0))
3017                                         Console.WriteLine("Lob IsNull");
3018                                 else {
3019                                         OracleLob lob = reader.GetOracleLob (0);
3020                                         if (lob == OracleLob.Null)
3021                                                 Console.WriteLine("Lob is OracleLob.Null");
3022                                         else {
3023                                                 byte[] blob = (byte[]) lob.Value;
3024                                                 string result = GetHexString(blob);
3025                                                 Console.WriteLine("Blob result: " + result);
3026                                                 if (ByteArrayCompare (ByteArray, blob))
3027                                                         Console.WriteLine("ByteArray and blob are the same: good");
3028                                                 else
3029                                                         Console.WriteLine("ByteArray and blob are not the same: bad");
3030                                         }
3031                                 }
3032                     }
3033                     catch(Exception ex) {
3034                         Console.WriteLine("I exploded:" + ex.ToString());
3035                         cmd.Transaction.Rollback();
3036
3037                     }
3038
3039                     return retVal;
3040
3041                 }
3042
3043                 private static OracleLob GetOracleLob(OracleTransaction transaction, byte[] blob)
3044                 {
3045                     string BLOB_CREATE = "DECLARE dpBlob BLOB; "
3046                     + "BEGIN "
3047                     + "   DBMS_LOB.CREATETEMPORARY(dpBlob , False, 0); "
3048                     + "  :tempBlob := dpBlob; "
3049                     + "END;";
3050
3051                     OracleLob tempLob = OracleLob.Null;
3052                     if (blob != null)
3053                     {
3054                         // Create a new command using the same connection
3055                         OracleCommand command = transaction.Connection.CreateCommand();
3056
3057                         // Assign the transaction to the command
3058                         command.Transaction = transaction;
3059
3060                         // Create blob storage on the Oracle server
3061                         command.CommandText = BLOB_CREATE;
3062
3063                         // Add a new output paramter to accept the blob storage reference
3064                         OracleParameter parm = new OracleParameter("tempBlob", OracleType.Blob);
3065                         parm.Direction = ParameterDirection.Output;
3066                         command.Parameters.Add(parm);
3067 //                      command.Parameters.Add(
3068 //                              new OracleParameter("tempBlob", OracleType.Blob)).Direction =
3069 //                                      ParameterDirection.Output;
3070
3071                         // Fire as your guns bear...
3072                         command.ExecuteNonQuery();
3073
3074                         // Retrieve the blob stream from the OracleLob parameter
3075                         //tempLob = (OracleLob)command.Parameters[0].Value;
3076                         tempLob = (OracleLob) parm.Value;
3077
3078                         // Prevent server side events from firing while we write to the stream
3079                         tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);
3080
3081                         // Write bytes to the stream
3082                         tempLob.Write(blob, 0, blob.Length);
3083
3084                         // Resume firing server events
3085                         tempLob.EndBatch();
3086                     }
3087
3088                     return tempLob;
3089                 }
3090
3091                 static void SetupMyPackage(OracleConnection con) \r
3092                 {\r
3093                         Console.WriteLine("Setup Oracle package curspkg_join...");\r
3094                 \r
3095                         Console.WriteLine ("  Drop table BLOBTEST2 ...");
3096                         try {
3097                                 OracleCommand cmd2 = con.CreateCommand ();
3098                                 //cmd2.Transaction = transaction;
3099                                 cmd2.CommandText = "DROP TABLE BLOBTEST2";
3100                                 cmd2.ExecuteNonQuery ();
3101                         }
3102                         catch (OracleException) {
3103                                 // ignore if table already exists
3104                         }
3105
3106                         Console.WriteLine ("  CREATE TABLE ...");
3107
3108                         OracleCommand create = con.CreateCommand ();
3109                         //create.Transaction = transaction;
3110                         create.CommandText = "CREATE TABLE BLOBTEST2 (BLOB_COLUMN BLOB)";
3111                         create.ExecuteNonQuery ();
3112
3113                         create.CommandText = "commit";\r
3114                         create.ExecuteNonQuery();\r
3115
3116                         Console.Error.WriteLine("    create or replace package MyPackage...");
3117                         OracleCommand cmd = con.CreateCommand();\r
3118                         cmd.CommandText = \r
3119                                 "CREATE OR REPLACE PACKAGE MyPackage AS\n" +\r
3120                                 " Procedure InsertBlob (i_Sig_File blob);\n" +\r
3121                                 "END MyPackage;";\r
3122                         cmd.ExecuteNonQuery();
3123 \r
3124                         Console.Error.WriteLine("    create or replace package body MyPackage...");
3125                         cmd.CommandText = \r
3126                                 "CREATE OR REPLACE PACKAGE BODY MyPackage AS\n" +\r
3127                                 "   Procedure InsertBlob (i_Sig_File blob)\n" +\r
3128                                 "   IS\n" +\r
3129                                 "   BEGIN\n" +
3130                                 "       INSERT INTO BLOBTEST2 (BLOB_COLUMN) VALUES(i_Sig_File); " +
3131                                 "   END InsertBlob; " +\r
3132                                 "END MyPackage;";\r
3133                         cmd.ExecuteNonQuery();
3134
3135                         cmd.CommandText = "commit";\r
3136                         cmd.ExecuteNonQuery();\r
3137                 }\r
3138
3139                 static byte[] ByteArrayCombine (byte[] b1, byte[] b2)
3140                 {
3141                         if (b1 == null)
3142                                 b1 = new byte[0];
3143                         if (b2 == null)
3144                                 b2 = new byte[0];
3145
3146                         byte[] bytes = new byte[b1.Length + b2.Length];
3147                         int i = 0;
3148                         for (int j = 0; j < b1.Length; j++) {
3149                                 bytes[i] = b1[j];
3150                                 i++;
3151                         }
3152                         for (int k = 0; k < b2.Length; k++) {
3153                                 bytes[i] = b2[k];
3154                                 i++;
3155                         }
3156                         return bytes;
3157                 }
3158
3159                 static bool ByteArrayCompare(byte[] ba1, byte[] ba2)
3160                 {
3161                     if (ba1 == null && ba2 == null)
3162                         return true;
3163
3164                     if (ba1 == null)
3165                         return false;
3166
3167                     if (ba2 == null)
3168                         return false;
3169
3170                     if (ba1.Length != ba2.Length)
3171                         return false;
3172
3173                    // for (int i = 0; i < ba1.Length; i++)
3174                    // {
3175                         //Console.WriteLine("i: " + i.ToString() + " ba1: " + ba1[i].ToString() + " ba2: " + ba2[i].ToString());
3176                     //}
3177
3178                     for (int i = 0; i < ba1.Length; i++)
3179                     {
3180                         if (ba1[i] != ba2[i])
3181                             return false;
3182                     }
3183
3184                     return true;
3185                 }
3186
3187                 [STAThread]
3188                 static void Main(string[] args)
3189                 {
3190                         if(args.Length != 3) {
3191                                 Console.WriteLine("Usage: mono TestOracleClient database userid password");
3192                                 return;
3193                         }
3194
3195                         string connectionString = String.Format(
3196                                 "Data Source={0};" +
3197                                 "User ID={1};" +
3198                                 "Password={2}",
3199                                 args[0], args[1], args[2]);
3200
3201                         conStr = connectionString;
3202
3203                         OracleConnection con1 = new OracleConnection();
3204
3205                         ShowConnectionProperties (con1);
3206
3207                         con1.ConnectionString = connectionString;
3208
3209                         con1.InfoMessage += new OracleInfoMessageEventHandler (OnInfoMessage);
3210                         con1.StateChange += new StateChangeEventHandler (OnStateChange);
3211
3212                         Console.WriteLine("Opening...");
3213                         con1.Open ();
3214                         Console.WriteLine("Opened.");
3215
3216                         ShowConnectionProperties (con1);
3217
3218                         InsertBlobTest (con1);
3219
3220                         Console.WriteLine ("Mono Oracle Test BEGIN ...");
3221                         MonoTest (con1);
3222                         Console.WriteLine ("Mono Oracle Test END ...");
3223
3224                         Wait ("");
3225
3226                         Console.WriteLine ("LOB Test BEGIN...");
3227                         CLOBTest (con1);
3228                         BLOBTest (con1);
3229                         Console.WriteLine ("LOB Test END.");
3230                         Wait ("");
3231
3232                         Console.WriteLine ("Read Simple Test BEGIN - scott.emp...");
3233                         ReadSimpleTest(con1, "SELECT e.*, e.rowid FROM scott.emp e");
3234                         Console.WriteLine ("Read Simple Test END - scott.emp");
3235
3236                         Wait ("");
3237
3238                         Console.WriteLine ("DataAdapter Test BEGIN...");
3239                         DataAdapterTest(con1);
3240                         Console.WriteLine ("DataAdapter Test END.");
3241
3242                         Wait ("");
3243
3244                         Console.WriteLine ("DataAdapter Test 2 BEGIN...");
3245                         // FIXME: test is failing in NET_2_0 profile but not in NET_1_1 profile
3246                         // Unhandled Exception: System.Data.OracleClient.OracleException: ORA-01400: cannot insert NULL
3247                         // into ("SCOTT"."MONO_ADAPTER_TEST"."NUMBER_WHOLE_VALUE")
3248                         // NUMBER_WHOLE_VALUE is a primary key on the table.
3249                         //DataAdapterTest2(con1);
3250                         Console.WriteLine ("***DataAdapter Test 2 FAILS!");
3251                         Console.WriteLine ("DataAdapter Test 2 END.");
3252
3253                         Wait ("");
3254
3255                         Console.WriteLine ("Rollback Test BEGIN...");
3256                         RollbackTest(con1);
3257                         Console.WriteLine ("Rollback Test END.");
3258
3259                         Wait ("");
3260
3261                         Console.WriteLine ("Commit Test BEGIN...");
3262                         CommitTest(con1);
3263                         Console.WriteLine ("Commit Test END.");
3264
3265                         Wait ("");
3266
3267                         Console.WriteLine ("Parameter Test BEGIN...");
3268                         ParameterTest(con1);
3269                         ReadSimpleTest(con1, "SELECT * FROM MONO_TEST_TABLE7");
3270                         Console.WriteLine ("Parameter Test END.");
3271
3272                         Wait ("");
3273
3274                         Console.WriteLine ("Stored Proc Test 1 BEGIN...");
3275                         StoredProcedureTest1 (con1);
3276                         ReadSimpleTest(con1, "SELECT * FROM MONO_TEST_TABLE1");
3277                         Console.WriteLine ("Stored Proc Test 1 END...");
3278
3279                         Wait ("");
3280
3281                         Console.WriteLine ("Stored Proc Test 2 BEGIN...");
3282                         StoredProcedureTest2 (con1);
3283                         ReadSimpleTest(con1, "SELECT * FROM MONO_TEST_TABLE2");
3284                         Console.WriteLine ("Stored Proc Test 2 END...");
3285
3286                         SetParameterOracleType (con1);
3287
3288                         Console.WriteLine ("Out Parameter and PL/SQL Block Test 1 BEGIN...");
3289                         OutParmTest1 (con1);
3290                         Console.WriteLine ("Out Parameter and PL/SQL Block Test 1 END...");
3291
3292                         Console.WriteLine ("Out Parameter and PL/SQL Block Test 2 BEGIN...");
3293                         OutParmTest2 (con1);
3294                         Console.WriteLine ("Out Parameter and PL/SQL Block Test 2 END...");
3295
3296                         Console.WriteLine ("Out Parameter and PL/SQL Block Test 3 BEGIN...");
3297                         OutParmTest3 (con1);
3298                         Console.WriteLine ("Out Parameter and PL/SQL Block Test 3 END...");
3299
3300                         Console.WriteLine ("Out Parameter and PL/SQL Block Test 4 BEGIN...");
3301                         OutParmTest4 (con1);
3302                         Console.WriteLine ("Out Parameter and PL/SQL Block Test 4 END...");
3303
3304                         Console.WriteLine ("Out Parameter and PL/SQL Block Test 5 BEGIN...");
3305                         OutParmTest5 (con1);
3306                         Console.WriteLine ("Out Parameter and PL/SQL Block Test 5 END...");
3307
3308                         Console.WriteLine ("Out Parameter and PL/SQL Block Test 6 BEGIN...");
3309                         OutParmTest6 (con1);
3310                         Console.WriteLine ("Out Parameter and PL/SQL Block Test 6 END...");
3311
3312                         Wait ("");
3313
3314                         Console.WriteLine ("Test a Non Query using Execute Reader BEGIN...");
3315                         TestNonQueryUsingExecuteReader (con1);
3316                         Console.WriteLine ("Test a Non Query using Execute Reader END...");
3317
3318                         Wait ("");
3319
3320                         Console.WriteLine ("Null Aggregate Warning BEGIN test...");
3321                         NullAggregateTest (con1);
3322                         Console.WriteLine ("Null Aggregate Warning END test...");
3323
3324                         Console.WriteLine ("Ref Cursor BEGIN tests...");
3325                         RefCursorTests (con1);
3326                         Console.WriteLine ("Ref Cursor END tests...");
3327
3328                         Console.WriteLine("Closing...");
3329                         con1.Close ();
3330                         Console.WriteLine("Closed.");
3331
3332                         conStr = conStr + ";pooling=true;min pool size=4;max pool size=" + MAX_CONNECTIONS.ToString ();\r
3333                         ConnectionPoolingTest1 ();\r                     ConnectionPoolingTest2 ();\r
3334
3335                         // Need to have an external authentication user setup in Linux and oracle
3336                         // before running this test
3337                         //ExternalAuthenticationTest();
3338
3339                         TestPersistSucurityInfo1();\r
3340                         TestPersistSucurityInfo2();\r
3341                         TestPersistSucurityInfo3();\r
3342                         TestPersistSucurityInfo4();\r
3343                         TestPersistSucurityInfo5();\r
3344                         TestPersistSucurityInfo6();
3345
3346                         Console.WriteLine("Done.");
3347                 }
3348         }
3349 }
3350