BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

Prepare and Execute

Oracle Tips by Burleson Consulting

The prepare and execute methods are used when the SQL commandneeds to be executed multiple times or when PL/SQL procedures need to be executed. These methods are also needed when the script has to deal with complex data types such as LOB types or dynamic cursors. The syntax is shown as follows:

$stmt=$db->Prepare($SQL);
$rs=$db->Execute($stmt,$bind_array
);

This syntax is simple enough, but it does not explain what the prepare method is really needed for.  Another example is shown as follows:

$SQL=”insert into TBL values (:C1,:C2,:C3)”;
$data=array( array(“a1”,”a2”,”a3”),
                      array(“b1”,”b2”,”b3”),
                      array(“c1”,”c2”,”c3”),
                      array(“d1”,”d2”,”d3”));
$stmt=$db->Prepare($SQL);
foreach($data as $row) {
     $db->Execute($stmt,array(“C1”=>$row[0],
                                                “C2”=>$row[1],
                                                “C3”=>$row[2]));
}

In this example, the expensive “parse” call is executed only once as a result of the prepare method.  The statement is parsed once, but executed multiple times; once for each element of the array, $data.  Normally, for statements executed only once, Prepare() is not needed.  Preparing SQL statements is done for two reasons: to eliminate the unnecessary and expensive parse calls and to use complex binds.

The next situation in which the prepare and execute methods are needed is when executing a stored procedure with output parameters.  To show how this is accomplished, a PL/SQL procedure with an output parameter is needed. The procedure below is one such procedure:

Example 28

create or replace
procedure cleanup(job_desc in varchar2, rows_cleaned out number)
as
begin
delete from emp where job like job_desc;
rows_cleaned:=SQL%ROWCOUNT;
end;
/

The procedure takes two arguments: one input, the job to remove from the EMP table; and one output, the number of rows that have been deleted. The PHP5 script that uses the procedure above is Example 26, slightly modified. The script also introduces several methods for transaction handling:

Example 28 (Cont.)

#!/usr/local/bin/php
<?php
require('adodb/adodb.inc.php');
require('adodb/adodb-exceptions.inc.php');
$DSN="oci8://scott:tiger@local"; 

$SQL="begin
         cleanup(:job,:numrec);
      end;";
$job="C%";
$numrec=-1; 

try {
    $db = NewADOConnection("$DSN");
    $db->BeginTrans();
    $sth=$db->Prepare($SQL);
    $db->InParameter($sth,$job,'job');
    $db->OutParameter($sth,$numrec,'numrec');
    $db->Execute($sth);
}
catch (Exception $e) {
    $db->RollbackTrans()
;
    die($e->getMessage());
}  

print "Cleanup deleted $numrec records.\n";
$db->CommitTrans()
;
?>

The first among the new methods shown here is BeginTrans().  This method begins a transaction and turns the autocommit off. Without starting a transaction with BeginTrans(), the transaction is automatically committed, which is not the desired result. It is, however, an expected behavior because both OCI8 and PEAR DB behave the same way.

The ADOdb manual recommends using StartTrans() instead, but StartTrans() does not work very well with Oracle RDBMS. The difference is that in the case of any SQL error, StartTrans() should rollback the transaction automatically. Every transaction is rolled back, automatically, regardless of any errors.  In other words, every transaction will fail.

The next two methods are InParameter()and OutParameter(). These two methods are used for explicit binding of program variables to input and out parameters of PL/SQL procedures.  They can also be utilized for binding dynamic cursors (REF cursors). The syntax for these two methods is shown as follows:

$db->InParameter($statement,$variable,$placeholder,$max_length,$type);
$db->OutParameter($statement,$variable,$placeholder,$max_length,$type);

If the maximum length is not specified, it defaults to 4000, the maximum size of the Oracle VARCHAR2 type. The “$type” variable is database type, inherited from the OCI8 module (ADOdb uses OCI8 the same way PEAR DB does) and is normally specified for LOB types only. These two methods are based on the oci_bind_by_name()function discussed in Chapter 3.

In the script above, the statement

$db->OutParameter($sth,$numrec,'numrec');

binds the variable $numrec with the placeholder  :numrec. When this script is executed, the result is completely predictable:

SQL>  select count(*) from emp;
  COUNT(*)
----------
        14 

SQL>  !example28.php
Cleanup deleted 4 records. 

SQL>  select count(*) from emp;
  COUNT(*)
----------
        10 

SQL>

There is another method available called Parameter(), which is deprecated according to the documentation, and a low level method called “bind” which is, more or less identical to the oci_bind_by_name() method.

