Delicious Bookmark this on Delicious Share on Facebook SlashdotSlashdot It! Digg! Digg

PHP : Function Reference : Oracle Functions [deprecated]

Oracle Functions [deprecated]



This extension is deprecated. Instead, use the improved oci8 extension. Functions documented here do list recommended oci8 alternatives.

This extension adds support for Oracle database server access.


This extension has been moved to the PECL repository and is no longer bundled with PHP as of PHP 5.1.0.


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

Resource Types

This extension defines two resource types: an Oracle connection identifier and an Oracle cursor index.

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.

ORA_BIND_INOUT (integer)
ORA_BIND_IN (integer)
ORA_BIND_OUT (integer)

Table of Contents

ora_bind — Binds a PHP variable to an Oracle parameter
ora_close — Closes an Oracle cursor
ora_columnname — Gets the name of an Oracle result column
ora_columnsize — Returns the size of an Oracle result column
ora_columntype — Gets the type of an Oracle result column
ora_commit — Commit an Oracle transaction
ora_commitoff — Disable automatic commit
ora_commiton — Enable automatic commit
ora_do — Parse, Exec, Fetch
ora_error — Gets an Oracle error message
ora_errorcode — Gets an Oracle error code
ora_exec — Execute a parsed statement on an Oracle cursor
ora_fetch_into — Fetch a row into the specified result array
ora_fetch — Fetch a row of data from a cursor
ora_getcolumn — Get data from a fetched column
ora_logoff — Close an Oracle connection
ora_logon — Open an Oracle connection
ora_numcols — Returns the number of columns
ora_numrows — Returns the number of rows
ora_open — Opens an Oracle cursor
ora_parse — Parse an SQL statement with Oracle
ora_plogon — Open a persistent Oracle connection
ora_rollback — Rolls back a transaction

Code Examples / Notes »


Using "@dbname" in ora_logon/ora_plogon will force a SQL*Net connect, even if the database is on the local host.  It's more efficient to use the ORACLE_SID environment variable.
putenv( "ORACLE_SID=dbname" );
$handle = ora_plogon( "SCOTT",  "TIGER");


Usefull system table: all_tab_comments
It shows all tables/views that you can read, including a lot of other usefull system tables like user_objects, user_tab_columns, user_constraints.

php kosmisk dk punctuation needed

