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 

Executing SQL

Many modern databases today use dialects of SQL, “Structured Query Language”. SQL statements are executed and their execution follows the same phases, regardless of the underlying database:

  • Parse Phase - During the parse phase, Oracle opens the statement handle, checks whether the statement is OK (both syntactically and whether the involved objects exist and are accessible) and creates an execution plan for executing this statement. Parse call does not return an error if the statement is not syntactically correct.  Parsing can be a very expensive operation that takes a lot of resources to execute. Special problem are so called “hard parses” which happen when there is no previously parsed version of the SQL to reuse.

  • Bind Phase - Once the plan is syntactically created, Oracle gathers the parameters from the client program needed for the execution. It makes the addresses of the program variables “known” to Oracle.

  • Execute Phase - During the execute phase, Oracle executes the statement, reports any possible errors, and if everything is as it should be, forms the result set. Unless the SQL statement being executed is a query, this is the last step of the execution.

  • Define Phase - Define is analogous to binds, only “output oriented”. The OCI define makes addresses of the output variables “known” to the Oracle process in order to make it possible to the fetch call to know where to put the output variables. The define call is frequently skipped and is largely considered unnecessary because of the automatic variables allocation in PHP.

  • Fetch Phase - During the fetch phase, Oracle brings the rows of the result to the program and makes them accessible by the PHP interpreter. Once more, the define and fetch phases are relevant for queries only. The Oracle OCI interface and the PHP OCI8 module contain calls to facilitate each of those phases.

The first thing to do with a SQL commandis to parse it.  SQL statements are parsed by calling the oci_parse()function. Here is the syntax:

$sth=$oci_parse($db,$SQL);

The oci_parse() function returns a statement handle, sometimes called a cursor, and parses the SQL statement in $SQL at the database pointed to by the $db database handle, returned by one of the connect functions. Contrary to what is expected, the parse function does not return an error if the function is syntactically incorrect or if underlying objects do not exist. An error is returned only after called to the oci_execute() function.

This was an optimization to bundle two calls together and save a trip over the network. In the large applications that execute numerous SQL statements, it is a significant performance improvement.

The OCI8 module uses the oci_execute() function to execute SQL statements. This syntax is extremely simple:

bool oci_execute (resource stmt [, int mode])

The oci_execute() function takes the statement handle generated by the oci_parse()function and executes it. The second argument is mode. What is mode? In PHP, the oci_execute() function issues an automatic commit after a successful execution.  This is the same thing as having AUTOCOMMIT set to ON in SQL*Plus.

This behavior is highly undesirable and should be prevented.  To prevent such a behavior, specify OCI_DEFAULT as the mode argument.  The illustration is presented below:

         $sth=oci_parse(”delete from emp”);
 

         /* This will not commit */
         oci_execute($sth,OCI_DEFAULT); 

          /* The next command will commit */
         oci_execute($sth);


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