Last updated: Sat, 17 Jul 2004

LXXVII. Oracle 8 functions

Introduction

These functions allow you to access Oracle9, Oracle8 and Oracle7 databases. It uses the Oracle Call Interface (OCI).

This extension is more flexible than the old 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. You are recommended to use this extension instead of old Oracle extension where possible.

Requirements

You will need the Oracle client libraries to use this extension. Windows users will need at least Oracle version 8.1 to use the php_oci8.dll dll.

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.

Installation

You have to compile PHP with the option --with-oci8[=DIR], where DIR defaults to your environment variable ORACLE_HOME.

Runtime Configuration

This extension has no configuration directives defined in php.ini.

Resource Types

Predefined Constants

The constants below are defined by this extension, and will only be available when the extension has either been compiled into PHP or dynamically loaded at runtime.

OCI_DEFAULT (integer): Statement execution mode. Statement is not committed automatically when using this mode.
OCI_DESCRIBE_ONLY (integer): Statement execution mode. Use this mode if you don't want to really execute query, but only get select-list description.
OCI_COMMIT_ON_SUCCESS (integer): Statement execution mode. Statement is automatically commited after oci_execute() call.
OCI_EXACT_FETCH (integer): Statement fetch mode. Used when the application knows in advance exactly how many rows it will be fetching. This mode turns prefetching off for Oracle release 8 or later mode. Cursor is cancelled after the desired rows are fetched and may result in reduced server-side resource usage.
OCI_SYSDATE (integer)
OCI_B_BFILE (integer): Used with oci_bind_by_name() when binding BFILEs.
OCI_B_CFILEE (integer): Used with oci_bind_by_name() when binding CFILEs.
OCI_B_CLOB (integer): Used with oci_bind_by_name() when binding CLOBs.
OCI_B_BLOB (integer): Used with oci_bind_by_name() when binding BLOBs.
OCI_B_ROWID (integer): Used with oci_bind_by_name() when binding ROWIDs.
OCI_B_CURSOR (integer): Used with oci_bind_by_name() when binding cursors, previously allocated with oci_new_descriptor().
OCI_B_NTY (integer): Used with oci_bind_by_name() when binding named data types.
OCI_B_BIN (integer)
SQLT_BFILEE (integer): The same as OCI_B_BFILE.
SQLT_CFILEE (integer): The same as OCI_B_CFILEE.
SQLT_CLOB (integer): The same as OCI_B_CLOB.
SQLT_BLOB (integer): The same as OCI_B_BLOB.
SQLT_RDD (integer): The same as OCI_B_ROWID.
SQLT_NTY (integer): The same as OCI_B_NTY.
OCI_FETCHSTATEMENT_BY_COLUMN (integer): Default mode of oci_fetch_all().
OCI_FETCHSTATEMENT_BY_ROW (integer): Alternative mode of oci_fetch_all().
OCI_ASSOC (integer): Used with oci_fetch_all() and oci_fetch_array() to get an associative array as a result.
OCI_NUM (integer): Used with oci_fetch_all() and oci_fetch_array() to get an enumerated array as a result.
OCI_BOTH (integer): Used with oci_fetch_all() and oci_fetch_array() to get an array with both associative and number indices.
OCI_RETURN_NULLS (integer): Used with oci_fetch_array() to get empty array elements if field's value is NULL.
OCI_RETURN_LOBS (integer): Used with oci_fetch_array() to get value of LOB instead of the descriptor.
OCI_DTYPE_FILE (integer): This flag tells oci_new_descriptor() to initialize new FILE descriptor.
OCI_DTYPE_LOB (integer): This flag tells oci_new_descriptor() to initialize new LOB descriptor.
OCI_DTYPE_ROWID (integer): This flag tells oci_new_descriptor() to initialize new ROWID descriptor.
OCI_D_FILE (integer): The same as OCI_DTYPE_FILE.
OCI_D_LOB (integer): The same as OCI_DTYPE_LOB.
OCI_D_ROWID (integer): The same as OCI_DTYPE_ROWID.

Examples

�rnek 1. OCI Hints


<?php

// by sergo at bacup dot 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.

�rnek 2. Using Stored Procedures


<?php

// by webmaster at remoterealty dot 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);



?>

