In a previous tutorial we covered connecting to an oracle database using Perl DBI and XML::Simple. This tutorial builds on that lesson and covers how you can perform Select and Insert Queries against the Oracle Database.
To recap we are connecting to our database using the DBI module and perl. In our lab setup, the Oracle database is listening at 10.10.10.20 on port 1521 and the Database is called DB1. Our Database user is called DB1-User and the password is Password123.
If you followed the previous tutorial mentioned above you should already have an XML file in your lib directory called dbConfig.xml with this connection information contained in it. You can continue to use that method to connect or follow the directions below.
Additionally, in the previous tutorial we put the queries into xml files. In this lesson we will will write out the queries in a subroutine. We will then call this subroutine from the script to select some data, print it to the screen, delete the record from the database, and insert a new one.
To begin your script should look like this:
#!/usr/bin/perl
XML file to store connection data
#use XML::Simple;
use DBI;
my $dir = "/opt/dbConnect/";
##uncomment if using XML file to store connection data
#my $libDir = "$dir/lib";
#my $configFile = "$libDir/dbConfig.xml";Next you need to setup the ORACLE_HOME environment variable, and database handle.
## Connect to the DB
$ENV{'ORACLE_HOME'} = "/opt/oracle";
my $dbh = DBI->connect("dbi:Oracle:10.10.10.20:1521",
DB1-User, Password123),
) or die "Cannot connect $DBI::errstrn";
Now we need to write our subroutine. We will come back to the main script and call the sub in a few minutes once we know what we need to send it.
The subroutine first needs to so a select statement, so we will start there. Then we will have it print the data selected to the console.
Add the following subroutine at the bottom of your script.
sub dbSelectPrint {
##Define your references that you will send the subroutine.
my $firstname = shift;
my $lastname = shift;
##Define the select statement
my $sth = $dbh->prepare(
qq/select from clients
where
firstname like ? and,
lastname like ?/);
##Execute the query
$sth-execute($firstname, $lastname) or print "error = $dbh->errstr";
##Print the results to the screen:
while (@_ = $sth->fetchrow_array()){ print "$_n";}
## Disconnect when done!
$dbh->disconnect;
##close the subroutine
}Now we need to Define our next Query to Delete a record and insert a new one. So lets start another subroutine.
sub deleteInsert{
my $lastname = shift;
my firstname = shift;
mt rCfgs = shift;
##Define the delete statement
my $delete = $dbh->prepare(
qq/delete from clients
where
firstname like ? and,
lastname like ?/
);
##Define the Insert statement
my $insert = $dbh->prepare(
qq/insert into clients(firstname, lastname, number, email)
values
(?, ?, ?, ?)/
);
##Execute the delete query
$delete->execute($firstname, $lastname) or print "error = $dbh->errstr";
##Execute the Insert query using the hash references passed to the subroutine
$insert->execute($rCfg->{'FIRSTNAME'}, $rCfg->{'LASTNAME'}, $rCfg->{'NUMBER'}, $rCfg->{'EMAIL'}) or print "error = $dbh->errstr";
## Disconnect when done!
$dbh->disconnect;
##close the subroutine
}So thats it for the subroutines, but now we need to call them. So go back into your script and above the subroutine section, but the following lines of code. Normally you would write something that would prompt the user to enter this data but for now we will just simply define a hash.
##Define the hash
my $rCfg = {'FIRSTNAME' => "John", 'LASTNAME' => "Doe", 'NUMBER' => "302-555-1212", 'EMAIL' => "john.doe@acme.com"};
## Call the select subroutine
selectPrint("John", "Doe");
## Call the Delete and Insert subroutine
deleteInsert($rCfg);Let us know if you run into troubles, were here to help!
Leave a Reply