3 V5.14 8 Sept 2011 (c) 2000-2011 John Lim (jlim#natsoft.com). All rights reserved.
4 Released under both BSD license and Lesser GPL library license.
5 Whenever there is any discrepancy between the two licenses,
6 the BSD license will take precedence.
8 Latest version is available at http://adodb.sourceforge.net
10 Oracle data driver. Requires Oracle client. Works on Windows and Unix and Oracle 7.
12 If you are using Oracle 8 or later, use the oci8 driver which is much better and more reliable.
15 // security - hide paths
16 if (!defined('ADODB_DIR')) die();
18 class ADODB_oracle
extends ADOConnection
{
19 var $databaseType = "oracle";
20 var $replaceQuote = "''"; // string to use to replace quotes
21 var $concat_operator='||';
23 var $_initdate = true; // init date to YYYY-MM-DD
24 var $metaTablesSQL = 'select table_name from cat';
25 var $metaColumnsSQL = "select cname,coltype,width from col where tname='%s' order by colno";
26 var $sysDate = "TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')";
27 var $sysTimeStamp = 'SYSDATE';
28 var $connectSID = true;
30 function ADODB_oracle()
34 // format and return date string in database date format
37 if (is_string($d)) $d = ADORecordSet
::UnixDate($d);
38 if (is_object($d)) $ds = $d->format($this->fmtDate
);
39 else $ds = adodb_date($this->fmtDate
,$d);
40 return 'TO_DATE('.$ds.",'YYYY-MM-DD')";
43 // format and return date string in database timestamp format
44 function DBTimeStamp($ts)
47 if (is_string($ts)) $ts = ADORecordSet
::UnixTimeStamp($ts);
48 if (is_object($ts)) $ds = $ts->format($this->fmtDate
);
49 else $ds = adodb_date($this->fmtTimeStamp
,$ts);
50 return 'TO_DATE('.$ds.",'RRRR-MM-DD, HH:MI:SS AM')";
56 $d = ADOConnection
::DBDate($d);
57 if (strncmp($d,"'",1)) return $d;
59 return substr($d,1,strlen($d)-2);
62 function BindTimeStamp($d)
64 $d = ADOConnection
::DBTimeStamp($d);
65 if (strncmp($d,"'",1)) return $d;
67 return substr($d,1,strlen($d)-2);
74 $this->autoCommit
= false;
75 ora_commitoff($this->_connectionID
);
80 function CommitTrans($ok=true)
82 if (!$ok) return $this->RollbackTrans();
83 $ret = ora_commit($this->_connectionID
);
84 ora_commiton($this->_connectionID
);
89 function RollbackTrans()
91 $ret = ora_rollback($this->_connectionID
);
92 ora_commiton($this->_connectionID
);
97 /* there seems to be a bug in the oracle extension -- always returns ORA-00000 - no error */
100 if ($this->_errorMsg
!== false) return $this->_errorMsg
;
102 if (is_resource($this->_curs
)) $this->_errorMsg
= @ora_error
($this->_curs
);
103 if (empty($this->_errorMsg
)) $this->_errorMsg
= @ora_error
($this->_connectionID
);
104 return $this->_errorMsg
;
110 if ($this->_errorCode
!== false) return $this->_errorCode
;
112 if (is_resource($this->_curs
)) $this->_errorCode
= @ora_errorcode
($this->_curs
);
113 if (empty($this->_errorCode
)) $this->_errorCode
= @ora_errorcode
($this->_connectionID
);
114 return $this->_errorCode
;
119 // returns true or false
120 function _connect($argHostname, $argUsername, $argPassword, $argDatabasename, $mode=0)
122 if (!function_exists('ora_plogon')) return null;
124 // <G. Giunta 2003/03/03/> Reset error messages before connecting
125 $this->_errorMsg
= false;
126 $this->_errorCode
= false;
128 // G. Giunta 2003/08/13 - This looks danegrously suspicious: why should we want to set
129 // the oracle home to the host name of remote DB?
130 // if ($argHostname) putenv("ORACLE_HOME=$argHostname");
132 if($argHostname) { // code copied from version submitted for oci8 by Jorma Tuomainen <jorma.tuomainen@ppoy.fi>
133 if (empty($argDatabasename)) $argDatabasename = $argHostname;
135 if(strpos($argHostname,":")) {
136 $argHostinfo=explode(":",$argHostname);
137 $argHostname=$argHostinfo[0];
138 $argHostport=$argHostinfo[1];
144 if ($this->connectSID
) {
145 $argDatabasename="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=".$argHostname
146 .")(PORT=$argHostport))(CONNECT_DATA=(SID=$argDatabasename)))";
148 $argDatabasename="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=".$argHostname
149 .")(PORT=$argHostport))(CONNECT_DATA=(SERVICE_NAME=$argDatabasename)))";
154 if ($argDatabasename) $argUsername .= "@$argDatabasename";
156 //if ($argHostname) print "<p>Connect: 1st argument should be left blank for $this->databaseType</p>";
158 $this->_connectionID
= ora_plogon($argUsername,$argPassword);
160 $this->_connectionID
= ora_logon($argUsername,$argPassword);
161 if ($this->_connectionID
=== false) return false;
162 if ($this->autoCommit
) ora_commiton($this->_connectionID
);
163 if ($this->_initdate
) {
164 $rs = $this->_query("ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD'");
165 if ($rs) ora_close($rs);
172 // returns true or false
173 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
175 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename, 1);
179 // returns query ID if successful, otherwise false
180 function _query($sql,$inputarr=false)
182 // <G. Giunta 2003/03/03/> Reset error messages before executing
183 $this->_errorMsg
= false;
184 $this->_errorCode
= false;
186 $curs = ora_open($this->_connectionID
);
188 if ($curs === false) return false;
189 $this->_curs
= $curs;
190 if (!ora_parse($curs,$sql)) return false;
191 if (ora_exec($curs)) return $curs;
192 // <G. Giunta 2004/03/03> before we close the cursor, we have to store the error message
193 // that we can obtain ONLY from the cursor (and not from the connection)
194 $this->_errorCode
= @ora_errorcode
($curs);
195 $this->_errorMsg
= @ora_error
($curs);
196 // </G. Giunta 2004/03/03>
202 // returns true or false
205 return @ora_logoff
($this->_connectionID
);
213 /*--------------------------------------------------------------------------------------
214 Class Name: Recordset
215 --------------------------------------------------------------------------------------*/
217 class ADORecordset_oracle
extends ADORecordSet
{
219 var $databaseType = "oracle";
222 function ADORecordset_oracle($queryID,$mode=false)
225 if ($mode === false) {
226 global $ADODB_FETCH_MODE;
227 $mode = $ADODB_FETCH_MODE;
229 $this->fetchMode
= $mode;
231 $this->_queryID
= $queryID;
233 $this->_inited
= true;
234 $this->fields
= array();
236 $this->_currentRow
= 0;
237 $this->EOF
= !$this->_fetch();
240 $this->_numOfRows
= 0;
241 $this->_numOfFields
= 0;
245 return $this->_queryID
;
250 /* Returns: an object containing field information.
251 Get column information in the Recordset object. fetchField() can be used in order to obtain information about
252 fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by
253 fetchField() is retrieved. */
255 function FetchField($fieldOffset = -1)
257 $fld = new ADOFieldObject
;
258 $fld->name
= ora_columnname($this->_queryID
, $fieldOffset);
259 $fld->type
= ora_columntype($this->_queryID
, $fieldOffset);
260 $fld->max_length
= ora_columnsize($this->_queryID
, $fieldOffset);
264 /* Use associative array to get fields array */
265 function Fields($colname)
268 $this->bind
= array();
269 for ($i=0; $i < $this->_numOfFields
; $i++
) {
270 $o = $this->FetchField($i);
271 $this->bind
[strtoupper($o->name
)] = $i;
275 return $this->fields
[$this->bind
[strtoupper($colname)]];
280 $this->_numOfRows
= -1;
281 $this->_numOfFields
= @ora_numcols
($this->_queryID
);
290 function _fetch($ignore_fields=false) {
291 // should remove call by reference, but ora_fetch_into requires it in 4.0.3pl1
292 if ($this->fetchMode
& ADODB_FETCH_ASSOC
)
293 return @ora_fetch_into
($this->_queryID
,$this->fields
,ORA_FETCHINTO_NULLS|ORA_FETCHINTO_ASSOC
);
295 return @ora_fetch_into
($this->_queryID
,$this->fields
,ORA_FETCHINTO_NULLS
);
298 /* close() only needs to be called if you are worried about using too much memory while your script
299 is running. All associated result memory for the specified result identifier will automatically be freed. */
303 return @ora_close
($this->_queryID
);
306 function MetaType($t,$len=-1)
310 $t = $fieldobj->type
;
311 $len = $fieldobj->max_length
;
314 switch (strtoupper($t)) {
320 if ($len <= $this->blobSize
) return 'C';
326 case 'LONG VARBINARY':
330 case 'DATE': return 'D';
332 //case 'T': return 'T';
334 case 'BIT': return 'L';
337 case 'INTEGER': return 'I';