Scripter en perl sous Oracle

Par Olivier JOLYDéveloppement

Pearl sous Oracle Server RDBMS

Depuis quelques temps Oracle livre avec ses clients et serveurs RDBMS sont livrés avec perl, pas dans une version récente certes mais qui permet de se connecter à la base et de travailler avec. Avec cette nouveauté, l’avantage est de pouvoir développer des scripts portables et léger pour l’administration des bases et pour un reporting minimal sans passer par de très (trop ?) lourdes et inutiles machines virtuelles (fin de troll … saurez-vous en repérer le début ?). Dans cet article, on donnera les quelques trucs minimaux pour se connecter à une base oracle et travailler avec.

Avertissement

Cet article n’a pas pour but de vous apprendre perl que vous connaissez déjà (non ?) mais bien se remémorer les quelques trucs utiles pour développer rapidement des scripts d’administration portables de windows vers unix en passant par linux et VMS !!!

Au fait, ça existe encore VMS ?

Environnement et bibliothèques perl

Perl est livré dans le répertoir perl de l’ORACLE_HOME, la localistion précise des binaires qui lui sont liés dépendent de l’OS et de la version d’Oracle RDBMS. Par exemple :

  • En version 10.2.0.3 sous windows 2008R2 64bits : %ORACLE_HOME%/perl/bin
  • En version 11.1.0.6 sous windows vista SP2 32bits : %ORACLE_HOME%/perl/5.8.3/bin/MSWin32-x86-multi-thread
  • En version 11.2.0.2 sous AIX 5.3 64bits : %ORACLE_HOME%/perl/bin
  • …

Sur les environnements de type UNIX il faut aussi positionner la variable d’environnement PERL5LIB (comme un PATH, par exemple pour AIX 64 bits et oracle 11.2.0.2 PERL5LIB=$ORACLE_HOME/perl/libsite_perl/5.10.0/aix-thread-multi-64all/:$ORACLE_HOME/perl/lib/5.10.0/ ).

Deux bibliothèques nécessaires à la connexion DBI et DBD::ORACLE. Exemples de connexions :

use DBI;

my $cnx;
my $c_usr  = "system";
my $c_pass = "amanger";

$cnx = DBI->connect( "dbi:Oracle:$ENV{ORACLE_SID}", "$c_usr", "$c_pass"
                   , { AutoCommit => 0, PrintError => 0} ) 
        or die PError ("Connexion impossiblen") ;

[TRAITEMENTS]
        
$cnx->disconnect() ;

ou pour une connexion as sysdba :

use DBI;
use DBD::Oracle qw(:ora_session_modes);

my $cnx;
my $c_usr  = "sys";
my $c_pass = "c_o_i";

$cnx = DBI->connect( "dbi:Oracle:$ENV{ORACLE_SID}", "$c_usr", "$c_pass"
                   , { AutoCommit => 0, PrintError => 0,  ora_session_mode => ORA_SYSDBA } ) 
        or die PError ("Connexion impossiblen") ;

[TRAITEMENTS]
        
$cnx->disconnect() ;

Exécuter une requête avec perl

On suppose qu’on dispose d’une connexion dans la variable $cnx.

Une ligne retournée

my $v_sql="select DBID
     , DB_UNIQUE_NAME
     , HOST_NAME
     , STATUS
     , INSTANCE_ROLE
from sys.v_$instance, sys.v_$database";

my $qry = $cnx->prepare($v_sql);
$qry->execute();
my ($dbid, $dbun, $db_hostname, $db_status, $db_role) = $qry->fetchrow_array();
$qry->finish();

Plusieur lignes retournées

my $v_sql="select * 
from sys.dba_objects";

my $qry = $cnx->prepare($v_sql);
$qry->execute();
my @t_objs = $qry->fetchrow_array();
$qry->finish();

Utilisation de bind variables

Notez l’utilisation de DBD ora_types …

use DBD::Oracle qw(:ora_types);

my $v_sql="select * 
from sys.dba_hist_snapshots where snap_id between :deb and :fin ";

my $qry = $cnx->prepare($v_sql);

$qry->bind_param( ":deb" , 1234  , { ora_type => ORA_NUMBER } )  ;
$qry->bind_param( ":fin" , 1256  , { ora_type => ORA_NUMBER } )  ;

$qry->execute();
my @t_snid = $qry->fetchrow_array();
$qry->finish();

Gérer les sorties oracle de type longs

$cnx->{LongReadLen} = 512*1024;
$cnx->{LongTruncOk} = 1;

my $v_sql="select sqlid, sql_fulltext from sys.v_$v_sql";

my $qry = $cnx->prepare($v_sql);
$qry->execute();
my @t_sqlt = $qry->fetchrow_array();
$qry->finish();

PL/SQL

Exécuter une procédure

my $v_sql="begin
 for c in (select lolo, lulu from toto)
 loop
  insert into tutu values (c.lulu, c.toto);
 end loop;
 commit;
end;";

my $qry = $cnx->prepare($v_sql);
$qry->execute();
$qry->finish();

Exécuter une procédure avec des bind variables

my $baz;
my $v_sql="begin foo.barr(:baz); end;";

my $qry = $cnx->prepare($v_sql);
$qry->bind_param_inout(":baz", $baz,400);

$qry->execute();
$qry->finish();

Récupérer la sortie de type dbms_output d’une procédure

$dbh->func( 1000000, 'dbms_output_enable' );

my $baz;
my $v_sql="begin foo.barr(); end;";

my $qry = $cnx->prepare($v_sql);
my $sortie_dbms = $cnx->func( 'dbms_output_get' );

$qry->execute();
$qry->finish();

Ce sont là les fonctionnalités perl liées à la base dont je me sers le plus. Plus d’inforamtion sur le site CPAN notamment concernant le module DBI et le module DBD::Oracle.