PHP  
downloads | documentation | faq | getting help | mailing lists | | php.net sites | links 
search for in the  
previousopenssl_x509_readOra_Bindnext
Last updated: Tue, 03 Sep 2002
view the printer friendly version or the printer friendly version with notes or change language to English | Brazilian Portuguese | Chinese | Czech | Dutch | Finnish | French | German | Hungarian | Japanese | Korean | Polish | Romanian | Russian | Spanish | Swedish | Turkish

LXXII. Funzioni Oracle

Introduzione

Questa estensione permette l'accesso ai server database Oracle. Vedere anche l'estensione OCI8.

Costanti Predefinite

Queste costanti sono definite da questa estensione e sono disponibili solo se l'estensione � stata compilata nel PHP o se � stata caricata dinamicamente a runtime.

ORA_BIND_INOUT (integer)

ORA_BIND_IN (integer)

ORA_BIND_OUT (integer)

ORA_FETCHINTO_ASSOC (integer)

ORA_FETCHINTO_NULLS (integer)

Sommario
Ora_Bind -- effettua il binding di una variabile PHP ad un parametro di Oracle
Ora_Close -- chiude un cursore Oracle
Ora_ColumnName -- restituisce il nome di un campo risultato Oracle
Ora_ColumnSize -- restituisce la dimensione di un campo risultato Oracle
Ora_ColumnType -- restituisce il tipo di un campo risultato Oracle
Ora_Commit -- esegue una transazione Oracle
Ora_CommitOff -- disabilita il commit automatico
Ora_CommitOn -- abilita il commit automatico
Ora_Do -- Parse, Exec, Fetch
Ora_Error -- restituisce il messaggio di errore di Oracle
Ora_ErrorCode -- restituisce il codice di errore di Oracle
Ora_Exec -- esegue dei comandi gi� analizzati su un cursore Oracle
Ora_Fetch_Into -- Scarica una tupla nell'array specificato
Ora_Fetch -- scarica una tupla di dati da un cursore
Ora_GetColumn -- restituisce i dati di un campo acquisito
Ora_Logoff -- chiude una connessione Oracle
Ora_Logon -- apre una connessione Oracle
Ora_Numcols -- Restituisce il numero di campi/colonne
Ora_Numrows -- Restituisce il numero di tuple
Ora_Open -- apre un cursore Oracle
Ora_Parse -- analizza un comando SQL
Ora_pLogon --  Apre una connessione Oracle permanente
Ora_Rollback -- esegue il rollback della transazione
User Contributed Notes
Funzioni Oracle
add a note about notes
[email protected]
05-Aug-1998 03: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.
");

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);
}
printf("
");
}
?>

[email protected]
22-Jan-1999 11:31

<?php



putenv(
"ORACLE_SID=sid1");

putenv(
"ORACLE_HOME=/u01/app/oracle/product/8.0.5");



$handle = ora_plogon(
"SCOTT@sid1", "TIGER")or die;

$cursor = ora_open($handle);

ora_commitoff($handle);



$query = "SELECT * FROM EMP";

ora_parse($cursor, $query) or die;

ora_exec($cursor);



echo "<HTML><PRE>\n";

echo "$query\n\n";

$numcols = 0;

while(ora_fetch($cursor)){

$numcols = ora_numcols($cursor);

for($column=0; $column < $numcols; $column++){

$data = trim(ora_getcolumn($cursor, $column));

if($data =="") $data = "NULL";

echo"$data\t";

}

echo "\n";

}

$numrows = ora_numrows($cursor);

echo "\nROWS RETURNED: $numrows\n";

echo "</PRE></HTML>\n";



ora_close($cursor);



?>

[email protected]
12-Feb-1999 08: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>

[email protected]
05-May-1999 03:37

The real name for Ora_FecthInto is Ora_Fetch_Into.
[email protected]
17-May-1999 04:56

In some situations the way, Kristian K�hntop describes a connection to oracle, dosn't work (in my situation for example): The Server becomes a <B>zombie</B> and any ORACLE connection dosn't work. I have heard about many other complaints about this. Currently I'm using Apache SSL 1.3.4 with PHP 3.0.6-Module. At startup of apache, I first <B>export the $ORACLE_HOME</B> etc. variables.


The solution was, that I <B>DON'T SET THE ENVORNMENT VARIABLES</B>!


With this it works until now.

[email protected]
05-Jun-1999 10:54

<H2>Attention to ora_numrows()</H2>

This function dosn't work as expected (e.g. will return 0 every time). The result of a thread was, that oracle has no such function built in.


But for web applicatin programming you need this.


A good supplement is to use COUNT(*):

<PRE>
If your query is:

