1 // testclob.cs - tests loading a text file into an oracle clob and vice-versa
2 using System;
3 using System.Data;
4 using System.Data.OracleClient;
5 using System.Text;
6 using System.IO;
7 
8 class TestClob
9 {
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"
13 
14 	public static void Main (string[] args)
15 	{
16 		OracleConnection con = new OracleConnection();
17 		con.ConnectionString = connectionString;
18 		con.Open();
19 
20 		CLOBTest (con);
21 		ReadClob (con);
22 
23 		con.Close();
24 		con = null;
25 	}
26 
27 	// read the CLOB into file "cs-parser2.cs"
28 	public static void ReadClob (OracleConnection connection)
29 	{
30 		OracleCommand rcmd = connection.CreateCommand ();
31 		rcmd.CommandText = "SELECT CLOB_COLUMN FROM CLOBTEST";
32 		OracleDataReader reader2 = rcmd.ExecuteReader ();
33 		if (!reader2.Read ())
34 			Console.WriteLine ("ERROR: RECORD NOT FOUND");
35 
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);
40 
41 		string lobvalue = (string) lob2.Value;
42 
43 		using (StreamWriter sw = new StreamWriter(outfilename)) {
44 			sw.Write(lobvalue);
45 		}
46 
47 		lob2.Close ();
48 		reader2.Close ();
49 
50 	}
51 
52 	public static void CLOBTest (OracleConnection connection)
53 	{
54 		Console.WriteLine ("  BEGIN TRANSACTION ...");
55 
56 		OracleTransaction transaction = connection.BeginTransaction ();
57 
58 		Console.WriteLine ("  Drop table CLOBTEST ...");
59 		try {
60 			OracleCommand cmd2 = connection.CreateCommand ();
61 			cmd2.Transaction = transaction;
62 			cmd2.CommandText = "DROP TABLE CLOBTEST";
63 			cmd2.ExecuteNonQuery ();
64 		}
65 		catch (OracleException oe1) {
66 			// ignore if table already exists
67 		}
68 
69 		Console.WriteLine ("  CREATE TABLE ...");
70 
71 		OracleCommand create = connection.CreateCommand ();
72 		create.Transaction = transaction;
73 		create.CommandText = "CREATE TABLE CLOBTEST (CLOB_COLUMN CLOB)";
74 		create.ExecuteNonQuery ();
75 
76 		Console.WriteLine ("  INSERT RECORD ...");
77 
78 		OracleCommand insert = connection.CreateCommand ();
79 		insert.Transaction = transaction;
80 		insert.CommandText = "INSERT INTO CLOBTEST VALUES (EMPTY_CLOB())";
81 		insert.ExecuteNonQuery ();
82 
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");
87 
88 		OracleDataReader reader = select.ExecuteReader ();
89 		if (!reader.Read ())
90 			Console.WriteLine ("ERROR: RECORD NOT FOUND");
91 
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);
96 
97 		UnicodeEncoding encoding = new UnicodeEncoding ();
98 
99 		try {
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);
105 			}
106 		}
107 		catch (Exception e) {
108 			Console.WriteLine("The file could not be read:");
109 			Console.WriteLine(e.Message);
110 		}
111 		lob.Close ();
112 
113 		Console.WriteLine ("  CLOSING READER...");
114 
115 		reader.Close ();
116 		transaction.Commit ();
117 	}
118 }