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