|
|
 |
User Contributed Notes
Oracle |
  |
[email protected]
05-Aug-1998 02: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("
");
}
?>
|
|
[email protected]
22-Jan-1999 10: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 07: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 02:37 |
|
The real name for Ora_FecthInto is Ora_Fetch_Into.
|
|
[email protected]
17-May-1999 03: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 09: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 05: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 05: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 03: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 08: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 12: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 01: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>
|
|
[email protected]
09-May-2000 06: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 03: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 08: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 03: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
|
|
|
|
[email protected]
05-Jan-2001 12: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);
?>
|
|
[email protected]
14-Jan-2001 12: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 04: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 09: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 09:18 |
|
Easy Oracle-Connection with my Class for Oracle/mySQL/MSSQL!
my
cDBC-Klass is free and with Documentation and sources.
visit my
Site:
|
|
|
|
| |
openssl_x509_read