mcs/class/System.Data.OracleClient/System.Data.OracleClient/OracleParameter.cs

   1 //
   2 // OracleParameter.cs
   3 //
   4 // Part of the Mono class libraries at
   5 // mcs/class/System.Data.OracleClient/System.Data.OracleClient
   6 //
   7 // Assembly: System.Data.OracleClient.dll
   8 // Namespace: System.Data.OracleClient
   9 //
  10 // Authors:
  11 //    Tim Coleman <tim@timcoleman.com>
  12 //    Daniel Moragn <monodanmorg@yahoo.com>
  13 //    Hubert FONGARNAND <informatique.internet@fiducial.fr>
  14 //
  15 // Copyright (C) Tim Coleman , 2003
  16 // Copyright (C) Daniel Morgan, 2005, 2008
  17 // Copyright (C) Hubert FONGARNAND, 2005
  18 //
  19 // Licensed under the MIT/X11 License.
  20 //
  21
  22 using System;
  23 using System.Collections;
  24 using System.ComponentModel;
  25 using System.Data;
  26 #if NET_2_0
  27 using System.Data.Common;
  28 #endif
  29 using System.Data.SqlTypes;
  30 using System.Data.OracleClient.Oci;
  31 using System.Globalization;
  32 using System.Runtime.InteropServices;
  33 using System.Text;
  34
  35 namespace System.Data.OracleClient
  36 {
  37         [TypeConverter (typeof(OracleParameter.OracleParameterConverter))]
  38         public sealed class OracleParameter :
  39 #if NET_2_0
  40                 DbParameter, IDbDataParameter, ICloneable
  41 #else
  42                 MarshalByRefObject, IDbDataParameter, IDataParameter, ICloneable
  43 #endif
  44         {
  45                 #region Fields
  46
  47                 string name;
  48                 OracleType oracleType = OracleType.VarChar;
  49                 OciDataType ociType;
  50                 int size;
  51                 ParameterDirection direction = ParameterDirection.Input;
  52                 bool isNullable;
  53                 byte precision;
  54                 byte scale;
  55                 string srcColumn;
  56 #if NET_2_0
  57                 bool sourceColumnNullMapping;
  58 #endif
  59                 DataRowVersion srcVersion;
  60                 DbType dbType = DbType.AnsiString;
  61                 int offset;
  62                 bool sizeSet;
  63                 bool oracleTypeSet;
  64                 object value = DBNull.Value;
  65                 OciLobLocator lobLocator;  // only if Blob or Clob
  66                 IntPtr bindOutValue = IntPtr.Zero;
  67                 OciDateTimeDescriptor dateTimeDesc;
  68                 IntPtr cursor = IntPtr.Zero;
  69
  70                 OracleParameterCollection container;
  71                 OciBindHandle bindHandle;
  72                 OracleConnection connection;
  73                 byte[] bytes;
  74                 IntPtr bindValue = IntPtr.Zero;
  75                 bool useRef;
  76                 OciDataType bindType;
  77                 OracleType bindOracleType;
  78
  79                 short indicator;
  80                 int bindSize;
  81
  82                 #endregion // Fields
  83
  84                 #region Constructors
  85
  86                 // constructor for cloning the object
  87                 private OracleParameter (OracleParameter value)
  88                 {
  89                         this.name = value.name;
  90                         this.oracleType = value.oracleType;
  91                         this.ociType = value.ociType;
  92                         this.size = value.size;
  93                         this.direction = value.direction;
  94                         this.isNullable = value.isNullable;
  95                         this.precision = value.precision;
  96                         this.scale = value.scale;
  97                         this.srcColumn = value.srcColumn;
  98                         this.srcVersion = value.srcVersion;
  99                         this.dbType = value.dbType;
 100                         this.offset = value.offset;
 101                         this.sizeSet = value.sizeSet;
 102                         this.value = value.value;
 103                         this.lobLocator = value.lobLocator;
 104                         this.oracleTypeSet = value.oracleTypeSet;
 105                 }
 106
 107                 public OracleParameter ()
 108                 {
 109                         this.name = String.Empty;
 110                         this.oracleType = OracleType.VarChar;
 111                         this.size = 0;
 112                         this.direction = ParameterDirection.Input;
 113                         this.isNullable = false;
 114                         this.precision = 0;
 115                         this.scale = 0;
 116                         this.srcColumn = String.Empty;
 117                         this.srcVersion = DataRowVersion.Current;
 118                         this.value = null;
 119                         this.oracleTypeSet = false;
 120                 }
 121
 122                 public OracleParameter (string name, object value)
 123                 {
 124                         this.name = name;
 125                         this.value = value;
 126                         srcColumn = string.Empty;
 127                         SourceVersion = DataRowVersion.Current;
 128                         InferOracleType (value);
 129                 }
 130
 131                 public OracleParameter (string name, OracleType oracleType)
 132                         : this (name, oracleType, 0, ParameterDirection.Input, false, 0, 0, String.Empty, DataRowVersion.Current, null)
 133                 {
 134                 }
 135
 136                 public OracleParameter (string name, OracleType oracleType, int size)
 137                         : this (name, oracleType, size, ParameterDirection.Input, false, 0, 0, String.Empty, DataRowVersion.Current, null)
 138                 {
 139                 }
 140
 141                 public OracleParameter (string name, OracleType oracleType, int size, string srcColumn)
 142                         : this (name, oracleType, size, ParameterDirection.Input, false, 0, 0, srcColumn, DataRowVersion.Current, null)
 143                 {
 144                 }
 145
 146 #if NET_2_0
 147                 public OracleParameter (string name, OracleType oracleType, int size, ParameterDirection direction, string sourceColumn, DataRowVersion sourceVersion, bool sourceColumnNullMapping, object value)
 148                 {
 149                         this.name = name;
 150                         if (size < 0)
 151                                 throw new ArgumentException("Size must be not be negative.");
 152                         this.size = size;
 153                         this.value = value;
 154                         SourceColumnNullMapping = sourceColumnNullMapping;
 155                         OracleType = oracleType;
 156                         Direction = direction;
 157                         SourceColumn = sourceColumn;
 158                         SourceVersion = sourceVersion;
 159                 }
 160 #endif
 161
 162                 public OracleParameter (string name, OracleType oracleType, int size, ParameterDirection direction, bool isNullable, byte precision, byte scale, string srcColumn, DataRowVersion srcVersion, object value)
 163                 {
 164                         this.name = name;
 165                         if (size < 0)
 166                                 throw new ArgumentException("Size must be not be negative.");
 167                         this.size = size;
 168                         this.value = value;
 169                         this.isNullable = isNullable;
 170                         this.precision = precision;
 171                         this.scale = scale;
 172
 173                         OracleType = oracleType;
 174                         Direction = direction;
 175                         SourceColumn = srcColumn;
 176                         SourceVersion = srcVersion;
 177                 }
 178
 179                 #endregion // Constructors
 180
 181                 #region Properties
 182
 183                 internal OracleParameterCollection Container {
 184                         get { return container; }
 185                         set { container = value; }
 186                 }
 187
 188 #if !NET_2_0
 189                 [Browsable (false)]
 190                 [RefreshProperties (RefreshProperties.All)]
 191                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
 192 #endif
 193                 public
 194 #if NET_2_0
 195                 override
 196 #endif
 197                 DbType DbType {
 198                         get { return dbType; }
 199                         set { SetDbType (value); }
 200                 }
 201
 202 #if !NET_2_0
 203                 [DefaultValue (ParameterDirection.Input)]
 204 #endif
 205                 [RefreshProperties (RefreshProperties.All)]
 206                 public
 207 #if NET_2_0
 208                 override
 209 #endif
 210                 ParameterDirection Direction {
 211                         get { return direction; }
 212                         set { direction = value; }
 213                 }
 214
 215 #if !NET_2_0
 216                 [Browsable (false)]
 217                 [DesignOnly (true)]
 218                 [DefaultValue (false)]
 219                 [EditorBrowsable (EditorBrowsableState.Never)]
 220 #endif
 221                 public
 222 #if NET_2_0
 223                 override
 224 #endif
 225                 bool IsNullable {
 226                         get { return isNullable; }
 227                         set { isNullable = value; }
 228                 }
 229
 230 #if NET_2_0
 231                 [EditorBrowsable (EditorBrowsableState.Advanced)]
 232 #else
 233                 [DefaultValue (0)]
 234 #endif
 235                 [Browsable (false)]
 236                 public int Offset {
 237                         get { return offset; }
 238                         set { offset = value; }
 239                 }
 240
 241                 [DefaultValue (OracleType.VarChar)]
 242                 [RefreshProperties (RefreshProperties.All)]
 243 #if NET_2_0
 244                 [DbProviderSpecificTypeProperty (true)]
 245 #endif
 246                 public OracleType OracleType {
 247                         get { return oracleType; }
 248                         set {
 249                                 oracleTypeSet = true;
 250                                 SetOracleType (value, false);
 251                         }
 252                 }
 253
 254 #if !NET_2_0
 255                 [DefaultValue ("")]
 256 #endif
 257                 public
 258 #if NET_2_0
 259                 override
 260 #endif
 261                 string ParameterName {
 262                         get {
 263                                 if (name == null)
 264                                         return string.Empty;
 265                                 return name;
 266                         }
 267                         set { name = value; }
 268                 }
 269
 270 #if NET_2_0
 271                 [Browsable (false)]
 272                 [EditorBrowsable (EditorBrowsableState.Never)]
 273                 [Obsolete("Set the precision of a decimal use the Math classes.")]
 274 #else
 275                 [DefaultValue (0)]
 276 #endif
 277                 public byte Precision {
 278                         get { return precision; }
 279                         set { /* NO EFFECT*/ }
 280                 }
 281
 282 #if NET_2_0
 283                 [Browsable (false)]
 284                 [EditorBrowsable (EditorBrowsableState.Never)]
 285                 [Obsolete("Set the precision of a decimal use the Math classes.")]
 286 #else
 287                 [DefaultValue (0)]
 288 #endif
 289                 public byte Scale {
 290                         get { return scale; }
 291                         set { /* NO EFFECT*/ }
 292                 }
 293
 294 #if !NET_2_0
 295                 [DefaultValue (0)]
 296 #endif
 297                 public
 298 #if NET_2_0
 299                 override
 300 #endif
 301                 int Size {
 302                         get { return size; }
 303                         set {
 304                                 sizeSet = true;
 305                                 size = value;
 306                         }
 307                 }
 308
 309 #if !NET_2_0
 310                 [DefaultValue ("")]
 311 #endif
 312                 public
 313 #if NET_2_0
 314                 override
 315 #endif
 316                 string SourceColumn {
 317                         get { return srcColumn; }
 318                         set { srcColumn = value; }
 319                 }
 320
 321 #if NET_2_0
 322                 [MonoTODO]
 323                 public override bool SourceColumnNullMapping {
 324                         get { return sourceColumnNullMapping; }
 325                         set { sourceColumnNullMapping = value; }
 326                 }
 327 #endif
 328
 329 #if !NET_2_0
 330                 [DefaultValue ("Current")]
 331 #endif
 332                 public
 333 #if NET_2_0
 334                 override
 335 #endif
 336                 DataRowVersion SourceVersion {
 337                         get { return srcVersion; }
 338                         set { srcVersion = value; }
 339                 }
 340
 341 #if !NET_2_0
 342                 [DefaultValue (null)]
 343 #endif
 344                 [RefreshProperties (RefreshProperties.All)]
 345                 [TypeConverter (typeof(StringConverter))]
 346                 public
 347 #if NET_2_0
 348                 override
 349 #endif
 350                 object Value {
 351                         get { return this.value; }
 352                         set {
 353                                 this.value = value;
 354                                 if (!oracleTypeSet)
 355                                         InferOracleType (value);
 356                         }
 357                 }
 358
 359                 #endregion // Properties
 360
 361                 #region Methods
 362
 363                 private void AssertSizeIsSet ()
 364                 {
 365                         switch (ociType) {
 366                         case OciDataType.VarChar2:
 367                         case OciDataType.String:
 368                         case OciDataType.VarChar:
 369                         case OciDataType.Char:
 370                         case OciDataType.CharZ:
 371                         case OciDataType.OciString:
 372                                 if (!sizeSet)
 373                                         throw new Exception ("Size must be set.");
 374                                 break;
 375                         default:
 376                                 break;
 377                         }
 378                 }
 379
 380                 internal void Bind (OciStatementHandle statement, OracleConnection con, uint pos)
 381                 {
 382                         connection = con;
 383
 384                         if (bindHandle == null)
 385                                 bindHandle = new OciBindHandle ((OciHandle) statement);
 386
 387                         IntPtr tmpHandle = bindHandle.Handle;
 388
 389                         if (Direction != ParameterDirection.Input)
 390                                 AssertSizeIsSet ();
 391                         if (!sizeSet)
 392                                 size = InferSize ();
 393
 394                         bindSize = size;
 395                         object v = value;
 396                         int status = 0;
 397                         bindType = ociType;
 398                         int rsize = 0;
 399
 400                         string svalue;
 401                         string sDate;
 402                         DateTime dt;
 403                         bool isnull = false;
 404
 405                         if (direction == ParameterDirection.Input || direction == ParameterDirection.InputOutput) {
 406                                 if (v == null)
 407                                         isnull = true;
 408                                 else if (v is DBNull)
 409                                         isnull = true;
 410                                 else {
 411                                         INullable mynullable = v as INullable;
 412                                         if (mynullable != null)
 413                                                 isnull = mynullable.IsNull;
 414                                 }
 415                         }
 416
 417                         if (isnull == true && direction == ParameterDirection.Input) {
 418                                 indicator = 0;
 419                                 bindType = OciDataType.VarChar2;
 420                                 bindSize = 0;
 421                         } else {
 422                                 switch(ociType) {
 423                                 case OciDataType.VarChar2:
 424                                 case OciDataType.String:
 425                                 case OciDataType.VarChar:
 426                                 case OciDataType.Char:
 427                                 case OciDataType.CharZ:
 428                                 case OciDataType.OciString:
 429                                         bindType = OciDataType.String;
 430                                         indicator = 0;
 431                                         svalue = "\0";
 432                                         // convert value from managed type to type to marshal
 433                                         if (direction == ParameterDirection.Input ||
 434                                                 direction == ParameterDirection.InputOutput) {
 435
 436                                                 svalue = v.ToString () + '\0';
 437                                         }
 438
 439                                         // set bind length to size of data
 440                                         bindSize = (size + 1) * 4;
 441
 442                                         // allocate memory based on bind length
 443                                         bytes = new byte[bindSize];
 444
 445                                         if (direction == ParameterDirection.Input ||
 446                                                 direction == ParameterDirection.InputOutput) {
 447
 448                                                 // convert managed type to memory allocated earlier
 449                                                 // in this case using OCIUnicodeToCharSet
 450                                                 rsize = 0;
 451                                                 // Get size of buffer
 452                                                 status = OciCalls.OCIUnicodeToCharSet (statement.Parent, null, svalue, out rsize);
 453                                                 // Fill buffer
 454                                                 status = OciCalls.OCIUnicodeToCharSet (statement.Parent, bytes, svalue, out rsize);
 455                                         }
 456                                         break;
 457                                 case OciDataType.Date:
 458                                         bindType = OciDataType.Date;
 459                                         bindSize = 7;
 460                                         // convert value from managed type to type to marshal
 461                                         if (direction == ParameterDirection.Input ||
 462                                                 direction == ParameterDirection.InputOutput) {
 463
 464                                                 if (isnull)
 465                                                         bytes = new byte [7];
 466                                                 else {
 467                                                         sDate = "";
 468                                                         dt = DateTime.MinValue;
 469                                                         if (v is String) {
 470                                                                 sDate = (string) v;
 471                                                                 dt = DateTime.Parse (sDate);
 472                                                         }
 473                                                         else if (v is DateTime)
 474                                                                 dt = (DateTime) v;
 475                                                         else if (v is OracleString) {
 476                                                                 sDate = v.ToString ();
 477                                                                 dt = DateTime.Parse (sDate);
 478                                                         }
 479                                                         else if (v is OracleDateTime) {
 480                                                                 OracleDateTime odt = (OracleDateTime) v;
 481                                                                 dt = (DateTime) odt.Value;
 482                                                         }
 483                                                         else
 484                                                                 throw new NotImplementedException ("For OracleType.DateTime, data type not implemented: " + v.GetType().ToString() + ".");
 485
 486                                                         // for Input and InputOuput, create byte array and pack DateTime into it
 487                                                         bytes = PackDate (dt);
 488                                                 }
 489                                         } else  {
 490                                                 // allocate 7-byte array for Output and ReturnValue to put date
 491                                                 bytes = new byte [7];
 492                                         }
 493                                         break;
 494                                 case OciDataType.Number:
 495                                         bindType = OciDataType.String;
 496                                         indicator = 0;
 497                                         svalue = "\0";
 498                                         // define size for binding
 499                                         bindSize = 30; // a NUMBER is 22 bytes but as a string we need more
 500                                         // allocate memory
 501                                         bytes = new byte [bindSize];
 502                                         // convert value from managed type to type to marshal
 503                                         if (direction == ParameterDirection.Input ||
 504                                                 direction == ParameterDirection.InputOutput) {
 505
 506                                                 svalue = null;
 507                                                 if(v is IFormattable)
 508                                                         svalue = ((IFormattable)v).ToString (null, con.SessionFormatProvider);
 509                                                 else if (v is OracleNumber)
 510                                                         svalue = ((OracleNumber)v).ToString(con.SessionFormatProvider);
 511                                                 else
 512                                                         svalue = v.ToString();
 513
 514                                                 svalue = svalue + "\0";
 515
 516                                                 rsize = 0;
 517                                                 // Get size of buffer
 518                                                 OciCalls.OCIUnicodeToCharSet (statement.Parent, null, svalue, out rsize);
 519
 520                                                 // Fill buffer
 521                                                 bytes = new byte [bindSize];
 522                                                 OciCalls.OCIUnicodeToCharSet (statement.Parent, bytes, svalue, out rsize);
 523                                         }
 524                                         break;
 525                                         case OciDataType.Long:
 526                                         case OciDataType.LongVarChar:
 527                                                 bindType = OciDataType.LongVarChar;
 528
 529                                                 // FIXME: use piecewise fetching for Long, Clob, Blob, and Long Raw
 530                                                 // See http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14250/oci05bnd.htm#sthref724
 531
 532                                                 bindSize = Size + 5; // 4 bytes prepended for length, bytes, 1 byte NUL character
 533
 534                                                 indicator = 0;
 535                                                 svalue = "\0";
 536                                                 // convert value from managed type to type to marshal
 537                                                 if (direction == ParameterDirection.Input ||
 538                                                         direction == ParameterDirection.InputOutput) {
 539
 540                                                         svalue = v.ToString () + '\0';
 541                                                 }
 542
 543                                                 bytes = new byte [bindSize];
 544                                                 // LONG is only ANSI
 545                                                 ASCIIEncoding enc = new ASCIIEncoding ();
 546
 547                                                 if (direction == ParameterDirection.Input ||
 548                                                         direction == ParameterDirection.InputOutput) {
 549                                                         int byteCount = 0;
 550                                                         if (svalue.Length > 0) {
 551                                                                 byteCount = enc.GetBytes (svalue, 4, svalue.Length, bytes, 0);
 552                                                                 // LONG VARCHAR prepends a 4-byte length
 553                                                                 if (byteCount > 0) {
 554                                                                         byte[] byteArrayLen = BitConverter.GetBytes ((uint) byteCount);
 555                                                                         bytes[0] = byteArrayLen[0];
 556                                                                         bytes[1] = byteArrayLen[1];
 557                                                                         bytes[2] = byteArrayLen[2];
 558                                                                         bytes[3] = byteArrayLen[3];
 559                                                                 }
 560                                                         }
 561                                                 }
 562                                                 break;
 563                                         case OciDataType.Clob:
 564                                                 if (direction == ParameterDirection.Input) {
 565                                                         svalue = v.ToString();
 566                                                         rsize = 0;
 567
 568                                                         // Get size of buffer
 569                                                         OciCalls.OCIUnicodeToCharSet (statement.Parent, null, svalue, out rsize);
 570
 571                                                         // Fill buffer
 572                                                         bytes = new byte[rsize];
 573                                                         OciCalls.OCIUnicodeToCharSet (statement.Parent, bytes, svalue, out rsize);
 574
 575                                                         bindType = OciDataType.Long;
 576                                                         bindSize = bytes.Length;
 577                                                 }
 578                                                 else if (direction == ParameterDirection.InputOutput)
 579                                                         // not the exact error that .net 2.0 throws, but this is better
 580                                                         throw new NotImplementedException ("Parameters of OracleType.Clob with direction of InputOutput are not supported.");
 581                                                 else {
 582                                                         // Output and Return parameters
 583                                                         bindSize = -1;
 584                                                         lobLocator = (OciLobLocator) connection.Environment.Allocate (OciHandleType.LobLocator);
 585                                                         if (lobLocator == null) {
 586                                                                 OciErrorInfo info = connection.ErrorHandle.HandleError ();
 587                                                                 throw new OracleException (info.ErrorCode, info.ErrorMessage);
 588                                                         }
 589                                                         bindOutValue = lobLocator.Handle;
 590                                                         bindValue = lobLocator.Handle;
 591                                                         lobLocator.ErrorHandle = connection.ErrorHandle;
 592                                                         lobLocator.Service = statement.Service;
 593                                                         useRef = true;
 594                                                 }
 595                                                 break;
 596                                 default:
 597                                         // FIXME: move this up - see how Char, Number, and Date are done...
 598                                         if (direction == ParameterDirection.Output ||
 599                                                 direction == ParameterDirection.InputOutput ||
 600                                                 direction == ParameterDirection.ReturnValue) {
 601
 602                                                 switch(ociType) {
 603                                                 case OciDataType.RowIdDescriptor:
 604                                                         size = 10;
 605                                                         bindType = OciDataType.Char;
 606                                                         bindSize = size * 2;
 607                                                         bindOutValue = OciCalls.AllocateClear (bindSize);
 608                                                         bindValue = bindOutValue;
 609                                                         break;
 610                                                 case OciDataType.TimeStamp:
 611                                                         dateTimeDesc = (OciDateTimeDescriptor) connection.Environment.Allocate (OciHandleType.TimeStamp);
 612                                                         if (dateTimeDesc == null) {
 613                                                                 OciErrorInfo info = connection.ErrorHandle.HandleError ();
 614                                                                 throw new OracleException (info.ErrorCode, info.ErrorMessage);
 615                                                         }
 616                                                         dateTimeDesc.ErrorHandle = connection.ErrorHandle;
 617                                                         bindSize = 11;
 618                                                         bindType = OciDataType.TimeStamp;
 619                                                         bindOutValue = dateTimeDesc.Handle;
 620                                                         bindValue = dateTimeDesc.Handle;
 621                                                         useRef = true;
 622                                                         break;
 623                                                 case OciDataType.Blob:
 624                                                         bindSize = -1;
 625                                                         lobLocator = (OciLobLocator) connection.Environment.Allocate (OciHandleType.LobLocator);
 626                                                         if (lobLocator == null) {
 627                                                                 OciErrorInfo info = connection.ErrorHandle.HandleError ();
 628                                                                 throw new OracleException (info.ErrorCode, info.ErrorMessage);
 629                                                         }
 630                                                         bindOutValue = lobLocator.Handle;
 631                                                         bindValue = lobLocator.Handle;
 632                                                         lobLocator.ErrorHandle = connection.ErrorHandle;
 633                                                         lobLocator.Service = statement.Service;
 634                                                         useRef = true;
 635                                                         break;
 636                                                 case OciDataType.RSet: // REF CURSOR
 637                                                         cursor = IntPtr.Zero;
 638                                                         OciCalls.OCIHandleAlloc (connection.Environment,
 639                                                                 out cursor,
 640                                                                 OciHandleType.Statement,
 641                                                                 0,
 642                                                                 IntPtr.Zero);
 643
 644                                                         bindSize = 0;
 645                                                         bindType = OciDataType.RSet;
 646                                                         break;
 647                                                 default:
 648                                                         // define other types
 649                                                         throw new NotImplementedException ("Data Type not implemented: " + ociType.ToString() + ".");
 650                                                 } // switch of ociDataType for output
 651                                                 bindValue = bindOutValue;
 652                                         }
 653                                         else if ((v == DBNull.Value || v == null || isnull == true) && direction == ParameterDirection.Input) {
 654                                                 indicator = 0;
 655                                                 bindType = OciDataType.VarChar2;
 656                                                 bindSize = 0;
 657                                         }
 658                                         else {
 659                                                 sDate = "";
 660                                                 dt = DateTime.MinValue;
 661                                                 if (bindOracleType == OracleType.Timestamp){
 662                                                         bindType = OciDataType.TimeStamp;
 663                                                         bindSize = 11;
 664                                                         dt = DateTime.MinValue;
 665                                                         sDate = "";
 666                                                         if (v is String){
 667                                                                 sDate = (string) v;
 668                                                                 dt = DateTime.Parse (sDate);
 669                                                         }
 670                                                         else if (v is DateTime)
 671                                                                 dt = (DateTime) v;
 672                                                         else if (v is OracleString){
 673                                                                 sDate = (string) v;
 674                                                                 dt = DateTime.Parse (sDate);
 675                                                         }
 676                                                         else if (v is OracleDateTime) {
 677                                                                 OracleDateTime odt = (OracleDateTime) v;
 678                                                                 dt = (DateTime) odt.Value;
 679                                                         }
 680                                                         else
 681                                                                 throw new NotImplementedException ("For OracleType.Timestamp, data type not implemented: " + v.GetType().ToString()); // ?
 682
 683                                                         short year = (short) dt.Year;
 684                                                         byte month = (byte) dt.Month;
 685                                                         byte day = (byte) dt.Day;
 686                                                         byte hour = (byte) dt.Hour;
 687                                                         byte min = (byte) dt.Minute;
 688                                                         byte sec = (byte) dt.Second;
 689                                                         uint fsec = (uint) dt.Millisecond;
 690                                                         string timezone = "";
 691                                                         dateTimeDesc = (OciDateTimeDescriptor) connection.Environment.Allocate (OciHandleType.TimeStamp);
 692                                                         if (dateTimeDesc == null) {
 693                                                                 OciErrorInfo info = connection.ErrorHandle.HandleError ();
 694                                                                 throw new OracleException (info.ErrorCode, info.ErrorMessage);
 695                                                         }
 696                                                         dateTimeDesc.ErrorHandle = connection.ErrorHandle;
 697                                                         dateTimeDesc.SetDateTime (connection.Session,
 698                                                                 connection.ErrorHandle,
 699                                                                 year, month, day, hour, min, sec, fsec,
 700                                                                 timezone);
 701                                                         useRef = true;
 702                                                 }
 703                                                 else if (bindOracleType == OracleType.Blob) {
 704                                                         bytes = (byte[]) v;
 705                                                         bindType = OciDataType.LongRaw;
 706                                                         bindSize = bytes.Length;
 707                                                 }
 708                                                 else if (bindOracleType == OracleType.Raw) {
 709                                                         byte[] val = v as byte[];
 710                                                         bindValue = OciCalls.AllocateClear (val.Length);
 711                                                         Marshal.Copy (val, 0, bindValue, val.Length);
 712                                                         bindSize = val.Length;
 713                                                 } else {
 714                                                         svalue = v.ToString () + '\0';
 715                                                         rsize = 0;
 716
 717                                                         // Get size of buffer
 718                                                         OciCalls.OCIUnicodeToCharSet (statement.Parent, null, svalue, out rsize);
 719
 720                                                         // Fill buffer
 721                                                         bytes = new byte[rsize];
 722                                                         OciCalls.OCIUnicodeToCharSet (statement.Parent, bytes, svalue, out rsize);
 723
 724                                                         bindType = OciDataType.String;
 725                                                         bindSize = svalue.Length;
 726                                                 } // else oracleType
 727                                         } // else - Input, Ouput...
 728                                         break;
 729                                 }
 730                         }
 731
 732                         // Now, call the appropriate OCI Bind function;
 733
 734                         if (useRef == true) {
 735                                 if (bindType == OciDataType.TimeStamp) {
 736                                         bindValue = dateTimeDesc.Handle;
 737                                         status = OciCalls.OCIBindByNameRef (statement,
 738                                                 out tmpHandle,
 739                                                 connection.ErrorHandle,
 740                                                 ParameterName,
 741                                                 ParameterName.Length,
 742                                                 ref bindValue,
 743                                                 bindSize,
 744                                                 bindType,
 745                                                 ref indicator,
 746                                                 IntPtr.Zero,
 747                                                 IntPtr.Zero,
 748                                                 0,
 749                                                 IntPtr.Zero,
 750                                                 0);
 751                                 }
 752                                 else {
 753                                         status = OciCalls.OCIBindByNameRef (statement,
 754                                                 out tmpHandle,
 755                                                 connection.ErrorHandle,
 756                                                 ParameterName,
 757                                                 ParameterName.Length,
 758                                                 ref bindValue,
 759                                                 bindSize,
 760                                                 bindType,
 761                                                 ref indicator,
 762                                                 IntPtr.Zero,
 763                                                 IntPtr.Zero,
 764                                                 0,
 765                                                 IntPtr.Zero,
 766                                                 0);
 767                                 }
 768                         }
 769                         else if (bindType == OciDataType.RSet) {
 770                                 status = OciCalls.OCIBindByNameRef (statement,
 771                                         out tmpHandle,
 772                                         connection.ErrorHandle,
 773                                         ParameterName,
 774                                         ParameterName.Length,
 775                                         ref cursor,
 776                                         bindSize,
 777                                         bindType,
 778                                         ref indicator,
 779                                         IntPtr.Zero,
 780                                         IntPtr.Zero,
 781                                         0,
 782                                         IntPtr.Zero,
 783                                         0);
 784                         }
 785                         else if (bytes != null) {
 786                                 status = OciCalls.OCIBindByNameBytes (statement,
 787                                         out tmpHandle,
 788                                         connection.ErrorHandle,
 789                                         ParameterName,
 790                                         ParameterName.Length,
 791                                         bytes,
 792                                         bindSize,
 793                                         bindType,
 794                                         ref indicator,
 795                                         IntPtr.Zero,
 796                                         IntPtr.Zero,
 797                                         0,
 798                                         IntPtr.Zero,
 799                                         0);
 800                         }
 801                         else {
 802                                 status = OciCalls.OCIBindByName (statement,
 803                                         out tmpHandle,
 804                                         connection.ErrorHandle,
 805                                         ParameterName,
 806                                         ParameterName.Length, // FIXME: this should be in bytes!
 807                                         bindValue,
 808                                         bindSize,
 809                                         bindType,
 810                                         ref indicator,
 811                                         IntPtr.Zero,
 812                                         IntPtr.Zero,
 813                                         0,
 814                                         IntPtr.Zero,
 815                                         0);
 816                         }
 817                         OciErrorHandle.ThrowExceptionIfError (connection.ErrorHandle, status);
 818
 819                         bindHandle.SetHandle (tmpHandle);
 820                 }
 821
 822                 object ICloneable.Clone ()
 823                 {
 824                         return new OracleParameter(this);
 825                 }
 826
 827                 private void InferOracleType (object value)
 828                 {
 829                         Type type = value.GetType ();
 830                         string exception = String.Format ("The parameter data type of {0} is invalid.", type.FullName);
 831                         switch (type.FullName) {
 832                         case "System.Int64":
 833                                 SetOracleType (OracleType.Number, true);
 834                                 break;
 835                         case "System.Boolean":
 836                         case "System.Byte":
 837                                 SetOracleType (OracleType.Byte, true);
 838                                 break;
 839                         case "System.String":
 840                         case "System.Data.OracleClient.OracleString":
 841                                 SetOracleType (OracleType.VarChar, true);
 842                                 break;
 843                         case "System.Data.OracleClient.OracleDateTime":
 844                         case "System.DateTime":
 845                                 SetOracleType (OracleType.DateTime, true);
 846                                 break;
 847                         case "System.Decimal":
 848                         case "System.Data.OracleClient.OracleNumber":
 849                                 SetOracleType (OracleType.Number, true);
 850                                 break;
 851                         case "System.Double":
 852                                 SetOracleType (OracleType.Double, true);
 853                                 break;
 854                         case "System.Byte[]":
 855                         case "System.Guid":
 856                                 SetOracleType (OracleType.Raw, true);
 857                                 break;
 858                         case "System.Int32":
 859                                 SetOracleType (OracleType.Int32, true);
 860                                 break;
 861                         case "System.Single":
 862                                 SetOracleType (OracleType.Float, true);
 863                                 break;
 864                         case "System.Int16":
 865                                 SetOracleType (OracleType.Int16, true);
 866                                 break;
 867                         case "System.DBNull":
 868                                 break; //unable to guess type
 869                         case "System.Data.OracleClient.OracleLob":
 870                                 SetOracleType (((OracleLob) value).LobType, true);
 871                                 break;
 872                         default:
 873                                 throw new ArgumentException (exception);
 874                         }
 875                 }
 876
 877                 private int InferSize ()
 878                 {
 879                         int newSize = 0;
 880
 881                         switch (ociType) {
 882                         case OciDataType.VarChar2:
 883                         case OciDataType.String:
 884                         case OciDataType.VarChar:
 885                         case OciDataType.Char:
 886                         case OciDataType.CharZ:
 887                         case OciDataType.OciString:
 888                         case OciDataType.Long:
 889                         case OciDataType.LongVarChar:
 890                                 if (value == null || value == DBNull.Value)
 891                                         newSize = 0;
 892                                 else
 893                                         newSize = value.ToString ().Length;
 894                                 break;
 895                         case OciDataType.RowIdDescriptor:
 896                                 newSize = 10;
 897                                 break;
 898                         case OciDataType.Integer:
 899                         case OciDataType.Number:
 900                         case OciDataType.Float:
 901                                 newSize = 22;
 902                                 break;
 903                         case OciDataType.Date:
 904                                 newSize = 7;
 905                                 break;
 906                         case OciDataType.TimeStamp:
 907                                 newSize = 11;
 908                                 break;
 909                         case OciDataType.Blob:
 910                         case OciDataType.Clob:
 911                         case OciDataType.RSet: // REF CURSOR
 912                                 newSize = -1;
 913                                 break;
 914                         default:
 915                                 if (value == null || value == DBNull.Value)
 916                                         newSize = 0;
 917                                 else
 918                                         newSize = value.ToString ().Length;
 919                                 break;
 920                         }
 921
 922                         sizeSet = true;
 923
 924                         return newSize;
 925                 }
 926
 927                 private void SetDbType (DbType type)
 928                 {
 929                         string exception = String.Format ("No mapping exists from DbType {0} to a known OracleType.", type);
 930                         switch (type) {
 931                         case DbType.AnsiString:
 932                                 oracleType = OracleType.VarChar;
 933                                 ociType = OciDataType.VarChar;
 934                                 break;
 935                         case DbType.AnsiStringFixedLength:
 936                                 oracleType = OracleType.Char;
 937                                 ociType = OciDataType.Char;
 938                                 break;
 939                         case DbType.Binary:
 940                         case DbType.Guid:
 941                                 oracleType = OracleType.Raw;
 942                                 ociType = OciDataType.Raw;
 943                                 break;
 944                         case DbType.Boolean:
 945                         case DbType.Byte:
 946                                 oracleType = OracleType.Byte;
 947                                 ociType = OciDataType.Integer;
 948                                 break;
 949                         case DbType.Currency:
 950                         case DbType.Decimal:
 951                         case DbType.Int64:
 952                                 oracleType = OracleType.Number;
 953                                 ociType = OciDataType.Number;
 954                                 break;
 955                         case DbType.Date:
 956                         case DbType.DateTime:
 957                         case DbType.Time:
 958                                 oracleType = OracleType.DateTime;
 959                                 ociType = OciDataType.Char;
 960                                 break;
 961                         case DbType.Double:
 962                                 oracleType = OracleType.Double;
 963                                 ociType = OciDataType.Float;
 964                                 break;
 965                         case DbType.Int16:
 966                                 oracleType = OracleType.Int16;
 967                                 ociType = OciDataType.Integer;
 968                                 break;
 969                         case DbType.Int32:
 970                                 oracleType = OracleType.Int32;
 971                                 ociType = OciDataType.Integer;
 972                                 break;
 973                         case DbType.Object:
 974                                 oracleType = OracleType.Blob;
 975                                 ociType = OciDataType.Blob;
 976                                 break;
 977                         case DbType.Single:
 978                                 oracleType = OracleType.Float;
 979                                 ociType = OciDataType.Float;
 980                                 break;
 981                         case DbType.String:
 982                                 oracleType = OracleType.NVarChar;
 983                                 ociType = OciDataType.VarChar;
 984                                 break;
 985                         case DbType.StringFixedLength:
 986                                 oracleType = OracleType.NChar;
 987                                 ociType = OciDataType.Char;
 988                                 break;
 989                         default:
 990                                 throw new ArgumentException (exception);
 991                         }
 992                         dbType = type;
 993                 }
 994
 995                 private void SetOracleType (OracleType type, bool inferring)
 996                 {
 997                         FreeHandle ();
 998                         string exception = String.Format ("No mapping exists from OracleType {0} to a known DbType.", type);
 999                         switch (type) {
1000                         case OracleType.BFile:
1001                         case OracleType.Blob:
1002                                 dbType = DbType.Binary;
1003                                 ociType = OciDataType.Blob;
1004                                 break;
1005                         case OracleType.LongRaw:
1006                         case OracleType.Raw:
1007                                 dbType = DbType.Binary;
1008                                 ociType = OciDataType.Raw;
1009                                 break;
1010                         case OracleType.Byte:
1011                                 dbType = DbType.Byte;
1012                                 ociType = OciDataType.Number;
1013                                 break;
1014                         case OracleType.Char:
1015                                 dbType = DbType.AnsiString;
1016                                 ociType = OciDataType.Char;
1017                                 break;
1018                         case OracleType.Clob:
1019                                 dbType = DbType.AnsiString;
1020                                 ociType = OciDataType.Clob;
1021                                 break;
1022                         case OracleType.LongVarChar:
1023                         case OracleType.RowId:
1024                         case OracleType.VarChar:
1025                                 dbType = DbType.AnsiString;
1026                                 ociType = OciDataType.VarChar;
1027                                 break;
1028                         case OracleType.Cursor: // REF CURSOR
1029                                 ociType = OciDataType.RSet;
1030                                 dbType = DbType.Object;
1031                                 break;
1032                         case OracleType.IntervalDayToSecond:
1033                                 dbType = DbType.AnsiStringFixedLength;
1034                                 ociType = OciDataType.Char;
1035                                 break;
1036                         case OracleType.Timestamp:
1037                         case OracleType.TimestampLocal:
1038                         case OracleType.TimestampWithTZ:
1039                                 dbType = DbType.DateTime;
1040                                 ociType = OciDataType.TimeStamp;
1041                                 break;
1042                         case OracleType.DateTime:
1043                                 dbType = DbType.DateTime;
1044                                 ociType = OciDataType.Date;
1045                                 break;
1046                         case OracleType.Double:
1047                                 dbType = DbType.Double;
1048                                 ociType = OciDataType.Number;
1049                                 break;
1050                         case OracleType.Float:
1051                                 dbType = DbType.Single;
1052                                 ociType = OciDataType.Number;
1053                                 break;
1054                         case OracleType.Int16:
1055                                 dbType = DbType.Int16;
1056                                 ociType = OciDataType.Number;
1057                                 break;
1058                         case OracleType.Int32:
1059                         case OracleType.IntervalYearToMonth:
1060                                 dbType = DbType.Int32;
1061                                 ociType = OciDataType.Number;
1062                                 break;
1063                         case OracleType.NChar:
1064                                 dbType = DbType.StringFixedLength;
1065                                 ociType = OciDataType.Char;
1066                                 break;
1067                         case OracleType.NClob:
1068                         case OracleType.NVarChar:
1069                                 dbType = DbType.String;
1070                                 ociType = OciDataType.Char;
1071                                 break;
1072                         case OracleType.Number:
1073                                 dbType = DbType.VarNumeric;
1074                                 ociType = OciDataType.Number;
1075                                 break;
1076                         case OracleType.SByte:
1077                                 dbType = DbType.SByte;
1078                                 ociType = OciDataType.Number;
1079                                 break;
1080                         case OracleType.UInt16:
1081                                 dbType = DbType.UInt16;
1082                                 ociType = OciDataType.Number;
1083                                 break;
1084                         case OracleType.UInt32:
1085                                 dbType = DbType.UInt32;
1086                                 ociType = OciDataType.Number;
1087                                 break;
1088                         default:
1089                                 throw new ArgumentException (exception);
1090                         }
1091
1092                         if (!inferring)
1093                                 oracleType = type;
1094                         bindOracleType = type;
1095                 }
1096
1097 #if NET_2_0
1098                 public override void ResetDbType ()
1099                 {
1100                         ResetOracleType ();
1101                 }
1102
1103                 public void ResetOracleType ()
1104                 {
1105                         oracleTypeSet = false;
1106                         InferOracleType (value);
1107                 }
1108 #endif // NET_2_0
1109
1110                 public override string ToString ()
1111                 {
1112                         return ParameterName;
1113                 }
1114
1115                 private void GetOutValue (OracleCommand cmd)
1116                 {
1117                         // used to update the parameter value
1118                         // for Output, the output of InputOutput, and Return parameters
1119                         value = DBNull.Value;
1120                         if (indicator == -1)
1121                                 return;
1122
1123                         int rsize = 0;
1124                         IntPtr env = IntPtr.Zero;
1125                         StringBuilder ret = null;
1126
1127                         // FIXME: redo all types - see how Char, Number, and Date are done
1128                         // here and in Bind()
1129
1130                         switch (ociType) {
1131                         case OciDataType.VarChar2:
1132                         case OciDataType.String:
1133                         case OciDataType.VarChar:
1134                         case OciDataType.Char:
1135                         case OciDataType.CharZ:
1136                         case OciDataType.OciString:
1137                         case OciDataType.RowIdDescriptor:
1138                                 // Get length of returned string
1139                                 rsize = 0;
1140                                 env = cmd.Connection.Environment;
1141                                 OciCalls.OCICharSetToUnicode (env, null, bytes, out rsize);
1142
1143                                 // Get string
1144                                 ret = new StringBuilder(rsize);
1145                                 OciCalls.OCICharSetToUnicode (env, ret, bytes, out rsize);
1146
1147                                 value = ret.ToString ();
1148                                 break;
1149                         case OciDataType.Long:
1150                         case OciDataType.LongVarChar:
1151                                 int longSize = 0;
1152                                 if (BitConverter.IsLittleEndian)
1153                                         longSize = BitConverter.ToInt32 (new byte [] {bytes [0], bytes [1], bytes [2], bytes [3]}, 0);
1154                                 else
1155                                         longSize = BitConverter.ToInt32 (new byte [] {bytes [3], bytes [2], bytes [1], bytes [0]}, 0);
1156
1157                                 ASCIIEncoding encoding = new ASCIIEncoding ();
1158                                 value = encoding.GetString (bytes, 4, longSize);
1159                                 encoding = null;
1160                                 break;
1161                         case OciDataType.Integer:
1162                         case OciDataType.Number:
1163                         case OciDataType.Float:
1164                                 rsize = 0;
1165                                 env = cmd.Connection.Environment;
1166                                 OciCalls.OCICharSetToUnicode (env, null, bytes, out rsize);
1167
1168                                 // Get string
1169                                 ret = new StringBuilder(rsize);
1170                                 OciCalls.OCICharSetToUnicode (env, ret, bytes, out rsize);
1171
1172                                 // if not empty, parse string as a decimal using session format
1173                                 if (ret.Length > 0)
1174                                         value = Decimal.Parse (ret.ToString (), cmd.Connection.SessionFormatProvider);
1175                                 break;
1176                         case OciDataType.TimeStamp:
1177                                 value = dateTimeDesc.GetDateTime (connection.Environment, dateTimeDesc.ErrorHandle);
1178                                 break;
1179                         case OciDataType.Date:
1180                                 value = UnpackDate (bytes);
1181                                 break;
1182                         case OciDataType.Blob:
1183                         case OciDataType.Clob:
1184                                 OracleLob lob = new OracleLob (lobLocator, ociType);
1185                                 lob.connection = connection;
1186                                 value = lob;
1187                                 break;
1188                         case OciDataType.RSet: // REF CURSOR
1189                                 OciStatementHandle cursorStatement = GetOutRefCursor (cmd);
1190                                 value = new OracleDataReader (cursorStatement.Command, cursorStatement, true, CommandBehavior.Default);
1191                                 break;
1192                         default:
1193                                 throw new NotImplementedException ("Data Type not implemented: " + ociType.ToString() + ".");
1194                         }
1195                 }
1196
1197                 internal OciStatementHandle GetOutRefCursor (OracleCommand cmd)
1198                 {
1199                                 OciStatementHandle cursorStatement = new OciStatementHandle (cmd.Connection.ServiceContext, cursor);
1200
1201                                 cursorStatement.ErrorHandle = cmd.ErrorHandle;
1202                                 cursorStatement.Command = cmd;
1203                                 cursorStatement.SetupRefCursorResult (cmd.Connection);
1204                                 cursorStatement.Service = cmd.Connection.ServiceContext;
1205                                 cursor = IntPtr.Zero;
1206                                 return cursorStatement;
1207                 }
1208
1209                 internal void Update (OracleCommand cmd)
1210                 {
1211                         if (Direction != ParameterDirection.Input)
1212                                 GetOutValue (cmd);
1213
1214                         FreeHandle ();
1215                 }
1216
1217                 internal void FreeHandle ()
1218                 {
1219                         switch (ociType) {
1220                         case OciDataType.Clob:
1221                         case OciDataType.Blob:
1222                                 lobLocator = null;
1223                                 break;
1224                         case OciDataType.Raw:
1225                                 Marshal.FreeHGlobal (bindValue);
1226                                 break;
1227                         case OciDataType.TimeStamp:
1228                                 break;
1229                         default:
1230                                 Marshal.FreeHGlobal (bindOutValue);
1231                                 break;
1232                         }
1233
1234                         bindOutValue = IntPtr.Zero;
1235                         bindValue = IntPtr.Zero;
1236
1237                         bindHandle = null;
1238                         connection = null;
1239                 }
1240
1241                 // copied from OciDefineHandle
1242                 [MonoTODO ("Be able to handle negative dates... i.e. BCE.")]
1243                 private DateTime UnpackDate (byte[] bytes)
1244                 {
1245                         byte century = bytes [0];
1246                         byte year    = bytes [1];
1247                         byte month   = bytes [2];
1248                         byte day     = bytes [3];
1249                         byte hour    = bytes [4];
1250                         byte minute  = bytes [5];
1251                         byte second  = bytes [6];
1252
1253
1254                         return new DateTime ((century - 100) * 100 + (year - 100),
1255                                                 month,
1256                                                 day,
1257                                                 hour - 1,
1258                                                 minute - 1,
1259                                                 second - 1);
1260
1261                 }
1262
1263                 private byte[] PackDate (DateTime dateValue)
1264                 {
1265                         byte[] buffer = new byte[7];
1266
1267                         buffer[0] = (byte)((dateValue.Year / 100) + 100); //century
1268                         buffer[1] = (byte)((dateValue.Year % 100) + 100); // Year
1269                         buffer[2] = (byte)dateValue.Month;
1270                         buffer[3] = (byte)dateValue.Day;
1271                         buffer[4] = (byte)(dateValue.Hour+1);
1272                         buffer[5] = (byte)(dateValue.Minute+1);
1273                         buffer[6] = (byte)(dateValue.Second+1);
1274
1275                         return buffer;
1276                 }
1277
1278                 #endregion // Methods
1279
1280                 internal sealed class OracleParameterConverter : ExpandableObjectConverter
1281                 {
1282                         public OracleParameterConverter ()
1283                         {
1284                         }
1285
1286                         [MonoTODO]
1287                         public override bool CanConvertTo (ITypeDescriptorContext context, Type destinationType)
1288                         {
1289                                 throw new NotImplementedException ();
1290                         }
1291
1292                         [MonoTODO]
1293                         public override object ConvertTo (ITypeDescriptorContext context, CultureInfo culture, object value, Type destinationType)
1294                         {
1295                                 throw new NotImplementedException ();
1296                         }
1297                 }
1298         }
1299 }