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);