SELECT * FROM hugo WHERE bla='schlonz'

then the number of rows is

SELECT COUNT(*) FROM hugo WHERE bla='schlonz'
</PRE>

This functionality will be built in in the next version of <A HREF=">PHPLIB</A>.

[email protected]
29-Jun-1999 06: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.
[email protected]
29-Jun-1999 06:33

ora_numrows($cursor) only works when there's an ora_fetch before. it will return the number of rows resulting from the query.
[email protected]
11-Jul-1999 04:11

<pre>
Since ora_numrows always will return a 0,
The code above could be written as follows;

By saying while ora_fetch do, the fetch will keep grabbing eah consecutive row until false.

Doing a COUNT(*) is poor coding.

&lt;?

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

$conn = ora_logon("scott", "tiger");
$curs = ora_open($conn);

ora_commitoff($conn);

$query = sprintf("select * from cat");

ora_parse($curs, $query);
ora_exec($curs);
$ncols = ora_numcols($curs);

while (ora_fetch($curs))
{
for ($i=0; $i<$ncols; $i++)
{
printf("%s = %s", ora_columnname($curs,$i), ora_getcolumn($curs,$i));
printf("
");
}
}

ora_close($curs);
ora_logoff($conn);

?>

Hope that helps

Wayne Hartley
Teaching and Learning Support Services
Queensland University of Technology
Brisbane, Queensland
Australia, 4000

</pre>

19-Oct-1999 09:16
Actually, [email protected] was CLOSE - the ora_fetch() issued early on doesn't allow the data to be displayed on the page. This do loop would work a little better...

<%

putenv("ORACLE_SID=D704");

putenv("ORACLE_HOME=C:\ORANT");

$conn = ora_plogon("test", "test");

$curs = ora_open($conn);

ora_commitoff($conn);

$query = "select code,descr,to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')
sysdate_x from simplicities";

/* Long version */

ora_parse($curs, $query);

ora_exec($curs);

ora_fetch($curs);



$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));

}

%>

<%

/* remember, we already fetched the first row to get column metrics */

do {

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

printf("%s = %s", ora_columnname($curs,$i), ora_getcolumn($curs,$ i));

printf("
");

}

printf("
");

} while (ora_fetch($curs)) ;

%>

[email protected]
03-Jan-2000 01: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)

Set TNS_ADMIN to the directory with your tnsnames.ora file and see if that dosen't help the TNS unable to connect problems



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


... does the trick for me.


[email protected]
16-Mar-2000 02:31

So everybody posts some select * ...
code so this works on WIN95 + HTTPD nice... getting data and

**** fixing nrows BUG
!Please developers fix it :)

ORA 73 works and 80 doesn't...
(dunno why) so here it is:

************************************

<PRE>
&lt;?php echo "Hi, I'm a PHP script trying to logon on OR73";
$conn = Ora_Logon("login@TNS", "password");
$curs = ora_open($conn);

$table="TABS";

$query = sprintf("select * from $table");

$curs1 = ora_open($conn);
$query1 = sprintf("select count(*) from $table");

echo "...OK 1
";
ora_parse($curs, $query);
ora_exec($curs);
ora_fetch($curs);
ora_parse($curs1, $query1);
ora_exec($curs1);
ora_fetch($curs1);
$ncols1 = ora_numcols($curs1);
$nrows1 = ora_numrows($curs1);

$real_rows=ora_getcolumn($curs1, 0);

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

<gt>");

