Comprehensive support for working with Oracle databases is available in PHP's OCI8 library. This library is included with DataLoad Scripting and all 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 Scripting and this is built on top of OCI8. This class simplifies access to Oracle and supports the most commonly required Oracle functionality. 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.
The class name is "Oracle" and an object is created based on this class using the following syntax. The object name can be anything the developer wishes to use and need not be $oraconn.
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:
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:
The "server" may either be a hostname or IP address. Example EZConnect strings are shown below:
$oraconn=new Oracle();
//Prompt the user for the connection details and update $OraDBConn
$OraDBConn=GetDBConn();
//Reset & show console
SetConsoleData(0, 0, 0, 0, 0);
ShowConsole();
SendMsg("Attempting to connect to Oracle...", INFO);
//Populate $connstr with the connection details
if($OraDBConn->usetns)
$connstr=$OraDBConn->alias;
else
$connstr=$OraDBConn->host . ":" . $OraDBConn->port . "/" . $OraDBConn->sid;
//Connect to Oracle. Change cursor to an hourglass while we are connecting.
SetHourglass(true);
$result=$oraconn->Connect($OraDBConn->username, $OraDBConn->password, $connstr);
SetHourglass(false);
if(!$result)
{
MessageBox("Failed to connect to database.");
return;
}
SendMsg("Connected.", INFO);
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 variables and functions is shown below.
Oracle Class Variables |
$username |
Oracle username used for current connection |
Description
Holds the last username used when connecting to the Oracle database, regardless of whether that connection was successful.
|
$tnsconn |
Oracle connection string, which may be a TNS alias or EZConnect string |
Description
Holds the connection string used when connecting to the Oracle database, regardless of whether that connection was successful. May be a TNS alias or a string in EZConnect format. |
$charset |
Oracle client character set |
Description
Character set specifying the encoding of data handled by the Oracle class. Data retrieved from the database will be in this encoding and data sent to Oracle must also be in this encoding. This character set specifies the encoding required for the client, i.e. the PHP script, and may be different to that used on the database server although it must be able to handle all encodings supported by the database character set. The default client character set is UTF8 and this will be used if no other character set is specified. It is recommended that UTF8 is used because DataLoad exchanges data using UTF8 encoding and PHP is setup to seamlessly handle UTF8 strings. |
$connected |
Boolean to indicate whether there is an existing connection to an Oracle database |
Description
$connected is true if there is an existing connection to an Oracle database, otherwise it is false. |
$prefetch |
Controls whether all SELECT'ed rows are automatically loaded in to an array |
Description
$prefetch is a boolean variable that controls whether all rows SELECT'ed in RunSQL are automatically loaded in to an array. If $prefetch is true then the $results array is populated with all rows. If false then the developer must use code from the PHP OCI8 library to fetch rows using the statement handler returned when the SELECT statement was parsed. By default $prefetch is true. |
$rowcount |
Number of rows affected by the last SQL statement executed |
Description
$rowcount holds the number of rows affected by the last SQL statement, which for SELECT statements is the number of rows returned and for INSERT, DELETE & UPDATE, etc, is the number of rows inserted, deleted and updated respectively. $rowcount is only updated on a SELECT statement where $prefetch is true. |
$results |
Array holding all rows returned by a SELECT statement or LoadDbmsBuffer |
Description
When a SELECT statement is successfully executed and $prefetch is true the $results array is populated with all the rows returned. This array is also populated when LoadDbmsOutput is used to retrieve data from the DBMS_OUTPUT buffer. |
$lasterror |
Array containing details of last error returned by Oracle |
Description
$lasterror is an array holding the details of the last error returned by Oracle. If no errors have occurred then $lasterror will be false. When an error occurs $lasterror will hold an associative array. In this array, code consists the oracle error code and message the oracle error string.
|
$handle |
Oracle connection identifier |
Description
$handle holds the OCI8 connection identifier for the current Oracle database connection. $handle can be used where direct access to OCI8 functionality is required. |
Oracle Class Functions |
Oracle |
Oracle class constructor |
Syntax
function __construct()
Example
$oraconn=new Oracle(); #Create Oracle object and assign to $oraconn
$oraconn=new Oracle("orasvr.1521/orafin", "apps", "appspw"); #Create object & connect
$oraconn=new Oracle($OraDBConn); #Create object & connect using information in OraDBConnClass object
Description
Call the Oracle constructor to create an object based on the Oracle class. Connection details may, optionally, be supplied as parameters. If these are provided the class will try to create a new connection to the database. If connection details are provided as parameters then either 1, 2, 3 or 4 parameters may be used. The syntax is as follows:
1 - Connect(Connection_String);
1 - Connect($OraDBConn); //OraDBConnClass object used as the parameter
2 - Connect(Username, Password);
3 - Connect(Username, Password, Connection_String);
4 - Connect(Username, Password, Connection_String, Character Set);
The Connection_String parameter value may be a tnsnames alias or EZConnect string (host:port/service_name).
If no parameters are provided but connection details have been provided in this session then these will be re-used.
If no connection details are available then the object is created but does not attempt to connect to the database. The Connect() function may then be used to establish a connection.
An object of class OraDBConn is returned by the GetDBConn() function. |
Connect |
Create a new Oracle database connection |
Syntax
function Connect()
Example
$oraconn->Connect("apps", "apps", "oralive"); #Create a new database connection
$oraconn->Connect("gl","gl"); #Reconnect to existing database as user "gl"
$oraconn->Connect(); #Reconnect to the existing database using the same credentials
$oraconn->Connect($OraDBConn); #Connect using information in OraDBConnClass object
Description
Call the Connect function to make a new Oracle database connection. The function can take between 0 and 5 parameters. The syntax when between 1 and 5 parameters are provided is:
1 - Connect(Connection_String); //String rather than object parameters used
1 - Connect($OraDBConn); //OraDBConnClass object used as the parameter
2 - Connect(Username, Password);
3 - Connect(Username, Password, Connection_String);
4 - Connect(Username, Password, Connection_String, CharacterSet);
5 - Connect(Username, Password, Connection_String, CharacterSet, New_Connection);
The Connection_String parameter value may be a tnsnames alias or EZConnect string (host:port/service_name).
The Oracle class must have a connection string, username and password before a connection can be made. If Connect() is called without specifying these values then either the last used values will be used for the missing parameters or the connection variables (see above) must have been previously set. For example, if a database connection has been made and Connect() is called again with just the username and password as parameters then the class will reconnect to the same database. The character set will always be UTF8 unless an alternative character set is specified. An object of class OraDBConn may be provided as a single parameter and this must contain all required connection details. An object of class OraDBConn is returned by the GetDBConn() function.
By default, new connections are always created and cached connections are not re-used. This behaviour may be controlled using the New_Connection parameter where 5 parameters are supplied. TRUE forces a new connection (the default) while FALSE allows the re-use of previous, cached connections. A new connection is normally wanted to ensure transactional isolation.
Connect returns true if a connection was successfully established and false in all other cases. Where the database connection fails the $lasterror variable is populated with the error details. |
ConnectAsApps |
Validates an Oracle E-Business Suite username/password and, if successful, connects to the database as the APPS user |
Syntax
function ConnectAsApps($OraDBConn)
Example
$oraconn->ConnectAsApps($OraDBConn);
Description
Call the ConnectAsApps function to validate the supplied Oracle E-Business Suite username/password, decrypt the APPS password and, if successful, make a new Oracle database connection as APPS. The function takes 1 parameter which is an object of class OraDBConnClass. That class provides variables to store all the Oracle and Oracle E-Business Suite details required to make a new connection. An objects of class OraDBConnClass is return by function GetDBConn(). The OraDBConnClass object must be fully populated, including the Oracle connection details (tnsnames alias or server, SID & port), an Oracle E-Business Suite username and password, and the Oracle E-Business Suite gateway username (typically "applysyspub"), password and the server security ID.
ConnectAsApps returns true if a connection was successfully established and false in all other cases. Where the function fails the $lasterror variable is populated with the error details.
Install Note
To use this function a number of Java files must be copied from the Oracle E-Business Suite server to the DataLoad install on the PC. These files are required to validate the Oracle E-Business Suite user's password and obtain the APPS password. The following files should be copied from the server to sub-directories under the DataLoad install. The file DatabaseClass.class may not exist on the server and in that case it is not required and can be ignored.
Oracle E-Business Server Source File |
DataLoad Sub-Directory Target |
$OA_JAVA/oracle/apps/fnd/security/AolSecurityPrivate.class |
[DataLoad]\PHP\Java\oracle\apps\fnd\security\AolSecurityPrivate.class |
$OA_JAVA/oracle/apps/fnd/common/VersionInfo.class |
[DataLoad]\PHP\Java\oracle\apps\fnd\common\VersionInfo.class |
$OA_JAVA/oracle/apps/fnd/metadata\DatabaseClass.class |
[DataLoad]\PHP\Java\oracle\apps\fnd\metadata\DatabaseClass.class |
|
Disconnect |
Close an Oracle database connection |
Syntax
function Disconnect()
Example
$oraconn->Disconnect();
Description
Call the Disconnect function to close an Oracle database connection. This function may be called regardless of whether there is an existing connection. The database connection is automatically closed when the Oracle object is destroyed however it is good practice to use the Disconnect function to explicitly close a connection.
|
RunSQL |
Run a SQL statement or PL/SQL |
Syntax
function RunSQL($l_sql, &$l_parse=NULL, &$l_bindvars=array())
Example
$oraconn->RunSQL("select fnd_profile.value('SIGNON_PASSWORD_LENGTH') from dual");
Please see the example loads delivered with DataLoad for more RunSQL examples.
Description
Call the RunSQL function to execute the SQL statement or PL/SQL in the $l_sql parameter. The function returns a value depending on the SQL executed:
- The function returns FALSE if the SQL errors.
- For SELECT, INSERT, DELETE & UPDATE statements, RunSQL returns the number of rows fetched or affected. If SELECT select returns 0 rows or prefetch==false then 0 is returned which can evaluate to FALSE in PHP. If the distinction between 0 and FALSE is important then use === or !=== to evaluate the return value.
- For all other SQL statements & PL/SQL sucessfully executed, RunSQL returns TRUE.
RunSQL also includes two optional parameters.
$l_parse is a parsed statement ID. When RunSQL is first called the variable used for $l_parse must be NULL. This indicates that the statement must be parsed by RunSQL. The variable will be updated with the parse ID so this can be re-used without re-parsing the statement. If an existing parse ID is provided via $l_parse we do not re-parse $l_sql. When $l_parse is used RunSQL will not close the SQL statement, enabling the developer to execute the statement again using the existing parse ID. This means the statement must be freed with OCI_Free_Statement() when it is no longer required, something that happens automatically when $l_parse is not used.
Using $l_parse is useful when the same SQL is called many times with only the values changing, so we can use bind variables, avoid re-parsing and thus maximise performance. $l_parse should also be used when $prefetch is false. In that case the developer should use the parse ID returned by $l_parse to get the SELECT'ed data using the OCI8 fetch functions.
$l_bindvars is an optional array of bind variables where each key is a bind variable name and the key value is the value to be bound. This may be used along with $l_parse to re-execute statements where just the values change and hence avoiding the overhead of re-parsing these statements. Following Oracle's convention, the bind variable name must start with ":" (a colon). This also means the array may contain values that won't be bound to Oracle and these are ignored.
|
LoadDBMSOutput |
Retrieve all text from the DBMS_OUTPUT buffer |
Syntax
function LoadDbmsOutput()
Example
//Turn on DBMS_OUTPUT and set buffer to maximum size for Oracle pre-10gR2.
if($oraconn->RunSQL("BEGIN DBMS_OUTPUT.ENABLE(1000000); END;"))
if($oraconn->RunSQL($plsql)) //Run some PL/SQL
if($oraconn->LoadDbmsOutput()) //Load the DBMS_OUTPUT buffer into $results array
foreach($oraconn->results as $record) //Loop through the results array
SendMsg($record);
Description
Call LoadDbmsOutput to retrieve all data loaded in to the DBMS_OUTPUT buffer. This is useful when information must be sent from PL/SQL and then used within the PHP script. LoadDbmsOutput returns true if data was retrieved from DBMS_OUTPUT and false in all other cases.
If data is available in the buffer, LoadDbmsOutput loads the lines in to the $results array and updates $rowcount with the number of lines added to this array. |
GetVersion |
Get the full version information for the Oracle database |
Syntax
function GetVersion()
Example
$versionbanner=$oraconn->GetVersion();
Description
Call GetVersion to get the full database version banner for the current database connection. The function will return the text banner, which will be in the following format:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
|
GetDBVersion |
Get the database version string |
Syntax
function GetDBVersion()
Example
$version=$oraconn->GetDBVersion();
Description
GetDBVersion returns a string containing the database version for the current database connection.
|