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