for ($i=0; $i<$ncols; $i++) {
printf("NASLOV KOLONE: [%s] = %s type[%d] = %s<lt>
<gt>",
$i, ora_columnname($curs, $i),
$i, ora_columntype($curs, $i));
}
printf("<b>SQL upit je: %s</b>
",$query);
printf ("
<table border=1>");
for ($j=0; $j<$real_rows; $j++)
{
printf("<tr>");
for ($i=0; $i<$ncols; $i++)
{
$col = ora_getcolumn($curs, $i);
printf("<td>. %s </td>",ora_getcolumn($curs, $i));
}
printf("</tr>");
ora_fetch($curs);
}
printf("</table>");



echo "<hr>KEWL ON ORACLE!!!";
?>
</PRE>

[email protected]
09-May-2000 07:41

I am also about to start using PHP 3.x.x on Oracle 8.1. I believe the call ora_parse(<cursor>,<sql_stmt_block>[,<defer>]) should enable you to call native functions in oracle including Stored Procedures.
[email protected]
27-Jul-2000 04:05

I contribute the following code example.

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

if ($Session = ora_plogon('Hello', 'World'))
{
print('

Session: ' . $Session . '

');

if ($Cursor = ora_open($Session))
{
print('

Cursor: ' . $Cursor . '

');

$SQL = 'SELECT NAME FROM PERSONS WHERE USERID = \''
. $ID . '\'';
print('

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;
}
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('
GetNameFromUserId End');
return $Result;
}
</PRE>

[email protected]
18-Aug-2000 09: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...

[email protected]
01-Nov-2000 04:01

vishal rajpara ( prangya) (203.199.79.124) Date: 2000-10-20 09:56:32


hi everybody,

i find somesolutions for oracle php connectivity
follow the belove steps:

Note:my server is Redhat6.0/Oracle8.1.5/Php4-beta/Apache.12 etc

1)run dbstart

now one imp thing is other group or server cant access
data so its access problem

so put this line in to your script:

PutEnv("ORACLE_SID=<your oracle sid"); PutEnv("ORACLE_HOME=/opt/oracle/product/8.1.5");

Now check it out its working or not:

if not put also this 2 line in code and check it.
PutEnv("TNS_ADMIN=/opt/oracle/product/8.1.5/network/admin");


Now here is my tnsnames.ora file pls check it out
docs which is inside yout $ORACLE_HOME/network/Doc pls read all information from all file here u find some good
information.

Here is my tnsnames.ora and sql.ora files

1)tnsnames.ora

1 # TNSNAMES.ORA Configuration File:/opt/oracle/product/8.1.5/network/admin/tnsnames.ora
2 # Generated by Oracle Net8 Assistant
3
4 ORACLE.PRANGYA.COM =
5 (DESCRIPTION =
6 (failover=on)(load_balance=off)
7 (ADDRESS_LIST =
8 (ADDRESS = (PROTOCOL = TCP)(HOST = krishna.prangya.com)(PORT = 1521))
9 )
10 (CONNECT_DATA =
11 (SID = oracle)
12 )
13 )
14 EXTPROC_CONNECTION_DATA =
15 (DESCRIPTION =
16 (ADDRESS_LIST =
17 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
18 )
19 (CONNECT_DATA =
20 (SID = PLSExtProc)
21 (PRESENTATION = RO)
22 )
23
)


now
2) # SQLNET.ORA Configuration File:/opt/oracle/product/8.1.5/network/admin/sqlnet.ora
2 # Generated by Oracle Net8 Assistant
3
4 NAMES.DEFAULT_DOMAIN = love.prangya.com
6
7 NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME)
8 automatic_ipc= off
~

now important thing is

start
$lsnrctl start
$lsnrctl dbsnmp_start

$tnslsnr start
$tnsping <your service name>

now run your php scipt its working of not check it out


1)first check that
$sqlplus system/manager
SQL>create tabel emp(name char(20), city char(20));
SQL>try to insert into table one record for checking
SQL> exit

now here is my php script which i use it for testing connection


##############################My Php Script##

&lt;?php
2 PutEnv("ORACLE_SID=oracle");
3 PutEnv("ORACLE_HOME=/opt/oracle/product/8.1.5"); 4
#PutEnv("TWO_TASK=/opt/oracle/product/8.1.5/network/admin/tnsnames.ora");
5 #PutEnv("ORA_NLS=/opt/oracle/product/8.1.5/ocommon/nls/admin/data");
6
7
8 echo "DEBUG:--->connecting oracle
"; 9 echo "DEBUG:--11111--------------------------<>
"; 10 $connection =
OCIPLogon("system","manager");
11 if ($connection == false){
12 echo "DEBUG:------2222----------------------<>
";
13 echo OCIError($connection).":".OCIError($connection)."
";
14 exit;
15 }
16
17
18 #$query = "insert into emp values ('kamal','pappu')";
19 $query = "select * from emp";
20 $cursor = OCIParse($connection, $query); 21 if ($cursor == false){
22 echo OCIError($cursor).":".OCIE

01-Jan-1970 01:00

[email protected]
05-Jan-2001 01: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 "
";
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
OCIExecute($sql_statement)
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>
<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);

?>

[email protected]
14-Jan-2001 01: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.
[email protected]
15-Mar-2001 05: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.

[email protected]
16-Mar-2001 10: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;
}

[email protected]
31-Jan-2002 10:18

Easy Oracle-Connection with my Class for Oracle/mySQL/MSSQL!
my cDBC-Klass is free and with Documentation and sources.

visit my Site:

add a note about notes
previousopenssl_x509_readOra_Bindnext
Last updated: Tue, 03 Sep 2002
show source | credits | stats | mirror sites
Copyright © 2001, 2002 The PHP Group
All rights reserved.
This mirror generously provided by:
Last updated: Fri Sep 6 19:51:45 2002 CEST