PL/SQL: Oracle database and PHP

1. Introduction:

As PHP connects to MySQL, it connects also with Oracle:
With MySQL: <?php $conn = @mysql_connect("localhost", "root", "mypasswd");?>
With Oracle-PHP: $conn = oci_connect('user-name', 'password', '//localhost:1521/XE');
or: Oracle-JDBC: 
	OracleDataSource ods = new OracleDataSource();  
	SQL: 	ods.setURL("jdbc:oracle:thin@host:1521:XE","user-name","password");
	PL/SQL: ods.setURL("jdbc:oracle:thin:user-name/password@host:1521/XE");
	Connection conn = ods.getConnection();
	

To use this  Oracle Call Interface (OCI) connection, once php is installed:
Go to php.ini file ( in this case in C:\WINDOWS\ directory), and uncomment
- Uncomment extension=php_oci8.dll (delete the ;)
- Then start the Apache.

Test your connection with this:
	<?php
	$conn = oci_connect('User-name', 'password', '//host:1521/XE');
	if (!$conn){ 
	echo "The Oracle connection failed.";
	exit;}
	else { 
	echo 'The connection to Oracle succeed.';
	}
	?>

Here is an example (phys_search.php) allowing the connection to the XE Oracle database 
and Physicists table. The form used gives two text area to search for a physicist 
by entering her/his first and last names (or some first initials).

Save the file in a directoy related web server (http://localhost:8033/PLSQL/)
and  double-click on the phys_search.php file

2. Related program:

<h2>Information Physicist Search by first and last names</h2>

<?php 
// The form:
function get_input($first = "", $last = "") 
{
echo <<<END
<form action="phys_search.php" method="post">
<table><tr><td>
First name:</td><td>
<input type="text" name="first" value="$first"></td></tr>
<tr><td>
Last name:</td><td>
<input type="text" name="last" value="$last"></td></tr>
<tr><td><br />
  </td><td>
<input type="submit"></td></tr></table>
</form>
END;
}

if(!isset($_REQUEST['first'])) {
echo "<b>Enter two arguments: complete first and last names, or 
initial substrings.</b><p><br />"; 
get_input();
}
else {
// check whether the input fields are not empty
if (empty($_REQUEST['first']) or empty($_REQUEST['last'])) {
echo "<b>Re-enter informations text in both fields:</b><p><br />"; 
get_input($_REQUEST['first'], $_REQUEST['last']);
}
else {
$conn = oci_connect('user-name', 'password', '//host:1521/XE');
// Are we connected?
if (!$conn) {
echo( " Unable to locate the table Physicists. " );
}
else{
echo( " <i> We are connected ... </i><br />" );
}

// execute the function that calls the stored procedure 
$phys = get_Physicists($conn, $_REQUEST['first'], $_REQUEST['last']);

// display results
print_results($phys, 'Result: Physicist Information:');


// close the database connection
oci_close($conn);
}
} 


// The following functions calls the  procedure that uses a ref cursor 
//to fetch records

function get_Physicists($conn, $firstname, $lastname)
{
$sql = "BEGIN get_phys_info(:first_name, :last_name, :refcur); END;";
$stmt = oci_parse($conn, $sql);
oci_bind_by_name($stmt, ':first_name', $firstname, 20);
oci_bind_by_name($stmt, ':last_name', $lastname, 25);
$refcur = oci_new_cursor($conn);
oci_bind_by_name($stmt, ':REFCUR', $refcur, -1, OCI_B_CURSOR);
oci_execute($stmt);
oci_execute($refcur, OCI_DEFAULT);
oci_fetch_all($refcur, $physicistrecords, null, null, OCI_FETCHSTATEMENT_BY_ROW);
return ($physicistrecords);
}


// To print the results
function print_results($returned_records, $report_title)
{
echo '<h3>'.htmlentities($report_title).'</h3>';
if (!$returned_records) {
echo '<p>No Records Found</p>';
}
else {
echo '<table border="1" cellspacing = "3" cellpadding = "7">';
foreach ($returned_records as $row) {
echo '<tr>';
foreach ($row as $field) {
print '<td>'.
($field ? htmlentities($field) : ' ').'</td>';
}
}
echo '</table>';
}
}

?>

3. Related result:

Fill in the form:


Related output