lib/WikiDB/adodb/drivers/adodb-oci8.inc.php

   1 <?php
   2 /*
   3
   4   version V5.19  23-Apr-2014  (c) 2000-2014 John Lim. All rights reserved.
   5
   6   Released under both BSD license and Lesser GPL library license.
   7   Whenever there is any discrepancy between the two licenses,
   8   the BSD license will take precedence.
   9
  10   Latest version is available at http://adodb.sourceforge.net
  11
  12   Code contributed by George Fourlanos <fou@infomap.gr>
  13
  14   13 Nov 2000 jlim - removed all ora_* references.
  15 */
  16
  17 // security - hide paths
  18 if (!defined('ADODB_DIR')) die();
  19
  20 /*
  21 NLS_Date_Format
  22 Allows you to use a date format other than the Oracle Lite default. When a literal
  23 character string appears where a date value is expected, the Oracle Lite database
  24 tests the string to see if it matches the formats of Oracle, SQL-92, or the value
  25 specified for this parameter in the POLITE.INI file. Setting this parameter also
  26 defines the default format used in the TO_CHAR or TO_DATE functions when no
  27 other format string is supplied.
  28
  29 For Oracle the default is dd-mon-yy or dd-mon-yyyy, and for SQL-92 the default is
  30 yy-mm-dd or yyyy-mm-dd.
  31
  32 Using 'RR' in the format forces two-digit years less than or equal to 49 to be
  33 interpreted as years in the 21st century (2000-2049), and years over 50 as years in
  34 the 20th century (1950-1999). Setting the RR format as the default for all two-digit
  35 year entries allows you to become year-2000 compliant. For example:
  36 NLS_DATE_FORMAT='RR-MM-DD'
  37
  38 You can also modify the date format using the ALTER SESSION command.
  39 */
  40
  41 # define the LOB descriptor type for the given type
  42 # returns false if no LOB descriptor
  43 function oci_lob_desc($type) {
  44         switch ($type) {
  45                 case OCI_B_BFILE: $result = OCI_D_FILE; break;
  46                 case OCI_B_CFILEE: $result = OCI_D_FILE; break;
  47                 case OCI_B_CLOB: $result = OCI_D_LOB; break;
  48                 case OCI_B_BLOB: $result = OCI_D_LOB; break;
  49                 case OCI_B_ROWID: $result = OCI_D_ROWID; break;
  50                 default: $result = false; break;
  51         }
  52         return $result;
  53 }
  54
  55 class ADODB_oci8 extends ADOConnection {
  56         var $databaseType = 'oci8';
  57         var $dataProvider = 'oci8';
  58         var $replaceQuote = "''"; // string to use to replace quotes
  59         var $concat_operator='||';
  60         var $sysDate = "TRUNC(SYSDATE)";
  61         var $sysTimeStamp = 'SYSDATE'; // requires oracle 9 or later, otherwise use SYSDATE
  62         var $metaDatabasesSQL = "SELECT USERNAME FROM ALL_USERS WHERE USERNAME NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN') ORDER BY 1";
  63         var $_stmt;
  64         var $_commit = OCI_COMMIT_ON_SUCCESS;
  65         var $_initdate = true; // init date to YYYY-MM-DD
  66         var $metaTablesSQL = "select table_name,table_type from cat where table_type in ('TABLE','VIEW') and table_name not like 'BIN\$%'"; // bin$ tables are recycle bin tables
  67         var $metaColumnsSQL = "select cname,coltype,width, SCALE, PRECISION, NULLS, DEFAULTVAL from col where tname='%s' order by colno"; //changed by smondino@users.sourceforge. net
  68         var $metaColumnsSQL2 = "select column_name,data_type,data_length, data_scale, data_precision,
  69     case when nullable = 'Y' then 'NULL'
  70     else 'NOT NULL' end as nulls,
  71     data_default from all_tab_cols
  72   where owner='%s' and table_name='%s' order by column_id"; // when there is a schema
  73         var $_bindInputArray = true;
  74         var $hasGenID = true;
  75         var $_genIDSQL = "SELECT (%s.nextval) FROM DUAL";
  76         var $_genSeqSQL = "
  77 DECLARE
  78         PRAGMA AUTONOMOUS_TRANSACTION;
  79 BEGIN
  80         execute immediate 'CREATE SEQUENCE %s START WITH %s';
  81 END;
  82 ";
  83
  84         var $_dropSeqSQL = "DROP SEQUENCE %s";
  85         var $hasAffectedRows = true;
  86         var $random = "abs(mod(DBMS_RANDOM.RANDOM,10000001)/10000000)";
  87         var $noNullStrings = false;
  88         var $connectSID = false;
  89         var $_bind = false;
  90         var $_nestedSQL = true;
  91         var $_hasOciFetchStatement = false;
  92         var $_getarray = false; // currently not working
  93         var $leftOuter = '';  // oracle wierdness, $col = $value (+) for LEFT OUTER, $col (+)= $value for RIGHT OUTER
  94         var $session_sharing_force_blob = false; // alter session on updateblob if set to true
  95         var $firstrows = true; // enable first rows optimization on SelectLimit()
  96         var $selectOffsetAlg1 = 1000; // when to use 1st algorithm of selectlimit.
  97         var $NLS_DATE_FORMAT = 'YYYY-MM-DD';  // To include time, use 'RRRR-MM-DD HH24:MI:SS'
  98         var $dateformat = 'YYYY-MM-DD'; // DBDate format
  99         var $useDBDateFormatForTextInput=false;
 100         var $datetime = false; // MetaType('DATE') returns 'D' (datetime==false) or 'T' (datetime == true)
 101         var $_refLOBs = array();
 102
 103         // var $ansiOuter = true; // if oracle9
 104
 105         function ADODB_oci8()
 106         {
 107                 $this->_hasOciFetchStatement = ADODB_PHPVER >= 0x4200;
 108                 if (defined('ADODB_EXTENSION')) $this->rsPrefix .= 'ext_';
 109         }
 110
 111         /*  function MetaColumns($table, $normalize=true) added by smondino@users.sourceforge.net*/
 112         function MetaColumns($table, $normalize=true)
 113         {
 114         global $ADODB_FETCH_MODE;
 115
 116                 $schema = '';
 117                 $this->_findschema($table, $schema);
 118
 119                 $false = false;
 120                 $save = $ADODB_FETCH_MODE;
 121                 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 122                 if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false);
 123
 124                 if ($schema)
 125                         $rs = $this->Execute(sprintf($this->metaColumnsSQL2, strtoupper($schema), strtoupper($table)));
 126                 else
 127                         $rs = $this->Execute(sprintf($this->metaColumnsSQL,strtoupper($table)));
 128
 129                 if (isset($savem)) $this->SetFetchMode($savem);
 130                 $ADODB_FETCH_MODE = $save;
 131                 if (!$rs) {
 132                         return $false;
 133                 }
 134                 $retarr = array();
 135                 while (!$rs->EOF) {
 136                         $fld = new ADOFieldObject();
 137                         $fld->name = $rs->fields[0];
 138                         $fld->type = $rs->fields[1];
 139                         $fld->max_length = $rs->fields[2];
 140                         $fld->scale = $rs->fields[3];
 141                         if ($rs->fields[1] == 'NUMBER') {
 142                                 if ($rs->fields[3] == 0) $fld->type = 'INT';
 143                                 $fld->max_length = $rs->fields[4];
 144                         }
 145                         $fld->not_null = (strncmp($rs->fields[5], 'NOT',3) === 0);
 146                         $fld->binary = (strpos($fld->type,'BLOB') !== false);
 147                         $fld->default_value = $rs->fields[6];
 148
 149                         if ($ADODB_FETCH_MODE == ADODB_FETCH_NUM) $retarr[] = $fld;
 150                         else $retarr[strtoupper($fld->name)] = $fld;
 151                         $rs->MoveNext();
 152                 }
 153                 $rs->Close();
 154                 if (empty($retarr))
 155                         return  $false;
 156                 else
 157                         return $retarr;
 158         }
 159
 160         function Time()
 161         {
 162                 $rs = $this->Execute("select TO_CHAR($this->sysTimeStamp,'YYYY-MM-DD HH24:MI:SS') from dual");
 163                 if ($rs && !$rs->EOF) return $this->UnixTimeStamp(reset($rs->fields));
 164
 165                 return false;
 166         }
 167
 168         /**
 169          * Multiple modes of connection are supported:
 170          *
 171          * a. Local Database
 172          *    $conn->Connect(false,'scott','tiger');
 173          *
 174          * b. From tnsnames.ora
 175          *    $conn->Connect($tnsname,'scott','tiger');
 176          *    $conn->Connect(false,'scott','tiger',$tnsname);
 177          *
 178          * c. Server + service name
 179          *    $conn->Connect($serveraddress,'scott,'tiger',$service_name);
 180          *
 181          * d. Server + SID
 182          *    $conn->connectSID = true;
 183          *    $conn->Connect($serveraddress,'scott,'tiger',$SID);
 184          *
 185          * @param string|false $argHostname DB server hostname or TNS name
 186          * @param string $argUsername
 187          * @param string $argPassword
 188          * @param string $argDatabasename Service name, SID (defaults to null)
 189          * @param int $mode Connection mode, defaults to 0
 190          *                  (0 = non-persistent, 1 = persistent, 2 = force new connection)
 191          *
 192          * @return bool
 193          */
 194         function _connect($argHostname, $argUsername, $argPassword, $argDatabasename=null, $mode=0)
 195         {
 196                 if (!function_exists('oci_pconnect')) return null;
 197                 #adodb_backtrace();
 198
 199                 $this->_errorMsg = false;
 200                 $this->_errorCode = false;
 201
 202                 if($argHostname) { // added by Jorma Tuomainen <jorma.tuomainen@ppoy.fi>
 203                         if (empty($argDatabasename)) $argDatabasename = $argHostname;
 204                         else {
 205                                 if(strpos($argHostname,":")) {
 206                                         $argHostinfo=explode(":",$argHostname);
 207                                         $argHostname=$argHostinfo[0];
 208                                         $argHostport=$argHostinfo[1];
 209                                 } else {
 210                                         $argHostport = empty($this->port)?  "1521" : $this->port;
 211                                 }
 212
 213                                 if (strncasecmp($argDatabasename,'SID=',4) == 0) {
 214                                         $argDatabasename = substr($argDatabasename,4);
 215                                         $this->connectSID = true;
 216                                 }
 217
 218                                 if ($this->connectSID) {
 219                                         $argDatabasename="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=".$argHostname
 220                                         .")(PORT=$argHostport))(CONNECT_DATA=(SID=$argDatabasename)))";
 221                                 } else
 222                                         $argDatabasename="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=".$argHostname
 223                                         .")(PORT=$argHostport))(CONNECT_DATA=(SERVICE_NAME=$argDatabasename)))";
 224                         }
 225                 }
 226
 227                 //if ($argHostname) print "<p>Connect: 1st argument should be left blank for $this->databaseType</p>";
 228                 if ($mode==1) {
 229                         $this->_connectionID = ($this->charSet)
 230                                 ? oci_pconnect($argUsername,$argPassword, $argDatabasename,$this->charSet)
 231                                 : oci_pconnect($argUsername,$argPassword, $argDatabasename);
 232                         if ($this->_connectionID && $this->autoRollback)  oci_rollback($this->_connectionID);
 233                 } else if ($mode==2) {
 234                         $this->_connectionID = ($this->charSet)
 235                                 ? oci_new_connect($argUsername,$argPassword, $argDatabasename,$this->charSet)
 236                                 : oci_new_connect($argUsername,$argPassword, $argDatabasename);
 237                 } else {
 238                         $this->_connectionID = ($this->charSet)
 239                                 ? oci_connect($argUsername,$argPassword, $argDatabasename,$this->charSet)
 240                                 : oci_connect($argUsername,$argPassword, $argDatabasename);
 241                 }
 242                 if (!$this->_connectionID) {
 243                         return false;
 244                 }
 245
 246                 if ($this->_initdate) {
 247                         $this->Execute("ALTER SESSION SET NLS_DATE_FORMAT='".$this->NLS_DATE_FORMAT."'");
 248                 }
 249
 250                 // looks like:
 251                 // Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option JServer Release 8.1.7.0.0 - Production
 252                 // $vers = oci_server_version($this->_connectionID);
 253                 // if (strpos($vers,'8i') !== false) $this->ansiOuter = true;
 254                 return true;
 255         }
 256
 257         function ServerInfo()
 258         {
 259                 $arr['compat'] = $this->GetOne('select value from sys.database_compatible_level');
 260                 $arr['description'] = @oci_server_version($this->_connectionID);
 261                 $arr['version'] = ADOConnection::_findvers($arr['description']);
 262                 return $arr;
 263         }
 264                 // returns true or false
 265         function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
 266         {
 267                 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename,1);
 268         }
 269
 270         // returns true or false
 271         function _nconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
 272         {
 273                 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename,2);
 274         }
 275
 276         function _affectedrows()
 277         {
 278                 if (is_resource($this->_stmt)) return @oci_num_rows($this->_stmt);
 279                 return 0;
 280         }
 281
 282         function IfNull( $field, $ifNull )
 283         {
 284                 return " NVL($field, $ifNull) "; // if Oracle
 285         }
 286
 287         // format and return date string in database date format
 288         function DBDate($d,$isfld=false)
 289         {
 290                 if (empty($d) && $d !== 0) return 'null';
 291
 292                 if ($isfld) {
 293                         $d = _adodb_safedate($d);
 294                         return 'TO_DATE('.$d.",'".$this->dateformat."')";
 295                 }
 296
 297                 if (is_string($d)) $d = ADORecordSet::UnixDate($d);
 298
 299                 if (is_object($d)) $ds = $d->format($this->fmtDate);
 300                 else $ds = adodb_date($this->fmtDate,$d);
 301
 302                 return "TO_DATE(".$ds.",'".$this->dateformat."')";
 303         }
 304
 305         function BindDate($d)
 306         {
 307                 $d = ADOConnection::DBDate($d);
 308                 if (strncmp($d,"'",1)) return $d;
 309
 310                 return substr($d,1,strlen($d)-2);
 311         }
 312
 313         function BindTimeStamp($ts)
 314         {
 315                 if (empty($ts) && $ts !== 0) return 'null';
 316                 if (is_string($ts)) $ts = ADORecordSet::UnixTimeStamp($ts);
 317
 318                 if (is_object($ts)) $tss = $ts->format("'Y-m-d H:i:s'");
 319                 else $tss = adodb_date("'Y-m-d H:i:s'",$ts);
 320
 321                 return $tss;
 322         }
 323
 324         // format and return date string in database timestamp format
 325         function DBTimeStamp($ts,$isfld=false)
 326         {
 327                 if (empty($ts) && $ts !== 0) return 'null';
 328                 if ($isfld) return 'TO_DATE(substr('.$ts.",1,19),'RRRR-MM-DD, HH24:MI:SS')";
 329                 if (is_string($ts)) $ts = ADORecordSet::UnixTimeStamp($ts);
 330
 331                 if (is_object($ts)) $tss = $ts->format("'Y-m-d H:i:s'");
 332                 else $tss = date("'Y-m-d H:i:s'",$ts);
 333
 334                 return 'TO_DATE('.$tss.",'RRRR-MM-DD, HH24:MI:SS')";
 335         }
 336
 337         function RowLock($tables,$where,$col='1 as adodbignore')
 338         {
 339                 if ($this->autoCommit) $this->BeginTrans();
 340                 return $this->GetOne("select $col from $tables where $where for update");
 341         }
 342
 343         function MetaTables($ttype=false,$showSchema=false,$mask=false)
 344         {
 345                 if ($mask) {
 346                         $save = $this->metaTablesSQL;
 347                         $mask = $this->qstr(strtoupper($mask));
 348                         $this->metaTablesSQL .= " AND upper(table_name) like $mask";
 349                 }
 350                 $ret = ADOConnection::MetaTables($ttype,$showSchema);
 351
 352                 if ($mask) {
 353                         $this->metaTablesSQL = $save;
 354                 }
 355                 return $ret;
 356         }
 357
 358         // Mark Newnham
 359         function MetaIndexes ($table, $primary = FALSE, $owner=false)
 360         {
 361                 // save old fetch mode
 362                 global $ADODB_FETCH_MODE;
 363
 364                 $save = $ADODB_FETCH_MODE;
 365                 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 366
 367                 if ($this->fetchMode !== FALSE) {
 368                         $savem = $this->SetFetchMode(FALSE);
 369                 }
 370
 371                 // get index details
 372                 $table = strtoupper($table);
 373
 374                 // get Primary index
 375                 $primary_key = '';
 376
 377                 $false = false;
 378                 $rs = $this->Execute(sprintf("SELECT * FROM ALL_CONSTRAINTS WHERE UPPER(TABLE_NAME)='%s' AND CONSTRAINT_TYPE='P'",$table));
 379                 if (!is_object($rs)) {
 380                         if (isset($savem))
 381                                 $this->SetFetchMode($savem);
 382                         $ADODB_FETCH_MODE = $save;
 383                         return $false;
 384                 }
 385
 386                 if ($row = $rs->FetchRow())
 387                         $primary_key = $row[1]; //constraint_name
 388
 389                 if ($primary==TRUE && $primary_key=='') {
 390                         if (isset($savem))
 391                                 $this->SetFetchMode($savem);
 392                         $ADODB_FETCH_MODE = $save;
 393                         return $false; //There is no primary key
 394                 }
 395
 396                 $rs = $this->Execute(sprintf("SELECT ALL_INDEXES.INDEX_NAME, ALL_INDEXES.UNIQUENESS, ALL_IND_COLUMNS.COLUMN_POSITION, ALL_IND_COLUMNS.COLUMN_NAME FROM ALL_INDEXES,ALL_IND_COLUMNS WHERE UPPER(ALL_INDEXES.TABLE_NAME)='%s' AND ALL_IND_COLUMNS.INDEX_NAME=ALL_INDEXES.INDEX_NAME",$table));
 397
 398
 399                 if (!is_object($rs)) {
 400                         if (isset($savem))
 401                                 $this->SetFetchMode($savem);
 402                         $ADODB_FETCH_MODE = $save;
 403                         return $false;
 404                 }
 405
 406                 $indexes = array ();
 407                 // parse index data into array
 408
 409                 while ($row = $rs->FetchRow()) {
 410                         if ($primary && $row[0] != $primary_key) continue;
 411                         if (!isset($indexes[$row[0]])) {
 412                                 $indexes[$row[0]] = array(
 413                                         'unique' => ($row[1] == 'UNIQUE'),
 414                                         'columns' => array()
 415                                 );
 416                         }
 417                         $indexes[$row[0]]['columns'][$row[2] - 1] = $row[3];
 418                 }
 419
 420                 // sort columns by order in the index
 421                 foreach ( array_keys ($indexes) as $index ) {
 422                         ksort ($indexes[$index]['columns']);
 423                 }
 424
 425                 if (isset($savem)) {
 426                         $this->SetFetchMode($savem);
 427                         $ADODB_FETCH_MODE = $save;
 428                 }
 429                 return $indexes;
 430         }
 431
 432         function BeginTrans()
 433         {
 434                 if ($this->transOff) return true;
 435                 $this->transCnt += 1;
 436                 $this->autoCommit = false;
 437                 $this->_commit = OCI_DEFAULT;
 438
 439                 if ($this->_transmode) $ok = $this->Execute("SET TRANSACTION ".$this->_transmode);
 440                 else $ok = true;
 441
 442                 return $ok ? true : false;
 443         }
 444
 445         function CommitTrans($ok=true)
 446         {
 447                 if ($this->transOff) return true;
 448                 if (!$ok) return $this->RollbackTrans();
 449
 450                 if ($this->transCnt) $this->transCnt -= 1;
 451                 $ret = oci_commit($this->_connectionID);
 452                 $this->_commit = OCI_COMMIT_ON_SUCCESS;
 453                 $this->autoCommit = true;
 454                 return $ret;
 455         }
 456
 457         function RollbackTrans()
 458         {
 459                 if ($this->transOff) return true;
 460                 if ($this->transCnt) $this->transCnt -= 1;
 461                 $ret = oci_rollback($this->_connectionID);
 462                 $this->_commit = OCI_COMMIT_ON_SUCCESS;
 463                 $this->autoCommit = true;
 464                 return $ret;
 465         }
 466
 467
 468         function SelectDB($dbName)
 469         {
 470                 return false;
 471         }
 472
 473         function ErrorMsg()
 474         {
 475                 if ($this->_errorMsg !== false) return $this->_errorMsg;
 476
 477                 if (is_resource($this->_stmt)) $arr = @oci_error($this->_stmt);
 478                 if (empty($arr)) {
 479                         if (is_resource($this->_connectionID)) $arr = @oci_error($this->_connectionID);
 480                         else $arr = @oci_error();
 481                         if ($arr === false) return '';
 482                 }
 483                 $this->_errorMsg = $arr['message'];
 484                 $this->_errorCode = $arr['code'];
 485                 return $this->_errorMsg;
 486         }
 487
 488         function ErrorNo()
 489         {
 490                 if ($this->_errorCode !== false) return $this->_errorCode;
 491
 492                 if (is_resource($this->_stmt)) $arr = @oci_error($this->_stmt);
 493                 if (empty($arr)) {
 494                         $arr = @oci_error($this->_connectionID);
 495                         if ($arr == false) $arr = @oci_error();
 496                         if ($arr == false) return '';
 497                 }
 498
 499                 $this->_errorMsg = $arr['message'];
 500                 $this->_errorCode = $arr['code'];
 501
 502                 return $arr['code'];
 503         }
 504
 505         /**
 506          * Format date column in sql string given an input format that understands Y M D
 507          */
 508         function SQLDate($fmt, $col=false)
 509         {
 510                 if (!$col) $col = $this->sysTimeStamp;
 511                 $s = 'TO_CHAR('.$col.",'";
 512
 513                 $len = strlen($fmt);
 514                 for ($i=0; $i < $len; $i++) {
 515                         $ch = $fmt[$i];
 516                         switch($ch) {
 517                         case 'Y':
 518                         case 'y':
 519                                 $s .= 'YYYY';
 520                                 break;
 521                         case 'Q':
 522                         case 'q':
 523                                 $s .= 'Q';
 524                                 break;
 525
 526                         case 'M':
 527                                 $s .= 'Mon';
 528                                 break;
 529
 530                         case 'm':
 531                                 $s .= 'MM';
 532                                 break;
 533                         case 'D':
 534                         case 'd':
 535                                 $s .= 'DD';
 536                                 break;
 537
 538                         case 'H':
 539                                 $s.= 'HH24';
 540                                 break;
 541
 542                         case 'h':
 543                                 $s .= 'HH';
 544                                 break;
 545
 546                         case 'i':
 547                                 $s .= 'MI';
 548                                 break;
 549
 550                         case 's':
 551                                 $s .= 'SS';
 552                                 break;
 553
 554                         case 'a':
 555                         case 'A':
 556                                 $s .= 'AM';
 557                                 break;
 558
 559                         case 'w':
 560                                 $s .= 'D';
 561                                 break;
 562
 563                         case 'l':
 564                                 $s .= 'DAY';
 565                                 break;
 566
 567                         case 'W':
 568                                 $s .= 'WW';
 569                                 break;
 570
 571                         default:
 572                         // handle escape characters...
 573                                 if ($ch == '\\') {
 574                                         $i++;
 575                                         $ch = substr($fmt,$i,1);
 576                                 }
 577                                 if (strpos('-/.:;, ',$ch) !== false) $s .= $ch;
 578                                 else $s .= '"'.$ch.'"';
 579
 580                         }
 581                 }
 582                 return $s. "')";
 583         }
 584
 585         function GetRandRow($sql, $arr = false)
 586         {
 587                 $sql = "SELECT * FROM ($sql ORDER BY dbms_random.value) WHERE rownum = 1";
 588
 589                 return $this->GetRow($sql,$arr);
 590         }
 591
 592         /**
 593          * This algorithm makes use of
 594          *
 595          * a. FIRST_ROWS hint
 596          * The FIRST_ROWS hint explicitly chooses the approach to optimize response
 597          * time, that is, minimum resource usage to return the first row. Results
 598          * will be returned as soon as they are identified.
 599          *
 600          * b. Uses rownum tricks to obtain only the required rows from a given offset.
 601          * As this uses complicated sql statements, we only use this if $offset >= 100.
 602          * This idea by Tomas V V Cox.
 603          *
 604          * This implementation does not appear to work with oracle 8.0.5 or earlier.
 605          * Comment out this function then, and the slower SelectLimit() in the base
 606          * class will be used.
 607          */
 608         function SelectLimit($sql,$nrows=-1,$offset=-1, $inputarr=false,$secs2cache=0)
 609         {
 610                 // seems that oracle only supports 1 hint comment in 8i
 611                 if ($this->firstrows) {
 612                         if ($nrows > 500 && $nrows < 1000) $hint = "FIRST_ROWS($nrows)";
 613                         else $hint = 'FIRST_ROWS';
 614
 615                         if (strpos($sql,'/*+') !== false)
 616                                 $sql = str_replace('/*+ ',"/*+$hint ",$sql);
 617                         else
 618                                 $sql = preg_replace('/^[ \t\n]*select/i',"SELECT /*+$hint*/",$sql);
 619                         $hint = "/*+ $hint */";
 620                 } else
 621                         $hint = '';
 622
 623                 if ($offset == -1 || ($offset < $this->selectOffsetAlg1 && 0 < $nrows && $nrows < 1000)) {
 624                         if ($nrows > 0) {
 625                                 if ($offset > 0) $nrows += $offset;
 626                                 //$inputarr['adodb_rownum'] = $nrows;
 627                                 if ($this->databaseType == 'oci8po') {
 628                                         $sql = "select * from (".$sql.") where rownum <= ?";
 629                                 } else {
 630                                         $sql = "select * from (".$sql.") where rownum <= :adodb_offset";
 631                                 }
 632                                 $inputarr['adodb_offset'] = $nrows;
 633                                 $nrows = -1;
 634                         }
 635                         // note that $nrows = 0 still has to work ==> no rows returned
 636
 637                         $rs = ADOConnection::SelectLimit($sql,$nrows,$offset,$inputarr,$secs2cache);
 638                         return $rs;
 639
 640                 } else {
 641                         // Algorithm by Tomas V V Cox, from PEAR DB oci8.php
 642
 643                         // Let Oracle return the name of the columns
 644                         $q_fields = "SELECT * FROM (".$sql.") WHERE NULL = NULL";
 645
 646                         $false = false;
 647                         if (! $stmt_arr = $this->Prepare($q_fields)) {
 648                                 return $false;
 649                         }
 650                         $stmt = $stmt_arr[1];
 651
 652                         if (is_array($inputarr)) {
 653                                 foreach($inputarr as $k => $v) {
 654                                         if (is_array($v)) {
 655                                                 if (sizeof($v) == 2) // suggested by g.giunta@libero.
 656                                                         oci_bind_by_name($stmt,":$k",$inputarr[$k][0],$v[1]);
 657                                                 else
 658                                                         oci_bind_by_name($stmt,":$k",$inputarr[$k][0],$v[1],$v[2]);
 659                                         } else {
 660                                                 $len = -1;
 661                                                 if ($v === ' ') $len = 1;
 662                                                 if (isset($bindarr)) {  // is prepared sql, so no need to oci_bind_by_name again
 663                                                         $bindarr[$k] = $v;
 664                                                 } else {                                // dynamic sql, so rebind every time
 665                                                         oci_bind_by_name($stmt,":$k",$inputarr[$k],$len);
 666                                                 }
 667                                         }
 668                                 }
 669                         }
 670
 671                         if (!oci_execute($stmt, OCI_DEFAULT)) {
 672                                 oci_free_statement($stmt);
 673                                 return $false;
 674                         }
 675
 676                         $ncols = oci_num_fields($stmt);
 677                         for ( $i = 1; $i <= $ncols; $i++ ) {
 678                                 $cols[] = '"'.oci_field_name($stmt, $i).'"';
 679                         }
 680                         $result = false;
 681
 682                         oci_free_statement($stmt);
 683                         $fields = implode(',', $cols);
 684                         if ($nrows <= 0) $nrows = 999999999999;
 685                         else $nrows += $offset;
 686                         $offset += 1; // in Oracle rownum starts at 1
 687
 688                         if ($this->databaseType == 'oci8po') {
 689                                         $sql = "SELECT $hint $fields FROM".
 690                                                 "(SELECT rownum as adodb_rownum, $fields FROM".
 691                                                 " ($sql) WHERE rownum <= ?".
 692                                                 ") WHERE adodb_rownum >= ?";
 693                                 } else {
 694                                         $sql = "SELECT $hint $fields FROM".
 695                                                 "(SELECT rownum as adodb_rownum, $fields FROM".
 696                                                 " ($sql) WHERE rownum <= :adodb_nrows".
 697                                                 ") WHERE adodb_rownum >= :adodb_offset";
 698                                 }
 699                                 $inputarr['adodb_nrows'] = $nrows;
 700                                 $inputarr['adodb_offset'] = $offset;
 701
 702                         if ($secs2cache>0) $rs = $this->CacheExecute($secs2cache, $sql,$inputarr);
 703                         else $rs = $this->Execute($sql,$inputarr);
 704                         return $rs;
 705                 }
 706         }
 707
 708         /**
 709          * Usage:
 710          * Store BLOBs and CLOBs
 711          *
 712          * Example: to store $var in a blob
 713          *    $conn->Execute('insert into TABLE (id,ablob) values(12,empty_blob())');
 714          *    $conn->UpdateBlob('TABLE', 'ablob', $varHoldingBlob, 'ID=12', 'BLOB');
 715          *
 716          * $blobtype supports 'BLOB' and 'CLOB', but you need to change to 'empty_clob()'.
 717          *
 718          * to get length of LOB:
 719          *    select DBMS_LOB.GETLENGTH(ablob) from TABLE
 720          *
 721          * If you are using CURSOR_SHARING = force, it appears this will case a segfault
 722          * under oracle 8.1.7.0. Run:
 723          *    $db->Execute('ALTER SESSION SET CURSOR_SHARING=EXACT');
 724          * before UpdateBlob() then...
 725          */
 726         function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB')
 727         {
 728
 729                 //if (strlen($val) < 4000) return $this->Execute("UPDATE $table SET $column=:blob WHERE $where",array('blob'=>$val)) != false;
 730
 731                 switch(strtoupper($blobtype)) {
 732                 default: ADOConnection::outp("<b>UpdateBlob</b>: Unknown blobtype=$blobtype"); return false;
 733                 case 'BLOB': $type = OCI_B_BLOB; break;
 734                 case 'CLOB': $type = OCI_B_CLOB; break;
 735                 }
 736
 737                 if ($this->databaseType == 'oci8po')
 738                         $sql = "UPDATE $table set $column=EMPTY_{$blobtype}() WHERE $where RETURNING $column INTO ?";
 739                 else
 740                         $sql = "UPDATE $table set $column=EMPTY_{$blobtype}() WHERE $where RETURNING $column INTO :blob";
 741
 742                 $desc = oci_new_descriptor($this->_connectionID, OCI_D_LOB);
 743                 $arr['blob'] = array($desc,-1,$type);
 744                 if ($this->session_sharing_force_blob) $this->Execute('ALTER SESSION SET CURSOR_SHARING=EXACT');
 745                 $commit = $this->autoCommit;
 746                 if ($commit) $this->BeginTrans();
 747                 $rs = $this->_Execute($sql,$arr);
 748                 if ($rez = !empty($rs)) $desc->save($val);
 749                 $desc->free();
 750                 if ($commit) $this->CommitTrans();
 751                 if ($this->session_sharing_force_blob) $this->Execute('ALTER SESSION SET CURSOR_SHARING=FORCE');
 752
 753                 if ($rez) $rs->Close();
 754                 return $rez;
 755         }
 756
 757         /**
 758          * Usage:  store file pointed to by $val in a blob
 759          */
 760         function UpdateBlobFile($table,$column,$val,$where,$blobtype='BLOB')
 761         {
 762                 switch(strtoupper($blobtype)) {
 763                 default: ADOConnection::outp( "<b>UpdateBlob</b>: Unknown blobtype=$blobtype"); return false;
 764                 case 'BLOB': $type = OCI_B_BLOB; break;
 765                 case 'CLOB': $type = OCI_B_CLOB; break;
 766                 }
 767
 768                 if ($this->databaseType == 'oci8po')
 769                         $sql = "UPDATE $table set $column=EMPTY_{$blobtype}() WHERE $where RETURNING $column INTO ?";
 770                 else
 771                         $sql = "UPDATE $table set $column=EMPTY_{$blobtype}() WHERE $where RETURNING $column INTO :blob";
 772
 773                 $desc = oci_new_descriptor($this->_connectionID, OCI_D_LOB);
 774                 $arr['blob'] = array($desc,-1,$type);
 775
 776                 $this->BeginTrans();
 777                 $rs = ADODB_oci8::Execute($sql,$arr);
 778                 if ($rez = !empty($rs)) $desc->savefile($val);
 779                 $desc->free();
 780                 $this->CommitTrans();
 781
 782                 if ($rez) $rs->Close();
 783                 return $rez;
 784         }
 785
 786         /**
 787          * Execute SQL
 788          *
 789          * @param sql           SQL statement to execute, or possibly an array holding prepared statement ($sql[0] will hold sql text)
 790          * @param [inputarr]    holds the input data to bind to. Null elements will be set to null.
 791          * @return              RecordSet or false
 792          */
 793         function Execute($sql,$inputarr=false)
 794         {
 795                 if ($this->fnExecute) {
 796                         $fn = $this->fnExecute;
 797                         $ret = $fn($this,$sql,$inputarr);
 798                         if (isset($ret)) return $ret;
 799                 }
 800                 if ($inputarr !== false) {
 801                         if (!is_array($inputarr)) $inputarr = array($inputarr);
 802
 803                         $element0 = reset($inputarr);
 804                         $array2d =  $this->bulkBind && is_array($element0) && !is_object(reset($element0));
 805
 806                         # see http://phplens.com/lens/lensforum/msgs.php?id=18786
 807                         if ($array2d || !$this->_bindInputArray) {
 808
 809                         # is_object check because oci8 descriptors can be passed in
 810                         if ($array2d && $this->_bindInputArray) {
 811                                 if (is_string($sql))
 812                                         $stmt = $this->Prepare($sql);
 813                                 else
 814                                         $stmt = $sql;
 815
 816                                 foreach($inputarr as $arr) {
 817                                         $ret = $this->_Execute($stmt,$arr);
 818                                         if (!$ret) return $ret;
 819                                 }
 820                                 return $ret;
 821                         } else {
 822                                 $sqlarr = explode(':',$sql);
 823                                 $sql = '';
 824                                 $lastnomatch = -2;
 825                                 #var_dump($sqlarr);echo "<hr>";var_dump($inputarr);echo"<hr>";
 826                                 foreach($sqlarr as $k => $str) {
 827                                                 if ($k == 0) { $sql = $str; continue; }
 828                                                 // we need $lastnomatch because of the following datetime,
 829                                                 // eg. '10:10:01', which causes code to think that there is bind param :10 and :1
 830                                                 $ok = preg_match('/^([0-9]*)/', $str, $arr);
 831
 832                                                 if (!$ok) $sql .= $str;
 833                                                 else {
 834                                                         $at = $arr[1];
 835                                                         if (isset($inputarr[$at]) || is_null($inputarr[$at])) {
 836                                                                 if ((strlen($at) == strlen($str) && $k < sizeof($arr)-1)) {
 837                                                                         $sql .= ':'.$str;
 838                                                                         $lastnomatch = $k;
 839                                                                 } else if ($lastnomatch == $k-1) {
 840                                                                         $sql .= ':'.$str;
 841                                                                 } else {
 842                                                                         if (is_null($inputarr[$at])) $sql .= 'null';
 843                                                                         else $sql .= $this->qstr($inputarr[$at]);
 844                                                                         $sql .= substr($str, strlen($at));
 845                                                                 }
 846                                                         } else {
 847                                                                 $sql .= ':'.$str;
 848                                                         }
 849
 850                                                 }
 851                                         }
 852                                         $inputarr = false;
 853                                 }
 854                         }
 855                         $ret = $this->_Execute($sql,$inputarr);
 856
 857
 858                 } else {
 859                         $ret = $this->_Execute($sql,false);
 860                 }
 861
 862                 return $ret;
 863         }
 864
 865         /*
 866          * Example of usage:
 867          *    $stmt = $this->Prepare('insert into emp (empno, ename) values (:empno, :ename)');
 868         */
 869         function Prepare($sql,$cursor=false)
 870         {
 871         static $BINDNUM = 0;
 872
 873                 $stmt = oci_parse($this->_connectionID,$sql);
 874
 875                 if (!$stmt) {
 876                         $this->_errorMsg = false;
 877                         $this->_errorCode = false;
 878                         $arr = @oci_error($this->_connectionID);
 879                         if ($arr === false) return false;
 880
 881                         $this->_errorMsg = $arr['message'];
 882                         $this->_errorCode = $arr['code'];
 883                         return false;
 884                 }
 885
 886                 $BINDNUM += 1;
 887
 888                 $sttype = @oci_statement_type($stmt);
 889                 if ($sttype == 'BEGIN' || $sttype == 'DECLARE') {
 890                         return array($sql,$stmt,0,$BINDNUM, ($cursor) ? oci_new_cursor($this->_connectionID) : false);
 891                 }
 892                 return array($sql,$stmt,0,$BINDNUM);
 893         }
 894
 895         /*
 896                 Call an oracle stored procedure and returns a cursor variable as a recordset.
 897                 Concept by Robert Tuttle robert@ud.com
 898
 899                 Example:
 900                         Note: we return a cursor variable in :RS2
 901                         $rs = $db->ExecuteCursor("BEGIN adodb.open_tab(:RS2); END;",'RS2');
 902
 903                         $rs = $db->ExecuteCursor(
 904                                 "BEGIN :RS2 = adodb.getdata(:VAR1); END;",
 905                                 'RS2',
 906                                 array('VAR1' => 'Mr Bean'));
 907
 908         */
 909         function ExecuteCursor($sql,$cursorName='rs',$params=false)
 910         {
 911                 if (is_array($sql)) $stmt = $sql;
 912                 else $stmt = ADODB_oci8::Prepare($sql,true); # true to allocate oci_new_cursor
 913
 914                 if (is_array($stmt) && sizeof($stmt) >= 5) {
 915                         $hasref = true;
 916                         $ignoreCur = false;
 917                         $this->Parameter($stmt, $ignoreCur, $cursorName, false, -1, OCI_B_CURSOR);
 918                         if ($params) {
 919                                 foreach($params as $k => $v) {
 920                                         $this->Parameter($stmt,$params[$k], $k);
 921                                 }
 922                         }
 923                 } else
 924                         $hasref = false;
 925
 926                 $rs = $this->Execute($stmt);
 927                 if ($rs) {
 928                         if ($rs->databaseType == 'array') oci_free_cursor($stmt[4]);
 929                         else if ($hasref) $rs->_refcursor = $stmt[4];
 930                 }
 931                 return $rs;
 932         }
 933
 934         /**
 935          * Bind a variable -- very, very fast for executing repeated statements in oracle.
 936          *
 937          * Better than using
 938          *    for ($i = 0; $i < $max; $i++) {
 939          *        $p1 = ?; $p2 = ?; $p3 = ?;
 940          *        $this->Execute("insert into table (col0, col1, col2) values (:0, :1, :2)", array($p1,$p2,$p3));
 941          *    }
 942          *
 943          * Usage:
 944          *    $stmt = $DB->Prepare("insert into table (col0, col1, col2) values (:0, :1, :2)");
 945          *    $DB->Bind($stmt, $p1);
 946          *    $DB->Bind($stmt, $p2);
 947          *    $DB->Bind($stmt, $p3);
 948          *    for ($i = 0; $i < $max; $i++) {
 949          *        $p1 = ?; $p2 = ?; $p3 = ?;
 950          *        $DB->Execute($stmt);
 951          *    }
 952          *
 953          * Some timings to insert 1000 records, test table has 3 cols, and 1 index.
 954          * - Time 0.6081s (1644.60 inserts/sec) with direct oci_parse/oci_execute
 955          * - Time 0.6341s (1577.16 inserts/sec) with ADOdb Prepare/Bind/Execute
 956          * - Time 1.5533s ( 643.77 inserts/sec) with pure SQL using Execute
 957          *
 958          * Now if PHP only had batch/bulk updating like Java or PL/SQL...
 959          *
 960          * Note that the order of parameters differs from oci_bind_by_name,
 961          * because we default the names to :0, :1, :2
 962          */
 963         function Bind(&$stmt,&$var,$size=4000,$type=false,$name=false,$isOutput=false)
 964         {
 965
 966                 if (!is_array($stmt)) return false;
 967
 968                 if (($type == OCI_B_CURSOR) && sizeof($stmt) >= 5) {
 969                         return oci_bind_by_name($stmt[1],":".$name,$stmt[4],$size,$type);
 970                 }
 971
 972                 if ($name == false) {
 973                         if ($type !== false) $rez = oci_bind_by_name($stmt[1],":".$stmt[2],$var,$size,$type);
 974                         else $rez = oci_bind_by_name($stmt[1],":".$stmt[2],$var,$size); // +1 byte for null terminator
 975                         $stmt[2] += 1;
 976                 } else if (oci_lob_desc($type)) {
 977                         if ($this->debug) {
 978                                 ADOConnection::outp("<b>Bind</b>: name = $name");
 979                         }
 980                         //we have to create a new Descriptor here
 981                         $numlob = count($this->_refLOBs);
 982                         $this->_refLOBs[$numlob]['LOB'] = oci_new_descriptor($this->_connectionID, oci_lob_desc($type));
 983                         $this->_refLOBs[$numlob]['TYPE'] = $isOutput;
 984
 985                         $tmp = $this->_refLOBs[$numlob]['LOB'];
 986                         $rez = oci_bind_by_name($stmt[1], ":".$name, $tmp, -1, $type);
 987                         if ($this->debug) {
 988                                 ADOConnection::outp("<b>Bind</b>: descriptor has been allocated, var (".$name.") binded");
 989                         }
 990
 991                         // if type is input then write data to lob now
 992                         if ($isOutput == false) {
 993                                 $var = $this->BlobEncode($var);
 994                                 $tmp->WriteTemporary($var);
 995                                 $this->_refLOBs[$numlob]['VAR'] = &$var;
 996                                 if ($this->debug) {
 997                                         ADOConnection::outp("<b>Bind</b>: LOB has been written to temp");
 998                                 }
 999                         } else {
1000                                 $this->_refLOBs[$numlob]['VAR'] = &$var;
1001                         }
1002                         $rez = $tmp;
1003                 } else {
1004                         if ($this->debug)
1005                                 ADOConnection::outp("<b>Bind</b>: name = $name");
1006
1007                         if ($type !== false) $rez = oci_bind_by_name($stmt[1],":".$name,$var,$size,$type);
1008                         else $rez = oci_bind_by_name($stmt[1],":".$name,$var,$size); // +1 byte for null terminator
1009                 }
1010
1011                 return $rez;
1012         }
1013
1014         function Param($name,$type='C')
1015         {
1016                 return ':'.$name;
1017         }
1018
1019         /**
1020          * Usage:
1021          *    $stmt = $db->Prepare('select * from table where id =:myid and group=:group');
1022          *    $db->Parameter($stmt,$id,'myid');
1023          *    $db->Parameter($stmt,$group,'group');
1024          *    $db->Execute($stmt);
1025          *
1026          * @param $stmt Statement returned by Prepare() or PrepareSP().
1027          * @param $var PHP variable to bind to
1028          * @param $name Name of stored procedure variable name to bind to.
1029          * @param [$isOutput] Indicates direction of parameter 0/false=IN  1=OUT  2= IN/OUT. This is ignored in oci8.
1030          * @param [$maxLen] Holds an maximum length of the variable.
1031          * @param [$type] The data type of $var. Legal values depend on driver.
1032          *
1033          * @link http://php.net/oci_bind_by_name
1034         */
1035         function Parameter(&$stmt,&$var,$name,$isOutput=false,$maxLen=4000,$type=false)
1036         {
1037                         if  ($this->debug) {
1038                                 $prefix = ($isOutput) ? 'Out' : 'In';
1039                                 $ztype = (empty($type)) ? 'false' : $type;
1040                                 ADOConnection::outp( "{$prefix}Parameter(\$stmt, \$php_var='$var', \$name='$name', \$maxLen=$maxLen, \$type=$ztype);");
1041                         }
1042                         return $this->Bind($stmt,$var,$maxLen,$type,$name,$isOutput);
1043         }
1044
1045         /**
1046          * returns query ID if successful, otherwise false
1047          * this version supports:
1048          *
1049          * 1. $db->execute('select * from table');
1050          *
1051          * 2. $db->prepare('insert into table (a,b,c) values (:0,:1,:2)');
1052          *    $db->execute($prepared_statement, array(1,2,3));
1053          *
1054          * 3. $db->execute('insert into table (a,b,c) values (:a,:b,:c)',array('a'=>1,'b'=>2,'c'=>3));
1055          *
1056          * 4. $db->prepare('insert into table (a,b,c) values (:0,:1,:2)');
1057          *    $db->bind($stmt,1); $db->bind($stmt,2); $db->bind($stmt,3);
1058          *    $db->execute($stmt);
1059          */
1060         function _query($sql,$inputarr=false)
1061         {
1062                 if (is_array($sql)) { // is prepared sql
1063                         $stmt = $sql[1];
1064
1065                         // we try to bind to permanent array, so that oci_bind_by_name is persistent
1066                         // and carried out once only - note that max array element size is 4000 chars
1067                         if (is_array($inputarr)) {
1068                                 $bindpos = $sql[3];
1069                                 if (isset($this->_bind[$bindpos])) {
1070                                 // all tied up already
1071                                         $bindarr = $this->_bind[$bindpos];
1072                                 } else {
1073                                 // one statement to bind them all
1074                                         $bindarr = array();
1075                                         foreach($inputarr as $k => $v) {
1076                                                 $bindarr[$k] = $v;
1077                                                 oci_bind_by_name($stmt,":$k",$bindarr[$k],is_string($v) && strlen($v)>4000 ? -1 : 4000);
1078                                         }
1079                                         $this->_bind[$bindpos] = $bindarr;
1080                                 }
1081                         }
1082                 } else {
1083                         $stmt=oci_parse($this->_connectionID,$sql);
1084                 }
1085
1086                 $this->_stmt = $stmt;
1087                 if (!$stmt) return false;
1088
1089                 if (defined('ADODB_PREFETCH_ROWS')) @oci_set_prefetch($stmt,ADODB_PREFETCH_ROWS);
1090
1091                 if (is_array($inputarr)) {
1092                         foreach($inputarr as $k => $v) {
1093                                 if (is_array($v)) {
1094                                         if (sizeof($v) == 2) // suggested by g.giunta@libero.
1095                                                 oci_bind_by_name($stmt,":$k",$inputarr[$k][0],$v[1]);
1096                                         else
1097                                                 oci_bind_by_name($stmt,":$k",$inputarr[$k][0],$v[1],$v[2]);
1098
1099                                         if ($this->debug==99) {
1100                                                 if (is_object($v[0]))
1101                                                         echo "name=:$k",' len='.$v[1],' type='.$v[2],'<br>';
1102                                                 else
1103                                                         echo "name=:$k",' var='.$inputarr[$k][0],' len='.$v[1],' type='.$v[2],'<br>';
1104
1105                                         }
1106                                 } else {
1107                                         $len = -1;
1108                                         if ($v === ' ') $len = 1;
1109                                         if (isset($bindarr)) {  // is prepared sql, so no need to oci_bind_by_name again
1110                                                 $bindarr[$k] = $v;
1111                                         } else {                                // dynamic sql, so rebind every time
1112                                                 oci_bind_by_name($stmt,":$k",$inputarr[$k],$len);
1113                                         }
1114                                 }
1115                         }
1116                 }
1117
1118                 $this->_errorMsg = false;
1119                 $this->_errorCode = false;
1120                 if (oci_execute($stmt,$this->_commit)) {
1121
1122                         if (count($this -> _refLOBs) > 0) {
1123
1124                                 foreach ($this -> _refLOBs as $key => $value) {
1125                                         if ($this -> _refLOBs[$key]['TYPE'] == true) {
1126                                                 $tmp = $this -> _refLOBs[$key]['LOB'] -> load();
1127                                                 if ($this -> debug) {
1128                                                         ADOConnection::outp("<b>OUT LOB</b>: LOB has been loaded. <br>");
1129                                                 }
1130                                                 //$_GLOBALS[$this -> _refLOBs[$key]['VAR']] = $tmp;
1131                                                 $this -> _refLOBs[$key]['VAR'] = $tmp;
1132                                         } else {
1133                                                 $this->_refLOBs[$key]['LOB']->save($this->_refLOBs[$key]['VAR']);
1134                                                 $this -> _refLOBs[$key]['LOB']->free();
1135                                                 unset($this -> _refLOBs[$key]);
1136                                                 if ($this->debug) {
1137                                                         ADOConnection::outp("<b>IN LOB</b>: LOB has been saved. <br>");
1138                                                 }
1139                                         }
1140                                 }
1141                         }
1142
1143                         switch (@oci_statement_type($stmt)) {
1144                                 case "SELECT":
1145                                         return $stmt;
1146
1147                                 case 'DECLARE':
1148                                 case "BEGIN":
1149                                         if (is_array($sql) && !empty($sql[4])) {
1150                                                 $cursor = $sql[4];
1151                                                 if (is_resource($cursor)) {
1152                                                         $ok = oci_execute($cursor);
1153                                                         return $cursor;
1154                                                 }
1155                                                 return $stmt;
1156                                         } else {
1157                                                 if (is_resource($stmt)) {
1158                                                         oci_free_statement($stmt);
1159                                                         return true;
1160                                                 }
1161                                                 return $stmt;
1162                                         }
1163                                         break;
1164                                 default :
1165
1166                                         return true;
1167                         }
1168                 }
1169                 return false;
1170         }
1171
1172         // From Oracle Whitepaper: PHP Scalability and High Availability
1173         function IsConnectionError($err)
1174         {
1175                 switch($err) {
1176                         case 378: /* buffer pool param incorrect */
1177                         case 602: /* core dump */
1178                         case 603: /* fatal error */
1179                         case 609: /* attach failed */
1180                         case 1012: /* not logged in */
1181                         case 1033: /* init or shutdown in progress */
1182                         case 1043: /* Oracle not available */
1183                         case 1089: /* immediate shutdown in progress */
1184                         case 1090: /* shutdown in progress */
1185                         case 1092: /* instance terminated */
1186                         case 3113: /* disconnect */
1187                         case 3114: /* not connected */
1188                         case 3122: /* closing window */
1189                         case 3135: /* lost contact */
1190                         case 12153: /* TNS: not connected */
1191                         case 27146: /* fatal or instance terminated */
1192                         case 28511: /* Lost RPC */
1193                         return true;
1194                 }
1195                 return false;
1196         }
1197
1198         // returns true or false
1199         function _close()
1200         {
1201                 if (!$this->_connectionID) return;
1202
1203                 if (!$this->autoCommit) oci_rollback($this->_connectionID);
1204                 if (count($this->_refLOBs) > 0) {
1205                         foreach ($this ->_refLOBs as $key => $value) {
1206                                 $this->_refLOBs[$key]['LOB']->free();
1207                                 unset($this->_refLOBs[$key]);
1208                         }
1209                 }
1210                 oci_close($this->_connectionID);
1211
1212                 $this->_stmt = false;
1213                 $this->_connectionID = false;
1214         }
1215
1216         function MetaPrimaryKeys($table, $owner=false,$internalKey=false)
1217         {
1218                 if ($internalKey) return array('ROWID');
1219
1220                 // tested with oracle 8.1.7
1221                 $table = strtoupper($table);
1222                 if ($owner) {
1223                         $owner_clause = "AND ((a.OWNER = b.OWNER) AND (a.OWNER = UPPER('$owner')))";
1224                         $ptab = 'ALL_';
1225                 } else {
1226                         $owner_clause = '';
1227                         $ptab = 'USER_';
1228                 }
1229                 $sql = "
1230 SELECT /*+ RULE */ distinct b.column_name
1231    FROM {$ptab}CONSTRAINTS a
1232           , {$ptab}CONS_COLUMNS b
1233   WHERE ( UPPER(b.table_name) = ('$table'))
1234         AND (UPPER(a.table_name) = ('$table') and a.constraint_type = 'P')
1235         $owner_clause
1236         AND (a.constraint_name = b.constraint_name)";
1237
1238                 $rs = $this->Execute($sql);
1239                 if ($rs && !$rs->EOF) {
1240                         $arr = $rs->GetArray();
1241                         $a = array();
1242                         foreach($arr as $v) {
1243                                 $a[] = reset($v);
1244                         }
1245                         return $a;
1246                 }
1247                 else return false;
1248         }
1249
1250         /**
1251          * returns assoc array where keys are tables, and values are foreign keys
1252          *
1253          * @param       str             $table
1254          * @param       str             $owner  [optional][default=NULL]
1255          * @param       bool    $upper  [optional][discarded]
1256          * @return      mixed[]                 Array of foreign key information
1257          *
1258          * @link http://gis.mit.edu/classes/11.521/sqlnotes/referential_integrity.html
1259          */
1260         function MetaForeignKeys($table, $owner=false, $upper=false)
1261         {
1262                 global $ADODB_FETCH_MODE;
1263
1264                 $save = $ADODB_FETCH_MODE;
1265                 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
1266                 $table = $this->qstr(strtoupper($table));
1267                 if (!$owner) {
1268                         $owner = $this->user;
1269                         $tabp = 'user_';
1270                 } else
1271                         $tabp = 'all_';
1272
1273                 $owner = ' and owner='.$this->qstr(strtoupper($owner));
1274
1275                 $sql =
1276 "select constraint_name,r_owner,r_constraint_name
1277         from {$tabp}constraints
1278         where constraint_type = 'R' and table_name = $table $owner";
1279
1280                 $constraints = $this->GetArray($sql);
1281                 $arr = false;
1282                 foreach($constraints as $constr) {
1283                         $cons = $this->qstr($constr[0]);
1284                         $rowner = $this->qstr($constr[1]);
1285                         $rcons = $this->qstr($constr[2]);
1286                         $cols = $this->GetArray("select column_name from {$tabp}cons_columns where constraint_name=$cons $owner order by position");
1287                         $tabcol = $this->GetArray("select table_name,column_name from {$tabp}cons_columns where owner=$rowner and constraint_name=$rcons order by position");
1288
1289                         if ($cols && $tabcol)
1290                                 for ($i=0, $max=sizeof($cols); $i < $max; $i++) {
1291                                         $arr[$tabcol[$i][0]] = $cols[$i][0].'='.$tabcol[$i][1];
1292                                 }
1293                 }
1294                 $ADODB_FETCH_MODE = $save;
1295
1296                 return $arr;
1297         }
1298
1299
1300         function CharMax()
1301         {
1302                 return 4000;
1303         }
1304
1305         function TextMax()
1306         {
1307                 return 4000;
1308         }
1309
1310         /**
1311          * Quotes a string.
1312          * An example is  $db->qstr("Don't bother",magic_quotes_runtime());
1313          *
1314          * @param string $s the string to quote
1315          * @param bool $magic_quotes if $s is GET/POST var, set to get_magic_quotes_gpc().
1316          *             This undoes the stupidity of magic quotes for GPC.
1317          *
1318          * @return string quoted string to be sent back to database
1319          */
1320         function qstr($s,$magic_quotes=false)
1321         {
1322                 //$nofixquotes=false;
1323
1324                 if ($this->noNullStrings && strlen($s)==0)$s = ' ';
1325                 if (!$magic_quotes) {
1326                         if ($this->replaceQuote[0] == '\\'){
1327                                 $s = str_replace('\\','\\\\',$s);
1328                         }
1329                         return  "'".str_replace("'",$this->replaceQuote,$s)."'";
1330                 }
1331
1332                 // undo magic quotes for " unless sybase is on
1333                 if (!ini_get('magic_quotes_sybase')) {
1334                         $s = str_replace('\\"','"',$s);
1335                         $s = str_replace('\\\\','\\',$s);
1336                         return "'".str_replace("\\'",$this->replaceQuote,$s)."'";
1337                 } else {
1338                         return "'".$s."'";
1339                 }
1340         }
1341
1342 }
1343
1344 /*--------------------------------------------------------------------------------------
1345         Class Name: Recordset
1346 --------------------------------------------------------------------------------------*/
1347
1348 class ADORecordset_oci8 extends ADORecordSet {
1349
1350         var $databaseType = 'oci8';
1351         var $bind=false;
1352         var $_fieldobjs;
1353
1354         //var $_arr = false;
1355
1356         function ADORecordset_oci8($queryID,$mode=false)
1357         {
1358                 if ($mode === false) {
1359                         global $ADODB_FETCH_MODE;
1360                         $mode = $ADODB_FETCH_MODE;
1361                 }
1362                 switch ($mode)
1363                 {
1364                 case ADODB_FETCH_ASSOC:$this->fetchMode = OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break;
1365                 case ADODB_FETCH_DEFAULT:
1366                 case ADODB_FETCH_BOTH:$this->fetchMode = OCI_NUM+OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break;
1367                 case ADODB_FETCH_NUM:
1368                 default:
1369                 $this->fetchMode = OCI_NUM+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break;
1370                 }
1371
1372                 $this->adodbFetchMode = $mode;
1373                 $this->_queryID = $queryID;
1374         }
1375
1376
1377         function Init()
1378         {
1379                 if ($this->_inited) return;
1380
1381                 $this->_inited = true;
1382                 if ($this->_queryID) {
1383
1384                         $this->_currentRow = 0;
1385                         @$this->_initrs();
1386                         if ($this->_numOfFields) $this->EOF = !$this->_fetch();
1387                         else $this->EOF = true;
1388
1389                         /*
1390                         // based on idea by Gaetano Giunta to detect unusual oracle errors
1391                         // see http://phplens.com/lens/lensforum/msgs.php?id=6771
1392                         $err = oci_error($this->_queryID);
1393                         if ($err && $this->connection->debug) ADOConnection::outp($err);
1394                         */
1395
1396                         if (!is_array($this->fields)) {
1397                                 $this->_numOfRows = 0;
1398                                 $this->fields = array();
1399                         }
1400                 } else {
1401                         $this->fields = array();
1402                         $this->_numOfRows = 0;
1403                         $this->_numOfFields = 0;
1404                         $this->EOF = true;
1405                 }
1406         }
1407
1408         function _initrs()
1409         {
1410                 $this->_numOfRows = -1;
1411                 $this->_numOfFields = oci_num_fields($this->_queryID);
1412                 if ($this->_numOfFields>0) {
1413                         $this->_fieldobjs = array();
1414                         $max = $this->_numOfFields;
1415                         for ($i=0;$i<$max; $i++) $this->_fieldobjs[] = $this->_FetchField($i);
1416                 }
1417         }
1418
1419         /**
1420          * Get column information in the Recordset object.
1421          * fetchField() can be used in order to obtain information about fields
1422          * in a certain query result. If the field offset isn't specified, the next
1423          * field that wasn't yet retrieved by fetchField() is retrieved
1424          *
1425          * @return object containing field information
1426          */
1427         function _FetchField($fieldOffset = -1)
1428         {
1429                 $fld = new ADOFieldObject;
1430                 $fieldOffset += 1;
1431                 $fld->name =oci_field_name($this->_queryID, $fieldOffset);
1432                 $fld->type = oci_field_type($this->_queryID, $fieldOffset);
1433                 $fld->max_length = oci_field_size($this->_queryID, $fieldOffset);
1434
1435                 switch($fld->type) {
1436                         case 'NUMBER':
1437                                 $p = oci_field_precision($this->_queryID, $fieldOffset);
1438                                 $sc = oci_field_scale($this->_queryID, $fieldOffset);
1439                                 if ($p != 0 && $sc == 0) $fld->type = 'INT';
1440                                 $fld->scale = $p;
1441                                 break;
1442
1443                         case 'CLOB':
1444                         case 'NCLOB':
1445                         case 'BLOB':
1446                                 $fld->max_length = -1;
1447                                 break;
1448                 }
1449                 return $fld;
1450         }
1451
1452         /* For some reason, oci_field_name fails when called after _initrs() so we cache it */
1453         function FetchField($fieldOffset = -1)
1454         {
1455                 return $this->_fieldobjs[$fieldOffset];
1456         }
1457
1458
1459 /*
1460         // 10% speedup to move MoveNext to child class
1461         function _MoveNext()
1462         {
1463         //global $ADODB_EXTENSION;if ($ADODB_EXTENSION) return @adodb_movenext($this);
1464
1465                 if ($this->EOF) return false;
1466
1467                 $this->_currentRow++;
1468                 if($this->fields = @oci_fetch_array($this->_queryID,$this->fetchMode))
1469                         return true;
1470                 $this->EOF = true;
1471
1472                 return false;
1473         }
1474 */
1475
1476
1477         function MoveNext()
1478         {
1479                 if ($this->fields = @oci_fetch_array($this->_queryID,$this->fetchMode)) {
1480                         $this->_currentRow += 1;
1481                         return true;
1482                 }
1483                 if (!$this->EOF) {
1484                         $this->_currentRow += 1;
1485                         $this->EOF = true;
1486                 }
1487                 return false;
1488         }
1489
1490 /*
1491         # does not work as first record is retrieved in _initrs(), so is not included in GetArray()
1492         function GetArray($nRows = -1)
1493         {
1494         global $ADODB_OCI8_GETARRAY;
1495
1496                 if (true ||  !empty($ADODB_OCI8_GETARRAY)) {
1497                         # does not support $ADODB_ANSI_PADDING_OFF
1498
1499                         //OCI_RETURN_NULLS and OCI_RETURN_LOBS is set by oci_fetch_all
1500                         switch($this->adodbFetchMode) {
1501                         case ADODB_FETCH_NUM:
1502
1503                                 $ncols = @oci_fetch_all($this->_queryID, $results, 0, $nRows, oci_fetch_all_BY_ROW+OCI_NUM);
1504                                 $results = array_merge(array($this->fields),$results);
1505                                 return $results;
1506
1507                         case ADODB_FETCH_ASSOC:
1508                                 if (ADODB_ASSOC_CASE != 2 || $this->databaseType != 'oci8') break;
1509
1510                                 $ncols = @oci_fetch_all($this->_queryID, $assoc, 0, $nRows, oci_fetch_all_BY_ROW);
1511                                 $results = array_merge(array($this->fields),$assoc);
1512                                 return $results;
1513
1514                         default:
1515                                 break;
1516                         }
1517                 }
1518
1519                 $results = ADORecordSet::GetArray($nRows);
1520                 return $results;
1521
1522         }
1523 */
1524
1525         // Optimize SelectLimit() by using oci_fetch()
1526         function GetArrayLimit($nrows,$offset=-1)
1527         {
1528                 if ($offset <= 0) {
1529                         $arr = $this->GetArray($nrows);
1530                         return $arr;
1531                 }
1532                 $arr = array();
1533                 for ($i=1; $i < $offset; $i++)
1534                         if (!@oci_fetch($this->_queryID)) return $arr;
1535
1536                 if (!$this->fields = @oci_fetch_array($this->_queryID,$this->fetchMode)) return $arr;;
1537                 $results = array();
1538                 $cnt = 0;
1539                 while (!$this->EOF && $nrows != $cnt) {
1540                         $results[$cnt++] = $this->fields;
1541                         $this->MoveNext();
1542                 }
1543
1544                 return $results;
1545         }
1546
1547
1548         // Use associative array to get fields array
1549         function Fields($colname)
1550         {
1551                 if (!$this->bind) {
1552                         $this->bind = array();
1553                         for ($i=0; $i < $this->_numOfFields; $i++) {
1554                                 $o = $this->FetchField($i);
1555                                 $this->bind[strtoupper($o->name)] = $i;
1556                         }
1557                 }
1558
1559                 return $this->fields[$this->bind[strtoupper($colname)]];
1560         }
1561
1562
1563
1564         function _seek($row)
1565         {
1566                 return false;
1567         }
1568
1569         function _fetch()
1570         {
1571                 return $this->fields = @oci_fetch_array($this->_queryID,$this->fetchMode);
1572         }
1573
1574         /**
1575          * close() only needs to be called if you are worried about using too much
1576          * memory while your script is running. All associated result memory for the
1577          * specified result identifier will automatically be freed.
1578          */
1579         function _close()
1580         {
1581                 if ($this->connection->_stmt === $this->_queryID) $this->connection->_stmt = false;
1582                 if (!empty($this->_refcursor)) {
1583                         oci_free_cursor($this->_refcursor);
1584                         $this->_refcursor = false;
1585                 }
1586                 @oci_free_statement($this->_queryID);
1587                 $this->_queryID = false;
1588         }
1589
1590         /**
1591          * not the fastest implementation - quick and dirty - jlim
1592          * for best performance, use the actual $rs->MetaType().
1593          *
1594          * @param       mixed   $t
1595          * @param       int             $len            [optional] Length of blobsize
1596          * @param       bool    $fieldobj       [optional][discarded]
1597          * @return      str                                     The metatype of the field
1598          */
1599         function MetaType($t, $len=-1, $fieldobj=false)
1600         {
1601                 if (is_object($t)) {
1602                         $fieldobj = $t;
1603                         $t = $fieldobj->type;
1604                         $len = $fieldobj->max_length;
1605                 }
1606
1607                 switch (strtoupper($t)) {
1608                 case 'VARCHAR':
1609                 case 'VARCHAR2':
1610                 case 'CHAR':
1611                 case 'VARBINARY':
1612                 case 'BINARY':
1613                 case 'NCHAR':
1614                 case 'NVARCHAR':
1615                 case 'NVARCHAR2':
1616                         if ($len <= $this->blobSize) return 'C';
1617
1618                 case 'NCLOB':
1619                 case 'LONG':
1620                 case 'LONG VARCHAR':
1621                 case 'CLOB':
1622                 return 'X';
1623
1624                 case 'LONG RAW':
1625                 case 'LONG VARBINARY':
1626                 case 'BLOB':
1627                         return 'B';
1628
1629                 case 'DATE':
1630                         return  ($this->connection->datetime) ? 'T' : 'D';
1631
1632
1633                 case 'TIMESTAMP': return 'T';
1634
1635                 case 'INT':
1636                 case 'SMALLINT':
1637                 case 'INTEGER':
1638                         return 'I';
1639
1640                 default:
1641                         return 'N';
1642                 }
1643         }
1644 }
1645
1646 class ADORecordSet_ext_oci8 extends ADORecordSet_oci8 {
1647         function ADORecordSet_ext_oci8($queryID,$mode=false)
1648         {
1649                 if ($mode === false) {
1650                         global $ADODB_FETCH_MODE;
1651                         $mode = $ADODB_FETCH_MODE;
1652                 }
1653                 switch ($mode)
1654                 {
1655                 case ADODB_FETCH_ASSOC:$this->fetchMode = OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break;
1656                 case ADODB_FETCH_DEFAULT:
1657                 case ADODB_FETCH_BOTH:$this->fetchMode = OCI_NUM+OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break;
1658                 case ADODB_FETCH_NUM:
1659                 default: $this->fetchMode = OCI_NUM+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break;
1660                 }
1661                 $this->adodbFetchMode = $mode;
1662                 $this->_queryID = $queryID;
1663         }
1664
1665         function MoveNext()
1666         {
1667                 return adodb_movenext($this);
1668         }
1669 }