��indekiler
oci_bind_by_name -- Binds the PHP variable to the Oracle placeholder
oci_cancel -- Cancels reading from cursor
oci_close -- Closes Oracle connection
collection->append -- Appends an object to the collection
collection->assign -- Assigns a value to the collection from another existing collection
collection->assignElem -- Assigns a value to the element of the collection
collection->getElem -- Returns value of the element
collection->max -- Gets the maximum number of elements in the collection
collection->size -- Returns size of the collection
collection->trim -- Trims elements from the end of the collection
oci_commit -- Commits outstanding statements
oci_connect -- Establishes a connection to Oracle server
oci_define_by_name -- Uses a PHP variable for the define-step during a SELECT
oci_error -- Returns the last error found
oci_execute -- Executes a statement
oci_fetch_all -- Fetches all rows of result data into an array
oci_fetch_array -- Returns the next row from the result data as an associative or numeric array, or both
oci_fetch_assoc -- Returns the next row from the result data as an associative array
oci_fetch_object -- Returns the next row from the result data as an object
oci_fetch_row -- Returns the next row from the result data as a numeric array
oci_fetch -- Fetches the next row into result-buffer
oci_field_is_null -- Checks if the field is NULL
oci_field_name -- Returns the name of a field from the statement
oci_field_precision -- Tell the precision of a field
oci_field_scale -- Tell the scale of the field
oci_field_size -- Returns field's size
oci_field_type_raw -- Tell the raw Oracle data type of the field
oci_field_type -- Returns field's data type
collection->free -- Frees resources associated with collection object
descriptor->free -- Frees resources associated with descriptor
oci_free_statement -- Frees all resources associated with statement or cursor
oci_internal_debug -- Enables or disables internal debug output
lob->append -- Appends data from the large object to another large object
lob->close -- Closes LOB descriptor
oci_lob_copy -- Copies large object
lob->eof -- Tests for end-of-file on a large object's descriptor
lob->erase -- Erases a specified portion of the internal LOB data
lob->export -- Exports LOB's contents to a file
lob->flush -- Flushes/writes buffer of the LOB to the server
lob->import -- Imports file data to the LOB
oci_lob_is_equal -- Compares two LOB/FILE locators for equality
lob->load -- Returns large object's contents
lob->read -- Reads part of large object
lob->rewind -- Moves the internal pointer to the beginning of the large object
lob->save -- Saves data to the large object
lob->seek -- Sets the internal pointer of the large object
lob->size -- Returns size of large object
lob->tell -- Returns current position of internal pointer of large object
lob->truncate -- Truncates large object
lob->writeTemporary -- Writes temporary large object
lob->write -- Writes data to the large object
oci_new_collection -- Allocates new collection object
oci_new_connect -- Establishes a new connection to the Oracle server
oci_new_cursor -- Allocates and returns a new cursor (statement handle)
oci_new_descriptor -- Initializes a new empty LOB or FILE descriptor
oci_num_fields -- Returns the number of result columns in a statement
oci_num_rows -- Returns number of rows affected during statement execution
oci_parse -- Prepares Oracle statement for execution
oci_password_change -- Changes password of Oracle's user
oci_pconnect -- Connect to an Oracle database using a persistent connection
oci_result -- Returns field's value from the fetched row
oci_rollback -- Rolls back outstanding transaction
oci_server_version -- Returns server version
oci_set_prefetch -- Sets number of rows to be prefetched
oci_statement_type -- Returns the type of an OCI statement
ocibindbyname -- Bind a PHP variable to an Oracle Placeholder
ocicancel -- Cancel reading from cursor
ocicloselob -- Closes lob descriptor
ocicollappend -- Append an object to the collection
ocicollassign -- Assign a collection from another existing collection
ocicollassignelem -- Assign element val to collection at index ndx
ocicollgetelem -- Retrieve the value at collection index ndx
ocicollmax -- Return the max value of a collection. For a varray this is the maximum length of the array
ocicollsize -- Return the size of a collection
ocicolltrim -- Trim num elements from the end of a collection
ocicolumnisnull -- Test whether a result column is NULL
ocicolumnname -- Returns the name of a column
ocicolumnprecision -- Tell the precision of a column
ocicolumnscale -- Tell the scale of a column
ocicolumnsize -- Return result column size
ocicolumntype -- Returns the data type of a column
ocicolumntyperaw -- Tell the raw oracle data type of a column
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 an array
ocifetchstatement -- Fetch all rows of result data into an array
ocifreecollection -- Deletes collection object
ocifreecursor -- Free all resources associated with a cursor
ocifreedesc -- Deletes a large object descriptor
ocifreestatement -- Free all resources associated with a statement
lob->getBuffering -- Returns current state of buffering for large object
ociinternaldebug -- Enables or disables internal debug output
ociloadlob -- Loads a large object
ocilogoff -- Disconnects from Oracle server
ocilogon -- Establishes a connection to Oracle
ocinewcollection -- Initialize a new collection
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 an Oracle 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 -- Saves a large object
ocisavelobfile -- Saves a large object file
ociserverversion -- Return a string containing server version information
lob->setBuffering -- Changes current state of buffering for large object
ocisetprefetch -- Sets number of rows to be prefetched
ocistatementtype -- Return the type of an OCI statement
ociwritelobtofile -- Saves a large object file
ociwritetemporarylob -- Writes temporary blob

oci_bind_by_name

deaggregate

Last updated: Sat, 17 Jul 2004

add a note User Contributed Notes
Oracle 8 functions

alvaro at demogracia dot com
10-Jun-2008 04:24


