mcs/class/System.Data.OracleClient/Test/refcursortest.cs

   1  using System;
   2  using System.Data;
   3  using System.Data.OracleClient;
   4
   5  public class Test
   6  {
   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