To make sure my oracle get's rolledback when my script once in a while dies or I make a mistake (statements are terminated with ";"...). I use this construction. Because if it isn't rolled back and you use plogon, other oracle connections might hang, until the apache process that ran the failed script is terminated, and therefore releases the connection and gives the implicit rollback.
The use of a shutdown_function:
$oracle = ora_plogon("user/pass@db", "") or die("Can't connect to db");
$cursor = ora_open($oracle);
function ora_shutdown() {
 global $oracle;
 global $cursor;


The following function returns more detailed information about the columns of a table. It returns an array of hashes, with columnname, type, size, precision, scale, nullable, comments and constraints.
function columninfo($cursor, $table) {
$query  = "SELECT";
$query .= "  user_tab_columns.column_name,";
$query .= "  user_tab_columns.data_type,";
$query .= "  user_tab_columns.data_length,";
$query .= "  user_tab_columns.data_precision,";
$query .= "  user_tab_columns.data_scale,";
$query .= "  user_tab_columns.nullable,";
$query .= "  user_col_comments.comments,";
$query .= "  (SELECT";
$query .= "     constraint_name";
$query .= "    FROM user_cons_columns";
$query .= "    WHERE column_name = user_tab_columns.column_name)";
$query .= " FROM user_tab_columns,";
$query .= " user_col_comments";
$query .= " WHERE user_tab_columns.table_name = '$table'";
$query .= " AND user_tab_columns.column_name = user_col_comments.column_name";
$query .= " ORDER BY user_tab_columns.column_id ASC NULLS FIRST";
ora_parse($cursor, $query) or die("<hr /><h2 style=\"color:#ff0000\">" . ora_error($cursor) . "</h2><tt>$query</tt>");
ora_exec($cursor) or die("<hr /><h2 style=\"color:#ff0000\">" . ora_error($cursor) . "</h2><tt>$query</tt>");
$ret_array = array();
while (ora_fetch($cursor)) {
$column_name = strtolower(ora_getcolumn($cursor, 0));
$data_type = ora_getcolumn($cursor, 1);
$data_length = ora_getcolumn($cursor, 2);
$data_precision = ora_getcolumn($cursor, 3);
$data_scale = ora_getcolumn($cursor, 4);
$nullable = ora_getcolumn($cursor, 5);
$nullable = ($nullable == "Y");
$comments = ora_getcolumn($cursor, 6);
$constraint_name = strtolower(ora_getcolumn($cursor, 7));
if ($data_precision) {
$data_length = $data_precision + (($data_scale) ? 1 : 0);
$ret_array[] = array(
"column_name" => $column_name,
"data_type" => $data_type,
"data_length" => $data_length,
"data_precision" => $data_precision,
"data_scale" => $data_scale,
"nullable" => $nullable,
"comments" => $comments,
"constraint_name" => $constraint_name
return $ret_array;


Some good links from OTN:
Installing Oracle, PHP, and Apache on Linux:
Forums Home->OTN->Technologies->PHP:
Forums Home->OTN->Technologies->Linux:


ora_numrows($cursor) only works when there's  an ora_fetch before. it will return the number of rows resulting from the query.


Oracle Example
You need to know the values of the following environment variables:
This is the path to your Oracle installation directory. It is usually defined in the UNIX login script of your oracle user and all Oracle client users.
This is the name of the database instance you want to connect to. It is also defined in the UNIX environment of your oracle user and all Oracle client users.
Find out the values of these variables by loggin in as a user who can connect to the database in question with sqlplus. Then type at your Unix shell prompt:
prompt> echo $ORACLE_HOME
prompt> echo $ORACLE_SID
A simple PHP script using ora_* functions
 $conn = ora_login("scott", "tiger");
 $curs = ora_open($conn);
 $query = sprintf("select * from cat");
/* Long version */
 ora_parse($curs, $query);
/* Short Version */
 ora_do($conn, $query);
 $ncols = ora_numcols($curs);
 $nrows = ora_numrows($curs);
 printf("Result size is $ncols cols by $nrows rows.
 for ($i=0; $i<$ncols; $i++) {
   printf("col[%s] = %s type[%d] = %s
     $i, ora_columnname($curs, $i),
     $i, ora_columntype($curs, $i));
 for ($j=0; $j<$nrows; $j++) {
   for ($i=0; $i<$ncols; $i++) {
     $col = ora_getcolumn($curs, $i);
     printf(&quotval[%d, %d] = %s * ", $j, $i, ora_getcolumn($curs, $i);


Oracle Client 9i on Windows XP (2000) plus IIS, plus Oracle RDBMS on remote server.
The bad thing that nothing will be working on windows-based comp. until you will not change the proper permissions on some files.
Fist you will need to set environment
ORACLE_SID (not necessary)
ORACLE_HOME (necessary)
TNS_ADMIN (necessary).
Second: add user IUSR_user read permission on the directory pointed by TNS_ADMIN. The same user permission r/x on the file orannts9.dll in ORACLE_HOME\bin (this was finded experimentally). After this instructions  ora_logon or ocilogon will be executed without deadly error codes ORA-12154 or ORA-12538.


Ok. Considering it took me about three hours to find the answer to this:
Running a stored procedure in Oracle 8i + PHP4 (explicitly).
$sql = "begin procedure_name($required_variables_string); end;";
... execute code here...
but your string should look like what is given above.
For example, if I was going to execute the following:
$sql = "SELECT * from some_table"; looks like straight SQL, vs the one above which looks more like a PL/SQL block.
A bit gay in my opinion, but this is my 2c for those who might need to know how to execute a stored procedure using PHP.

marcel dot normann

Note: All ora_* functions will not work with Oracle 10g. You will have to use oci* instead.

nasser /

More on the header files needed to compile PHP...
I installed the client side of Oracle 9i for Linux in different configurations, and could not get the oci*.h files that PHP requires for compilation.  So in the end, I searched for them through the jar files, and here's the result:
As you probably know, the Oracle for Linux package comes in three download files, or three disks.  Go to this path:
The rest of the directories are based on the release number, so they may look a little bit different but you get the picture:
and use "jar xf" to extract it and grab the header files.  Copy them to:
And your make will work.  Since this wasted half of a day of mine (even after going through lots of comments on the Internet) I hope it saves somebody else's time...


If you use Oracle and PHP on the same computer, you can start only "dbstart" for runing PHP with Oracle and don't start "lsnrctl" maybe for security reason.


If you ever come accross trying to store non ASCII characters over PHP, ORACLE will probably not store thos characters properly, allthough you've set NLS_LANG and ORA_NLS33 in the Oracle User's ENV. the trick is to set these environment at Apaches' Startup. putenv and getenv didn't help at all. ) i meant apache's init script of course, provided you've got a SYS V style UNIX.


I had a hard time getting Oracle to work with the new PHP5 - here is a short step by step guide that may be usefull to others..
Oracle installation problems and workarounds for Fedora2 and Debian..
1) Get the oracle client stuff and put it into /usr/lib/oracle..
My output from "ls -R /usr/lib/oracle/"
classes12.jar  ojdbc14.jar
2) Set the ORACLE environment vars
export ORACLE_HOME=/usr/lib/oracle/
export LD_LIBRARY_PATH=/usr/lib/oracle/
3) Get the oracle source files
nzerror.h  oci1.h    ociap.h   ocidef.h  ocidfn.h   oci.h    ocikpr.h    ori.h  oro.h nzt.h oci8dp.h  ociapr.h  ocidem.h  ociextp.h  ocikp.h  oratypes.h  orl.h  ort.h
4) edit the file oci.h and add a line in the top below the comments
5) Copy the oracle source files (.h and .c) files to
php-5.0.0/ext/oci8/ and also copy them to php-5.0.0/main/
6) Goto the php-5.0.0. dir and do a ./configure --with-oci8 and other stuff you need..
7) make install
8) Add these lines to /etc/rc.d/initd/httpd
export LD_LIBRARY_PATH=/usr/lib/oracle/
export ORACLE_HOME=/usr/lib/oracle/
export LD_LIBRARY_PATH=/usr/lib/oracle/
export TZ=GMT
# Notice the line export NLS_DUAL_CURRENCY='.' is needed to get the stuff to run correctly on Fedora 2 .. otherwise you get a nasty misleading error about invalid username or password!.


I got error messages when compiling after configured php-4.3.4, apache-1.3.29 and with Oracle client 9.2.0 (MySQL also) installed.
My configure command was:
./configure \
--with-mysql \
--with-oracle=$ORACLE_HOME \
--with-oci8=$ORACLE_HOME \
--enable-sigchild \
--enable-track-vars \
--with-xml \
--with-apache=../apache_1.3.29 \
What made the error was lack of some headers (ociextp.h, oci.h...etc) These headers are located in the following dirs:
Just install the Oracle developement kit in the Oracle database folder, I suggest using custum selection if passing php compilation is all yo want:)
One can either make sure these two directories are included or just copy the *.h into php-4.3.4/include


