Oracle Database Class

Description

Holds the last username used when connecting to the Oracle database, regardless of whether that connection was successful.

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.

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.

Description

$connected is true if there is an existing connection to an Oracle database, otherwise it is false.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

Syntax

function GetDBVersion()

Example

$version=$oraconn->GetDBVersion();

Description

GetDBVersion returns a string containing the database version for the current database connection.