|
|
LXXIII. Oracle functionsIntroduction
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.
User Contributed Notes Oracle functions |
add a note |
kk at shonline dot de
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("select * 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("val[%d, %d] = %s * ", $j, $i, ora_getcolumn($curs,
$i);
}
printf(" ");
}
?>
|
|
stevel at nettek-llc dot com
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);
?>
|
|
cord at ragesoft dot com
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>
|
|
sthibaudeau at sqli dot fr
05-May-1999 03:37 |
|
The real name for Ora_FecthInto is Ora_Fetch_Into.
|
|
aulbach at unter dot franken dot de
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.
|
|
aulbach at unter dot franken dot de
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>.
|
|
denkwerk-hamburg at denkwerk dot com
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.
|
|
denkwerk-hamburg at denkwerk dot com
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.
|
|
w dot hartley at qut dot edu dot au
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.
<?
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)) ;
%>
|
|
lmg at webfarm dot com
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.
|
|
kfutivic at delfin dot hr
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>
<?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>
|
|
tnyongesa at uswest dot net
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.
|
|
elf at messer dot de
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>
|
|
rs at aelea dot com
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...
|
|
vishalrajpara at usa dot net
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##
<?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
|
|
|
dmearls at yahoo dot com
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.
<?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);
?>
|
|
aedunov at cv dot jinr dot ru
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.
|
|
jjuffermans at chello dot com
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.
|
|
jjuffermans at chello dot com
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;
}
|
|
php at fireball88 dot de
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:
|
|
thebestion at gmx dot de
09-Oct-2002 04: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]; }
|
|
04-Dec-2002 05:09 |
|
A clean install of oracle and php works fine but an install on clients
system that has multiple instances of oracle, plenty of other software and
PHP can not connect to the DB.
<b>Warning</b>: Oracle:
Connection Failed: ORA-12203: TNS:unable to connect to destination
in <b>c:\inetpubvirtual\wwwroot\test.php</b> on line
<b>22</b>
Cannot connect to The
database.
putenv("ORACLE_HOME=d:/oracle/ora81"); putenv("ORACLE_SID=TESTORA");
Line
22: $conn = ora_logon("internal","oracle"); also
tried $conn =
ora_logon("internal@TESTORA","oracle");
This is
on Windows NT 4.0 server with oracle 8.1.5 i
Comments on this
issure are welcome
|
|
matthew_mcginnis at hotmail dot com
11-Apr-2003 01:53 |
|
Environment: WinNT 4.0 SP5 Oracle8i (NOT on the web
server) Apache 1.3.27 PHP 4.3.1 (Manual Install - CGI)
I followed the directions closely on the manual installation, but
it did not work for me. PHP worked in general, but the Oracle functions
only worked after I moved all the dll files to the same directory as the
Apache executable. The php.ini file was in C:\winnt as directed.
I
tried putting the dlls in %SYSTEMROOT% (C:\winnt\system32) but this failed
miserably. I also tried winnt\system and winnt. The only thing that
worked for me was to finally put them in the Apache folder. Hope that
helps.
BTW...I checked the extensions_dir and all other parameters
over and over...they are all correct.
|
|
john at no_gay_refereces_please dot com
23-Apr-2003 08: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.
|
|
add a note |
| |