The two remaining methods, CommitTrans() and RollbackTrans()are self explanatory. They terminate a transaction by issuing a commit or rollback, respectively.

At the beginning of this section, it was noted that prepare and execute are used for avoiding parse calls when executing SQL multiple times, for calling stored procedures with OUT parameters and for dealing with special data types like LOB data and REF cursors. ADOdb libray has specific methods for dealing with these situations. The methods are PrepareSP() and ExecuteCursor(). These methods will be demonstrated in our next example.

Chapter 3 presented Example 17 dealing with REF cursors by using the oci_bind_by_name() function from the OCI8 bundled module. Example 17 calls the PL/SQL procedure PHP_DEMO1.DEMO1, which took two arguments, the first one was an input argument called “title”, and the second one was the output parameter storing a REF cursor. In the PHP script, the procedure was executed, returned a REF cursor, which was then executed and the results retrieved and printed out. The source for the PHP_DEMO1.DEMO1 package is available in Chapter 3.  Example 17 is rewritten below using the ADOdb library, using the methods mentioned above.

Example 29

#!/usr/local/bin/php
<?php
require('adodb/adodb.inc.php');
require('adodb/adodb-exceptions.inc.php'); 

$DSN="oci8://scott:tiger@local";
$title="CLERK";
$SQL="begin
         php_demo1.demo1(:title,:csr);
      end;"; 

try {
    $db = NewADOConnection($DSN);
    $db->SetFetchMode
(ADODB_FETCH_NUM);
    $db->BeginTrans();
    $sth=$db->PrepareSP
($SQL);
    $db->InParameter($sth,$title,'title');
    $rs=$db->ExecuteCursor
($sth,'csr');
}
catch (Exception $e) {
    $db->RollbackTrans()
;
    die($e->getMessage());
}
$nrows=$rs->RecordCount();
while ($row=$rs->FetchRow()) {
    foreach($row as $col) {
       echo "$col\t";
    }
    echo "\n";
}

echo "This statement returned $nrows rows\n";
?>

This script is a few lines shorter and much cleaner than the OCI8 version, and yet it does exactly the same thing. There are a few new methods which are Oracle-specific.  The PrepareSP() method prepares a call to stored procedure (thus the letters ”SP” at the end) and is supported only for Oracle and MySQL databases. The method invoked to execute SQL is not Execute(), but ExecuteCursor(). The syntax for the PrepareSP() method is extremely simple:

$stmt=$db->PrepareSP ($SQL);

This method is meant only for parsing calls to PL/SQL procedures with the REF cursor or LOB arguments. The ExecuteCursor() method was created specially to support REF cursor  handling. The syntax is, as in the case with PrepareSP() method, extremely simple:

$rs=$db->Execute($statement_handle,'REF_cursor_placeholder');

The returning value is a result set which contains the result of the executed cursors, bound to the placeholder which is passed to the ExecuteCursor() method as the second argument.  This result set can be handled the same way queries are normally handled with ADOdb.  The last thing to notice in this example is the use of

$db->SetFetchMode(ADODB_FETCH_NUM);

to retrieve only the array indexed by numbers and not the column names. This is the same thing that was accomplished by setting the fetch mode to OCI_NUM in Example 17.

The next case which requires the use of the PrepareSP() and InParameter()or OutParameter()methods is handling LOB columns. As was said before, ADOdb is based on the existing PHP5 functions used to support various databases. For Oracle RDBMS, there is the OCI8 module.  This means that for handling LOB columns the InParameter() or OutParameter() methods would be used like this:

$db->InParameter($stmt,$clob,'clob',-1,OCI_B_CLOB);
                                       - or -
$db->OutParameter($stmt,$clob,'clob',-1,OCI_B_CLOB);

So far this section has been demonstrating the handling of LOB columns by showing the HTML form to upload a text file into SCOTT.POETRY table, created in Chapter 3. In Chapter 4, the login form was rewritten to use PEAR DB. There is no need to rewrite it again, because PEAR DB and ADOdb use the same form of DSN. In other words, the database connection can be shared between a script that uses PEAR DB and a script that uses ADOdb.

The only line in Example23.php that needs changing is the one that reads:

header('Location: pear_file.html');

Predictably, the line will be changed to:

header('Location: adodb_file.html');

The file “pear_file.html” is a HTML form with two HTML fields and the file “pear_file.php” in the “action” part of the <FORM> tag. If a copy is made of “pear_file.html” and then called “adodb_file.html”, it can be changed in such a way to invoke “adodb_file.php” instead of “pear_file.php”. So, in order to change Example 23 into Example 30, the only thing to rewrite is the file “pear_file.php”. Here it is:

