Introduction to Bind calls with the Oracle Perl DBI

Oracle Tips by Mladen Gogala

Binding Variables with Oracle DBI

The problem at hand, the one that will be solved by binding variables is the following: we need to find out name, job, department name and salary for an employee, by the employee number. The employee number is read from standard input. The SQL statement that we will use to solve the problem looks like this:

The expression ":EMPNO" is called a "placeholder" and has to be defined before the statement can be executed.  Connecting a placeholder with a program variable is known as binding . Perl supports several types of binds. Let's see the most popular and the simplest one first:

Example 3

This script produces the following output:

bash-3.00$ ./ttt

Enter EMPNO:7934

Employee Name       Job                   Department             Salary

-------------------------------------------------------------------------------

MILLER                  CLERK           ACCOUNTING      1300

In case that a non-existing employee number is entered, no output is produced. So, here we have a script that reads a value from the standard input, and needs that value to complete the SQL execution. The placeholder used here is a simple question mark: "?" :"WHERE empno=?". That is a DBI specific trick, later emulated in other languages and frameworks, like PEAR::DB. The trick is that DBI uses so called positional bind, in which each question mark is a placeholder, replaced by  its sequence number. It can't be simpler then that, but there is a problem:

If our SQL statement was something like:

 WHERE empno = - and  ?<10000

2 different placeholders would be needed. If a condition includes the same placeholder appearing in several places in the same SQL, positional binds can not be used, we have to resort to the named binds. Named binds differ from positional binds in such a way that placeholders are not simple question marks but are named instead. As a matter of fact, our first example was a part of a named bind sequence. The statement below

will lead to $sth->bind_param(":EMPNO", $empno); Other then that, positional binds are identical to the named ones. I also find named binds easier to follow and less confusing then a forest  of question marks that I have to count by using my fingers in order to really understand the underlying SQL. I have only 10 fingers, which is somewhat limiting when it comes to really complex SQL statements. I also adopted the naming convention which mandates naming placeholders the same as the variables they're being bound to, only in capital letters, just like in the code snippet above.

So far, Oracle has just been reading script variables and using them in SQL. What happens if Oracle needs to write something into a script variable? In other words, what happens if the placeholder is in place of the input/output  parameter in PL/SQL procedure? It turns out that we need somewhat more complex bind. In order to do that, let's  take a look at the following  PL/SQL script:

In a Perl script, we would have to call it, by using a snippet like this:

my $SCR="begin

                            dbitest(:EMPNO,:DNAME);

                    end;";

Unfortunately, the bind method we've studied so far cannot help us here. In other words, using calls like $sth->bind_param(":ENAME",$ename) and $sth->bind_param(":DNAME", $dname) would produce nothing but a nasty error message. Let's modify our example 3 and demonstrate:

Example 3a (Doesn't work)

The execution produces the following:

That is not nice. So, how do we fix that? DBI has another type of bind method, used for PL/SQL procedures, LOB parameters or ref cursors. This method is called bind_param_inout. There are  two important differences between bind_param and bind_param_inout:

• Bind_param takes a program variable as an argument while bind_param_inout takes a REFERENCE to program variable as an argument. In other words, we're passing a pointer to variable to Oracle, not the variable itself.
   
• Specifying the maximum length of the bind variable is mandatory for bind_param_inout.

Bind using the value of the variable, like bind_param are also called binds by value, as the value of the variable is made available to Oracle. Binds using bind_param_inout are also known as binds by reference or binds by address as reference (address) of a Perl variable is made known to Oracle.

So, let's now modify our example 3a  to use bind by reference:

Example 3b

The execution now proceeds in much nicer fashion:

bash-3.00$ ./ttt

Enter EMPNO:7934

Department name: ACCOUNTING

The only difference is the use of bind_param_inout  instead of bind_param in the "b" version. Can the "inout" version of the call be used for simple querying as in the example 3? Yes, it can, but it is simpler to use binds by value then binds by address. Interestingly enough both binds by value and binds by address (binds by reference) support positional binds, with "?"  for placeholders. Binds by reference also have to be used with RETURNING INTO clause as a part of an INSERT statement.

So far, this article has covered connecting to Oracle, simple fetches, binding variables to placeholders and executing PL/SQL scripts. To really spice it up, this article will also cover working with LOB data types and with REF cursors.

For a complete treatment of PHP and Oracle, see Mladen Gogala's great new book "Easy Oracle PHP".  This unique book focuses on removing the guesswork from creating dynamic web pages with Oracle content, and it's only $19.95.