# Oracle PHP

Oracle is one of the most popular relational databases in the world. It is an industrialstrength engine preferred by large corporations using databases of exceeding complexity. Oracle database administrators are scarce and command high salaries.

PHP supports two generations of Oracle libraries, Version 7 and Version 8. The functions that use Oracle 7 begin with ora_, such as ora_logon. The functions that work with Oracle 8 begin with oci, such as ocilogon. The Oracle 8 library supports connecting to older Oracle databases. I've included descriptions of the older functions because it's possible you're in the situation of not having access to the newer libraries. Aside from compiling Oracle support into PHP, you may also load an extension using the dl function.

The Oracle 7 functions require two environment variables to be set: ORACLE_HOME and ORACLE_SID. They are most likely not set for your Web server, so you must use the putenv function to set them. You will notice code to accomplish this in the examples below. Thies Arntzen, Stig Bakken, Mitch Golden, Andreas Karajannis, and Rasmus Lerdorf contributed to the Oracle 7 extension. Oracle 8 support was added to PHP by Stig Bakken and Thies Arntzen. Llists constants created by the Oracle 8 Extension. When Oracle is installed, it creates a test user. The login is scott and the password is tiger. I'll take advantage of this in the examples below.

boolean ocibindbyname (integer statement, string placeholder, reference variable, integer length, integer type)
The ocibindbyname function binds an Oracle placeholder to a PHP variable. You must supply a valid statement identifier as created by ociparse, the name of the placeholder, a reference to a PHP variable, and the maximum length of the bind data. You may use a value of -1 to use the length of the variable passed as the variable argument. The optional type argument specifies a data type and is necessary if you wish to bind to an abstract data type. Use one of the following constants to set the data type: OCI_B_BLOB, OCI_B_CFILE, OCI_B_CLOB, OCI_B_FILE, OCI_B_ROWID. Make sure you use ocinewdescriptor before binding to an abstract data type. You also need to use -1 for the length argument.

