library/adodb/drivers/adodb-oci8.inc.php

   1 <?php
   2 /*
   3
   4   version V5.14 8 Sept 2011 (c) 2000-2011 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
 170   Multiple modes of connection are supported:
 171
 172   a. Local Database
 173     $conn->Connect(false,'scott','tiger');
 174
 175   b. From tnsnames.ora
 176     $conn->Connect(false,'scott','tiger',$tnsname);
 177     $conn->Connect($tnsname,'scott','tiger');
 178
 179   c. Server + service name
 180     $conn->Connect($serveraddress,'scott,'tiger',$service_name);
 181
 182   d. Server + SID
 183         $conn->connectSID = true;
 184         $conn->Connect($serveraddress,'scott,'tiger',$SID);
 185
 186
 187 Example TNSName:
 188 ---------------
 189 NATSOFT.DOMAIN =
 190   (DESCRIPTION =
 191         (ADDRESS_LIST =
 192           (ADDRESS = (PROTOCOL = TCP)(HOST = kermit)(PORT = 1523))
 193         )
 194         (CONNECT_DATA =
 195           (SERVICE_NAME = natsoft.domain)
 196         )
 197   )
 198
 199   There are 3 connection modes, 0 = non-persistent, 1 = persistent, 2 = force new connection
 200
 201 */
 202         function _connect($argHostname, $argUsername, $argPassword, $argDatabasename,$mode=0)
 203         {
 204                 if (!function_exists('OCIPLogon')) return null;
 205                 #adodb_backtrace();
 206
 207         $this->_errorMsg = false;
 208                 $this->_errorCode = false;
 209
 210                 if($argHostname) { // added by Jorma Tuomainen <jorma.tuomainen@ppoy.fi>
 211                         if (empty($argDatabasename)) $argDatabasename = $argHostname;
 212                         else {
 213                                 if(strpos($argHostname,":")) {
 214                                         $argHostinfo=explode(":",$argHostname);
 215                                         $argHostname=$argHostinfo[0];
 216                                         $argHostport=$argHostinfo[1];
 217                                 } else {
 218                                         $argHostport = empty($this->port)?  "1521" : $this->port;
 219                                 }
 220
 221                                 if (strncasecmp($argDatabasename,'SID=',4) == 0) {
 222                                         $argDatabasename = substr($argDatabasename,4);
 223                                         $this->connectSID = true;
 224                                 }
 225
 226                                 if ($this->connectSID) {
 227                                         $argDatabasename="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=".$argHostname
 228                                         .")(PORT=$argHostport))(CONNECT_DATA=(SID=$argDatabasename)))";
 229                                 } else
 230                                         $argDatabasename="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=".$argHostname
 231                                         .")(PORT=$argHostport))(CONNECT_DATA=(SERVICE_NAME=$argDatabasename)))";
 232                         }
 233                 }
 234
 235                 //if ($argHostname) print "<p>Connect: 1st argument should be left blank for $this->databaseType</p>";
 236                 if ($mode==1) {
 237                         $this->_connectionID = ($this->charSet) ?
 238                                 OCIPLogon($argUsername,$argPassword, $argDatabasename,$this->charSet)
 239                                 :
 240                                 OCIPLogon($argUsername,$argPassword, $argDatabasename)
 241                                 ;
 242                         if ($this->_connectionID && $this->autoRollback)  OCIrollback($this->_connectionID);
 243                 } else if ($mode==2) {
 244                         $this->_connectionID = ($this->charSet) ?
 245                                 OCINLogon($argUsername,$argPassword, $argDatabasename,$this->charSet)
 246                                 :
 247                                 OCINLogon($argUsername,$argPassword, $argDatabasename);
 248
 249                 } else {
 250                         $this->_connectionID = ($this->charSet) ?
 251                                 OCILogon($argUsername,$argPassword, $argDatabasename,$this->charSet)
 252                                 :
 253                                 OCILogon($argUsername,$argPassword, $argDatabasename);
 254                 }
 255                 if (!$this->_connectionID) return false;
 256                 if ($this->_initdate) {
 257                         $this->Execute("ALTER SESSION SET NLS_DATE_FORMAT='".$this->NLS_DATE_FORMAT."'");
 258                 }
 259
 260                 // looks like:
 261                 // Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option JServer Release 8.1.7.0.0 - Production
 262                 // $vers = OCIServerVersion($this->_connectionID);
 263                 // if (strpos($vers,'8i') !== false) $this->ansiOuter = true;
 264                 return true;
 265         }
 266
 267         function ServerInfo()
 268         {
 269                 $arr['compat'] = $this->GetOne('select value from sys.database_compatible_level');
 270                 $arr['description'] = @OCIServerVersion($this->_connectionID);
 271                 $arr['version'] = ADOConnection::_findvers($arr['description']);
 272                 return $arr;
 273         }
 274                 // returns true or false
 275         function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
 276         {
 277                 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename,1);
 278         }
 279
 280         // returns true or false
 281         function _nconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
 282         {
 283                 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename,2);
 284         }
 285
 286         function _affectedrows()
 287         {
 288                 if (is_resource($this->_stmt)) return @OCIRowCount($this->_stmt);
 289                 return 0;
 290         }
 291
 292         function IfNull( $field, $ifNull )
 293         {
 294                 return " NVL($field, $ifNull) "; // if Oracle
 295         }
 296
 297         // format and return date string in database date format
 298         function DBDate($d,$isfld=false)
 299         {
 300                 if (empty($d) && $d !== 0) return 'null';
 301                 if ($isfld) return 'TO_DATE('.$d.",'".$this->dateformat."')";
 302
 303                 if (is_string($d)) $d = ADORecordSet::UnixDate($d);
 304
 305                 if (is_object($d)) $ds = $d->format($this->fmtDate);
 306                 else $ds = adodb_date($this->fmtDate,$d);
 307
 308                 return "TO_DATE(".$ds.",'".$this->dateformat."')";
 309         }
 310
 311         function BindDate($d)
 312         {
 313                 $d = ADOConnection::DBDate($d);
 314                 if (strncmp($d,"'",1)) return $d;
 315
 316                 return substr($d,1,strlen($d)-2);
 317         }
 318
 319         function BindTimeStamp($ts)
 320         {
 321                 if (empty($ts) && $ts !== 0) return 'null';
 322                 if (is_string($ts)) $ts = ADORecordSet::UnixTimeStamp($ts);
 323
 324                 if (is_object($ts)) $tss = $ts->format("'Y-m-d H:i:s'");
 325                 else $tss = adodb_date("'Y-m-d H:i:s'",$ts);
 326
 327                 return $tss;
 328         }
 329
 330         // format and return date string in database timestamp format
 331         function DBTimeStamp($ts,$isfld=false)
 332         {
 333                 if (empty($ts) && $ts !== 0) return 'null';
 334                 if ($isfld) return 'TO_DATE(substr('.$ts.",1,19),'RRRR-MM-DD, HH24:MI:SS')";
 335                 if (is_string($ts)) $ts = ADORecordSet::UnixTimeStamp($ts);
 336
 337                 if (is_object($ts)) $tss = $ts->format("'Y-m-d H:i:s'");
 338                 else $tss = date("'Y-m-d H:i:s'",$ts);
 339
 340                 return 'TO_DATE('.$tss.",'RRRR-MM-DD, HH24:MI:SS')";
 341         }
 342
 343         function RowLock($tables,$where,$col='1 as adodbignore')
 344         {
 345                 if ($this->autoCommit) $this->BeginTrans();
 346                 return $this->GetOne("select $col from $tables where $where for update");
 347         }
 348
 349         function MetaTables($ttype=false,$showSchema=false,$mask=false)
 350         {
 351                 if ($mask) {
 352                         $save = $this->metaTablesSQL;
 353                         $mask = $this->qstr(strtoupper($mask));
 354                         $this->metaTablesSQL .= " AND upper(table_name) like $mask";
 355                 }
 356                 $ret = ADOConnection::MetaTables($ttype,$showSchema);
 357
 358                 if ($mask) {
 359                         $this->metaTablesSQL = $save;
 360                 }
 361                 return $ret;
 362         }
 363
 364         // Mark Newnham
 365         function MetaIndexes ($table, $primary = FALSE, $owner=false)
 366         {
 367         // save old fetch mode
 368         global $ADODB_FETCH_MODE;
 369
 370         $save = $ADODB_FETCH_MODE;
 371         $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 372
 373         if ($this->fetchMode !== FALSE) {
 374                $savem = $this->SetFetchMode(FALSE);
 375         }
 376
 377                 // get index details
 378                 $table = strtoupper($table);
 379
 380                 // get Primary index
 381                 $primary_key = '';
 382
 383                 $false = false;
 384                 $rs = $this->Execute(sprintf("SELECT * FROM ALL_CONSTRAINTS WHERE UPPER(TABLE_NAME)='%s' AND CONSTRAINT_TYPE='P'",$table));
 385                 if ($row = $rs->FetchRow())
 386                    $primary_key = $row[1]; //constraint_name
 387
 388                 if ($primary==TRUE && $primary_key=='') {
 389                          if (isset($savem))
 390                 $this->SetFetchMode($savem);
 391                         $ADODB_FETCH_MODE = $save;
 392                         return $false; //There is no primary key
 393                 }
 394
 395         $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));
 396
 397
 398         if (!is_object($rs)) {
 399                         if (isset($savem))
 400                                 $this->SetFetchMode($savem);
 401                         $ADODB_FETCH_MODE = $save;
 402             return $false;
 403         }
 404
 405                 $indexes = array ();
 406         // parse index data into array
 407
 408         while ($row = $rs->FetchRow()) {
 409                         if ($primary && $row[0] != $primary_key) continue;
 410             if (!isset($indexes[$row[0]])) {
 411                                 $indexes[$row[0]] = array(
 412                                    'unique' => ($row[1] == 'UNIQUE'),
 413                                    'columns' => array()
 414                                 );
 415             }
 416             $indexes[$row[0]]['columns'][$row[2] - 1] = $row[3];
 417         }
 418
 419         // sort columns by order in the index
 420         foreach ( array_keys ($indexes) as $index ) {
 421             ksort ($indexes[$index]['columns']);
 422         }
 423
 424                 if (isset($savem)) {
 425             $this->SetFetchMode($savem);
 426                         $ADODB_FETCH_MODE = $save;
 427                 }
 428         return $indexes;
 429         }
 430
 431         function BeginTrans()
 432         {
 433                 if ($this->transOff) return true;
 434                 $this->transCnt += 1;
 435                 $this->autoCommit = false;
 436                 $this->_commit = OCI_DEFAULT;
 437
 438                 if ($this->_transmode) $ok = $this->Execute("SET TRANSACTION ".$this->_transmode);
 439                 else $ok = true;
 440
 441                 return $ok ? true : false;
 442         }
 443
 444         function CommitTrans($ok=true)
 445         {
 446                 if ($this->transOff) return true;
 447                 if (!$ok) return $this->RollbackTrans();
 448
 449                 if ($this->transCnt) $this->transCnt -= 1;
 450                 $ret = OCIcommit($this->_connectionID);
 451                 $this->_commit = OCI_COMMIT_ON_SUCCESS;
 452                 $this->autoCommit = true;
 453                 return $ret;
 454         }
 455
 456         function RollbackTrans()
 457         {
 458                 if ($this->transOff) return true;
 459                 if ($this->transCnt) $this->transCnt -= 1;
 460                 $ret = OCIrollback($this->_connectionID);
 461                 $this->_commit = OCI_COMMIT_ON_SUCCESS;
 462                 $this->autoCommit = true;
 463                 return $ret;
 464         }
 465
 466
 467         function SelectDB($dbName)
 468         {
 469                 return false;
 470         }
 471
 472         function ErrorMsg()
 473         {
 474                 if ($this->_errorMsg !== false) return $this->_errorMsg;
 475
 476                 if (is_resource($this->_stmt)) $arr = @OCIError($this->_stmt);
 477                 if (empty($arr)) {
 478                         if (is_resource($this->_connectionID)) $arr = @OCIError($this->_connectionID);
 479                         else $arr = @OCIError();
 480                         if ($arr === false) return '';
 481                 }
 482                 $this->_errorMsg = $arr['message'];
 483                 $this->_errorCode = $arr['code'];
 484                 return $this->_errorMsg;
 485         }
 486
 487         function ErrorNo()
 488         {
 489                 if ($this->_errorCode !== false) return $this->_errorCode;
 490
 491                 if (is_resource($this->_stmt)) $arr = @OCIError($this->_stmt);
 492                 if (empty($arr)) {
 493                         $arr = @OCIError($this->_connectionID);
 494                         if ($arr == false) $arr = @OCIError();
 495                         if ($arr == false) return '';
 496                 }
 497
 498                 $this->_errorMsg = $arr['message'];
 499                 $this->_errorCode = $arr['code'];
 500
 501                 return $arr['code'];
 502         }
 503
 504         // Format date column in sql string given an input format that understands Y M D
 505         function SQLDate($fmt, $col=false)
 506         {
 507                 if (!$col) $col = $this->sysTimeStamp;
 508                 $s = 'TO_CHAR('.$col.",'";
 509
 510                 $len = strlen($fmt);
 511                 for ($i=0; $i < $len; $i++) {
 512                         $ch = $fmt[$i];
 513                         switch($ch) {
 514                         case 'Y':
 515                         case 'y':
 516                                 $s .= 'YYYY';
 517                                 break;
 518                         case 'Q':
 519                         case 'q':
 520                                 $s .= 'Q';
 521                                 break;
 522
 523                         case 'M':
 524                                 $s .= 'Mon';
 525                                 break;
 526
 527                         case 'm':
 528                                 $s .= 'MM';
 529                                 break;
 530                         case 'D':
 531                         case 'd':
 532                                 $s .= 'DD';
 533                                 break;
 534
 535                         case 'H':
 536                                 $s.= 'HH24';
 537                                 break;
 538
 539                         case 'h':
 540                                 $s .= 'HH';
 541                                 break;
 542
 543                         case 'i':
 544                                 $s .= 'MI';
 545                                 break;
 546
 547                         case 's':
 548                                 $s .= 'SS';
 549                                 break;
 550
 551                         case 'a':
 552                         case 'A':
 553                                 $s .= 'AM';
 554                                 break;
 555
 556                         case 'w':
 557                                 $s .= 'D';
 558                                 break;
 559
 560                         case 'l':
 561                                 $s .= 'DAY';
 562                                 break;
 563
 564                          case 'W':
 565                                 $s .= 'WW';
 566                                 break;
 567
 568                         default:
 569                         // handle escape characters...
 570                                 if ($ch == '\\') {
 571                                         $i++;
 572                                         $ch = substr($fmt,$i,1);
 573                                 }
 574                                 if (strpos('-/.:;, ',$ch) !== false) $s .= $ch;
 575                                 else $s .= '"'.$ch.'"';
 576
 577                         }
 578                 }
 579                 return $s. "')";
 580         }
 581
 582         function GetRandRow($sql, $arr = false)
 583         {
 584                 $sql = "SELECT * FROM ($sql ORDER BY dbms_random.value) WHERE rownum = 1";
 585
 586                 return $this->GetRow($sql,$arr);
 587         }
 588
 589         /*
 590         This algorithm makes use of
 591
 592         a. FIRST_ROWS hint
 593         The FIRST_ROWS hint explicitly chooses the approach to optimize response time,
 594         that is, minimum resource usage to return the first row. Results will be returned
 595         as soon as they are identified.
 596
 597         b. Uses rownum tricks to obtain only the required rows from a given offset.
 598          As this uses complicated sql statements, we only use this if the $offset >= 100.
 599          This idea by Tomas V V Cox.
 600
 601          This implementation does not appear to work with oracle 8.0.5 or earlier. Comment
 602          out this function then, and the slower SelectLimit() in the base class will be used.
 603         */
 604         function SelectLimit($sql,$nrows=-1,$offset=-1, $inputarr=false,$secs2cache=0)
 605         {
 606                 // seems that oracle only supports 1 hint comment in 8i
 607                 if ($this->firstrows) {
 608                         if ($nrows > 500 && $nrows < 1000) $hint = "FIRST_ROWS($nrows)";
 609                         else $hint = 'FIRST_ROWS';
 610
 611                         if (strpos($sql,'/*+') !== false)
 612                                 $sql = str_replace('/*+ ',"/*+$hint ",$sql);
 613                         else
 614                                 $sql = preg_replace('/^[ \t\n]*select/i',"SELECT /*+$hint*/",$sql);
 615                 }
 616
 617                 if ($offset == -1 || ($offset < $this->selectOffsetAlg1 && 0 < $nrows && $nrows < 1000)) {
 618                         if ($nrows > 0) {
 619                                 if ($offset > 0) $nrows += $offset;
 620                                 //$inputarr['adodb_rownum'] = $nrows;
 621                                 if ($this->databaseType == 'oci8po') {
 622                                         $sql = "select * from (".$sql.") where rownum <= ?";
 623                                 } else {
 624                                         $sql = "select * from (".$sql.") where rownum <= :adodb_offset";
 625                                 }
 626                                 $inputarr['adodb_offset'] = $nrows;
 627                                 $nrows = -1;
 628                         }
 629                         // note that $nrows = 0 still has to work ==> no rows returned
 630
 631                         $rs = ADOConnection::SelectLimit($sql,$nrows,$offset,$inputarr,$secs2cache);
 632                         return $rs;
 633
 634                 } else {
 635                          // Algorithm by Tomas V V Cox, from PEAR DB oci8.php
 636
 637                          // Let Oracle return the name of the columns
 638                         $q_fields = "SELECT * FROM (".$sql.") WHERE NULL = NULL";
 639
 640                         $false = false;
 641                         if (! $stmt_arr = $this->Prepare($q_fields)) {
 642                                 return $false;
 643                         }
 644                         $stmt = $stmt_arr[1];
 645
 646                         if (is_array($inputarr)) {
 647                                 foreach($inputarr as $k => $v) {
 648                                         if (is_array($v)) {
 649                                                 if (sizeof($v) == 2) // suggested by g.giunta@libero.
 650                                                         OCIBindByName($stmt,":$k",$inputarr[$k][0],$v[1]);
 651                                                 else
 652                                                         OCIBindByName($stmt,":$k",$inputarr[$k][0],$v[1],$v[2]);
 653                                         } else {
 654                                                 $len = -1;
 655                                                 if ($v === ' ') $len = 1;
 656                                                 if (isset($bindarr)) {  // is prepared sql, so no need to ocibindbyname again
 657                                                         $bindarr[$k] = $v;
 658                                                 } else {                                // dynamic sql, so rebind every time
 659                                                         OCIBindByName($stmt,":$k",$inputarr[$k],$len);
 660
 661                                                 }
 662                                         }
 663                                 }
 664                         }
 665
 666                          if (!OCIExecute($stmt, OCI_DEFAULT)) {
 667                                  OCIFreeStatement($stmt);
 668                                  return $false;
 669                          }
 670
 671                          $ncols = OCINumCols($stmt);
 672                          for ( $i = 1; $i <= $ncols; $i++ ) {
 673                                  $cols[] = '"'.OCIColumnName($stmt, $i).'"';
 674                          }
 675                          $result = false;
 676
 677                          OCIFreeStatement($stmt);
 678                          $fields = implode(',', $cols);
 679                          if ($nrows <= 0) $nrows = 999999999999;
 680                          else $nrows += $offset;
 681                          $offset += 1; // in Oracle rownum starts at 1
 682
 683                         if ($this->databaseType == 'oci8po') {
 684                                          $sql = "SELECT /*+ FIRST_ROWS */ $fields FROM".
 685                                           "(SELECT rownum as adodb_rownum, $fields FROM".
 686                                           " ($sql) WHERE rownum <= ?".
 687                                           ") WHERE adodb_rownum >= ?";
 688                                 } else {
 689                                          $sql = "SELECT /*+ FIRST_ROWS */ $fields FROM".
 690                                           "(SELECT rownum as adodb_rownum, $fields FROM".
 691                                           " ($sql) WHERE rownum <= :adodb_nrows".
 692                                           ") WHERE adodb_rownum >= :adodb_offset";
 693                                 }
 694                                 $inputarr['adodb_nrows'] = $nrows;
 695                                 $inputarr['adodb_offset'] = $offset;
 696
 697                         if ($secs2cache>0) $rs = $this->CacheExecute($secs2cache, $sql,$inputarr);
 698                         else $rs = $this->Execute($sql,$inputarr);
 699                         return $rs;
 700                 }
 701
 702         }
 703
 704         /**
 705         * Usage:
 706         * Store BLOBs and CLOBs
 707         *
 708         * Example: to store $var in a blob
 709         *
 710         *       $conn->Execute('insert into TABLE (id,ablob) values(12,empty_blob())');
 711         *       $conn->UpdateBlob('TABLE', 'ablob', $varHoldingBlob, 'ID=12', 'BLOB');
 712         *
 713         *       $blobtype supports 'BLOB' and 'CLOB', but you need to change to 'empty_clob()'.
 714         *
 715         *  to get length of LOB:
 716         *       select DBMS_LOB.GETLENGTH(ablob) from TABLE
 717         *
 718         * If you are using CURSOR_SHARING = force, it appears this will case a segfault
 719         * under oracle 8.1.7.0. Run:
 720         *        $db->Execute('ALTER SESSION SET CURSOR_SHARING=EXACT');
 721         * before UpdateBlob() then...
 722         */
 723
 724         function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB')
 725         {
 726
 727                 //if (strlen($val) < 4000) return $this->Execute("UPDATE $table SET $column=:blob WHERE $where",array('blob'=>$val)) != false;
 728
 729                 switch(strtoupper($blobtype)) {
 730                 default: ADOConnection::outp("<b>UpdateBlob</b>: Unknown blobtype=$blobtype"); return false;
 731                 case 'BLOB': $type = OCI_B_BLOB; break;
 732                 case 'CLOB': $type = OCI_B_CLOB; break;
 733                 }
 734
 735                 if ($this->databaseType == 'oci8po')
 736                         $sql = "UPDATE $table set $column=EMPTY_{$blobtype}() WHERE $where RETURNING $column INTO ?";
 737                 else
 738                         $sql = "UPDATE $table set $column=EMPTY_{$blobtype}() WHERE $where RETURNING $column INTO :blob";
 739
 740                 $desc = OCINewDescriptor($this->_connectionID, OCI_D_LOB);
 741                 $arr['blob'] = array($desc,-1,$type);
 742                 if ($this->session_sharing_force_blob) $this->Execute('ALTER SESSION SET CURSOR_SHARING=EXACT');
 743                 $commit = $this->autoCommit;
 744                 if ($commit) $this->BeginTrans();
 745                 $rs = $this->_Execute($sql,$arr);
 746                 if ($rez = !empty($rs)) $desc->save($val);
 747                 $desc->free();
 748                 if ($commit) $this->CommitTrans();
 749                 if ($this->session_sharing_force_blob) $this->Execute('ALTER SESSION SET CURSOR_SHARING=FORCE');
 750
 751                 if ($rez) $rs->Close();
 752                 return $rez;
 753         }
 754
 755         /**
 756         * Usage:  store file pointed to by $val in a blob
 757         */
 758         function UpdateBlobFile($table,$column,$val,$where,$blobtype='BLOB')
 759         {
 760                 switch(strtoupper($blobtype)) {
 761                 default: ADOConnection::outp( "<b>UpdateBlob</b>: Unknown blobtype=$blobtype"); return false;
 762                 case 'BLOB': $type = OCI_B_BLOB; break;
 763                 case 'CLOB': $type = OCI_B_CLOB; break;
 764                 }
 765
 766                 if ($this->databaseType == 'oci8po')
 767                         $sql = "UPDATE $table set $column=EMPTY_{$blobtype}() WHERE $where RETURNING $column INTO ?";
 768                 else
 769                         $sql = "UPDATE $table set $column=EMPTY_{$blobtype}() WHERE $where RETURNING $column INTO :blob";
 770
 771                 $desc = OCINewDescriptor($this->_connectionID, OCI_D_LOB);
 772                 $arr['blob'] = array($desc,-1,$type);
 773
 774                 $this->BeginTrans();
 775                 $rs = ADODB_oci8::Execute($sql,$arr);
 776                 if ($rez = !empty($rs)) $desc->savefile($val);
 777                 $desc->free();
 778                 $this->CommitTrans();
 779
 780                 if ($rez) $rs->Close();
 781                 return $rez;
 782         }
 783
 784                 /**
 785          * Execute SQL
 786          *
 787          * @param sql           SQL statement to execute, or possibly an array holding prepared statement ($sql[0] will hold sql text)
 788          * @param [inputarr]    holds the input data to bind to. Null elements will be set to null.
 789          * @return              RecordSet or false
 790          */
 791         function Execute($sql,$inputarr=false)
 792         {
 793                 if ($this->fnExecute) {
 794                         $fn = $this->fnExecute;
 795                         $ret = $fn($this,$sql,$inputarr);
 796                         if (isset($ret)) return $ret;
 797                 }
 798                 if ($inputarr) {
 799                         #if (!is_array($inputarr)) $inputarr = array($inputarr);
 800
 801                         $element0 = reset($inputarr);
 802                         $array2d =  $this->bulkBind && is_array($element0) && !is_object(reset($element0));
 803
 804                         # see http://phplens.com/lens/lensforum/msgs.php?id=18786
 805                         if ($array2d || !$this->_bindInputArray) {
 806
 807                         # is_object check because oci8 descriptors can be passed in
 808                         if ($array2d && $this->_bindInputArray) {
 809                                 if (is_string($sql))
 810                                         $stmt = $this->Prepare($sql);
 811                                 else
 812                                         $stmt = $sql;
 813
 814                                 foreach($inputarr as $arr) {
 815                                         $ret = $this->_Execute($stmt,$arr);
 816                                         if (!$ret) return $ret;
 817                                 }
 818                                 return $ret;
 819                         } else {
 820                                 $sqlarr = explode(':',$sql);
 821                                 $sql = '';
 822                                 $lastnomatch = -2;
 823                                 #var_dump($sqlarr);echo "<hr>";var_dump($inputarr);echo"<hr>";
 824                                 foreach($sqlarr as $k => $str) {
 825                                                 if ($k == 0) { $sql = $str; continue; }
 826                                                 // we need $lastnomatch because of the following datetime,
 827                                                 // eg. '10:10:01', which causes code to think that there is bind param :10 and :1
 828                                                 $ok = preg_match('/^([0-9]*)/', $str, $arr);
 829
 830                                                 if (!$ok) $sql .= $str;
 831                                                 else {
 832                                                         $at = $arr[1];
 833                                                         if (isset($inputarr[$at]) || is_null($inputarr[$at])) {
 834                                                                 if ((strlen($at) == strlen($str) && $k < sizeof($arr)-1)) {
 835                                                                         $sql .= ':'.$str;
 836                                                                         $lastnomatch = $k;
 837                                                                 } else if ($lastnomatch == $k-1) {
 838                                                                         $sql .= ':'.$str;
 839                                                                 } else {
 840                                                                         if (is_null($inputarr[$at])) $sql .= 'null';
 841                                                                         else $sql .= $this->qstr($inputarr[$at]);
 842                                                                         $sql .= substr($str, strlen($at));
 843                                                                 }
 844                                                         } else {
 845                                                                 $sql .= ':'.$str;
 846                                                         }
 847
 848                                                 }
 849                                         }
 850                                         $inputarr = false;
 851                                 }
 852                         }
 853                         $ret = $this->_Execute($sql,$inputarr);
 854
 855
 856                 } else {
 857                         $ret = $this->_Execute($sql,false);
 858                 }
 859
 860                 return $ret;
 861         }
 862
 863         /*
 864                 Example of usage:
 865
 866                 $stmt = $this->Prepare('insert into emp (empno, ename) values (:empno, :ename)');
 867         */
 868         function Prepare($sql,$cursor=false)
 869         {
 870         static $BINDNUM = 0;
 871
 872                 $stmt = OCIParse($this->_connectionID,$sql);
 873
 874                 if (!$stmt) {
 875                         $this->_errorMsg = false;
 876                         $this->_errorCode = false;
 877                         $arr = @OCIError($this->_connectionID);
 878                         if ($arr === false) return false;
 879
 880                         $this->_errorMsg = $arr['message'];
 881                         $this->_errorCode = $arr['code'];
 882                         return false;
 883                 }
 884
 885                 $BINDNUM += 1;
 886
 887                 $sttype = @OCIStatementType($stmt);
 888                 if ($sttype == 'BEGIN' || $sttype == 'DECLARE') {
 889                         return array($sql,$stmt,0,$BINDNUM, ($cursor) ? OCINewCursor($this->_connectionID) : false);
 890                 }
 891                 return array($sql,$stmt,0,$BINDNUM);
 892         }
 893
 894         /*
 895                 Call an oracle stored procedure and returns a cursor variable as a recordset.
 896                 Concept by Robert Tuttle robert@ud.com
 897
 898                 Example:
 899                         Note: we return a cursor variable in :RS2
 900                         $rs = $db->ExecuteCursor("BEGIN adodb.open_tab(:RS2); END;",'RS2');
 901
 902                         $rs = $db->ExecuteCursor(
 903                                 "BEGIN :RS2 = adodb.getdata(:VAR1); END;",
 904                                 'RS2',
 905                                 array('VAR1' => 'Mr Bean'));
 906
 907         */
 908         function ExecuteCursor($sql,$cursorName='rs',$params=false)
 909         {
 910                 if (is_array($sql)) $stmt = $sql;
 911                 else $stmt = ADODB_oci8::Prepare($sql,true); # true to allocate OCINewCursor
 912
 913                 if (is_array($stmt) && sizeof($stmt) >= 5) {
 914                         $hasref = true;
 915                         $ignoreCur = false;
 916                         $this->Parameter($stmt, $ignoreCur, $cursorName, false, -1, OCI_B_CURSOR);
 917                         if ($params) {
 918                                 foreach($params as $k => $v) {
 919                                         $this->Parameter($stmt,$params[$k], $k);
 920                                 }
 921                         }
 922                 } else
 923                         $hasref = false;
 924
 925                 $rs = $this->Execute($stmt);
 926                 if ($rs) {
 927                         if ($rs->databaseType == 'array') OCIFreeCursor($stmt[4]);
 928                         else if ($hasref) $rs->_refcursor = $stmt[4];
 929                 }
 930                 return $rs;
 931         }
 932
 933         /*
 934                 Bind a variable -- very, very fast for executing repeated statements in oracle.
 935                 Better than using
 936                         for ($i = 0; $i < $max; $i++) {
 937                                 $p1 = ?; $p2 = ?; $p3 = ?;
 938                                 $this->Execute("insert into table (col0, col1, col2) values (:0, :1, :2)",
 939                                         array($p1,$p2,$p3));
 940                         }
 941
 942                 Usage:
 943                         $stmt = $DB->Prepare("insert into table (col0, col1, col2) values (:0, :1, :2)");
 944                         $DB->Bind($stmt, $p1);
 945                         $DB->Bind($stmt, $p2);
 946                         $DB->Bind($stmt, $p3);
 947                         for ($i = 0; $i < $max; $i++) {
 948                                 $p1 = ?; $p2 = ?; $p3 = ?;
 949                                 $DB->Execute($stmt);
 950                         }
 951
 952                 Some timings:
 953                         ** Test table has 3 cols, and 1 index. Test to insert 1000 records
 954                         Time 0.6081s (1644.60 inserts/sec) with direct OCIParse/OCIExecute
 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 OCIBindByName,
 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 OCIBindByName($stmt[1],":".$name,$stmt[4],$size,$type);
 970         }
 971
 972                 if ($name == false) {
 973                         if ($type !== false) $rez = OCIBindByName($stmt[1],":".$stmt[2],$var,$size,$type);
 974                         else $rez = OCIBindByName($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'] = OCINewDescriptor($this->_connectionID, oci_lob_desc($type));
 983                         $this->_refLOBs[$numlob]['TYPE'] = $isOutput;
 984
 985                         $tmp = $this->_refLOBs[$numlob]['LOB'];
 986                 $rez = OCIBindByName($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 = OCIBindByName($stmt[1],":".$name,$var,$size,$type);
1008                         else $rez = OCIBindByName($stmt[1],":".$name,$var,$size); // +1 byte for null terminator
1009                 }
1010
1011                 return $rez;
1012         }
1013
1014         function Param($name,$type=false)
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                 See OCIBindByName documentation at php.net.
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 OCIBindByName 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                                                 OCIBindByName($stmt,":$k",$bindarr[$k],is_string($v) && strlen($v)>4000 ? -1 : 4000);
1078                                         }
1079                                         $this->_bind[$bindpos] = $bindarr;
1080                                 }
1081                         }
1082                 } else {
1083                         $stmt=OCIParse($this->_connectionID,$sql);
1084                 }
1085
1086                 $this->_stmt = $stmt;
1087                 if (!$stmt) return false;
1088
1089                 if (defined('ADODB_PREFETCH_ROWS')) @OCISetPrefetch($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                                                 OCIBindByName($stmt,":$k",$inputarr[$k][0],$v[1]);
1096                                         else
1097                                                 OCIBindByName($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 ocibindbyname again
1110                                                 $bindarr[$k] = $v;
1111                                         } else {                                // dynamic sql, so rebind every time
1112                                                 OCIBindByName($stmt,":$k",$inputarr[$k],$len);
1113                                         }
1114                                 }
1115                         }
1116                 }
1117
1118         $this->_errorMsg = false;
1119                 $this->_errorCode = false;
1120                 if (OCIExecute($stmt,$this->_commit)) {
1121 //OCIInternalDebug(1);
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 (@OCIStatementType($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 = OCIExecute($cursor);
1153                                 return $cursor;
1154                                                 }
1155                                                 return $stmt;
1156                     } else {
1157                                                 if (is_resource($stmt)) {
1158                                                         OCIFreeStatement($stmt);
1159                                                         return true;
1160                                                 }
1161                         return $stmt;
1162                     }
1163                     break;
1164                 default :
1165                                         // ociclose -- no because it could be used in a LOB?
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) OCIRollback($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                 OCILogoff($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         // http://gis.mit.edu/classes/11.521/sqlnotes/referential_integrity.html
1251         function MetaForeignKeys($table, $owner=false)
1252         {
1253         global $ADODB_FETCH_MODE;
1254
1255                 $save = $ADODB_FETCH_MODE;
1256                 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
1257                 $table = $this->qstr(strtoupper($table));
1258                 if (!$owner) {
1259                         $owner = $this->user;
1260                         $tabp = 'user_';
1261                 } else
1262                         $tabp = 'all_';
1263
1264                 $owner = ' and owner='.$this->qstr(strtoupper($owner));
1265
1266                 $sql =
1267 "select constraint_name,r_owner,r_constraint_name
1268         from {$tabp}constraints
1269         where constraint_type = 'R' and table_name = $table $owner";
1270
1271                 $constraints = $this->GetArray($sql);
1272                 $arr = false;
1273                 foreach($constraints as $constr) {
1274                         $cons = $this->qstr($constr[0]);
1275                         $rowner = $this->qstr($constr[1]);
1276                         $rcons = $this->qstr($constr[2]);
1277                         $cols = $this->GetArray("select column_name from {$tabp}cons_columns where constraint_name=$cons $owner order by position");
1278                         $tabcol = $this->GetArray("select table_name,column_name from {$tabp}cons_columns where owner=$rowner and constraint_name=$rcons order by position");
1279
1280                         if ($cols && $tabcol)
1281                                 for ($i=0, $max=sizeof($cols); $i < $max; $i++) {
1282                                         $arr[$tabcol[$i][0]] = $cols[$i][0].'='.$tabcol[$i][1];
1283                                 }
1284                 }
1285                 $ADODB_FETCH_MODE = $save;
1286
1287                 return $arr;
1288         }
1289
1290
1291         function CharMax()
1292         {
1293                 return 4000;
1294         }
1295
1296         function TextMax()
1297         {
1298                 return 4000;
1299         }
1300
1301         /**
1302          * Quotes a string.
1303          * An example is  $db->qstr("Don't bother",magic_quotes_runtime());
1304          *
1305          * @param s                     the string to quote
1306          * @param [magic_quotes]        if $s is GET/POST var, set to get_magic_quotes_gpc().
1307          *                              This undoes the stupidity of magic quotes for GPC.
1308          *
1309          * @return  quoted string to be sent back to database
1310          */
1311         function qstr($s,$magic_quotes=false)
1312         {
1313                 //$nofixquotes=false;
1314
1315                 if ($this->noNullStrings && strlen($s)==0)$s = ' ';
1316                 if (!$magic_quotes) {
1317                         if ($this->replaceQuote[0] == '\\'){
1318                                 $s = str_replace('\\','\\\\',$s);
1319                         }
1320                         return  "'".str_replace("'",$this->replaceQuote,$s)."'";
1321                 }
1322
1323                 // undo magic quotes for " unless sybase is on
1324                 if (!ini_get('magic_quotes_sybase')) {
1325                         $s = str_replace('\\"','"',$s);
1326                         $s = str_replace('\\\\','\\',$s);
1327                         return "'".str_replace("\\'",$this->replaceQuote,$s)."'";
1328                 } else {
1329                         return "'".$s."'";
1330                 }
1331         }
1332
1333 }
1334
1335 /*--------------------------------------------------------------------------------------
1336                  Class Name: Recordset
1337 --------------------------------------------------------------------------------------*/
1338
1339 class ADORecordset_oci8 extends ADORecordSet {
1340
1341         var $databaseType = 'oci8';
1342         var $bind=false;
1343         var $_fieldobjs;
1344
1345         //var $_arr = false;
1346
1347         function ADORecordset_oci8($queryID,$mode=false)
1348         {
1349                 if ($mode === false) {
1350                         global $ADODB_FETCH_MODE;
1351                         $mode = $ADODB_FETCH_MODE;
1352                 }
1353                 switch ($mode)
1354                 {
1355                 case ADODB_FETCH_ASSOC:$this->fetchMode = OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break;
1356                 case ADODB_FETCH_DEFAULT:
1357                 case ADODB_FETCH_BOTH:$this->fetchMode = OCI_NUM+OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break;
1358                 case ADODB_FETCH_NUM:
1359                 default:
1360                 $this->fetchMode = OCI_NUM+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break;
1361                 }
1362
1363                 $this->adodbFetchMode = $mode;
1364                 $this->_queryID = $queryID;
1365         }
1366
1367
1368         function Init()
1369         {
1370                 if ($this->_inited) return;
1371
1372                 $this->_inited = true;
1373                 if ($this->_queryID) {
1374
1375                         $this->_currentRow = 0;
1376                         @$this->_initrs();
1377                         $this->EOF = !$this->_fetch();
1378
1379                         /*
1380                         // based on idea by Gaetano Giunta to detect unusual oracle errors
1381                         // see http://phplens.com/lens/lensforum/msgs.php?id=6771
1382                         $err = OCIError($this->_queryID);
1383                         if ($err && $this->connection->debug) ADOConnection::outp($err);
1384                         */
1385
1386                         if (!is_array($this->fields)) {
1387                                 $this->_numOfRows = 0;
1388                                 $this->fields = array();
1389                         }
1390                 } else {
1391                         $this->fields = array();
1392                         $this->_numOfRows = 0;
1393                         $this->_numOfFields = 0;
1394                         $this->EOF = true;
1395                 }
1396         }
1397
1398         function _initrs()
1399         {
1400                 $this->_numOfRows = -1;
1401                 $this->_numOfFields = OCInumcols($this->_queryID);
1402                 if ($this->_numOfFields>0) {
1403                         $this->_fieldobjs = array();
1404                         $max = $this->_numOfFields;
1405                         for ($i=0;$i<$max; $i++) $this->_fieldobjs[] = $this->_FetchField($i);
1406                 }
1407         }
1408
1409           /*            Returns: an object containing field information.
1410                           Get column information in the Recordset object. fetchField() can be used in order to obtain information about
1411                           fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by
1412                           fetchField() is retrieved.            */
1413
1414         function _FetchField($fieldOffset = -1)
1415         {
1416                 $fld = new ADOFieldObject;
1417                 $fieldOffset += 1;
1418                 $fld->name =OCIcolumnname($this->_queryID, $fieldOffset);
1419                 $fld->type = OCIcolumntype($this->_queryID, $fieldOffset);
1420                 $fld->max_length = OCIcolumnsize($this->_queryID, $fieldOffset);
1421                 switch($fld->type) {
1422                 case 'NUMBER':
1423                         $p = OCIColumnPrecision($this->_queryID, $fieldOffset);
1424                         $sc = OCIColumnScale($this->_queryID, $fieldOffset);
1425                         if ($p != 0 && $sc == 0) $fld->type = 'INT';
1426                         $fld->scale = $p;
1427                         break;
1428
1429                 case 'CLOB':
1430                 case 'NCLOB':
1431                 case 'BLOB':
1432                         $fld->max_length = -1;
1433                         break;
1434                 }
1435                 return $fld;
1436         }
1437
1438         /* For some reason, OCIcolumnname fails when called after _initrs() so we cache it */
1439         function FetchField($fieldOffset = -1)
1440         {
1441                 return $this->_fieldobjs[$fieldOffset];
1442         }
1443
1444
1445         /*
1446         // 10% speedup to move MoveNext to child class
1447         function _MoveNext()
1448         {
1449         //global $ADODB_EXTENSION;if ($ADODB_EXTENSION) return @adodb_movenext($this);
1450
1451                 if ($this->EOF) return false;
1452
1453                 $this->_currentRow++;
1454                 if(@OCIfetchinto($this->_queryID,$this->fields,$this->fetchMode))
1455                         return true;
1456                 $this->EOF = true;
1457
1458                 return false;
1459         }       */
1460
1461
1462         function MoveNext()
1463         {
1464                 if (@OCIfetchinto($this->_queryID,$this->fields,$this->fetchMode)) {
1465                         $this->_currentRow += 1;
1466                         return true;
1467                 }
1468                 if (!$this->EOF) {
1469                         $this->_currentRow += 1;
1470                         $this->EOF = true;
1471                 }
1472                 return false;
1473         }
1474
1475         /*
1476         # does not work as first record is retrieved in _initrs(), so is not included in GetArray()
1477         function GetArray($nRows = -1)
1478         {
1479         global $ADODB_OCI8_GETARRAY;
1480
1481                 if (true ||  !empty($ADODB_OCI8_GETARRAY)) {
1482                         # does not support $ADODB_ANSI_PADDING_OFF
1483
1484                         //OCI_RETURN_NULLS and OCI_RETURN_LOBS is set by OCIfetchstatement
1485                         switch($this->adodbFetchMode) {
1486                         case ADODB_FETCH_NUM:
1487
1488                                 $ncols = @OCIfetchstatement($this->_queryID, $results, 0, $nRows, OCI_FETCHSTATEMENT_BY_ROW+OCI_NUM);
1489                                 $results = array_merge(array($this->fields),$results);
1490                                 return $results;
1491
1492                         case ADODB_FETCH_ASSOC:
1493                                 if (ADODB_ASSOC_CASE != 2 || $this->databaseType != 'oci8') break;
1494
1495                                 $ncols = @OCIfetchstatement($this->_queryID, $assoc, 0, $nRows, OCI_FETCHSTATEMENT_BY_ROW);
1496                                 $results = array_merge(array($this->fields),$assoc);
1497                                 return $results;
1498
1499                         default:
1500                                 break;
1501                         }
1502                 }
1503
1504                 $results = ADORecordSet::GetArray($nRows);
1505                 return $results;
1506
1507         } */
1508
1509         /* Optimize SelectLimit() by using OCIFetch() instead of OCIFetchInto() */
1510         function GetArrayLimit($nrows,$offset=-1)
1511         {
1512                 if ($offset <= 0) {
1513                         $arr = $this->GetArray($nrows);
1514                         return $arr;
1515                 }
1516                 $arr = array();
1517                 for ($i=1; $i < $offset; $i++)
1518                         if (!@OCIFetch($this->_queryID)) return $arr;
1519
1520                 if (!@OCIfetchinto($this->_queryID,$this->fields,$this->fetchMode)) return $arr;;
1521                 $results = array();
1522                 $cnt = 0;
1523                 while (!$this->EOF && $nrows != $cnt) {
1524                         $results[$cnt++] = $this->fields;
1525                         $this->MoveNext();
1526                 }
1527
1528                 return $results;
1529         }
1530
1531
1532         /* Use associative array to get fields array */
1533         function Fields($colname)
1534         {
1535                 if (!$this->bind) {
1536                         $this->bind = array();
1537                         for ($i=0; $i < $this->_numOfFields; $i++) {
1538                                 $o = $this->FetchField($i);
1539                                 $this->bind[strtoupper($o->name)] = $i;
1540                         }
1541                 }
1542
1543                  return $this->fields[$this->bind[strtoupper($colname)]];
1544         }
1545
1546
1547
1548         function _seek($row)
1549         {
1550                 return false;
1551         }
1552
1553         function _fetch()
1554         {
1555                 return @OCIfetchinto($this->_queryID,$this->fields,$this->fetchMode);
1556         }
1557
1558         /*              close() only needs to be called if you are worried about using too much memory while your script
1559                         is running. All associated result memory for the specified result identifier will automatically be freed.               */
1560
1561         function _close()
1562         {
1563                 if ($this->connection->_stmt === $this->_queryID) $this->connection->_stmt = false;
1564                 if (!empty($this->_refcursor)) {
1565                         OCIFreeCursor($this->_refcursor);
1566                         $this->_refcursor = false;
1567                 }
1568                 @OCIFreeStatement($this->_queryID);
1569                 $this->_queryID = false;
1570
1571         }
1572
1573         function MetaType($t,$len=-1)
1574         {
1575                 if (is_object($t)) {
1576                         $fieldobj = $t;
1577                         $t = $fieldobj->type;
1578                         $len = $fieldobj->max_length;
1579                 }
1580                 switch (strtoupper($t)) {
1581                 case 'VARCHAR':
1582                 case 'VARCHAR2':
1583                 case 'CHAR':
1584                 case 'VARBINARY':
1585                 case 'BINARY':
1586                 case 'NCHAR':
1587                 case 'NVARCHAR':
1588                 case 'NVARCHAR2':
1589                                  if ($len <= $this->blobSize) return 'C';
1590
1591                 case 'NCLOB':
1592                 case 'LONG':
1593                 case 'LONG VARCHAR':
1594                 case 'CLOB':
1595                 return 'X';
1596
1597                 case 'LONG RAW':
1598                 case 'LONG VARBINARY':
1599                 case 'BLOB':
1600                         return 'B';
1601
1602                 case 'DATE':
1603                         return  ($this->connection->datetime) ? 'T' : 'D';
1604
1605
1606                 case 'TIMESTAMP': return 'T';
1607
1608                 case 'INT':
1609                 case 'SMALLINT':
1610                 case 'INTEGER':
1611                         return 'I';
1612
1613                 default: return 'N';
1614                 }
1615         }
1616 }
1617
1618 class ADORecordSet_ext_oci8 extends ADORecordSet_oci8 {
1619         function ADORecordSet_ext_oci8($queryID,$mode=false)
1620         {
1621                 if ($mode === false) {
1622                         global $ADODB_FETCH_MODE;
1623                         $mode = $ADODB_FETCH_MODE;
1624                 }
1625                 switch ($mode)
1626                 {
1627                 case ADODB_FETCH_ASSOC:$this->fetchMode = OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break;
1628                 case ADODB_FETCH_DEFAULT:
1629                 case ADODB_FETCH_BOTH:$this->fetchMode = OCI_NUM+OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break;
1630                 case ADODB_FETCH_NUM:
1631                 default: $this->fetchMode = OCI_NUM+OCI_RETURN_NULLS+OCI_RETURN_LOBS; break;
1632                 }
1633                 $this->adodbFetchMode = $mode;
1634                 $this->_queryID = $queryID;
1635         }
1636
1637         function MoveNext()
1638         {
1639                 return adodb_movenext($this);
1640         }
1641 }
1642 ?>