Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Introduction to Bind calls with the Oracle Perl DBI

Oracle Tips by Mladen Gogala

Mladen Gogala is the author of "Easy Oracle PHP".

See the whole article: Perl DBI and Oracle

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:

SELECT e.ename,e.job,d.dname,e.sal
FROM  emp e, dept d
WHERE e.deptno=d.deptno AND
               e.empno=:EMPNO

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

#!/usr/bin/perl -w
use strict;
use DBI;
my $db = DBI->connect( "dbi:Oracle:Local", "scott", "tiger" )
                || die( $DBI::errstr . "\n" );
$db->{AutoCommit}    = 0;
$db->{RaiseError}    = 1;
$db->{ora_check_sql} = 0;
$db->{RowCacheSize}  = 16;
my $SEL = "SELECT e.ename,e.job,d.dname,e.sal
                      FROM   emp e, dept d
                      WHERE e.deptno=d.deptno AND
                                      e.empno=?";
my $sth = $db->prepare($SEL);
 
print "Enter EMPNO:";
my $empno = <STDIN>;
chomp($empno);
 
$sth->bind_param( 1, $empno );
$sth->execute();
my ( $ename, $job, $dept, $sal ) = $sth->fetchrow_array();
write if  $sth->rows>0;
 
no strict;
 
format STDOUT_TOP =
Employee Name          Job                            Department               Salary
----------------------------------------------------------------------------------------------------
.
 
format STDOUT =
@<<<<<<<<<<<        @<<<<<<<<<<<<   @<<<<<<<<<             @<<<<<<<<<
$ename,                       $job,                         $dept,                           $sal
.
 
END {
    $db->disconnect if defined($db);
}

 

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

SELECT e.ename,e.job,d.dname,e.sal
FROM  emp e, dept d
WHERE e.deptno=d.deptno AND
                e.empno=:EMPNO

 

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:

 

CREATE OR REPLACE PROCEDURE DBITEST(emp_no in number, d_name out varchar2)
AS
BEGIN
SELECT  D.DNAME  INTO D_NAME
FROM EMP E, DEPT D
WHERE E.EMPNO=EMP_NO AND
                                            D.DEPTNO=E.DEPTNO;
EXCEPTION
         WHEN NO_DATA_FOUND THEN
         D_NAME:='NO SUCH EMPLOYEE';
END;
/

 

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)

#!/usr/bin/perl -w
use strict;
my $dname;
use DBI;
my $db = DBI->connect( "dbi:Oracle:Local", "SCOTT", "TIGER" )
    || die( $DBI::errstr . "\n" );
$db->{AutoCommit}    = 0;
$db->{RaiseError}    = 1;
$db->{ora_check_sql} = 0;
$db->{RowCacheSize}  = 16;
my $SEL = "begin
                               dbitest(:EMPNO,:DNAME);
                     end;";
 
my $sth = $db->prepare($SEL);
 
print "Enter EMPNO:";
my $empno = <STDIN>;
chomp($empno);
 
$sth->bind_param( ":EMPNO", $empno );
$sth->bind_param( ":DNAME", $dname);
$sth->execute();
print "Department name: $dname\n";
 
END {
    $db->disconnect if defined($db);
}

 

The execution produces the following:

bash-3.00$ ./ttt
Enter EMPNO:7934
DBD::Oracle::st execute failed: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SCOTT.DBITEST", line 4
ORA-06512: at line 2 (DBD ERROR: OCIStmtExecute) [for Statement "begin
                   dbitest(:EMPNO,:DNAME);
           end;" with ParamValues: :empno='7934', :dname=undef] at ./ttt line 23, <STDIN> line 1.
DBD::Oracle::st execute failed: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SCOTT.DBITEST", line 4
ORA-06512: at line 2 (DBD ERROR: OCIStmtExecute) [for Statement "begin
                   dbitest(:EMPNO,:DNAME);
           end;" with ParamValues: :empno='7934', :dname=undef] at ./ttt line 23, <STDIN> line 1.
bash-3.00$

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

#!/usr/bin/perl -w
use strict;
my $dname;
use DBI;
my $db = DBI->connect( "dbi:Oracle:TESTDB01", "mgdba", "qwerty" )
    || die( $DBI::errstr . "\n" );
$db->{AutoCommit}    = 0;
$db->{RaiseError}    = 1;
$db->{ora_check_sql} = 0;
$db->{RowCacheSize}  = 16;
my $SEL = "begin
                               dbitest(:EMPNO,:DNAME);
                     end;";
 
my $sth = $db->prepare($SEL);
 
print "Enter EMPNO:";
my $empno = <STDIN>;
chomp($empno);
 
$sth->bind_param_inout( ":EMPNO", \$empno, 20);
$sth->bind_param_inout( ":DNAME", \$dname, 20 );
$sth->execute();
print "Department name: $dname\n";
 
END {
    $db->disconnect if defined($db);
}

 

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.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

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:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational