1 // testclob.cs - tests loading a text file into an oracle clob and vice-versa
4 using System.Data.OracleClient;
10 static string infilename = @"cs-parser.cs";
11 static string outfilename = @"cs-parser2.cs";
12 static string connectionString = "data source=palis;user id=scott;password=tiger"
14 public static void Main (string[] args)
16 OracleConnection con = new OracleConnection();
17 con.ConnectionString = connectionString;
27 // read the CLOB into file "cs-parser2.cs"
28 public static void ReadClob (OracleConnection connection)
30 OracleCommand rcmd = connection.CreateCommand ();
31 rcmd.CommandText = "SELECT CLOB_COLUMN FROM CLOBTEST";
32 OracleDataReader reader2 = rcmd.ExecuteReader ();
34 Console.WriteLine ("ERROR: RECORD NOT FOUND");
36 Console.WriteLine (" TESTING OracleLob OBJECT 2...");
37 OracleLob lob2 = reader2.GetOracleLob (0);
38 Console.WriteLine (" LENGTH: {0}", lob2.Length);
39 Console.WriteLine (" CHUNK SIZE: {0}", lob2.ChunkSize);
41 string lobvalue = (string) lob2.Value;
43 using (StreamWriter sw = new StreamWriter(outfilename)) {
52 public static void CLOBTest (OracleConnection connection)
54 Console.WriteLine (" BEGIN TRANSACTION ...");
56 OracleTransaction transaction = connection.BeginTransaction ();
58 Console.WriteLine (" Drop table CLOBTEST ...");
60 OracleCommand cmd2 = connection.CreateCommand ();
61 cmd2.Transaction = transaction;
62 cmd2.CommandText = "DROP TABLE CLOBTEST";
63 cmd2.ExecuteNonQuery ();
65 catch (OracleException oe1) {
66 // ignore if table already exists
69 Console.WriteLine (" CREATE TABLE ...");
71 OracleCommand create = connection.CreateCommand ();
72 create.Transaction = transaction;
73 create.CommandText = "CREATE TABLE CLOBTEST (CLOB_COLUMN CLOB)";
74 create.ExecuteNonQuery ();
76 Console.WriteLine (" INSERT RECORD ...");
78 OracleCommand insert = connection.CreateCommand ();
79 insert.Transaction = transaction;
80 insert.CommandText = "INSERT INTO CLOBTEST VALUES (EMPTY_CLOB())";
81 insert.ExecuteNonQuery ();
83 OracleCommand select = connection.CreateCommand ();
84 select.Transaction = transaction;
85 select.CommandText = "SELECT CLOB_COLUMN FROM CLOBTEST FOR UPDATE";
86 Console.WriteLine (" SELECTING A CLOB (CHARACTER) VALUE FROM CLOBTEST");
88 OracleDataReader reader = select.ExecuteReader ();
90 Console.WriteLine ("ERROR: RECORD NOT FOUND");
92 Console.WriteLine (" TESTING OracleLob OBJECT ...");
93 OracleLob lob = reader.GetOracleLob (0);
94 Console.WriteLine (" LENGTH: {0}", lob.Length);
95 Console.WriteLine (" CHUNK SIZE: {0}", lob.ChunkSize);
97 UnicodeEncoding encoding = new UnicodeEncoding ();
100 // read file "cs-parser.cs" into the oracle clob
101 using (StreamReader sr = new StreamReader(infilename)) {
102 string sbuff = sr.ReadToEnd ();
103 byte[] evalue = encoding.GetBytes (sbuff);
104 lob.Write (evalue, 0, evalue.Length);
107 catch (Exception e) {
108 Console.WriteLine("The file could not be read:");
109 Console.WriteLine(e.Message);
113 Console.WriteLine (" CLOSING READER...");
116 transaction.Commit ();