How to execute a Pl sql file from a perl script

Question

Hi folks can you please help me in understanding how to call a 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 named test.sql I want to call this file from a perl script. I know first we have to connect to db and then perform the process but I Don now know how to execute this file from a perl script

Maybe this is helpful (it may not be quite the same, but perhaps it can give you a couple of ideas on how to proceed): stackoverflow.com/questions/11612713/… — Klas Lindbäck
– Klas Lindbäck, Commented Sep 3, 2014 at 13:41
Hi tlp with dbi I heard we can process only one statement at a time how to overcome that thanks for your reply — user2647888
– user2647888, Commented Sep 3, 2014 at 15:53

Martin Drautzburg · Accepted Answer · 2014-09-03 16:02:09Z

2

Basically you need to

use the DBI module with the appropriate driver (Oracle or whatever)
slurp in the script into a variable by using plain perl
open a DB connection
prepare the slurped in script
execute the statement handle
disconnect from the DB

Here is an example (I am not showing how to slurp in the 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();

answered Sep 3, 2014 at 16:02

Martin Drautzburg

5,2731 gold badge29 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user2647888 Over a year ago

Hi Martin by slurp in meaning I'm not getting you

Martin Drautzburg Over a year ago

I mean you make a string variable ($script in my example) whose value is the entire script you want to be executed in the DB. Google for it. It is something like open FILE, "<xxx.pl"; @lines = <FILE>;print "@lines\n"; You don't need to print it of course.

user2647888 Over a year ago

Hi extremely sorry i'm totally new to perl i found out something as you said but can you help me integrate both ?

Martin Drautzburg Over a year ago

You should be able to figure this out yourself.

David W. Over a year ago

@user2647888 - You asked me to look at this, but I see you accepted this answer. Do you still have a question?

Collectives™ on Stack Overflow

How to execute a Pl sql file from a perl script

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related