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