Database Reference
In-Depth Information
Comprehensive Perl DBI Example Program
Now it is time to put it all together. The following Perl DBI program illustrates many aspects of
Perl DBI programming. It uses all four connect attributes and inserts rows into a table called
CUSTOMER
in a loop. The customer identification (
CUSTOMER.ID
) is generated by a sequence. The
CREATE
statements for the database objects needed to run the example are embedded as a POD
(Plain Old Documentation) section in the Perl source. POD sections are ignored by the Perl
compiler.
The number of rows to insert is passed as an argument. To ensure good performance, the
program commits only once, just before exiting, parses the
INSERT
statement with bind vari-
ables only once before entering the loop, and merely makes execute and bind calls inside the
loop. Another performance boost is achieved by using an
INSERT
TRIGGER
with
INSERT
RETURNING
instead of first fetching the next sequence number with a
SELECT
statement and then passing
the sequence number back to the DBMS instance in the
INSERT
statement. The latter approach
impairs performance, since it incurs unnecessary network round trips between client and server.
The higher the network latency, the more severe the impact on response time will be.
As an aside, the fastest way to insert rows using a sequence to generate primary keys is to
reference the sequence with
sequence_name
.
NEXTVAL
in the
INSERT
statement. This requires
fewer CPU cycles than a PL/SQL trigger. Following is an example:
INSERT INTO customer(id, name, phone)
VALUES (customer_id_seq.nextval, :name, :phone)
RETURNING id INTO :id
The downside is that access to the sequence must be coded in every application, whereas
it would be coded centrally if a trigger were used.
The program features the ability to enable
SQL_TRACE
based on an environment variable
setting. This is a feature any application should have, since it reduces the effort needed to
compile performance diagnostic data. The DBI method
do
is used to execute
ALTER
SESSION
statements. This method is also appropriate for executing non-reusable statements such as
CREATE
TABLE
.
The program also shows how to call PL/SQL packages by preparing and executing an
anonymous block. An alternative way to call PL/SQL routines (functions, packages, and proce-
dures) that works with DBI but is not used in the following example, is the SQL statement
CALL
(see
Oracle Database SQL Reference 10g Release 2
, page 13-53). I have added plenty of comments
to point out what is happening in the Perl program
insert_perf4.pl
here:
1 #!/usr/bin/env perl
2
3 =pod
4
5 create table customer(
6 id number(*,0) not null,
7 name varchar2(10),
8 phone varchar2(30)
9 );
10 create sequence customer_id_seq;
11 create or replace trigger ins_customer before insert on customer for each row
