LXX. Oracle 8 functions

These functions allow you to access Oracle8 and Oracle7 databases. It uses the Oracle8 Call-Interface (OCI8). You will need the Oracle8 client libraries to use this extension.

This extension is more flexible than the standard Oracle extension. It supports binding of global and local PHP variables to Oracle placeholders, has full LOB, FILE and ROWID support and allows you to use user-supplied define variables.

Before using this extension, make sure that you have set up your Oracle environment variables properly for the Oracle user, as well as your web daemon user. The variables you might need to set are as follows:

  • ORACLE_HOME

  • ORACLE_SID

  • LD_PRELOAD

  • LD_LIBRARY_PATH

  • NLS_LANG

  • ORA_NLS33

After setting up the environment variables for your webserver user, be sure to also add the webserver user (nobody, www) to the oracle group.

If your webserver doesn't start or crashes at startup: Check that Apache is linked with the pthread library:

# ldd /www/apache/bin/httpd 
    libpthread.so.0 => /lib/libpthread.so.0 (0x4001c000)
    libm.so.6 => /lib/libm.so.6 (0x4002f000)
    libcrypt.so.1 => /lib/libcrypt.so.1 (0x4004c000)
    libdl.so.2 => /lib/libdl.so.2 (0x4007a000)
    libc.so.6 => /lib/libc.so.6 (0x4007e000)
    /lib/ld-linux.so.2 => /lib/ld-linux.so.2 (0x40000000)

If the libpthread is not listed you have to reinstall Apache:

# cd /usr/src/apache_1.3.xx
# make clean
# LIBS=-lpthread ./config.status
# make
# make install

Please note that on some systems like UnixWare it is libthread instead of libpthread. PHP and Apache have to be configured with EXTRA_LIBS=-lthread.

Example 1. OCI Hints

<?php
// by sergo@bacup.ru

// Use option: OCI_DEFAULT for execute command to delay execution
OCIExecute($stmt, OCI_DEFAULT);

// for retrieve data use (after fetch):

$result = OCIResult($stmt, $n);
if (is_object ($result)) $result = $result->load();

// For INSERT or UPDATE statement use:

$sql = "insert into table (field1, field2) values (field1 = 'value',
 field2 = empty_clob()) returning field2 into :field2";
OCIParse($conn, $sql);
$clob = OCINewDescriptor($conn, OCI_D_LOB);
OCIBindByName ($stmt, ":field2", &$clob, -1, OCI_B_CLOB);
OCIExecute($stmt, OCI_DEFAULT);
$clob->save ("some text");
OCICommit($conn);

?>

You can easily access stored procedures in the same way as you would from the commands line.

Example 2. Using Stored Procedures

<?php
// by webmaster@remoterealty.com
$sth = OCIParse ( $dbh, "begin sp_newaddress( :address_id, '$firstname',
 '$lastname', '$company', '$address1', '$address2', '$city', '$state',
 '$postalcode', '$country', :error_code );end;" );

// This calls stored procedure sp_newaddress, with :address_id being an
// in/out variable and :error_code being an out variable. 
// Then you do the binding:

   OCIBindByName ( $sth, ":address_id", $addr_id, 10 );
   OCIBindByName ( $sth, ":error_code", $errorcode, 10 );
   OCIExecute ( $sth );

?>

Table of Contents
OCIBindByName --  Bind a PHP variable to an Oracle Placeholder
OCICancel -- Cancel reading from cursor
OCICollAppend -- Coming soon
OCICollAssign -- Coming soon
OCICollAssignElem -- Coming soon
OCICollGetElem -- Coming soon
OCICollMax -- Coming soon
OCICollSize -- Coming soon
OCICollTrim -- Coming soon
OCIColumnIsNULL -- Test whether a result column is NULL
OCIColumnName -- Returns the name of a column
OCIColumnPrecision -- Coming soon
OCIColumnScale -- Coming soon
OCIColumnSize -- Return result column size
OCIColumnType -- Returns the data type of a column
OCIColumnTypeRaw -- Coming soon
OCICommit -- Commits outstanding transactions
OCIDefineByName --  Use a PHP variable for the define-step during a SELECT
OCIError -- Return the last error of stmt|conn|global
OCIExecute -- Execute a statement
OCIFetch -- Fetches the next row into result-buffer
OCIFetchInto -- Fetches the next row into result-array
OCIFetchStatement -- Fetch all rows of result data into an array
OCIFreeCollection -- Coming soon
OCIFreeCursor --  Free all resources associated with a cursor
OCIFreeDesc -- Deletes a large object descriptor
OCIFreeStatement --  Free all resources associated with a statement
OCIInternalDebug --  Enables or disables internal debug output
OCILoadLob -- Coming soon
OCILogOff -- Disconnects from Oracle
OCILogon -- Establishes a connection to Oracle
OCINewCollection -- Coming soon
OCINewCursor --  Return a new cursor (Statement-Handle)
OCINewDescriptor --  Initialize a new empty LOB or FILE descriptor
OCINLogon -- Establishes a new connection to Oracle
OCINumCols --  Return the number of result columns in a statement
OCIParse -- Parse a query and return a statement
OCIPLogon -- Connect to an Oracle database using a persistent connection
OCIResult -- Returns column value for fetched row
OCIRollback -- Rolls back outstanding transactions
OCIRowCount -- Gets the number of affected rows
OCISaveLob -- Coming soon
OCISaveLobFile -- Coming soon
OCIServerVersion -- Return a string containing server version information
OCISetPrefetch -- Sets number of rows to be prefetched
OCIStatementType -- Return the type of an OCI statement
OCIWriteLobToFile -- Coming soon