PHP  
downloads | documentation | faq | getting help | mailing lists | reporting bugs | php.net sites | links | my php.net 
search for in the  
<openssl_x509_readora_bind>
view the version of this page
Last updated: Thu, 21 Aug 2003

LXXIV. Oracle functions

Introduction

This extension adds support for Oracle database server access. See also the OCI8 extension.

Installation

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

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)

ORA_FETCHINTO_ASSOC (integer)

ORA_FETCHINTO_NULLS (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


add a note add a note User Contributed Notes
Oracle functions
helvecio_oliveira at yahoo dot com dot br
14-Oct-2003 02:45
Some good links from OTN:

Installing Oracle, PHP, and Apache on Linux:
otn.oracle.com/tech/opensource/php/apache/inst_php_apache_linux.html

Forums Home->OTN->Technologies->PHP:
forums.oracle.com/forums/forum.jsp?forum=178

Forums Home->OTN->Technologies->Linux:
forums.oracle.com/forums/forum.jsp?forum=135
john at no_gay_refereces_please dot com
24-Apr-2003 03:03
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.
thebestion at gmx dot de
09-Oct-2002 11:48
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;
 ora_exec($cursor);
 $numcols = ora_numcols($cursor);
 $i=0;
 while(ora_fetch($cursor)){
  for($column=0; $column < $numcols; $column++){
   $colname = trim(ora_columnname($cursor, $column));
   $data = trim(ora_getcolumn($cursor, $column));

   $templist[$i][$colname]=$data;
  }
  $i++;
 }
 return $templist;
}

i.e.:

$conn = ora_plogon( "user", "passwd")or die;

$cursor = ora_open($conn);

ora_commitoff($conn);

$query = "SELECT * FROM table";

$result=get_query($conn,$query);

for($i=0;$result[$i];$i++){
 echo $result[$i][column];
}
jjuffermans at chello dot com
16-Mar-2001 07:11
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;
    }
jjuffermans at chello dot com
16-Mar-2001 02:10
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.
aedunov at cv dot jinr dot ru
14-Jan-2001 10:09
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.
dmearls at yahoo dot com
05-Jan-2001 10:15
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.

&lt;?php

putenv('ORACLE_SID=DMEARLS.WORLD');
putenv('ORACLE_HOME=d:\oracle\ora81');
putenv('TNS_ADMIN=d:\oracle\ora81\network\admin');
$foo=exec("echo %ORACLE_SID%");
echo $foo;
//Set("ORACLE_SID=DMEARLS.WORLD");
//Set("ORACLE_HOME=D:\oracle\isuites");
       //$db = "dmearls.world";
       // create connection
    //  $connection = ora_logon("scott","tiger")
      $connection = OCILogon("scott","tiger","dmearls.world")
               or die("Couldn't logon to database.");

       // create SQL statement
       $sql = "SELECT ENAME, SAL
               FROM EMP
               ORDER BY SAL";
echo "<br>";
echo $connection;
echo "<br>";
echo $sql;
echo "<br>";

       // parse SQL statement
       $sql_statement = OCIParse($connection,$sql)
               or die("Couldn't parse statement.");
echo $sql_statement;
       // execute SQL query
       OCIExecute($sql_statement)
               or die("Couldn't execute statement.");

       // get number of columns for use later
       $num_columns = OCINumCols($sql_statement);
echo "<br>";
echo $num_columns;
       // start results formatting
       echo "<TABLE BORDER=1>";
       echo "<TR>
             <TH>Name</TH>
             <TH>Salary</TH>
             </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
       OCIFreeStatement($sql_statement);
       OCILogoff($connection);

       ?>
rs at aelea dot com
19-Aug-2000 04:28
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...
elf at messer dot de
27-Jul-2000 11:05
I contribute the following code example.

<PRE>
function GetNameFromUserId ($ID, &$User, &$Msg)
 {
  print('<BR>GetNameFromUserId Start');

  if ($Session = ora_plogon('Hello', 'World'))
   {
    print('<P>Session: ' . $Session . '<P>');

    if ($Cursor = ora_open($Session))
     {
      print('<P>Cursor: ' . $Cursor . '<P>');

      $SQL = 'SELECT NAME FROM PERSONS WHERE USERID = \''
             . $ID . '\'';
      print('<P>SQL: ' . $SQL . '<P>');

      // 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 .= '<BR>Row[' . $Key . '] = ' . $Value;
             }

            $Result = 0;
           }
          else
           {
            $Msg = ora_error($Cursor);
            $Result = ora_errorcode($Cursor);
           }   // end of if expression
         }
        else
         {
          $Msg = ora_error($Cursor);
          $Result = ora_errorcode($Cursor);
         }   // end of if expression
       }
      else
       {
        $Msg = ora_error($Cursor);
        $Result = ora_errorcode($Cursor);
       }   // end of if expression

      ora_close($Cursor);
     }
    else
     {
      $Msg = ora_error($Session);
      $Result = ora_errorcode($Session);
     }   // end of if expression

    ora_logoff($Session);
   }
  else
   {
    $Msg = ora_error($Session);
    $Result = ora_errorcode($Session);
   }   // end of if expression

  print('<BR>GetNameFromUserId End');
  return $Result;
 }
</PRE>
lmg at webfarm dot com
03-Jan-2000 10:16
...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)<br>
Set TNS_ADMIN to the directory with your tnsnames.ora file and see if that dosen't help the TNS unable to connect problems<p>

setenv("TNS_ADMIN=/opt/oracle/product/8.1.5/network/admin");

<P>
... does the trick for me.<P>
denkwerk-hamburg at denkwerk dot com
30-Jun-1999 01:33
ora_numrows($cursor) only works when there's  an ora_fetch before. it will return the number of rows resulting from the query.
denkwerk-hamburg at denkwerk dot com
30-Jun-1999 01:28
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.
cord at ragesoft dot com
13-Feb-1999 05:21
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.

<PRE>
putenv( "ORACLE_SID=dbname" );
$handle = ora_plogon( "SCOTT",  "TIGER");
</PRE>
kk at shonline dot de
05-Aug-1998 10:09
Oracle Example
Prerequisites:

 You need to know the values of the following environment variables:

ORACLE_HOME
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.

ORACLE_SID
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
/opt/oracle/oracle/8.0.3
prompt> echo $ORACLE_SID
ORACLE

A simple PHP script using ora_* functions

<?php
  putenv
("ORACLE_SID=ORACLE");
 
putenv("ORACLE_HOME=/opt/oracle/oracle/8.0.3");

 
$conn ora_login("scott""tiger");
 
$curs ora_open($conn);

 
ora_commitoff($conn);

 
$query sprintf(&quotselect from cat");

/* Long version */
/*
  ora_parse($curs, $query);
  ora_exec($curs);
  ora_fetch($curs);
*/
/* Short Version */
  ora_do($conn, $query);

  $ncols = ora_numcols($curs);
  $nrows = ora_numrows($curs);
  printf("
Result size is $ncols cols by $nrows rows.<br>");

  for ($i=0; $i<$ncols; $i++) 
{
    printf("
col[%s] = %s type[%d] = %s<br>",
      $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$iora_getcolumn($curs$i);
    }
   
printf("<br>");
  }
 
?>

<openssl_x509_readora_bind>
 Last updated: Thu, 21 Aug 2003
show source | credits | sitemap | mirror sites 
Copyright © 2001-2003 The PHP Group
All rights reserved.
This mirror generously provided by: http://php.mirrors.ilisys.com.au/
Last updated: Sat 01 Nov 2003 04:13:36 EST EST