DataLoad
Overview
Creating Scripts
Event Handlers
How to...?
PHP Functions
Oracle Library
Character Encoding

User Guide Home
DataLoad Home

Oracle Database Class

Comprehensive support for working with Oracle databases is available in the OCI8 library included with PHP. This library is also provided as part of DataLoad Scripting and all of the OCI8 functionality may be used in DataLoad PHP scripts without any further configuration. A simple class for using Oracle databases is also provided with DataLoad and this is built on top of OCI8. This class supports the most commonly required Oracle functionality and because it is written using OCI8 the underlying OCI8 functions and variables may still be used whenever required.

To use the DataLoad Oracle class the class definition must be included in the PHP script as shown below. The standard DataLoad PHP template already includes this although it is commented out, so the comment prefix (\\) must be removed where this template is used.

require 'DLOraLib.php';

The class name is "Oracle" and an object is created based on this class using the following syntax. Of course, the object name can be anything the developer wishes to use and need not be $oraconn.

$oraconn=new Oracle();

The Oracle database connection details, i.e. the connection string, username, password and character set may optionally be supplied to the Oracle constructor so that a database connection is also initiated when the object is created. This usage takes the following form:

$oraconn=new Oracle(connection_string, username, password[, charset]);

The connection_string can either be a TNS alias, i.e. defined in a tnsnames.ora file, or an Oracle EZConnect string. EZConnect provides a simple way to connect to Oracle databases that does not require a tnsnames.ora file. The EZConnect string must be in the following format:

server.port/SID

The "server" may either be a hostname or IP address. Example EZConnect strings are shown below:

orasvr.1521/live
10.156.18.29.1521/oradev

Example PHP code that demonstrates connecting to an Oracle database can be seen here.

If the Oracle connection is not established via the Oracle class constructor, the Connect function should be used (see below). Having connected to an Oracle database the other Oracle class functions may be used to work with the database. Finally the Disconnect function is used to close the connection. The full range of Oracle class functions and variables is shown below.

Function
Description
   
Oracle Class Variables
$username Oracle username used for current connection
$tnsconn Oracle connection string, which may be a TNS alias or EZConnect string
$charset Oracle client character set
$connected Boolean to indicate whether there is an existing connection to an Oracle database
$prefetch Controls whether all SELECT'ed rows are automatically loaded in to an array
$rowcount Number of rows affected by the last SQL statement executed
$results Array holding all rows returned by a SELECT statement or LoadDbmsBuffer
$lasterror Array containing details of last error returned by Oracle
$handle Oracle connection identifier
Oracle Class Functions
Oracle Oracle class constructor
Connect Create a new Oracle database connection
ConnectAsApps Validates an Oracle E-Business Suite username/password and, if successful, connects to the database as the APPS user
Disconnect Close an Oracle database connection
RunSQL Run a SQL statement or PL/SQL
LoadDBMSOutput Retrieve all text from the DBMS_OUTPUT buffer
GetVersion Get the full version information for the Oracle database
GetDBVersion Get the database version string