<?
//set-up data to insert
$NewEmployee = array( array(8001, 'Smith', 'Clerk'), array(8002, 'Jones', 'Analyst'), array(8003, 'Atkinson', 'President') ); //connect to database$Connection = ocilogon("scott", "tiger");
//assemble query
$Query = "INSERT INTO emp (EMPNO, ENAME, JOB, HIREDATE) ";$Query .= "VALUES (:empno, :ename, :job, SYSDATE ) ";
$Query .= "RETURNING ROWID INTO :rowid "; //parse query$Statement = ociparse($Connection,$Query);
//create descriptor the abstract data type
$RowID = ocinewdescriptor($Connection, OCI_D_ROWID);
//bind input and output variables
ocibindbyname($Statement, ":empno", &$EmployeeNumber, 32);
ocibindbyname($Statement, ":ename", &$EmployeeName, 32);
ocibindbyname($Statement, ":job", &$Job, 32);
ocibindbyname($Statement, ":rowid", &$RowID, -1, OCI_B_ROWID);
//loop over each new employee
while(list($key,$EmployeeInfo) = each($NewEmployee)) { list($EmployeeNumber, $EmployeeName,$Job) =
$EmployeeInfo; //execute query, do not automatically commit ociexecute($Statement, OCI_DEFAULT);
print("$EmployeeNumber has ROWID$RowID<BR>n");
}
//free the statement
ocifreestatement($Statement); //undo the inserts //Normally, you won't do this, if we undo the inserts //each time, we can run the example over and over ocirollback($Connection);
//close connection
ocilogoff($Connection); ?> boolean ocicancel(integer statement) The ocicancel function fetches the next row from a statement. Internally it calls the OCIStmtFetch function, which is part of OCI, specifying zero for the number of rows. In every other way, it is identical to ocifetch. boolean ocicolumnisnull(integer statement, value column) Use ocicolumnisnull to test whether a column is null. You may specify columns by number, in which case columns are numbered starting with 1, or you may specify columns by name. See ocifetch for an example of use. string ocicolumnname(integer statement, integer column) The ocicolumnname function returns the name of a column given the column number. Columns are numbered starting with 1. See ocifetch for an example of use. integer ocicolumnsize(integer statement, value column) The ocicolumnsize function returns the size of a column. You may specify columns by number, in which case columns are numbered starting with 1, or you may specify columns by name. See ocifetch for an example of use. value ocicolumntype(integer statement, integer column) Use ocicolumntype to get the type of the specified column. You may specify columns by number, in which case columns are numbered starting with 1, or you may specify columns by name. The name of the type will be returned if it is one of the following: BFILE, BLOB, CHAR, CLOB, DATE, LONG RAW, LONG, NUMBER, RAW, REFCURSOR, ROWID, VARCHAR. Otherwise, an integer code from the data type will be returned. See ocifetch for an example of use. boolean ocicommit(integer connection) The ocicommit function commits all previous statements executed on the connection. By default, statements are committed when executed. You can override this functionality when you call ociexecute, by specifying a mode. boolean ocidefinebyname(integer statement, string column, reference variable, integer type) The ocidefinebyname function associates a column with a PHP variable. When the statement is executed, the value of the column will be copied into the variable. The statement argument must be an integer returned by ociparse. The column name must be written in upper case, otherwise Oracle will not recognize it. Unrecognized column names do not produce errors. Since the variable you pass in ocidefinebyname will be modified, you need to pass it by reference. That mean preceding it with an ampersand (&). The type argument appears to be necessary only if you are attaching to an abstract data type, such as a ROWID. Abstract data types require ocinewdescriptor be used prior to ocidefinebyname. If the type argument is left out, the variable will be set as a nullterminated string. <? //connect to database$Connection = ocilogon("scott", "tiger");
//assemble query
$Query = "SELECT ENAME, HIREDATE ";$Query .= "FROM emp ";
$Query .= "WHERE JOB='CLERK' "; //parse query$Statement = ociparse($Connection,$Query);
//associate two columns with variables
ocidefinebyname($Statement, "ENAME", &$EmployeeName);
ocidefinebyname($Statement, "HIREDATE", &$HireDate);
//execute query
ociexecute($Statement); //fetch each row while(ocifetch($Statement))
{
print("$EmployeeName was hired$HireDate<BR>n");
}
//free the statement
ocifreestatement($Statement); //close connection ocilogoff($Connection);
?>

array ocierror(integer identifier)
If an error has occurred, the ocierror function returns an associative array that describes it. If no error has occurred, FALSE is returned. The identifier argument may be either a statement identifier or a connection identifier. The returned array will have two elements, code and message. You may also call ocierror with no argument to get information about a failed login. See ocifetch for an example of use.

boolean ociexecute(integer statement, integer mode)
Use ociexecute to execute a statement. The mode argument is optional. It controls whether the statement will be committed after execution. By default, OCI_COMMIT_ON_EXECUTE is used. If you do not wish to commit the transaction immediately, use OCI_DEFAULT. See ocifetch for an example of use.

boolean ocifetch(integer statement)
The ocifetch function prepares the next row of data to be read with ociresult. When no rows remain, FALSE is returned.

