Perl

From Oracle FAQ

Jump to: navigation, search

Perl is a powerful scripting language that is available on almost all operating systems. It can be downloaded for free from www.cpan.org.

From the Perl documentation:

Perl is a high-level programming language with an eclectic heritage written by Larry Wall and a cast of thousands. It derives from the ubiquitous C programming language and to a lesser extent from sed, awk, the Unix shell, and at least a dozen other tools and languages. Perl's process, file, and text manipulation facilities make it particularly well-suited for tasks involving quick prototyping, system utilities, software tools, system management tasks, database access, graphical programming, networking, and world wide web programming. These strengths make it especially popular with system administrators and CGI script authors, but mathematicians, geneticists, journalists, and even managers also use Perl. Maybe you should, too.

Contents

[edit]

Perl and Oracle

You need two CPAN modules to enable Oracle connectivity: The first is the main DBI (DB Interface) module, the other is a DBD (DB Driver) module. Several DBD modules are available - to support the different database systems, including Oracle and MySQL. The Oracle module is called DBD::Oracle.

  • DBI (previously called DBperl) is a database independent interface module for Perl. It defines a set of methods, variables and conventions that provide a consistent database interface independent of the actual database being used.
  • DBD::Oracle is the Oracle specific module for DBI. It can be downloaded from CPAN (download now).
[edit]

Perl Code

[edit]

CONNECT to Oracle with DBD::Oracle

Look at these example code pieces. In this example, ORCL is the database connect descriptor. 

use strict;
use DBI;
my $dbh = DBI->connect( 'dbi:Oracle:orcl',
                        'scott',
                        'tiger',
                      ) || die "Database connection not made: $DBI::errstr";
$dbh->disconnect;

... or with additional connect options: 

use strict;
use DBI;
my $dbh = DBI->connect( 'dbi:Oracle:orcl',
                        'scott',
                        'tiger',
                        { RaiseError => 1, AutoCommit => 0 }
                      ) || die "Database connection not made: $DBI::errstr";
$dbh->disconnect;

This example connects to two databases simultaneously: 

my $dbh1 = DBI->connect( "dbi:Oracle:ORCL1", "scott", "tiger" )
   or die "Can't connect to 1st Oracle database: $DBI::errstr\n";
my $dbh2 = DBI->connect( "dbi:Oracle:ORCL2", "scott", "tiger" )
   or die "Can't connect to 2nd Oracle database: $DBI::errstr\n";
$dbh1->disconnect or warn "DB1 disconnection failed: $DBI::errstr\n";
$dbh2->disconnect or warn "DB2 disconnection failed: $DBI::errstr\n";
[edit]

SELECT data from Oracle with DBD::Oracle

Look at this example: 

my $sql = qq{ SELECT TNAME, TABTYPE FROM TAB };    # Prepare and execute SELECT
my $sth = $dbh->prepare($sql);
$sth->execute();

my($tname, $tabtype);                     # Declare columns
$sth->bind_columns(undef, \$tname, \$tabtype);

print "List of tables:\n\n";              # Fetch rows from DB
while( $sth->fetch() ) {
    print "Object: $tname, type: $tabtype\n";
}
$sth->finish();                           # Close cursor
[edit]

Invoke stored procedures with DBI

There is currently no standard way to call stored procedures with DBI. However, if you have created a stored procedure within an Oracle database, you can use $dbh->do() to immediately execute the procedure: 

$dbh->do("BEGIN myPackage.myProcedure; END;");
[edit]

DBD::Oracle and NULL values

NULL values are treated as the value "undef" in DBI. Use one of the following methods to insert NULLs into an Oracle database: 

$rc = $dbh->do("INSERT INTO tableName VALUES(SYDDATE, NULL)");

or... 

$sth = $dbh->prepare(qq{ INSERT INTO tableName VALUES (?, ?) });
$sth->execute("10-JUN-2005", undef);

When queried, NULLs should be tested against undef. Example: 

if (!defined $var) { ...
[edit]

External links

Retrieved from "http://www.orafaq.com/wiki/Perl"
Personal tools