I got a simple way to let php5 work with oracle 9i seamlessly.
   According to the normal case,it reports a error not to find libclntsh while compiling the php5.You could do a soft link as follow:
 ln -s $ORACLE_HOME/lib/ $ORACLE_HOME/lib/
  After that, you recompile the php5 and it'sl ok now.


I contribute the following code example.
function GetNameFromUserId ($ID, &$User, &$Msg)
GetNameFromUserId Start');
 if ($Session = ora_plogon('Hello', 'World'))

Session: ' . $Session . '

   if ($Cursor = ora_open($Session))

Cursor: ' . $Cursor . '

            . $ID . '\'';

SQL: ' . $SQL . '

     // Prepare SQL statement
     if (ora_parse($Cursor, $SQL))
       if (ora_exec($Cursor))
         $Row[] = '';
         // Retrieve a complete row from the result set
         if (ora_fetch_into($Cursor, &$Row))
           $User = 'count = ' . count($Row) . ': ';
           while (list($Key, $Value) = each($Row))
             $User .= '
Row[' . $Key . '] = ' . $Value;
           $Result = 0;
           $Msg = ora_error($Cursor);
           $Result = ora_errorcode($Cursor);
          }   // end of if expression
         $Msg = ora_error($Cursor);
         $Result = ora_errorcode($Cursor);
        }   // end of if expression
       $Msg = ora_error($Cursor);
       $Result = ora_errorcode($Cursor);
      }   // end of if expression
     $Msg = ora_error($Session);
     $Result = ora_errorcode($Session);
    }   // end of if expression
   $Msg = ora_error($Session);
   $Result = ora_errorcode($Session);
  }   // end of if expression
