1 using System; 2 using System.Data; 3 using System.Data.OracleClient; 4 5 public class Test 6 { Main(string[] args)7 public static void Main (string[] args) 8 { 9 string connectionString = 10 "Data Source=testdb;" + 11 "User ID=scott;" + 12 "Password=tiger;"; 13 OracleConnection connection = null; 14 connection = new OracleConnection (connectionString); 15 connection.Open (); 16 17 Console.WriteLine("Setup test package and data..."); 18 OracleCommand cmddrop = connection.CreateCommand(); 19 20 cmddrop.CommandText = "DROP TABLE TESTTABLE"; 21 try { 22 cmddrop.ExecuteNonQuery(); 23 } 24 catch(OracleException e) { 25 Console.WriteLine("Ignore this error: " + e.Message); 26 } 27 cmddrop.Dispose(); 28 cmddrop = null; 29 30 Console.WriteLine("Create table TESTTABLE..."); 31 OracleCommand cmd = connection.CreateCommand(); 32 33 // create table TESTTABLE 34 cmd.CommandText = 35 "create table TESTTABLE (\n" + 36 " col1 numeric(18,0),\n" + 37 " col2 varchar(32),\n" + 38 " col3 date, col4 blob)"; 39 40 cmd.ExecuteNonQuery(); 41 Console.WriteLine("Insert 3 rows..."); 42 // insert some rows into TESTTABLE 43 cmd.CommandText = 44 "insert into TESTTABLE\n" + 45 "(col1, col2, col3, col4)\n" + 46 "values(45, 'Mono', sysdate, EMPTY_BLOB())"; 47 cmd.ExecuteNonQuery(); 48 49 cmd.CommandText = 50 "insert into TESTTABLE\n" + 51 "(col1, col2, col3, col4)\n" + 52 "values(136, 'Fun', sysdate, EMPTY_BLOB())"; 53 cmd.ExecuteNonQuery(); 54 55 cmd.CommandText = 56 "insert into TESTTABLE\n" + 57 "(col1, col2, col3, col4)\n" + 58 "values(526, 'System.Data.OracleClient', sysdate, EMPTY_BLOB())"; 59 cmd.ExecuteNonQuery(); 60 61 Console.WriteLine("commit..."); 62 63 cmd.CommandText = "commit"; 64 cmd.ExecuteNonQuery(); 65 66 Console.WriteLine("Update blob..."); 67 68 // update BLOB and CLOB columns 69 OracleCommand select = connection.CreateCommand (); 70 select.Transaction = connection.BeginTransaction(); 71 select.CommandText = "SELECT col1, col4 FROM testtable FOR UPDATE"; 72 OracleDataReader readerz = select.ExecuteReader (); 73 if (!readerz.Read ()) 74 Console.WriteLine ("ERROR: RECORD NOT FOUND"); 75 // update blob_value 76 Console.WriteLine(" Update BLOB column on table testtable..."); 77 OracleLob blob = readerz.GetOracleLob (1); 78 byte[] bytes = new byte[6] { 0x31, 0x32, 0x33, 0x34, 0x35, 0x036 }; 79 blob.Write (bytes, 0, bytes.Length); 80 blob.Close (); 81 readerz.Close(); 82 select.Transaction.Commit(); 83 select.Dispose(); 84 select = null; 85 86 87 cmd.CommandText = "commit"; 88 cmd.ExecuteNonQuery(); 89 90 Console.WriteLine("Create package..."); 91 92 // create Oracle package TestTablePkg 93 cmd.CommandText = 94 "CREATE OR REPLACE PACKAGE TestTablePkg\n" + 95 "AS\n" + 96 " TYPE T_CURSOR IS REF CURSOR;\n" + 97 "\n" + 98 " PROCEDURE GetData(tableCursor OUT T_CURSOR);\n" + 99 "END TestTablePkg;"; 100 cmd.ExecuteNonQuery(); 101 102 // create Oracle package body for package TestTablePkg 103 cmd.CommandText = 104 "CREATE OR REPLACE PACKAGE BODY TestTablePkg AS\n" + 105 " PROCEDURE GetData(tableCursor OUT T_CURSOR)\n" + 106 " IS\n" + 107 " BEGIN\n" + 108 " OPEN tableCursor FOR\n" + 109 " SELECT *\n" + 110 " FROM TestTable;\n" + 111 " END GetData;\n" + 112 "END TestTablePkg;"; 113 cmd.ExecuteNonQuery(); 114 115 cmd.Dispose(); 116 cmd = null; 117 118 Console.WriteLine("Set up command and parameters to call stored proc..."); 119 OracleCommand command = new OracleCommand("TestTablePkg.GetData", connection); 120 command.CommandType = CommandType.StoredProcedure; 121 OracleParameter parameter = new OracleParameter("tableCursor", OracleType.Cursor); 122 parameter.Direction = ParameterDirection.Output; 123 command.Parameters.Add(parameter); 124 125 Console.WriteLine("Execute..."); 126 command.ExecuteNonQuery(); 127 128 Console.WriteLine("Get OracleDataReader for cursor output parameter..."); 129 OracleDataReader reader = (OracleDataReader) parameter.Value; 130 131 Console.WriteLine("Read data***..."); 132 int r = 0; 133 while (reader.Read()) { 134 Console.WriteLine("Row {0}", r); 135 for (int f = 0; f < reader.FieldCount; f ++) { 136 Console.WriteLine("FieldType: " + reader.GetFieldType(f).ToString()); 137 object val = ""; 138 if (f==3) { 139 Console.WriteLine("blob"); 140 //OracleLob lob = reader.GetOracleLob (f); 141 //val = lob.Value; 142 val = reader.GetValue(f); 143 if (((byte[])val).Length == 0) 144 val = "Empty Blob (Not Null)"; 145 else 146 val = BitConverter.ToString((byte[])val); 147 } 148 else 149 val = reader.GetOracleValue(f); 150 151 Console.WriteLine(" Field {0} Value: {1}", f, val); 152 } 153 r ++; 154 } 155 Console.WriteLine("Rows retrieved: {0}", r); 156 157 Console.WriteLine("Clean up..."); 158 reader.Close(); 159 reader = null; 160 command.Dispose(); 161 command = null; 162 163 connection.Close(); 164 connection = null; 165 } 166 } 167 168 169 170