Database Reference
In-Depth Information
prepare
call inside the loop. Extra network round trips due to parse calls and excess CPU
consumption due to superfluous soft parses would be incurred.
Inside the
for
loop, which stretches from line 97 to 108, the
bind_param_inout
method is
used to tell the DBI which variable to use for receiving the sequence number returned to the
client due to the SQL statement
INSERT
RETURNING
id
INTO
:id
. The bind variables
name
and
phone
are for sending values to the DBMS. This is accomplished with the DBI method
bind_param
.
The
eval
block is followed by an
if
statement, which checks the special Perl variable
$@
for
an exception. If an exception has occurred,
$@
contains the error message. Otherwise
$@
is an
empty string, considered by Perl as a boolean expression that evaluates as
FALSE
, such that the
if
branch in line 112 is not entered. In case of an exception, any rows already inserted are
discarded by issuing
rollback
in line 114.
If all is well,
commit
is called once in line 118, the statement handle
$sth
is released in line
120, and the client disconnects from the DBMS in line 121. Following is a transcript of running
the Perl program
insert_perf4.pl
:
$ ./insert_perf4.pl 3
DBI Version: 1.41
DBD::Oracle Version: 1.15
Connected to ORACLE instance ten, release 10.2.0.1.0 (compatible=10.2.0.1.0);
Database TEN
Session 145 on Thursday , 19. July 2007 21:57:54 (week 29)
New customer with id 1 inserted.
New customer with id 2 inserted.
New customer with id 3 inserted.
Exception Handling
Before the curtain drops, I'd like to show the exception handling with
eval
in action. Setting the
tablespace where the table
CUSTOMER
resides to status read-only causes the
execute
method call
in line 106 to fail. Execution will continue at the
if
statement in line 111. Following is a step by
step test:
SQL> CONNECT system
Enter password:
Connected.
SQL> ALTER TABLESPACE users READ ONLY;
Tablespace altered.
SQL> EXIT
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
$ ./insert_perf4.pl 1
DBI Version: 1.48
DBD::Oracle Version: 1.16
Connected to ORACLE instance ten, release 10.2.0.1.0 (compatible=10.2.0.1.0); Databa
se TEN
Session 145 on Thursday , 19. July 2007 23:52:32 (week 29)