GetNameFromUserId End');
 return $Result;

Here is my little but very very useful tutorial :-)
CONNECTION : (in connect.php)
$ora_conn = ora_logon("databaseName@service","pass");
DISCONNECT : (in disconnect.php)
In each page, you must include both files
blah blah
Now the hard... SELECT ! (in select.php)
// Comment the line error_reporting if you want to
// debug your code - for example the "NO DATA FOUND"
// when no result. Oracle returns this like an error.
// Create an array, a cursor, count number of cols,
// perform the fetch and insert in the array.
$results = array();
$ora_cur = ora_do($ora_conn, $query);
// Don't panic, the $query will be set, wait a bit :)
if ($ora_cur)
 // Number of cols
 $numCols = ora_numcols($ora_cur);
 // Put the first line in the array...
 $row = array();
 for($i=0; $i<$numCols; $i++)
 { // for each column
   $row[ora_columnname($ora_cur,$i)] = ora_getcolumn($ora_cur,$i);
 // "Fetch" all records, one by one, and create an array for each one.
 // Each array is "pushed" in the $results array.
 while (ora_fetch($ora_cur))
 { // for each record
   $row = array();
   for($i=0; $i<$numCols; $i++)
   { // for each column
     $row[ora_columnname($ora_cur,$i)] = ora_getcolumn($ora_cur,$i);
   // Push $results
// Now turns error_reporting on. Comment this line if you want
// to see "NO DATA FOUND" warnings.
And now let's enjoy with this simple page !! :)
echo "blah blah";
$query = "select NAME, AGE from people where...";
// Browse $results array and display results :
if (count($results)) // if no "NO DATA FOUND"
$res is like :
array(0 => array('NAME' => 'Sylvain', 'AGE' => '21'),
     1 => array('NAME' => 'Somebody','AGE' => '888'));
 echo $res[1]['NAME']; // name
 echo $res[1]['AGE']; // age
 // ALLWAYS write in uppercase !
else // NO_DATA_FOUND :(
echo "There's nobody in your database :D";
echo "blah blah and again blah";
//...another queries
$query = "...";
Another queries (insert, update...) are simpler. Let's see :)
$query = "UPDATE people SET url='' where name='Sylvain'";
and query.php is :
$cursor = ora_open($ora_conn); // Create a cursor
if($cursor) // if created
 // Parse query
 $resultat = ora_parse($cursor,$query);
 $resultat = ora_exec($cursor); // Execute
 $a=ora_commit($ora_conn); // perform COMMIT
 $b=ora_close($cursor); // close cursor
{ // Write an error message
 echo "oh, oh ! problem...";
Now enjoy using it !
For more information, see my website in French


Here is a code sample that I used on NT 4.0 running Apache/Oracle 8.1.6/PHP 4.0 : I used it to connect to the sample scott-tiger schema EMP table. It is pretty much a hack, but I hope it helps you.
$foo=exec("echo %ORACLE_SID%");
echo $foo;
      //$db = "";
      // create connection
   //  $connection = ora_logon("scott","tiger")
     $connection = OCILogon("scott","tiger","")
              or die("Couldn't logon to database.");
      // create SQL statement
      $sql = "SELECT ENAME, SAL
              FROM EMP
              ORDER BY SAL";
echo "
echo $connection;
echo "
echo $sql;
echo "
      // parse SQL statement
      $sql_statement = OCIParse($connection,$sql)
              or die("Couldn't parse statement.");
echo $sql_statement;
      // execute SQL query
              or die("Couldn't execute statement.");
      // get number of columns for use later
      $num_columns = OCINumCols($sql_statement);
echo "
echo $num_columns;
      // start results formatting
      echo "<TABLE BORDER=1>";
      echo "<TR>
      // format results by row
      while (OCIFetch($sql_statement)) {
              echo "<TR>";
              for ($i = 1; $i < $num_columns; $i++) {
                      $column_value1 = OCIResult($sql_statement,$i);
                      $column_value2 = OCIResult($sql_statement,$i+1);
                      //echo $i;
                              echo "<TD>$column_value1</TD>";
                              echo "<TD>$column_value2</TD>";
              echo "</TR>";
      echo "</TABLE>";
      // free resources and close connection

javi ros

Here are the translate of some functions from ORA to OCI:
function Ora_Logon($usuario, $password)
       $con = oci_connect($usuario,$password);
       return $con;
function Ora_Open($conexion) {
       return $cursor;
function Ora_Parse(&$cursor, $consulta) {
       return $cursor;
function Ora_Exec(&$cursor) {
       return $cursor;
function Ora_Fetch(&$cursor)
       if ($cursor[2] == 1) $cursor[2]=0;
       return oci_fetch($cursor[1]);
function Ora_GetColumn(&$cursor, $indice)
       if ($cursor[2] == 1) {
       $valor = oci_result($cursor[1],$indice+1);
       return $valor;
function Ora_Close(&$cursor)
function Ora_Logoff($conexion) {


Connecting to remote oracle database -
Is the same as a local database, but the remote database must be configured in your tnsnames.ora - its then provided to PHP by way of tns...


A simple function
# list query in array
# $conn: connection
# $query: query_string
# return: $templist[$row][$column]=$value;
function get_query($conn,$query){
$cursor = ora_open($conn);
ora_parse($cursor, $query) or die;
$numcols = ora_numcols($cursor);
 for($column=0; $column < $numcols; $column++){
  $colname = trim(ora_columnname($cursor, $column));
  $data = trim(ora_getcolumn($cursor, $column));
return $templist;
$conn = ora_plogon( "user", "passwd")or die;
$cursor = ora_open($conn);
$query = "SELECT * FROM table";
echo $result[$i][column];


...and with the dawning of the new year, my TNS problem is gone. I've been having trouble connecting to a remote db server from Linux running Apache/PHP+Oracle. I'd set the ORACLE_HOME and ORACLE_SID (although, I'm not sure if it cares about the SID when connecting via SQL*Net/Net8) with no luck. The environment variable that does the trick is TNS_ADMIN. (I saw a mention of this on phpbuilder and thought I'd mention it here)
Set TNS_ADMIN to the directory with your tnsnames.ora file and see if that dosen't help the TNS unable to connect problems

... does the trick for me.

andre@sundale net

>All ora_* functions will not work with Oracle 10g.
Appears to work ok for me under FreeBSD and sustain relatively high load.
New datatypes like CLOB do not work though, the module is based on Oracle7 client library.

Change Language

Follow Navioo On Twitter
.NET Functions
Apache-specific Functions
Alternative PHP Cache
Advanced PHP debugger
Array Functions
Aspell functions [deprecated]
BBCode Functions
BCMath Arbitrary Precision Mathematics Functions
PHP bytecode Compiler
Bzip2 Compression Functions
Calendar Functions
CCVS API Functions [deprecated]
Class/Object Functions
Classkit Functions
ClibPDF Functions [deprecated]
COM and .Net (Windows)
Crack Functions
Character Type Functions
Cybercash Payment Functions
Credit Mutuel CyberMUT functions
Cyrus IMAP administration Functions
Date and Time Functions
DB++ Functions
Database (dbm-style) Abstraction Layer Functions
dBase Functions
DBM Functions [deprecated]
dbx Functions
Direct IO Functions
Directory Functions
DOM Functions
DOM XML Functions
enchant Functions
Error Handling and Logging Functions
Exif Functions
Expect Functions
File Alteration Monitor Functions
Forms Data Format Functions
Fileinfo Functions
filePro Functions
Filesystem Functions
Filter Functions
Firebird/InterBase Functions
Firebird/Interbase Functions (PDO_FIREBIRD)
FriBiDi Functions
FrontBase Functions
FTP Functions
Function Handling Functions
GeoIP Functions
Gettext Functions
GMP Functions
gnupg Functions
Haru PDF Functions
hash Functions
Hyperwave Functions
Hyperwave API Functions
i18n Functions
IBM Functions (PDO_IBM)
iconv Functions
ID3 Functions
IIS Administration Functions
Image Functions
Imagick Image Library
Informix Functions
Informix Functions (PDO_INFORMIX)
Ingres II Functions
IRC Gateway Functions
PHP / Java Integration
JSON Functions
LDAP Functions
libxml Functions
Lotus Notes Functions
LZF Functions
Mail Functions
Mailparse Functions
Mathematical Functions
MaxDB PHP Extension
MCAL Functions
Mcrypt Encryption Functions
MCVE (Monetra) Payment Functions
Memcache Functions
Mhash Functions
Mimetype Functions
Ming functions for Flash
Miscellaneous Functions
mnoGoSearch Functions
Microsoft SQL Server Functions
Microsoft SQL Server and Sybase Functions (PDO_DBLIB)
Mohawk Software Session Handler Functions
mSQL Functions
Multibyte String Functions
muscat Functions
MySQL Functions
MySQL Functions (PDO_MYSQL)
MySQL Improved Extension
Ncurses Terminal Screen Control Functions
Network Functions
Newt Functions
NSAPI-specific Functions
Object Aggregation/Composition Functions
Object property and method call overloading
Oracle Functions
ODBC Functions (Unified)
ODBC and DB2 Functions (PDO_ODBC)
OpenAL Audio Bindings
OpenSSL Functions
Oracle Functions [deprecated]
Oracle Functions (PDO_OCI)
Output Control Functions
Ovrimos SQL Functions
Paradox File Access
Parsekit Functions
Process Control Functions
Regular Expression Functions (Perl-Compatible)
PDF Functions
PDO Functions
Phar archive stream and classes
PHP Options&Information
POSIX Functions
Regular Expression Functions (POSIX Extended)
PostgreSQL Functions
PostgreSQL Functions (PDO_PGSQL)
Printer Functions
Program Execution Functions
PostScript document creation
Pspell Functions
qtdom Functions
Rar Functions
GNU Readline
GNU Recode Functions
RPM Header Reading Functions
runkit Functions
SAM - Simple Asynchronous Messaging
Satellite CORBA client extension [deprecated]
SCA Functions
SDO Functions
SDO XML Data Access Service Functions
SDO Relational Data Access Service Functions
SESAM Database Functions
PostgreSQL Session Save Handler
Session Handling Functions
Shared Memory Functions
SimpleXML functions
SNMP Functions
SOAP Functions
Socket Functions
Standard PHP Library (SPL) Functions
SQLite Functions
SQLite Functions (PDO_SQLITE)
Secure Shell2 Functions
Statistics Functions
Stream Functions
String Functions
Subversion Functions
Shockwave Flash Functions
Swish Functions
Sybase Functions
TCP Wrappers Functions
Tidy Functions
Tokenizer Functions
Unicode Functions
URL Functions
Variable Handling Functions
Verisign Payflow Pro Functions
vpopmail Functions
W32api Functions
WDDX Functions
win32ps Functions
win32service Functions
xattr Functions
xdiff Functions
XML Parser Functions
XML-RPC Functions
XMLReader functions
XMLWriter Functions
XSL functions
XSLT Functions
YAZ Functions
YP/NIS Functions
Zip File Functions
Zlib Compression Functions
eXTReMe Tracker