<?
//connect to database
$Connection = ocilogon("scott", "tiger"); //assemble query$Query = "SELECT * ";
$Query .= "FROM emp "; //parse query$Statement = ociparse($Connection,$Query);
//execute query
ociexecute($Statement); //check that the query executed sucessfully if($Error = ocierror($Statement)) { print($Error["code"] . ": " . $Error["message"] . "<BR>n"); exit; } //start HTML table print("<TABLE>n"); //build headers from column information print("<TR>n"); for($i=1; $i = ocinumcols($Statement); $i++) { print("<TH>"); //print a line like "<TH>ENAME VARCHAR2(10)</TH>" print(ocicolumnname($Statement, $i) . " "); print(ocicolumntype($Statement, $i)); print("(" . ocicolumnsize($Statement, $i) . ")"); print("</TH>n"); } print("</TR>n"); //fetch each row while(ocifetch($Statement))
{
print("<TR>n");
//loop over each column
for($i=1;$i = ocinumcols($Statement);$i++)
{
//print a line like "<TD>SMITH/TD>"
print("<TD>");
if(ocicolumnisnull($Statement,$i))
{
print("(null)");
}
else
{
print(ociresult($Statement,$i));
}
print("</TD>n");
}
print("</TR>n");
}
//close table
print("</TABLE>n");
//free the statement
ocifreestatement($Statement); //close connection ocilogoff($Connection);
?>

boolean ocifetchinto(integer statement, reference data, integer mode)
Use ocifetchinto to get the next row of data from an executed statement and place it in an array. The data argument will contain an array that by default will be indexed by integers starting with1. The optional mode argument controls how the array is indexed. You may add the constants listed to get the features you desire.

integer ocifetchstatement(integer statement, reference data)
The ocifetchstatement function places an array with all the result data in the data argument and returns the number of rows. The data array is indexed by the names of the columns. Each element is an array itself which is indexed by integers starting with zero. Each element in this subarray corresponds to a row.

<?
//connect to database
$Connection = ocilogon("scott", "tiger"); //assemble query$Query = "SELECT * ";
$Query .= "FROM emp "; //parse query$Statement = ociparse($Connection,$Query);
//execute query
ociexecute($Statement); print("<TABLE>n"); //fetch all rows into array if($Rows = ocifetchstatement($Statement, &$Data))
{
while(list($key,$value) = each($Data)) { print("<TR>n"); //name of column print("<TH>$key/TH>n");
//print data
for($i=0;$i < $Rows;$i++)
{
print("<TD>$value[$i]/<TD>n");
}
print("</TR>n");
}
}
print("</TABLE>n");
//free the statement
ocifreestatement($Statement); //close connection ocilogoff($Connection);
?>

boolean ocifreecursor(integer cursor)
Use ocifreecursor to free the memory associated with a cursor you created with ocinewcursor.

boolean ocifreestatement(integer statement)
Use ocifreestatement to free the memory associated with a statement. The statement argument is an integer returned by ociparse.

ociinternaldebug(boolean on)
The ociinternaldebug function controls whether debugging information is generated. The debug output will be sent to the browser. It is off by default, of course.

boolean ocilogoff(integer connection)
Use ocilogoff to close a connection.

integer ocilogon(string user, string password, string sid)
The ocilogon function establishes a connection to an Oracle database. The identifier it returns is used to create statements, cursors, and descriptors. The user and password arguments are required. The optional sid argument specifies the server; if it is left out, the ORACLE_SID environment variable will be used. If you attempt to create a second connection to the same database, you will not really get another connection. This means that commits or rollbacks affect all statements created by your script. If you want a separate connection, use ocinlogon instead.

integer ocinewcursor(integer connection)
Use ocinewcursor to create a cursor. The cursor identifier that is returned is similar to a statement identifier. Use ocifreecursor to free the memory associated with a cursor. You can use a cursor to get the data returned by a stored procedure.

