Perl DBI and the DBD::Oracle driver
Connecting
Selecting
Inserting
Deleting
Updating
Transactions
dbish
Perl DBI and the DBD::Oracle driver
This is not trying to be a know-it-all guide to DBI, but merely a short introduction on how connecting and using Oracle in your Perl programs could be done.
Connecting
To connect to the Oracle database through DBI you can either set up your enviroment like with sqlplus or you can define everything in the connect routine provided by DBI. I will try to describe the last of the two options.
use DBI;
my $dbh = DBI->connect('dbi:Oracle:host=localhost;sid=ORCL;port=1521',
'scott', 'tiger', { RaiseError => 1, AutoCommit => 0 });
This connect string requires that you have the Oracle TNS listener running.
Selecting
With our open database handle we can now begin to select from the database.
my $sth = $dbh->prepare("select table_name from user_tables");
while (my ($table_name) = $sth->fetchrow_array()) {
print $table_name, "\n";
}
$sth->finish();
There are more ways to select with the DBI interface, the prepare way is particular smart if you have more rows or you need placeholders. There is a method called selectrow_array which is very nice if you only need one row of data.
my $table_count =
$dbh->selectrow_array("select count(table_name) from user_tables");
Inserting
As with selecting there are multiple ways to insert rows with DBI. Depending on your needs and wheter you need to insert more than one row there are two different solutions. First an example on inserting a single row.
$dbh->do("insert into table_name (name) values (?)", undef, 'scott');
This example introduced something new called a placeholder. A placeholder is defined as a '?' in your SQL-statement and DBI will need arguments for each of the placeholders you have in your SQL-statement. Using placeholders instead of inserting the variabled directly in the SQL-statement is much better, because you put off the actual insert of the value to after the SQL-statement has been parsed by the Oracle database, which should save you from having to check the variable you're inserting from characters that need to be quoted.
There is an even smarter approach if you need to insert many rows.
my $sth = $dbh->prepare("insert into table_name (name) values (?)");
while (my ($line) = <>) {
$sth->execute($line);
}
$sth->finish();
Transactions
use DBI;
my $dbh = DBI->connect('dbi:Oracle:host=localhost;sid=ORCL;port=1521',
'scott', 'tiger', { RaiseError => 1, AutoCommit => 0 });
eval {
my $sth = $dbh->prepare("insert into table_name (name) values (?)");
$sth->execute('flipflop');
$sth->finish();
$dbh->commit();
}
if ($@) {
$dbh->rollback();
die $@;
}
$dbh->disconnect();
]: Failed opening '../../../comments.php' for inclusion (include_path='.:/usr/share/php:/usr/share/pear') in