Example 30

<html>
<head>
  <title>Upload File (ADOdb)</title>
</head> 

<body>
  <?php
  require_once('adodb/adodb.inc.php');
  require_once('adodb/adodb-exceptions.inc.php');
  session_start();
  $db=NewADOConnection("oci8");
  $DSN=$_SESSION['DSN'];
 

  $INS="insert into poetry(file_desc,file_data)
        values (:pdesc,:cont)";
  $clob=file_get_contents($_FILES['file']['tmp_name']); 

  try {
        $db->Connect($DSN['database'],
                     $DSN['username'],
                     $DSN['password']);
        $db->BeginTrans();
        $sth=$db->PrepareSP
($INS);
        $db->InParameter($sth,$_POST['desc'],'pdesc');
        $db->InParameter($sth,$clob,'cont',-1,OCI_B_CLOB);
        $db->Execute($sth);
  }
  catch (Exception $e) {
         $db->RollbackTrans()
;
         die($e->getTraceAsString());
  }
  $db->CommitTrans();
  ?>
 

  <center>
    <h2>File <?=$_FILES['file']['name']?>
     uploaded successfully!</h2>
  </center>
</body>
</html>
 

There are only three things worth noting in this script:

Binding of a CLOB column to the placeholder. This is done almost exactly the same way as in the oci_bind_by_name() function from the OCI8 module. The OCI_B_CLOB flag is defined in the OCI8 module.

Using the PrepareSP method instead of Prepare(). When binding LOB variables or using OUT parameters, the “SP” variety of the prepare method must be used.

Using the getTraceAsString() method from the Exception class  in the “die” function instead of the getMessage() method. Why is that?  There are several database operations in the “try” block”; one connect, two binds and an execute. If an Oracle error occurs, it is be nice to know the exact line in the script where the error appeared, and the circumstances of the error.  Below is the output of the getTraceAsString() method. The error was caused by deliberately changing the name of the “pdesc” placeholder into “desc” which is short for “describe”, a reserved word.

#0 /usr/local/PHP/adodb/adodb-exceptions.inc.php(76): adodb_throw() #1 /usr/local/PHP/adodb/adodb.inc.php(859): adodb_throw('oci8', 'EXECUTE', 1745, 'ORA-01745: inva...', Array, false, Object(ADODB_oci8)) #2 /usr/local/PHP/adodb/adodb.inc.php(833): ADODB_oci8->_Execute(Array, false) #3 /home/mgogala/work/PHP/adodb_file.php(26): ADODB_oci8->Execute(Array) #4 {main}

The above result reveals that the Oracle error ORA-1745 occurred at line 26 of the script “adodb_file.php”.  This error is shown below: 

$ oerr ora 1745
01745, 00000, "invalid host/bind variable name"
// *Cause:
// *Action:

So, this method reveals information about the nature and location of the error, which is very useful for debugging.  The “oerr” command is available on most of  Oracle installations (it is not available on MS Windows). For details about the “oerr” command, please see the Oracle Concepts manual.

Sometimes, when the LOB value is large, the same method used to insert a LOB value in Chapter 3 has to be used with ADOdb. The LOB column was first initialized by inserting an empty LOB by using the empty_clob() PL/SQL method and then updating it by using a special bind. This method is also supported by ADOdb.

ADOdb has updateBLOB(), updateCLOB() and updateBLOBFile() methods to perform updates for various types of  LOB columns.

Semantics of those methods is shown as follows:

$db->updateCLOB($table_name,
                               $LOB_column_name,
                               $LOB_column_value,
                               $where condition);

In case of the POETRY table, the whole thing would look like this:

insert into POETRY(FILE_DESC,FILE_DATA
                 values(:PDESC,empty_clob());
 

$db->updateCLOB('POETRY',
                                'FILE_DATA',
                                $clob,
                                'FILE_ID=POEM_ID_SEQ.CURRVAL');

 To understand this example, it is important to remember the schema structure laid out in Chapter 3. The FILE_ID column is populated by a “BEFORE INSERT trigger which uses the sequence POEM_ID_SEQ to generate the next number.

The other two ADODdb methods to update LOB values behave the same way as updateCLOB() and have the same syntax.

SEE CODE DEPOT FOR FULL SCRIPTS


The above book excerpt is from  "Easy Oracle PHP: Creating Dynamic Web Pages with Oracle Data". 

You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:

http://www.rampant-books.com/book_2005_2_php_oracle.htm

 


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

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

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter