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; 36 private static string conStr; 37 public static readonly int MAX_CONNECTIONS = 30; // max connections default to 100, but I will set to 30. 38 OracleTest()39 public OracleTest() 40 { 41 42 } 43 MonoTest(OracleConnection con)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 ReadScalar(OracleConnection con, string selectSql)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 ReadOracleScalar(OracleConnection con, string selectSql)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 ReadSimpleTest(OracleConnection con, string selectSql)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 DataAdapterTest(OracleConnection connection)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 DataAdapterTest2(OracleConnection con)403 public static void DataAdapterTest2 (OracleConnection con) 404 { 405 DataAdapterTest2_Setup (con); 406 ReadSimpleTest (con, "SELECT * FROM mono_adapter_test"); 407 408 GetMetaData (con, "SELECT * FROM mono_adapter_test"); 409 410 DataAdapterTest2_Insert (con); 411 ReadSimpleTest (con, "SELECT * FROM mono_adapter_test"); 412 413 DataAdapterTest2_Update (con); 414 ReadSimpleTest (con, "SELECT * FROM mono_adapter_test"); 415 416 DataAdapterTest2_Delete (con); 417 ReadSimpleTest (con, "SELECT * FROM mono_adapter_test"); 418 } 419 GetMetaData(OracleConnection con, string sql)420 public static void GetMetaData (OracleConnection con, string sql) 421 { 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 431 DataTable dt; 432 dt = rdr.GetSchemaTable(); 433 foreach (DataRow schemaRow in dt.Rows) { 434 foreach (DataColumn schemaCol in dt.Columns) { 435 Console.WriteLine(schemaCol.ColumnName + 436 " = " + 437 schemaRow[schemaCol]); 438 Console.WriteLine("---Type: " + schemaRow[schemaCol].GetType ().ToString()); 439 } 440 Console.WriteLine(""); 441 } 442 443 Console.WriteLine("Read Schema with No KeyInfo"); 444 445 rdr = cmd.ExecuteReader(); 446 447 dt = rdr.GetSchemaTable(); 448 foreach (DataRow schemaRow in dt.Rows) { 449 foreach (DataColumn schemaCol in dt.Columns) { 450 Console.WriteLine(schemaCol.ColumnName + 451 " = " + 452 schemaRow[schemaCol]); 453 Console.WriteLine("---Type: " + schemaRow[schemaCol].GetType ().ToString()); 454 Console.WriteLine(); 455 } 456 } 457 458 } 459 DataAdapterTest2_Setup(OracleConnection con)460 public static void DataAdapterTest2_Setup (OracleConnection con) 461 { 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(); 557 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); 588 } 589 DataAdapterTest2_Insert(OracleConnection con)590 public static void DataAdapterTest2_Insert (OracleConnection con) 591 { 592 Console.WriteLine("================================"); 593 Console.WriteLine("=== Adapter Insert ============="); 594 Console.WriteLine("================================"); 595 OracleTransaction transaction = con.BeginTransaction (); 596 597 Console.WriteLine(" Create adapter..."); 598 OracleDataAdapter da = new OracleDataAdapter("select * from mono_adapter_test", con); 599 da.SelectCommand.Transaction = transaction; 600 601 Console.WriteLine(" Create command builder..."); 602 OracleCommandBuilder mycb = new OracleCommandBuilder(da); 603 604 Console.WriteLine(" Create data set ..."); 605 DataSet ds = new DataSet(); 606 607 Console.WriteLine(" Fill data set via adapter..."); 608 da.Fill(ds, "mono_adapter_test"); 609 610 Console.WriteLine(" New Row..."); 611 DataRow myRow; 612 myRow = ds.Tables["mono_adapter_test"].NewRow(); 613 614 byte[] bytes = new byte[] { 0x45,0x46,0x47,0x48,0x49,0x50 }; 615 616 Console.WriteLine(" Set values in the new DataRow..."); 617 myRow["varchar2_value"] = "OracleClient"; 618 myRow["number_whole_value"] = 22; 619 myRow["number_scaled_value"] = 12.34; 620 myRow["number_integer_value"] = 456; 621 myRow["float_value"] = 98.76; 622 myRow["date_value"] = new DateTime(2001,07,09); 623 Console.WriteLine(" *** FIXME; char value not working"); 624 //myRow["char_value"] = "Romeo"; 625 myRow["clob_value"] = "clobtest"; 626 myRow["blob_value"] = bytes; 627 628 Console.WriteLine(" Add DataRow to DataTable..."); 629 ds.Tables["mono_adapter_test"].Rows.Add(myRow); 630 631 Console.WriteLine("da.Update(ds..."); 632 da.Update(ds, "mono_adapter_test"); 633 634 transaction.Commit(); 635 636 mycb.Dispose(); 637 mycb = null; 638 } 639 DataAdapterTest2_Update(OracleConnection con)640 public static void DataAdapterTest2_Update (OracleConnection con) 641 { 642 Console.WriteLine("================================"); 643 Console.WriteLine("=== Adapter Update ============="); 644 Console.WriteLine("================================"); 645 646 OracleTransaction transaction = con.BeginTransaction (); 647 648 Console.WriteLine(" Create adapter..."); 649 OracleCommand selectCmd = con.CreateCommand (); 650 selectCmd.Transaction = transaction; 651 selectCmd.CommandText = "SELECT * FROM mono_adapter_test"; 652 OracleDataAdapter da = new OracleDataAdapter(selectCmd); 653 Console.WriteLine(" Create command builder..."); 654 OracleCommandBuilder mycb = new OracleCommandBuilder(da); 655 Console.WriteLine(" Create data set ..."); 656 DataSet ds = new DataSet(); 657 658 Console.WriteLine(" Set missing schema action..."); 659 660 Console.WriteLine(" Fill data set via adapter..."); 661 da.Fill(ds, "mono_adapter_test"); 662 DataRow myRow; 663 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]; 674 675 byte[] bytes = new byte[] { 0x62,0x63,0x64,0x65,0x66,0x67 }; 676 677 Console.WriteLine(" Set values in the new DataRow..."); 678 679 myRow["varchar2_value"] = "Super Power!"; 680 681 myRow["number_scaled_value"] = 12.35; 682 myRow["number_integer_value"] = 457; 683 myRow["float_value"] = 198.76; 684 myRow["date_value"] = new DateTime(2002,08,09); 685 //myRow["char_value"] = "Juliet"; 686 myRow["clob_value"] = "this is a clob"; 687 myRow["blob_value"] = bytes; 688 689 Console.WriteLine("da.Update(ds..."); 690 da.Update(ds, "mono_adapter_test"); 691 692 transaction.Commit(); 693 694 mycb.Dispose(); 695 mycb = null; 696 } 697 DataAdapterTest2_Delete(OracleConnection con)698 public static void DataAdapterTest2_Delete (OracleConnection con) 699 { 700 Console.WriteLine("================================"); 701 Console.WriteLine("=== Adapter Delete ============="); 702 Console.WriteLine("================================"); 703 OracleTransaction transaction = con.BeginTransaction (); 704 705 Console.WriteLine(" Create adapter..."); 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 ..."); 713 DataSet ds = new DataSet(); 714 715 Console.WriteLine("Fill data set via adapter..."); 716 da.Fill(ds, "mono_adapter_test"); 717 718 Console.WriteLine("delete row..."); 719 ds.Tables["mono_adapter_test"].Rows[0].Delete(); 720 721 Console.WriteLine("da.Update(table..."); 722 da.Update(ds, "mono_adapter_test"); 723 724 Console.WriteLine("Commit..."); 725 transaction.Commit(); 726 727 mycb.Dispose(); 728 mycb = null; 729 } 730 TestNonQueryUsingExecuteReader(OracleConnection con)731 static void TestNonQueryUsingExecuteReader(OracleConnection con) 732 { 733 OracleDataReader reader = null; 734 OracleTransaction trans = null; 735 736 Console.WriteLine(" drop table mono_adapter_test..."); 737 OracleCommand cmd = con.CreateCommand(); 738 739 cmd.CommandText = "DROP TABLE MONO_ADAPTER_TEST"; 740 trans = con.BeginTransaction(); 741 cmd.Transaction = trans; 742 try { 743 reader = cmd.ExecuteReader(); 744 Console.WriteLine(" RowsAffected before read: " + reader.RecordsAffected.ToString()); 745 reader.Read(); 746 Console.WriteLine(" RowsAffected after read: " + reader.RecordsAffected.ToString()); 747 reader.Close(); 748 Console.WriteLine(" RowsAffected after close: " + reader.RecordsAffected.ToString()); 749 trans.Commit(); 750 } 751 catch(OracleException e) { 752 Console.WriteLine(" OracleException caught: " + e.Message); 753 trans.Commit(); 754 } 755 756 Console.WriteLine(" Create table mono_adapter_test..."); 757 cmd.CommandText = "CREATE TABLE MONO_ADAPTER_TEST ( " + 758 " varchar2_value VarChar2(32), " + 759 " number_whole_value Number(18,0) PRIMARY KEY ) "; 760 trans = con.BeginTransaction(); 761 cmd.Transaction = trans; 762 reader = cmd.ExecuteReader(); 763 Console.WriteLine(" RowsAffected before read: " + reader.RecordsAffected.ToString()); 764 reader.Read(); 765 Console.WriteLine(" RowsAffected after read: " + reader.RecordsAffected.ToString()); 766 reader.Close(); 767 Console.WriteLine(" RowsAffected after close: " + reader.RecordsAffected.ToString()); 768 trans.Commit(); 769 770 Console.WriteLine("Insert into table mono_adapter_test..."); 771 772 string sql = 773 "INSERT INTO MONO_ADAPTER_TEST " + 774 "(VARCHAR2_VALUE,NUMBER_WHOLE_VALUE) " + 775 "VALUES(:p1,:p2)"; 776 777 OracleCommand cmd2 = con.CreateCommand(); 778 trans = con.BeginTransaction(); 779 cmd2.Transaction = trans; 780 cmd2.CommandText = sql; 781 782 OracleParameter myParameter1 = new OracleParameter("p1", OracleType.VarChar, 32); 783 myParameter1.Direction = ParameterDirection.Input; 784 785 OracleParameter myParameter2 = new OracleParameter("p2", OracleType.Number); 786 myParameter2.Direction = ParameterDirection.Input; 787 788 myParameter2.Value = 182; 789 myParameter1.Value = "Mono"; 790 791 cmd2.Parameters.Add (myParameter1); 792 cmd2.Parameters.Add (myParameter2); 793 794 // insert 1 record 795 reader = cmd2.ExecuteReader(); 796 Console.WriteLine(" RowsAffected before read: " + reader.RecordsAffected.ToString()); 797 reader.Read(); 798 Console.WriteLine(" RowsAffected after read: " + reader.RecordsAffected.ToString()); 799 reader.Close(); 800 Console.WriteLine(" RowsAffected after close: " + reader.RecordsAffected.ToString()); 801 802 // insert another record 803 Console.WriteLine(" Insert another record..."); 804 myParameter2.Value = 183; 805 myParameter1.Value = "Oracle"; 806 reader = cmd2.ExecuteReader(); 807 Console.WriteLine(" RowsAffected before read: " + reader.RecordsAffected.ToString()); 808 reader.Read(); 809 Console.WriteLine(" RowsAffected after read: " + reader.RecordsAffected.ToString()); 810 reader.Close(); 811 Console.WriteLine(" RowsAffected after close: " + reader.RecordsAffected.ToString()); 812 813 trans.Commit(); 814 trans = null; 815 816 ReadSimpleTest(con, "SELECT * FROM MONO_ADAPTER_TEST"); 817 } 818 CommitCursor(OracleConnection con)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 } 827 RollbackTest(OracleConnection connection)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 CommitTest(OracleConnection connection)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 ParameterTest2(OracleConnection connection)896 public static void ParameterTest2 (OracleConnection connection) 897 { 898 Console.WriteLine(" Setting NLS_DATE_FORMAT..."); 899 900 OracleCommand cmd2 = connection.CreateCommand(); 901 cmd2.CommandText = "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'"; 902 903 cmd2.ExecuteNonQuery (); 904 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 936 OracleTransaction trans = connection.BeginTransaction (); 937 OracleCommand cmd = connection.CreateCommand (); 938 cmd.Transaction = trans; 939 940 cmd.CommandText = "INSERT INTO MONO_TEST_TABLE7 " + 941 "(COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10) " + 942 "VALUES(:P1,:P2,:P3,:P4,:P5,:P6,:P7,:P8,:P9,:P10)"; 943 944 Console.WriteLine(" Add parameters..."); 945 946 OracleParameter parm1 = cmd.Parameters.Add (":P1", OracleType.VarChar, 8); 947 OracleParameter parm2 = cmd.Parameters.Add (":P2", OracleType.VarChar, 32); 948 949 OracleParameter parm3 = cmd.Parameters.Add (":P3", OracleType.Number); 950 OracleParameter parm4 = cmd.Parameters.Add (":P4", OracleType.Number); 951 952 OracleParameter parm5 = cmd.Parameters.Add (":P5", OracleType.DateTime); 953 OracleParameter parm6 = cmd.Parameters.Add (":P6", OracleType.DateTime); 954 955 // FIXME: fix BLOBs and CLOBs in OracleParameter 956 957 OracleParameter parm7 = cmd.Parameters.Add (":P7", OracleType.Blob); 958 OracleParameter parm8 = cmd.Parameters.Add (":P8", OracleType.Blob); 959 960 OracleParameter parm9 = cmd.Parameters.Add (":P9", OracleType.Clob); 961 OracleParameter parm10 = cmd.Parameters.Add (":P10", OracleType.Clob); 962 963 // TODO: implement out, return, and ref parameters 964 965 string s = "Mono"; 966 decimal d = 123456789012345.678M; 967 DateTime dt = DateTime.Now; 968 969 string clob = "Clob"; 970 byte[] blob = new byte[] { 0x31, 0x32, 0x33, 0x34, 0x35 }; 971 972 Console.WriteLine(" Set Values..."); 973 974 parm1.Value = s; 975 parm2.Value = DBNull.Value; 976 977 parm3.Value = d; 978 parm4.Value = DBNull.Value; 979 980 parm5.Value = dt; 981 parm6.Value = DBNull.Value; 982 983 parm7.Value = blob; 984 parm8.Value = DBNull.Value; 985 986 parm9.Value = clob; 987 parm10.Value = DBNull.Value; 988 989 Console.WriteLine(" ExecuteNonQuery..."); 990 991 cmd.ExecuteNonQuery (); 992 trans.Commit(); 993 } 994 ParameterTest(OracleConnection connection)995 public static void ParameterTest (OracleConnection connection) 996 { 997 Console.WriteLine(" Setting NLS_DATE_FORMAT..."); 998 999 OracleCommand cmd2 = connection.CreateCommand(); 1000 cmd2.CommandText = "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'"; 1001 1002 cmd2.ExecuteNonQuery (); 1003 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 1035 OracleTransaction trans = connection.BeginTransaction (); 1036 OracleCommand cmd = connection.CreateCommand (); 1037 cmd.Transaction = trans; 1038 1039 cmd.CommandText = "INSERT INTO MONO_TEST_TABLE7 " + 1040 "(COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10) " + 1041 "VALUES(:P1,:P2,:P3,:P4,:P5,:P6,:P7,:P8,:P9,:P10)"; 1042 1043 Console.WriteLine(" Add parameters..."); 1044 1045 OracleParameter parm1 = cmd.Parameters.Add (":P1", OracleType.VarChar, 8); 1046 OracleParameter parm2 = cmd.Parameters.Add (":P2", OracleType.VarChar, 32); 1047 1048 OracleParameter parm3 = cmd.Parameters.Add (":P3", OracleType.Number); 1049 OracleParameter parm4 = cmd.Parameters.Add (":P4", OracleType.Number); 1050 1051 OracleParameter parm5 = cmd.Parameters.Add (":P5", OracleType.DateTime); 1052 OracleParameter parm6 = cmd.Parameters.Add (":P6", OracleType.DateTime); 1053 1054 // FIXME: fix BLOBs and CLOBs in OracleParameter 1055 1056 OracleParameter parm7 = cmd.Parameters.Add (":P7", OracleType.Blob); 1057 OracleParameter parm8 = cmd.Parameters.Add (":P8", OracleType.Blob); 1058 1059 OracleParameter parm9 = cmd.Parameters.Add (":P9", OracleType.Clob); 1060 OracleParameter parm10 = cmd.Parameters.Add (":P10", OracleType.Clob); 1061 1062 // TODO: implement out, return, and ref parameters 1063 1064 string s = "Mono"; 1065 decimal d = 123456789012345.678M; 1066 DateTime dt = DateTime.Now; 1067 1068 string clob = "Clob"; 1069 byte[] blob = new byte[] { 0x31, 0x32, 0x33, 0x34, 0x35 }; 1070 1071 Console.WriteLine(" Set Values..."); 1072 1073 parm1.Value = s; 1074 parm2.Value = DBNull.Value; 1075 1076 parm3.Value = d; 1077 parm4.Value = DBNull.Value; 1078 1079 parm5.Value = dt; 1080 parm6.Value = DBNull.Value; 1081 1082 parm7.Value = blob; 1083 parm8.Value = DBNull.Value; 1084 1085 parm9.Value = clob; 1086 parm10.Value = DBNull.Value; 1087 1088 Console.WriteLine(" ExecuteNonQuery..."); 1089 1090 cmd.ExecuteNonQuery (); 1091 trans.Commit(); 1092 } 1093 CLOBTest(OracleConnection connection)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 BLOBTest(OracleConnection connection)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 Wait(string msg)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 GetHexString(byte[] bytes)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 StoredProcedureTest1(OracleConnection con)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 (); 1322 cmd3.CommandType = CommandType.StoredProcedure; 1323 cmd3.CommandText = "sp_test1"; 1324 cmd3.ExecuteNonQuery (); 1325 } 1326 StoredProcedureTest2(OracleConnection con)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 exist 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 (); 1373 cmd3.CommandType = CommandType.StoredProcedure; 1374 cmd3.CommandText = "sp_test2"; 1375 1376 OracleParameter myParameter1 = new OracleParameter("parm1", OracleType.VarChar); 1377 myParameter1.Value = "yyy13"; 1378 myParameter1.Size = 8; 1379 myParameter1.Direction = ParameterDirection.Input; 1380 1381 OracleParameter myParameter2 = new OracleParameter("parm2", OracleType.VarChar); 1382 myParameter2.Value = "iii13"; 1383 myParameter2.Size = 32; 1384 myParameter2.Direction = ParameterDirection.Input; 1385 1386 cmd3.Parameters.Add (myParameter1); 1387 cmd3.Parameters.Add (myParameter2); 1388 1389 cmd3.ExecuteNonQuery (); 1390 } 1391 OutParmTest1(OracleConnection con)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 OutParmTest2(OracleConnection con)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 OutParmTest3(OracleConnection con)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 OutParmTest4(OracleConnection con)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 OutParmTest5(OracleConnection con)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 GetOracleClobValue(OracleParameter parm)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 CreateTemporaryLobLocator(OracleCommand cmd, OracleType lobType)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 OutParmTest6(OracleConnection con)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 ShowConnectionProperties(OracleConnection con)2299 static void ShowConnectionProperties (OracleConnection con) 2300 { 2301 try { 2302 Console.WriteLine ("ServerVersion: " + con.ServerVersion); 2303 } catch (System.InvalidOperationException ioe) { 2304 Console.WriteLine ("InvalidOperationException caught."); 2305 Console.WriteLine ("Message: " + ioe.Message); 2306 } 2307 2308 Console.WriteLine ("DataSource: " + con.DataSource); 2309 } 2310 NullAggregateTest(OracleConnection con)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 OnInfoMessage(object sender, OracleInfoMessageEventArgs e)2365 static void OnInfoMessage (object sender, OracleInfoMessageEventArgs e) 2366 { 2367 Console.WriteLine("InfoMessage Message: " + e.Message.ToString()); 2368 Console.WriteLine("InfoMessage Code: " + e.Code.ToString()); 2369 Console.WriteLine("InfoMessage Source: " + e.Source.ToString()); 2370 } 2371 OnStateChange(object sender, StateChangeEventArgs e)2372 static void OnStateChange (object sender, StateChangeEventArgs e) 2373 { 2374 Console.WriteLine("StateChange CurrentSate:" + e.CurrentState.ToString ()); 2375 Console.WriteLine("StateChange OriginalState:" + e.OriginalState.ToString ()); 2376 } 2377 RefCursorTests(OracleConnection con)2378 static void RefCursorTests(OracleConnection con) 2379 { 2380 SetupRefCursorTests(con); // for ref cursor tests 1 thru 3 2381 RefCursorTest1(con); // using BEGIN/END 2382 RefCursorTest2(con); // using call 2383 RefCursorTest3(con); // using CommandType.StoredProcedure 2384 2385 RefCursorTest4(con); 2386 } 2387 SetupRefCursorTests(OracleConnection con)2388 static void SetupRefCursorTests(OracleConnection con) 2389 { 2390 Console.WriteLine("Setup Oracle package curspkg_join..."); 2391 2392 OracleCommand cmd = con.CreateCommand(); 2393 2394 Console.Error.WriteLine(" create or replace package curspkg_join..."); 2395 cmd.CommandText = 2396 "CREATE OR REPLACE PACKAGE curspkg_join AS\n" + 2397 "TYPE t_cursor IS REF CURSOR;\n" + 2398 "Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor);\n" + 2399 "END curspkg_join;"; 2400 cmd.ExecuteNonQuery(); 2401 2402 Console.Error.WriteLine(" create or replace package body curspkg_join..."); 2403 cmd.CommandText = 2404 "CREATE OR REPLACE PACKAGE BODY curspkg_join AS\n" + 2405 " Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor)\n" + 2406 " IS\n" + 2407 " v_cursor t_cursor;\n" + 2408 " BEGIN\n" + 2409 " IF n_EMPNO <> 0 THEN\n" + 2410 " OPEN v_cursor FOR\n" + 2411 " SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME\n" + 2412 " FROM SCOTT.EMP, SCOTT.DEPT\n" + 2413 " WHERE EMP.DEPTNO = DEPT.DEPTNO\n" + 2414 " AND EMP.EMPNO = n_EMPNO;\n" + 2415 "\n" + 2416 " ELSE\n" + 2417 " OPEN v_cursor FOR\n" + 2418 " SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME\n" + 2419 " FROM SCOTT.EMP, SCOTT.DEPT\n" + 2420 " WHERE EMP.DEPTNO = DEPT.DEPTNO;\n" + 2421 "\n" + 2422 " END IF;\n" + 2423 " io_cursor := v_cursor;\n" + 2424 " END open_join_cursor1;\n" + 2425 "END curspkg_join;"; 2426 cmd.ExecuteNonQuery(); 2427 2428 cmd.CommandText = "commit"; 2429 cmd.ExecuteNonQuery(); 2430 } 2431 RefCursorTest4(OracleConnection connection)2432 public static void RefCursorTest4(OracleConnection connection) 2433 { 2434 Console.WriteLine("Setup test package and data for RefCursorTest4..."); 2435 OracleCommand cmddrop = connection.CreateCommand(); 2436 2437 cmddrop.CommandText = "DROP TABLE TESTTABLE"; 2438 try { 2439 cmddrop.ExecuteNonQuery(); 2440 } 2441 catch(OracleException e) { 2442 Console.WriteLine("Ignore this error: " + e.Message); 2443 } 2444 cmddrop.Dispose(); 2445 cmddrop = null; 2446 2447 OracleCommand cmd = connection.CreateCommand(); 2448 2449 // create table TESTTABLE 2450 cmd.CommandText = 2451 "create table TESTTABLE (\n" + 2452 " col1 numeric(18,0),\n" + 2453 " col2 char(32),\n" + 2454 " col3 date)"; 2455 cmd.ExecuteNonQuery(); 2456 2457 // insert some rows into TESTTABLE 2458 cmd.CommandText = 2459 "insert into TESTTABLE\n" + 2460 "(col1, col2, col3)\n" + 2461 "values(45, 'Mono', sysdate)"; 2462 cmd.ExecuteNonQuery(); 2463 2464 cmd.CommandText = 2465 "insert into TESTTABLE\n" + 2466 "(col1, col2, col3)\n" + 2467 "values(136, 'Fun', sysdate)"; 2468 cmd.ExecuteNonQuery(); 2469 2470 cmd.CommandText = 2471 "insert into TESTTABLE\n" + 2472 "(col1, col2, col3)\n" + 2473 "values(526, 'System.Data.OracleClient', sysdate)"; 2474 cmd.ExecuteNonQuery(); 2475 2476 cmd.CommandText = "commit"; 2477 cmd.ExecuteNonQuery(); 2478 2479 // create Oracle package TestTablePkg 2480 cmd.CommandText = 2481 "CREATE OR REPLACE PACKAGE TestTablePkg\n" + 2482 "AS\n" + 2483 " TYPE T_CURSOR IS REF CURSOR;\n" + 2484 "\n" + 2485 " PROCEDURE GetData(tableCursor OUT T_CURSOR);\n" + 2486 "END TestTablePkg;"; 2487 cmd.ExecuteNonQuery(); 2488 2489 // create Oracle package body for package TestTablePkg 2490 cmd.CommandText = 2491 "CREATE OR REPLACE PACKAGE BODY TestTablePkg AS\n" + 2492 " PROCEDURE GetData(tableCursor OUT T_CURSOR)\n" + 2493 " IS\n" + 2494 " BEGIN\n" + 2495 " OPEN tableCursor FOR\n" + 2496 " SELECT *\n" + 2497 " FROM TestTable;\n" + 2498 " END GetData;\n" + 2499 "END TestTablePkg;"; 2500 cmd.ExecuteNonQuery(); 2501 2502 cmd.Dispose(); 2503 cmd = null; 2504 2505 Console.WriteLine("Set up command and parameters to call stored proc..."); 2506 OracleCommand command = new OracleCommand("TestTablePkg.GetData", connection); 2507 command.CommandType = CommandType.StoredProcedure; 2508 OracleParameter parameter = new OracleParameter("tableCursor", OracleType.Cursor); 2509 parameter.Direction = ParameterDirection.Output; 2510 command.Parameters.Add(parameter); 2511 2512 Console.WriteLine("Execute..."); 2513 command.ExecuteNonQuery(); 2514 2515 Console.WriteLine("Get OracleDataReader for cursor output parameter..."); 2516 OracleDataReader reader = (OracleDataReader) parameter.Value; 2517 2518 Console.WriteLine("Read data..."); 2519 int r = 0; 2520 while (reader.Read()) { 2521 Console.WriteLine("Row {0}", r); 2522 for (int f = 0; f < reader.FieldCount; f ++) { 2523 object val = reader.GetValue(f); 2524 Console.WriteLine(" Field {0} Value: {1}", f, val.ToString()); 2525 } 2526 r ++; 2527 } 2528 Console.WriteLine("Rows retrieved: {0}", r); 2529 2530 Console.WriteLine("Clean up..."); 2531 reader.Close(); 2532 reader = null; 2533 command.Dispose(); 2534 command = null; 2535 } 2536 RefCursorTest1(OracleConnection con)2537 static void RefCursorTest1(OracleConnection con) 2538 { 2539 Console.WriteLine("Ref Cursor Test 1 - using BEGIN/END for proc - Begin..."); 2540 2541 Console.WriteLine("Create command..."); 2542 OracleCommand cmd = new OracleCommand(); 2543 cmd.Connection = con; 2544 2545 cmd.CommandText = 2546 "BEGIN\n" + 2547 " curspkg_join.open_join_cursor1(:n_Empno,:io_cursor);\n" + 2548 "END;"; 2549 2550 // PL/SQL definition of stored procedure in package curspkg_join 2551 // open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor) 2552 2553 Console.WriteLine("Create parameters..."); 2554 2555 OracleParameter parm1 = new OracleParameter("n_Empno", OracleType.Number); 2556 parm1.Direction = ParameterDirection.Input; 2557 parm1.Value = 7902; 2558 2559 OracleParameter parm2 = new OracleParameter("io_cursor", OracleType.Cursor); 2560 parm2.Direction = ParameterDirection.Output; 2561 2562 cmd.Parameters.Add(parm1); 2563 cmd.Parameters.Add(parm2); 2564 2565 // positional parm 2566 //cmd.Parameters.Add(new OracleParameter("io_cursor", OracleType.Cursor)).Direction = ParameterDirection.Output; 2567 // named parm 2568 //cmd.Parameters.Add("n_Empno", OracleType.Number, 4).Value = 7902; 2569 2570 OracleDataReader reader; 2571 Console.WriteLine("Execute Non Query..."); 2572 cmd.ExecuteNonQuery(); 2573 2574 Console.WriteLine("Get data reader (ref cursor) from out parameter..."); 2575 reader = (OracleDataReader) cmd.Parameters["io_cursor"].Value; 2576 2577 int x, count; 2578 count = 0; 2579 2580 Console.WriteLine("Get data from ref cursor..."); 2581 while (reader.Read()) { 2582 for (x = 0; x < reader.FieldCount; x++) 2583 Console.Write(reader[x] + " "); 2584 2585 Console.WriteLine(); 2586 count += 1; 2587 } 2588 Console.WriteLine(count.ToString() + " Rows Returned."); 2589 2590 reader.Close(); 2591 } 2592 RefCursorTest2(OracleConnection con)2593 static void RefCursorTest2(OracleConnection con) 2594 { 2595 Console.WriteLine("Ref Cursor Test 2 - using call - Begin..."); 2596 2597 Console.WriteLine("Create command..."); 2598 OracleCommand cmd = new OracleCommand(); 2599 cmd.Connection = con; 2600 cmd.CommandText = "call curspkg_join.open_join_cursor1(:n_Empno,:io_cursor)"; 2601 2602 // PL/SQL definition of stored procedure in package curspkg_join 2603 // open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor) 2604 2605 Console.WriteLine("Create parameters..."); 2606 2607 OracleParameter parm1 = new OracleParameter("n_Empno", OracleType.Number); 2608 parm1.Direction = ParameterDirection.Input; 2609 parm1.Value = 7902; 2610 2611 OracleParameter parm2 = new OracleParameter("io_cursor", OracleType.Cursor); 2612 parm2.Direction = ParameterDirection.Output; 2613 2614 cmd.Parameters.Add(parm1); 2615 cmd.Parameters.Add(parm2); 2616 2617 // positional parm 2618 //cmd.Parameters.Add(new OracleParameter("io_cursor", OracleType.Cursor)).Direction = ParameterDirection.Output; 2619 // named parm 2620 //cmd.Parameters.Add("n_Empno", OracleType.Number, 4).Value = 7902; 2621 2622 OracleDataReader reader; 2623 Console.WriteLine("Execute Non Query..."); 2624 cmd.ExecuteNonQuery(); 2625 2626 Console.WriteLine("Get data reader (ref cursor) from out parameter..."); 2627 reader = (OracleDataReader) cmd.Parameters["io_cursor"].Value; 2628 2629 int x, count; 2630 count = 0; 2631 2632 Console.WriteLine("Get data from ref cursor..."); 2633 while (reader.Read()) { 2634 for (x = 0; x < reader.FieldCount; x++) 2635 Console.Write(reader[x] + " "); 2636 2637 Console.WriteLine(); 2638 count += 1; 2639 } 2640 Console.WriteLine(count.ToString() + " Rows Returned."); 2641 2642 reader.Close(); 2643 } 2644 RefCursorTest3(OracleConnection con)2645 static void RefCursorTest3(OracleConnection con) 2646 { 2647 Console.WriteLine("Ref Cursor Test 3 - CommandType.StoredProcedure - Begin..."); 2648 2649 Console.WriteLine("Create command..."); 2650 OracleCommand cmd = new OracleCommand(); 2651 cmd.Connection = con; 2652 cmd.CommandText = "curspkg_join.open_join_cursor1"; 2653 cmd.CommandType = CommandType.StoredProcedure; 2654 2655 // PL/SQL definition of stored procedure in package curspkg_join 2656 // open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor) 2657 2658 Console.WriteLine("Create parameters..."); 2659 2660 OracleParameter parm1 = new OracleParameter("n_Empno", OracleType.Number); 2661 parm1.Direction = ParameterDirection.Input; 2662 parm1.Value = 7902; 2663 2664 OracleParameter parm2 = new OracleParameter("io_cursor", OracleType.Cursor); 2665 parm2.Direction = ParameterDirection.Output; 2666 2667 cmd.Parameters.Add(parm1); 2668 cmd.Parameters.Add(parm2); 2669 2670 // positional parm 2671 //cmd.Parameters.Add(new OracleParameter("io_cursor", OracleType.Cursor)).Direction = ParameterDirection.Output; 2672 // named parm 2673 //cmd.Parameters.Add("n_Empno", OracleType.Number, 4).Value = 7902; 2674 2675 OracleDataReader reader; 2676 Console.WriteLine("Execute Non Query..."); 2677 cmd.ExecuteNonQuery(); 2678 2679 Console.WriteLine("Get data reader (ref cursor) from out parameter..."); 2680 reader = (OracleDataReader) cmd.Parameters["io_cursor"].Value; 2681 2682 int x, count; 2683 count = 0; 2684 2685 Console.WriteLine("Get data from ref cursor..."); 2686 while (reader.Read()) { 2687 for (x = 0; x < reader.FieldCount; x++) 2688 Console.Write(reader[x] + " "); 2689 2690 Console.WriteLine(); 2691 count += 1; 2692 } 2693 Console.WriteLine(count.ToString() + " Rows Returned."); 2694 2695 reader.Close(); 2696 } 2697 ExternalAuthenticationTest()2698 static void ExternalAuthenticationTest () 2699 { 2700 string user = Environment.UserName; 2701 if (!Environment.UserDomainName.Equals(String.Empty)) 2702 user = Environment.UserDomainName + "\\" + Environment.UserName; 2703 Console.WriteLine("Environment UserDomainName and UserName: " + user); 2704 Console.WriteLine("Open connection using external authentication..."); 2705 OracleConnection con = new OracleConnection("Data Source=palis;Integrated Security=true"); 2706 try { 2707 con.Open(); 2708 OracleCommand cmd = con.CreateCommand(); 2709 cmd.CommandText = "SELECT USER FROM DUAL"; 2710 OracleDataReader reader = cmd.ExecuteReader(); 2711 if (reader.Read()) 2712 Console.WriteLine("User: " + reader.GetString(reader.GetOrdinal("USER"))); 2713 con.Close(); 2714 } 2715 catch (Exception e) { 2716 Console.WriteLine("Exception caught: " + e.Message); 2717 Console.WriteLine("Probably not setup for external authentication."); 2718 } 2719 con.Dispose(); 2720 con = null; 2721 } 2722 TestPersistSucurityInfo1()2723 public static void TestPersistSucurityInfo1() 2724 { 2725 Console.WriteLine("\nTestPersistSucurityInfo1 - persist security info=false"); 2726 OracleConnection con = new OracleConnection("data source=palis;user id=scott;password=tiger;persist security info=false"); 2727 Console.WriteLine("ConnectionString before open: " + con.ConnectionString); 2728 con.Open(); 2729 Console.WriteLine("ConnectionString after open: " + con.ConnectionString); 2730 con.Close(); 2731 Console.WriteLine("ConnectionString after close: " + con.ConnectionString); 2732 con = null; 2733 } 2734 TestPersistSucurityInfo2()2735 public static void TestPersistSucurityInfo2() 2736 { 2737 Console.WriteLine("\nTestPersistSucurityInfo2 - persist security info=true"); 2738 OracleConnection con = new OracleConnection("data source=palis;user id=scott;password=tiger;persist security info=true"); 2739 Console.WriteLine("ConnectionString before open: " + con.ConnectionString); 2740 con.Open(); 2741 Console.WriteLine("ConnectionString after open: " + con.ConnectionString); 2742 con.Close(); 2743 Console.WriteLine("ConnectionString after close: " + con.ConnectionString); 2744 con = null; 2745 } 2746 TestPersistSucurityInfo3()2747 public static void TestPersistSucurityInfo3() 2748 { 2749 Console.WriteLine("\nTestPersistSucurityInfo3 - use default for persist security info which is false"); 2750 OracleConnection con = new OracleConnection("data source=palis;user id=scott;password=tiger"); 2751 Console.WriteLine("ConnectionString before open: " + con.ConnectionString); 2752 con.Open(); 2753 Console.WriteLine("ConnectionString after open: " + con.ConnectionString); 2754 con.Close(); 2755 Console.WriteLine("ConnectionString after close: " + con.ConnectionString); 2756 con = null; 2757 } 2758 TestPersistSucurityInfo4()2759 public static void TestPersistSucurityInfo4() 2760 { 2761 Console.WriteLine("\nTestPersistSucurityInfo4 - persist security info=false with password at front"); 2762 OracleConnection con = new OracleConnection(";password=tiger;data source=palis;user id=scott;persist security info=false"); 2763 Console.WriteLine("ConnectionString before open: " + con.ConnectionString); 2764 con.Open(); 2765 Console.WriteLine("ConnectionString after open: " + con.ConnectionString); 2766 con.Close(); 2767 Console.WriteLine("ConnectionString after close: " + con.ConnectionString); 2768 con = null; 2769 } 2770 TestPersistSucurityInfo5()2771 public static void TestPersistSucurityInfo5() 2772 { 2773 Console.WriteLine("\nTestPersistSucurityInfo5 - persist security info=false"); 2774 OracleConnection con = new OracleConnection("data source=palis;user id=scott;password=tiger;persist security info=false"); 2775 Console.WriteLine("ConnectionString before open: " + con.ConnectionString); 2776 con.Open(); 2777 Console.WriteLine("ConnectionString after open: " + con.ConnectionString); 2778 Console.WriteLine("ConnectionState for con: " + con.State.ToString() + "\n"); 2779 2780 Console.WriteLine("Clone OracleConnection..."); 2781 OracleConnection con2 = (OracleConnection) ((ICloneable) con).Clone(); 2782 2783 Console.WriteLine("ConnectionState for con2: " + con2.State.ToString()); 2784 Console.WriteLine("con2 ConnectionString before open: " + con2.ConnectionString); 2785 con2.Open(); 2786 Console.WriteLine("con2 ConnectionString after open: " + con2.ConnectionString); 2787 con2.Close(); 2788 Console.WriteLine("con2 ConnectionString after close: " + con2.ConnectionString); 2789 2790 con.Close(); 2791 } 2792 TestPersistSucurityInfo6()2793 public static void TestPersistSucurityInfo6() 2794 { 2795 Console.WriteLine("\nTestPersistSucurityInfo6 - external auth using persist security info"); 2796 2797 string user = Environment.UserName; 2798 if (!Environment.UserDomainName.Equals(String.Empty)) 2799 user = Environment.UserDomainName + "\\" + Environment.UserName; 2800 Console.WriteLine("Environment UserDomainName and UserName: " + user); 2801 Console.WriteLine("Open connection using external authentication..."); 2802 OracleConnection con = new OracleConnection("Data Source=palis;Integrated Security=true"); 2803 Console.WriteLine("ConnectionString before open: " + con.ConnectionString); 2804 try { 2805 con.Open(); 2806 OracleCommand cmd = con.CreateCommand(); 2807 cmd.CommandText = "SELECT USER FROM DUAL"; 2808 OracleDataReader reader = cmd.ExecuteReader(); 2809 if (reader.Read()) 2810 Console.WriteLine("User: " + reader.GetString(reader.GetOrdinal("USER"))); 2811 con.Close(); 2812 Console.WriteLine("ConnectionString after close: " + con.ConnectionString); 2813 } 2814 catch (Exception e) { 2815 Console.WriteLine("Exception caught: " + e.Message); 2816 Console.WriteLine("Probably not setup for external authentication. This is fine."); 2817 } 2818 con.Dispose(); 2819 Console.WriteLine("ConnectionString after dispose: " + con.ConnectionString); 2820 con = null; 2821 Console.WriteLine("\n\n"); 2822 } 2823 ConnectionPoolingTest1()2824 public static void ConnectionPoolingTest1 () 2825 { 2826 Console.WriteLine("Start Connection Pooling Test 1..."); 2827 OracleConnection[] connections = null; 2828 int maxCon = MAX_CONNECTIONS + 1; // add 1 more over the max connections to cause it to wait for the next available connection 2829 int i = 0; 2830 2831 try { 2832 connections = new OracleConnection[maxCon]; 2833 2834 for (i = 0; i < maxCon; i++) { 2835 Console.WriteLine(" Open connection: {0}", i); 2836 connections[i] = new OracleConnection(conStr); 2837 connections[i].Open (); 2838 } 2839 } catch (InvalidOperationException e) { 2840 Console.WriteLine("Expected exception InvalidOperationException caught."); 2841 Console.WriteLine(e); 2842 } 2843 2844 for (i = 0; i < maxCon; i++) { 2845 if (connections[i] != null) { 2846 Console.WriteLine(" Close connection: {0}", i); 2847 if (connections[i].State == ConnectionState.Open) 2848 connections[i].Close (); 2849 connections[i].Dispose (); 2850 connections[i] = null; 2851 } 2852 } 2853 2854 connections = null; 2855 2856 Console.WriteLine("Done Connection Pooling Test 1."); 2857 } 2858 ConnectionPoolingTest2()2859 public static void ConnectionPoolingTest2 () 2860 { 2861 Console.WriteLine("Start Connection Pooling Test 2..."); 2862 OracleConnection[] connections = null; 2863 int maxCon = MAX_CONNECTIONS; 2864 int i = 0; 2865 2866 connections = new OracleConnection[maxCon]; 2867 2868 for (i = 0; i < maxCon; i++) { 2869 Console.WriteLine(" Open connection: {0}", i); 2870 connections[i] = new OracleConnection(conStr); 2871 connections[i].Open (); 2872 } 2873 2874 Console.WriteLine("Start another thread..."); 2875 t = new Thread(new ThreadStart(AnotherThreadProc)); 2876 t.Start (); 2877 2878 Console.WriteLine("Sleep..."); 2879 Thread.Sleep(100); 2880 2881 Console.WriteLine("Closing..."); 2882 for (i = 0; i < maxCon; i++) { 2883 if (connections[i] != null) { 2884 Console.WriteLine(" Close connection: {0}", i); 2885 if (connections[i].State == ConnectionState.Open) 2886 connections[i].Close (); 2887 connections[i].Dispose (); 2888 connections[i] = null; 2889 } 2890 } 2891 2892 connections = null; 2893 } 2894 AnotherThreadProc()2895 private static void AnotherThreadProc () 2896 { 2897 Console.WriteLine("Open connection via another thread..."); 2898 OracleConnection[] connections = null; 2899 int maxCon = MAX_CONNECTIONS; 2900 int i = 0; 2901 2902 connections = new OracleConnection[maxCon]; 2903 2904 for (i = 0; i < maxCon; i++) { 2905 Console.WriteLine(" Open connection: {0}", i); 2906 connections[i] = new OracleConnection(conStr); 2907 connections[i].Open (); 2908 } 2909 2910 Console.WriteLine("Done Connection Pooling Test 2."); 2911 System.Environment.Exit (0); 2912 } 2913 SetParameterOracleType(OracleConnection con)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 InsertBlobTest(OracleConnection con)2949 public static void InsertBlobTest(OracleConnection con) 2950 { 2951 checkTNS(); 2952 SetupMyPackage(con); 2953 InsertBlob(con); 2954 } 2955 checkTNS()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 InsertBlob(OracleConnection con)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 GetOracleLob(OracleTransaction transaction, byte[] blob)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 SetupMyPackage(OracleConnection con)3091 static void SetupMyPackage(OracleConnection con) 3092 { 3093 Console.WriteLine("Setup Oracle package curspkg_join..."); 3094 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"; 3114 create.ExecuteNonQuery(); 3115 3116 Console.Error.WriteLine(" create or replace package MyPackage..."); 3117 OracleCommand cmd = con.CreateCommand(); 3118 cmd.CommandText = 3119 "CREATE OR REPLACE PACKAGE MyPackage AS\n" + 3120 " Procedure InsertBlob (i_Sig_File blob);\n" + 3121 "END MyPackage;"; 3122 cmd.ExecuteNonQuery(); 3123 3124 Console.Error.WriteLine(" create or replace package body MyPackage..."); 3125 cmd.CommandText = 3126 "CREATE OR REPLACE PACKAGE BODY MyPackage AS\n" + 3127 " Procedure InsertBlob (i_Sig_File blob)\n" + 3128 " IS\n" + 3129 " BEGIN\n" + 3130 " INSERT INTO BLOBTEST2 (BLOB_COLUMN) VALUES(i_Sig_File); " + 3131 " END InsertBlob; " + 3132 "END MyPackage;"; 3133 cmd.ExecuteNonQuery(); 3134 3135 cmd.CommandText = "commit"; 3136 cmd.ExecuteNonQuery(); 3137 } 3138 ByteArrayCombine(byte[] b1, byte[] b2)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 ByteArrayCompare(byte[] ba1, byte[] ba2)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] Main(string[] args)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 (); 3333 ConnectionPoolingTest1 (); 3334 ConnectionPoolingTest2 (); 3335 3336 // Need to have an external authentication user setup in Linux and oracle 3337 // before running this test 3338 //ExternalAuthenticationTest(); 3339 3340 TestPersistSucurityInfo1(); 3341 TestPersistSucurityInfo2(); 3342 TestPersistSucurityInfo3(); 3343 TestPersistSucurityInfo4(); 3344 TestPersistSucurityInfo5(); 3345 TestPersistSucurityInfo6(); 3346 3347 Console.WriteLine("Done."); 3348 } 3349 } 3350 } 3351 3352