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 }