If you've followed the instructions and you can't even connect to the DB server, welcome to the Oracle hell. Most of the information you'll find is deprecated, incomplete, not for your platform, unnecessary or just plain wrong.



Typically, you won't need at all those complicate setups you'll read about and they'll probably make things harder. I suggest you get Systernal's "Filemon" utility (for Windows, in Unix you may do with strace) and find out what exact config files and DLLs are being tried by php.exe (or httpd.exe if PHP runs as Apache module or...). Pretty often, the issue is that (e.g.) TNSNAMES.ORA does not have the correct line ending or Apache is looking for a DLL that does not even exist in your hard disc; learning that prevents you to waste time adding more and more useless environmental variables.



Goog luck.

christopher dot jones at oracle dot com
17-Jun-2007 09:25


There are several good books on PHP and Oracle available - search your favorite bookstore.  There is also a free book from Oracle "The Underground PHP and Oracle Manual" that covers the OCI8 extension: http://otn.oracle.com/tech/php/pdf/underground-php-oracle-manual.pdf

(free registration for OTN required, IIRC). Disclaimer: I'm one of its authors.

James VL
28-Feb-2007 10:49


re: Andrei Kubar



I was using Oracle Instant Client on Windows XP, and was getting the



PHP Startup: Unable to load dynamic library 'C:\php\ext\php_oci8.dll' - The specified module could not be found.



error at startup, even though I had done all (most?) of the PHP and Oracle setup routines.



For me it wasn't the lack of mfc*.dll files, but a simple PATH issue: in addition to defining the environment variable TNS_ADMIN, I had to include my instant client directory in my PATH.

darkstar_ae at hotmail dot com
25-Apr-2005 10:39


When fetching associative arrays, use uppercase string indices. It appears the PHP OCI Library is less lenient with the field names returned by Oracle.



e.g.



echo $row['field1']; // This won't return anything.



as opposed to:



echo $row['FIELD1'];

edahnke at consultant dot com
08-Jan-2002 12:01


Here's a little snipet that shows how to insert multiple clob fields. Worked for me.



$dbh = OCILogon($dst_user_name, $dst_password, $dst_db_name);



for($i = 0; $i < $src_rec_cnt; $i++) {

    $query = "insert into bid (id,time,resume,experience,comments) values ('$id[$i]','$time[$i]',empty_clob(),empty_clob(),empty_clob()) returning  resume,experience,comments into :resume,:experience,:comments";



    $stmt = OCIParse($dbh, $query);



    $clob1 = OCINewDescriptor($dbh, OCI_D_LOB);

    $clob2 = OCINewDescriptor($dbh, OCI_D_LOB);

    $clob3 = OCINewDescriptor($dbh, OCI_D_LOB);

    

    OCIBindByName ($stmt, ":resume", &$clob1, -1, OCI_B_CLOB);

    OCIBindByName ($stmt, ":experience", &$clob2, -1, OCI_B_CLOB);

    OCIBindByName ($stmt, ":comments", &$clob3, -1, OCI_B_CLOB);



    OCIExecute($stmt, OCI_DEFAULT);

    

    @$clob1->save ($resume[$i]);

    @$clob2->save ($experience[$i]);

    @$clob3->save ($comments[$i]);

    

    OCICommit($dbh);

doug at redhive dot com
19-Jul-2001 02:38


if you feel like you have too many oracle statements clouding up your php, i came up with a function to open a connection (if necessary), parse your sql statement, and return the executed query.  after you call the function, you can do whatever needs to be done.  makes like so much simpler: (do whatever you want with the errors)





function execute_query($query, &$connected) {


    global $ORACLE_USER, $ORACLE_PASS, $ORACLE_SID;





    if(!$connected) {


        $connected = @OCIPLogon($ORACLE_USER, $ORACLE_PASS,  $ORACLE_SID);





        if($error = OCIError()) {


            die("<font color=red>ERROR!! Couldn't connect to server!</font>");


        }


    }





    $stmt = @OCIParse($connected, $query);


    if($error = OCIError($cn))    {


        die("<font color=red>ERROR!! Statement syntax error!</font>");


    }





    @OCIExecute($stmt);


    if($error = OCIError($stmt)) {


        die("<font color=red>ERROR!! Could not execute statement!</font>");


    }





    return $stmt;


}

jasendorf at lcounty dot com
23-May-2001 03:48


VERY IMPORTANT!  OCIPLogon only keeps a persistent connection if you are running PHP as a module.  This is particularly important to Windows users who are used to running PHP as a CGI.

junk at netburp dot com
19-Oct-2000 02:39


Here's a clue about rowid.





Don't forget about the oracle functions:





"rowidtochar" and "chartorowid"





"select rowidtochar(rowid) as FOO from table ...." 





When you want to pass the rowid in a form or link, that's 


the only way to go.

add a note

oci_bind_by_name

deaggregate

Last updated: Sat, 17 Jul 2004