<?
//open connection
$Connection = ocilogen ("scott", "tiger"); //create cursor$Cursor = ocinewcursor ($Connection); //create statement that calls a stored procedure$Query = "BEGIN ";
$Query .= "docalculation;(:price); ";$Query .= "END; ";
$Statement = ociparse ($Connection, $Query); //bind placeholder to cursor ocibindbyname ($Statement, "price", &$Cursor, -1, OCI_B_CURSOR); //execute statement ociexecute ($Statement);
//execute cursor
ociexecute($Cursor); //loop over results in cursor while (ocifetchinto ($Cursor, &$Results)) { print("$Results<BR>n")
}
//free memory for cursor
ocifreecursor ($Cursor); //free memory for statement ocifreestatement ($Statement);
//close connection
ocilogoff ($Connection); ?> string ocinewdescriptor(integer connection, integer type) The ocinewdescriptor function allocates memory for descriptors and LOB locators. The type defaults to being a file, but you may specify OCI_D_FILE, OCI_D_LOB, or OCI_D_ROWID. See ocibindbyname for an example of use. integer ocinlogon(string user, string password, string sid) The ocinlogon function establishes a unique connection to an Oracle database. The identifier it returns is used to create statements, cursors, and descriptors. The user and password arguments are required. The optional sid argument specifies the server, and if left out, the ORACLE_SID environment variable will be used. Compare this function to ocilogon and ociplogon. integer ocinumcols(integer statement) The ocinumcols function returns the number of columns in a statement. See ocifetch for an example of use. integer ociparse(integer connection, string query) The ociparse function creates a statement from a query. It requires a valid connection identifier. integer ociplogon(string user, string password, string sid) The ociplogon function establishes a persistent connection to an Oracle database. These connections exist as long as the server process. When you request a persistent connection, you may get a connection that already exists, thus saving the overhead of establishing a connection. The returned identifier is used to create statements, cursors, and descriptors. The user and password arguments are required. The optional sid argument specifies the server, and if left out, the ORACLE_SID environment variable will be used. Compare this function to ocilogon and ocinlogon. string ociresult(integer statement, value column) Use ociresult to get the value of a column on the current row. The column may be identified by number or name. Columns are numbered starting with 1. Results are returned as strings, except in the case of LOBs, ROWIDs, and FILEs. See ocifetch for an example of use. boolean ocirollback(integer connection) Use ocirollback to issue a rollback operation on the given connection. By default, calls to ociexecute are committed automatically, so be sure to override this functionality if you wish to use ocirollback. Keep in mind that if you used ocilogon or ociplogon to get more than one connection, they may not be unique. Therefore issuing a rollback will affect all statements. To avoid this situation use ocinlogon instead. integer ocirowcount(integer statement) The ocirowcount function returns the number of rows affected by an update, insert, or delete. string ociserverversion(integer connection) Use ociserverversion to get a string describing the version of the server for a connection. integer ocisetprefetch(integer statement, integer size) The ociprefetch function sets the size of a buffer that Oracle uses to prefetch results into. The size argument will be multiplied by 1024 to set the actual number of bytes. string ocistatementtype(integer statement) Use ocistatementtype to get a string that describes the type of the statement. The types you can expect are ALTER, BEGIN, CREATE, DECLARE, DELETE, DROP, INSERT, SELECT, UNKNOWN, and UPDATE. boolean ora_bind(integer cursor, string variable, string parameter, integer length, integer type) The ora_bind function binds a PHP variable to a parameter in an Oracle query. This causes data to flow between the two entities. You must call ora_parse before binding any variables. The type parameter is optional. It specifies whether data may go only into or out of the Oracle parameter. By default, data may go both ways. The type may be defined using the following constants: ORA_BIND_IN, ORA_BIND_INOUT, ORA_BIND_OUT. <? //in case these aren't set for httpd putenv("ORACLE_HOME=/usr/local/oracle7"); putenv("ORACLE_SID=ORCL"); //connect to server$Connection = ora_logon("scott", "tiger");
//open cursor
$Cursor = ora_open($Connection);
$Query = "DECLARE php_in INTEGER; ";$Query .= "BEGIN ";
$Query .= ":php_out := :php_in + 3; ";$Query .= "END;";
//parse query
ora_parse($Cursor,$Query);
ora_bind($Cursor, "input", ":php_in", 11, ORA_BIND_IN); ora_bind($Cursor, "output", ":php_out", 11, ORA_BIND_OUT);
$input = 10; //execute query ora_exec($Cursor);
print("$output<BR>n"); //close the oracle cursor ora_close($Cursor);
//disconnect.
ora_logoff($Connection); ?> boolean ora_close(integer cursor) The ora_close function closes a connection opened by ora_open. See ora_bind for an example of use. string ora_columnname(integer cursor, integer column) The ora_columnname function returns the name of the specified column. Columns are numbered from zero. See ora_exec for an example of use. integer ora_columnsize(integer cursor, string column) The ora_columnsize function returns the size of the specified column. Columns are numbered from zero. Alternatively, you may specify a column by its name. See ora_exec for an example of use. string ora_columntype(integer cursor, integer column) The ora_columntype function returns the data type of the specified column. Columns are numbered from zero. Alternatively, you may specify a column by its name. The type will be one of the following: CHAR, CURSOR, DATE, LONG, LONG RAW, NUMBER, ROWID, VARCHAR, VARCHAR2. See ora_exec for an example of use. boolean ora_commit(integer connection) The ora_commit function commits all the pending transactions on the connection. By default, all transactions are committed after a call to ora_exec. boolean ora_commitoff(integer connection) Use ora_commitoff to turn off automatic commits. By default, PHP commits each transaction. boolean ora_commiton(integer connection) Use ora_commiton to turn on automatic commits. By default, PHP commits each transaction. integer ora_do(integer connection, string query) The ora_do function executes a query on the given connection. PHP takes care of creating a cursor, parsing the query, and executing it. A cursor identifier is returned. <? //in case these aren't set for httpd putenv("ORACLE_HOME=/usr/local/oracle7"); putenv("ORACLE_SID=ORCL"); // connect to server if($Connection = ora_logon("scott", "tiger"))
{
$Query = "SELECT ENAME ";$Query .= "FROM emp ";
$Query .= "WHERE ENAME LIKE 'SMI%' "; if($Cursor = ora_do($Connection,$Query))
{
ora_fetch($Cursor); print(ora_columnname($Cursor, 0) . "<BR>n");
// Close the Oracle cursor
ora_close($Cursor); } // disconnect. ora_logoff($Connection);
}
?>

