ba6.us - Dave's Database Related Stuff

Example 1: Run a Simple SQL Command

This script runs a simple SQL command. If an Oracle error is thrown, it will be reported to the user.

#!/bin/perl
use strict;
use DBI;my $dbh = DBI->connect( 'dbi:Oracle:[enter_sidname_here]',
'[enter_oracle_username_here]',
'[enter_password]',
{
RaiseError => 1,
AutoCommit => 0
}
) || die "Database connection not made: $DBI::errstr";# Text of SQL Statement
my $sql = qq{ DROP TABLE EMPLOYEES };

# Run SQL Statement
$dbh->do( $sql );

# Disconnect
$dbh->disconnect() or warn "DB disconnection failed: $DBI::errstrn"; 

Example 2: Run a Query and Print Results

This script runs a query and scans through the results. To add more fields to the query and output make sure to update the bind_columns function with the new fields.

#!/bin/perl
use strict;
use DBI;# Run a SELECT
my $dbh = DBI->connect( 'dbi:Oracle:[enter_sidname_here]',
'[enter_oracle_username_here]',
'[enter_password]',
{
RaiseError => 1,
AutoCommit => 0
}
) || die "Database connection not made: $DBI::errstr";

my $sql = qq{ SELECT TABLE_NAME FROM USER_TABLES };

# Prepare and execute SELECT
my $sth = $dbh->prepare($sql);
$sth->execute();

# Declare and Bind Column(s)
my($table_name);
$sth->bind_columns(undef, $table_name);

# Fetch rows from DB
print "List of tables:nn";
while( $sth->fetch() ) {
print "Object: $table_name";
}

# Close cursor
$sth->finish();
$dbh->disconnect() or warn "DB disconnection failed: $DBI::errstrn";