Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
 

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:

Example 16

<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

 

Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks
 

Hit Counter