string ora_error(integer identifier)
The ora_error function returns a string that describes the error for the last command sent to the Oracle database. The identifier may be either a connection identifier or a cursor identifier.

The message takes the form of XXX-NNNNN, where XXX tells you where the error came from and NNNNN tells you the error number. If you want to look up a description of the error, you can use Oracle's oerr command. See ora_exec for an example of use.

integer ora_errorcode(integer identifier)
The ora_errorcode function returns the error number for the last command sent to the Oracle server. The identifier may be either a connection identifier or a cursor identifier. See ora_exec for an example of use.

boolean ora_exec(integer cursor)
The ora_exec function executes a query previously parsed by the ora_parse function. Compare this function to ora_do.

<?
//in case these aren't set for httpd
putenv("ORACLE_HOME=/usr/local/oracle7");
putenv("ORACLE_SID=ORCL");
function reportError($id,$message)
{
print("$message<BR>n"); print("Error Code: " . ora_errorcode($id) . "<BR>n");
print("Error Message: " . ora_error($id) . "<BR>n"); } //connect to server if(!($Connection = ora_logon("scott", "tiger")))
{
print("Could not connect to database!<BR>n");
exit;
}
//open cursor
if(!($Cursor = ora_open($Connection)))
{
reportError($Connection, "Cursor could not be opened!"); exit; }$Query = "SELECT * ";
$Query .= "FROM emp "; //parse query if(!ora_parse($Cursor, $Query)) { reportError($Cursor, "Statement could not be parsed!");
exit;
}
// execute query
if(!ora_exec($Cursor)) { reportError($Cursor, "Statement could not be executed!");
exit;
}
//start table
print("<TABLE BORDER="1">n");
//print header row that describes each column
print("<TR>n");
for($i = 0;$i ora_numcols($Cursor);$i++)
{
print("<TH>");
// get column info
print(ora_columnname($Cursor,$i) . ": ");
print(ora_columntype($Cursor,$i) . " ");
print("(" . ora_columnsize($Cursor,$i) . ")");
print("</TH>n");
}
print("</TR>n");
// get each row
while(ora_fetch($Cursor)) { print("<TR>n"); //loop over each column for($i = 0; $i ora_numcols($Cursor); $i++) { print("<TD>"); // get column print(ora_getcolumn($Cursor, $i)); print("</TD>n"); } print("</TR>n"); } //close table print("</TABLE>n"); print("<BR>n"); print("Rows: " . ora_numrows($Cursor));
print("<BR>n");
// Close the Oracle cursor
ora_close($Cursor); // disconnect. ora_logoff($Connection);
?>

boolean ora_fetch(integer cursor)
The ora_fetch function causes a row from an executed query to be fetched into the cursor. This allows you to call ora_getcolumn. See ora_exec for an example of use.

integer ora_fetch_into(integer cursor, reference fields, integer flags)
The ora_fetch_into function gets the next row from the cursor and puts it into the fields argument, which must be passed by reference. Fields will contain an array indexed by numbers, starting with zero. The number of fields fetched is returned. The optional flags argument is a bit field that uses two constants, ORA_FETCHINTO_ASSOC and ORA_FETCHINTO_NULLS. The first instructs ora_fetch_into to create array elements named by their database fields. The second allows causes null columns to be represented as empty strings.

<?
//in case these aren't set for httpd
putenv("ORACLE_HOME=/usr/local/oracle7");
putenv("ORACLE_SID=ORCL");
//connect to server
if(!($Connection = ora_logon("scott", "tiger"))) { print("Could not connect to database!<BR>n"); exit; }$Query = "SELECT EMPNO ";
$Query .= "FROM emp "; if(!($Cursor = ora_do($Connection,$Query)))
{
print("Cursor could not be opened!<BR>n");
print("Error Code: " . ora_errorcode($Connection) . "<BR>n"); print("Error Message: " . ora_error($Connection) .
"<BR>n");
exit;
}
while(ora_fetch_into($Cursor, &$Column))
{
print("$Column[0]<BR>n"); } // Close the Oracle cursor ora_close($Cursor);
// disconnect.
ora_logoff(\$Connection);
?>

string ora_getcolumn(integer cursor, integer column)
The ora_getcolumn function returns the value of the column for the current row. Columns are indexed starting with zero. Long columns are limited to 64K. See ora_exec for an example of use.

boolean ora_logoff(integer connection)
Use ora_logoff to disconnect from the database server. See ora_exec for an example of use.

The ora_logon function begins a connection with an Oracle database server. A connection identifier is returned. See ora_exec for an example of use. As stated at the beginning of this section, you must define environment variables that specify the server in order to make a successful connection.

integer ora_numcols(integer cursor)
The ora_numcols function returns the number of columns for a query that has been executed. See ora_exec for an example of use.

integer ora_numrows(integer cursor)
The ora_numrows function returns the number of rows in the result set for an executed query. See ora_exec for an example of use.

integer ora_open(integer connection)
The ora_open function opens a cursor for the given connection. See ora_ exec for an example of use.

boolean ora_parse(integer cursor, string query)
The ora_parse function parses a query and readies it for execution. See ora_exec for an example of use.

The ora_plogon function returns a connection identifier. A persistent connection will be created. It will last as long as the server process. Later calls to either ora_logon or ora_plogon will find persistent connections and use them instead of creating new ones. Connections created with ora_plogon should not be used with ora_logoff.

boolean ora_rollback(integer connection)
The ora_rollback function performs a rollback on the given connection. Automatic commits must be turned off first.

« Previous Topics
Odbc
Informix
Filepro
Next Topics »
Sybase
Mysql
Msql