|
|
 |
User Contributed Notes
Oracle functions |
  |
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("
");
}
?>
|
|
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);
?>
|
|
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>
|
|
05-May-1999 02:37 |
|
The real name for Ora_FecthInto is Ora_Fetch_Into.
|
|
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.
|
|
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>.
|
|
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.
|
|
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.
|
|
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)) ;
%>
|
|
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.
|
|
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>
|
|
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.
|
|
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>
|
|
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...
|
|
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
|
|
|
|
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);
?>
|
|
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.
|
|
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.
|
|
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;
}
|
|
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