Mladen Gogala is available to assist you
with your toughest Perl DBI problems. Just call.

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:

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:

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.

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.

��

Burleson is the American Team

Note:This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.

Verify
experience!Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.

Errata? Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just e-mail: