 |
|
ss
Oracle Tips by Burleson |
Binding
and Defining Variables
Oracle bind passes the variables by reference.
This is understandable if how Oracle obtains the value of a program
variable is known. Oracle can get the value of a variable if it knows
three things: the address of the value, the length in bytes of the
value and to what type to convert the value to. The format of the
statement is this:
oci_bind_by_name($sth,":job",$job,32);
The variable $job is passed by reference,
which means that its address is passed to the bind call. The number
“32” means that the maximum length of the string found at the given
location is 32 bytes.
The next example shows passing parameters to an
Oracle procedure and an anonymous PL/SQL block. For this example,
Example13a.php and Example13.php from the last chapter are
reused. These examples are the framework for logging in the database
and invoking another script. So, Example13a.php established
database login and invoked the script called “query1.php”.
This script executed a query and displayed the results on the screen.
Instead of invoking query1.php, the script invoked this time is called
“analyze_schema.php” and offers the choice of schemas to
compute statistics. In other words, in order to transform
Example13a.php into Example16.php, the user replaces the following
line:
header('Location: query1.php');
with the line:
header('Location: analyze_schema.php');
An Oracle login is shared with the original
because of the session variables. Once the schema is selected, the
PHP script computes statistics for it using the DBMS_STATS package
available as of Oracle RDBMS8i.
Bind establishes the connection between the
database field and the HTML form field. The following example reveals
what the file analyze_schema.php looks like:
<html>
<head>
<title>Analyze Schema</title>
</head>
<body>
<?php
require_once('OCI_Session.php');
session_start();
$dbh=$_SESSION['dbh'];
$dbh->refresh();
if (!$dbh->db) {
die($dbh->err['message']);
}
$schema="";
$STATS="begin
dbms_stats.gather_schema_stats(
ownname => :SCHEMA ,
estimate_percent => :PERCENT,
block_sample => TRUE,
method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',
degree => :DEGREE,
cascade => TRUE);
end;";
$USRS="select distinct owner as username
from dba_tables
order by 1";
try {
$stat=oci_parse($dbh->db,$STATS);
if (!$stat) {
$e=oci_error($stat);
throw new Exception ('PARSE STAT:'.$e['message']);
}
$usr=oci_parse($dbh->db,$USRS);
if (!$usr) {
$e=oci_error($usr);
throw new Exception ('PARSE USR:'.$e['message']);
}
if (!oci_define_by_name($usr,"USERNAME",$schema)) {
$e=oci_error($usr);
throw new Exception ('DEFINE USR:'.$e['message']);
}
if (!oci_execute($usr)) {
$e=oci_error($usr);
throw new Exception ('EXEC USR:'.$e['message']);
}
} catch (Exception $e) { ?>
<br><br><br>
<center>
<?php die($e->getMessage()); ?>
</center>
<?php
}
if ( empty($_POST['ANALYZE'])) { ?>
<form action=”<?=$_SERVER['PHP_SELF']?>” method="post">
<br><br><br><br<br>
<center>
<h2> Choose Schema To Analyze</h2>
<hr>
USERS:<select name="USERS" >
<?php
while (oci_fetch($usr)) { ?>
<option value=”<?=$schema?>” > <?=$schema?></option>
<?php } ?>
</select>
<P>
PERCENT:<input type="text" name="PERCENT"
value="5" size="5" maxlength="4">
DEGREE:<input type="text" name="DEGREE"
value="1" size="5" maxlength="5">
</P><br>
<input type="submit" name="ANALYZE" value="SUBMIT">
</center>
<hr>
</form>
<?php }
else {
oci_free_statement($usr);
oci_bind_by_name($stat,":SCHEMA",$_POST['USERS'],32);
oci_bind_by_name($stat,":PERCENT",$_POST['PERCENT'],32);
oci_bind_by_name($stat,":DEGREE",$_POST['DEGREE'],32);
oci_execute($stat);
$e=oci_error($stat);
if ($e['code'] != 0 ) {
die($e['message']);
} else { ?>
<br><br><br><br>
<center>
<h2>Schema <?=$_POST['USERS']?> was successfully
analyzed.</h2>
<?php
$_POST['ANALYZE']=NULL;
?>
<a href="<?=$_SERVER['PHP_SELF']?>”>
Analyze another schema.</A>
</center>
<?php
}
}
?>
</body>
</html>
This rather large example will be used to explain several
things in this chapter. Obviously, it defines a literal $STATS, an
anonymous PL/SQL block analyzing statistics for the schema defined by
the placeholder, :SCHEMA. The choice of possible schemas is offered by
executing another SQL command, the one defined in the literal $USRS.
These two SQL blocks are parsed in the now familiar try and catch
block. Thus, a form which looks like the one below is produced.
A drop down list called “USERS” is available, and
if pressed, all schemas in the database containing tables are shown.
Once a schema is chosen and the submit button is pressed, the fields
in the form are bound to the placeholders in the PL/SQL block. The
built-in dbms_stats package is invoked, and if everything is
executed without a problem, a screen appears like the one in the
illustration below.
This screen reports the status and offers the
possibility of analyzing another schema by following the link. What
happens if the wrong parameters are entered? Because schemas offered
from the existing schemas and an existing schema can only be chosen,
an incorrect schema cannot be entered. If an incorrect degree of
parallelism is entered, Oracle quietly ignores it.
What happens if an incorrect percentage is
entered? To illustrate this point an incorrect percentage of 110% of
the table data is entered to analyze the data. The results are shown
in the screen below:
An Oracle error appears revealing exactly where
the script went wrong.
This example contains the calls to
oci_define_by_name(), which implements the define phase in a query
execution. What are defines and what is the difference among bind and
define?
Variables that are bound to placeholders are
needed for the statement execution, and Oracle can both read from and
write to the variables made known to Oracle by a bind call. A bind
call must bind variables to a corresponding placeholder.
Variables that are known to Oracle by a define
call cannot be read from and have no placeholder. Explicit define
calls are rarely used and are used in this example for educational
purposes only. Normally, calls like oci_fetch_array() described
later in this chapter, allocate the output variables by themselves.
This example was also invoked from Example16.php
which established a login to the Oracle database. Example 16 is a
re-engineered Example 13, which is used throughout this book to
establish a login and invoke the next script. Now that the two HTML
forms, HTML links, PL/SQL procedures and complex error handling are
available, the whole example suddenly looks like a small application.
All the examples in this book are available on the accompanying CD and
can be reused freely.
See
code depot for complete scripts
The above book excerpt is from:
Easy Oracle
PHP
Create Dynamic Web Pages with Oracle Data
ISBN
0-9761573-0-6
Mladen Gogala
http://www.rampant-books.com/book_2005_2_php_oracle.htm
|
