How to execute sql pl file from Perl script

Question

Hi guys, please help me in understanding how to call pl sql file from perl script

I have a pl sql file like this

DECLARE
 x NUMBER := 100;
BEGIN
FOR i IN 1..10 LOOP
  IF MOD(i,2) = 0 THEN     -- i is even
     INSERT INTO temp VALUES (i, x, 'i is even');
  ELSE
     INSERT INTO temp VALUES (i, x, 'i is odd');
  END IF;
  x := x + 100;
END LOOP;
COMMIT;
END; 

The file is called test.sql. I want to call this file from a perl script. I know we must first connect to db and then execute this process, but now I know how to execute this file with a perl script

Answer 1

Basically you need

• use the DBI module with the appropriate driver (Oracle or something else)
• slurp in script to variable using simple perl
• open database connection
• prepare the selection in the script
• execute instruction handle
• disconnect from DB

( , script):

use DBI;
use DBD::Oracle;

my $service="xxx";
my $user = "yyy";
my $pass = "zzz";

my $DBH = DBI->connect
  (
   "dbi:Oracle:$service", 
   "$user", "$pass",
   { 
    RaiseError => 0, 
    PrintError => 0, 
    AutoCommit => 0, 
    ShowErrorStatement  => 0
   }
  ) or die;

my $script = qq(
    declare
        x number := 1;
    begin
        insert into xxx values (x);
        commit;
    end;
);

my $sth = $DBH->prepare($script) or die;
$sth->execute() or die;

$DBH->disconnect();