Database Reference
In-Depth Information
99 $sth->bind_param_inout(":id", \$id, 38);
100 # bind_param is for sending bind variable values to the DBMS
101 # assign value to bind variable (placeholder :name)
102 $sth->bind_param(":name", $name);
103 # assign value to bind variable "phone"
104 $sth->bind_param(":phone", $phone);
105 # execute the INSERT statement
106 $sth->execute();
107 printf "New customer with id %d inserted.\n", $id;
108 }
109 };
110 # check for exceptions
111 if ($@) {
112 printf STDERR "ROLLBACK due to Oracle error %d: %s\n", $dbh->err, $@;
113 # ROLLBACK any previous INSERTs
114 $dbh->rollback;
115 exit;
116 } else {
117 # commit once at end
118 $dbh->commit;
119 }
120 $sth->finish; # close statement handle
121 $dbh->disconnect; # disconnect from ORACLE instance
Line 35 imports the package
File::Basename
, which contains the function
basename
. The
Perl command
basename
works in the same way as the UNIX command by the same name. It
returns the file component from a path name by stripping one or more directories from the
string. The command
basename
is used to make sure that neither the module name nor the
TRACEFILE_IDENTIFIER
contain illegal or unwanted characters such as the directory separator
(
/
) that may be contained in the Perl variable
$0
.
The connect statement, which sets the recommended values for
RaiseError
(1),
PrintError
(0), and
AutoCommit
(0) is in line 39. An
eval
block used to catch exceptions from
DBI method invocations encompasses lines 47 to 109. The check for the environment variable
SQL_TRACE_LEVEL
is in line 51. The range of values is the same as for event 10046 (1, 4, 8, 12, see
also Chapter 24). If the environment variable is set, SQL trace is enabled in line 53 with
ALTER
SESSION
SET
EVENTS
. The name of the Perl program is used as the
TRACEFILE_IDENTIFIER
in line
52 to facilitate locating trace files in the directory set as
USER_DUMP_DEST
.
Lines 62 to 77 show how to call PL/SQL packages by embedding them in an anonymous
block. The publicly accessible package
DBMS_UTILITY
is called three times in a single anonymous
block to retrieve information on the instance and database.
Lines 79 to 86 and 92 exemplify how conveniently fetching data can be coded with the DBI.
With
bind_columns
, variables with meaningful names are used for accessing column values
retrieved with the Perl DBI method
fetchrow_array
. Without
bind_columns
, array syntax such
as
$row[
column_index
]
must be used, where
column_index
starts at 0 and designates columns
in the
SELECT
column list from left to right.
The
INSERT
statement, which contains bind variables and is thus reusable, is parsed once
before entering the loop by calling
prepare
in line 94. It would be a costly mistake to place the
