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

If the same SQL statement has to be executed for many sets of values and since parsing is so expensive, it needs to be reused over and over again until the set of values is exhausted. This is achieved by putting placeholders in the SQL statement and binding those placeholders with variables in the program. So, if an INSERT statement like the following is available, it can be reused many times for many different quadruplets of values (col1, col2, col3, col4):

INSERT INTO TBL VALUES (:COL1,:COL2,:COL3,:COL4)

This is achieved by binding the placeholders such as :COL1 and :COL2 to actual program variables and then executing the statement again, whenever the value of the program variables bound to the placeholders changes. Not surprisingly, this is known as binding variables and is performed by the function called oci_bind_by name().

The following is the description of the oci_bind_by_name() function, coming directly from the on-line manual:

DESCRIPTION

bool oci_bind_by_name ( resource stmt, string ph_name, mixed &variable [, int maxlength [, int type]])

oci_bind_by_name() binds the PHP variable variable to the Oracle placeholder ph_name. Whether it will be used for input or output will be determined at run-time and the necessary storage space will be allocated. The length parameter sets the maximum length for the bind. If you set length to -1 oci_bind_by_name() will use the current length of variable to set the maximum length.

The concept is simple. Take a statement handle, placeholder and a variable and bundle it all together with a bind call. These types of variables need additional attention. PHP is weakly typed, so it does not have any types worth mentioning. Oracle, on the other hand, sharply distinguishes between the database types. For most of the Oracle types, type description is not necessary.

However, if the application uses more complex Oracle types, type description is necessary. Those types are ROWID, object types, cursors and large objects, also known as LOB types. Term LOB is somewhat confusing as the term LOB object is also used for the locator objects, used to access various forms of large objects (BLOB,CLOB, BFILE).  In further text the term LOB will refer to the data type itself while locator will be separately mentioned to clarify the difference wherever there is a possibility of confusion.

The following list contains the flags used to describe those variable types:

  •  OCI_B_FILE - for BFILEs.

  • OCI_B_CFILE - for CFILEs.

  • OCI_B_CLOB - for CLOBs.

  • OCI_B_BLOB - for BLOBs.

  • OCI_B_ROWID - for ROWIDs.

  • OCI_B_NTY - for named datatypes.

  • OCI_B_CURSOR - for cursors, that were created before with oci_new_cursor().

These Oracle data types are special and the user cannot bind them to regular variables. They are bound to specially allocated descriptors, allocated by the oci_new_descriptor() or oci_new_cursor() functions. Both functions are shown in the section devoted to examples with cursors and LOB variables at the end of this chapter.

The first example in this section is shown below. In Example 15, the user binds the PHP variable $job to the placeholder named “:job”.

Example 15

$ cat ./example15.php

#!/usr/local/bin/php

<?php

    $db=oci_new_connect("scott","tiger");

    $job="C%";

    $SQL="select ename,job,hiredate,sal from

          emp

          where job like :job

          order by sal";

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

    if (!oci_bind_by_name($sth,":job",$job,32)) {

       $err =oci_error($sth);

       die($err['message']);

    }

    @oci_execute($sth);

    if (!defined($sth)) {

       $err =oci_error($sth);

    }

    while ($row=oci_fetch_array($sth)) {

          echo $row[0],"\t",$row[1],"\t",$row[2],

                  "\t",$row[3],"\n";

    }

    echo "This statement returned ",

          oci_num_rows($sth)," rows\n";

?>

Execution of Example 15 gives the following result:

$ ./example15.php

SMITH   CLERK   17-DEC-80    800

JAMES   CLERK   03-DEC-81    950

ADAMS   CLERK   23-MAY-87    1100

MILLER  CLERK   23-JAN-82    1300

This statement returned 4 rows


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 -2017 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