Home » Open Source » Programming Interfaces » Perl Script To Call A Simple Oracle Stored Procedure
Perl Script To Call A Simple Oracle Stored Procedure [message #203624] Wed, 15 November 2006 20:06 Go to next message
wtolentino
Messages: 390
Registered: March 2005
Senior Member
sorry if i post this in the wrong forum. i am a newbie to perl scripting and would like to know how can i call an oracle stored procedure using perl scripting. can anyone provide some example codes. thanks.

[Updated on: Wed, 15 November 2006 20:07]

Report message to a moderator

Re: Perl Script To Call A Simple Oracle Stored Procedure [message #203674 is a reply to message #203624] Thu, 16 November 2006 00:30 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You use Perl::DBI

If you haven't already, download Perl::DBI and DBD::Oracle from http://www.cpan.org

Read the documentation provided. It will show you how to connect to the database and execute a variety of statements.
$dbh =  DBI->connect('dbi:Oracle:', 'usr/pwd@db');


To execute a stored proc, you use the prepare a string to create a statement handle, and then execute the statement handle.
my $sth = $dbh->prepare('BEGIN my_proc(args); END;) || die $dbh->errstr;
$sth->execute || die $dbh->errstr;


This is pretty basic. You can also use bind variables.

Since the DBI doco is pretty generic (non-Oracle) also read the DBD::Oracle doco, which will show you more Oracle-specific examples.

Ross Leishman
icon8.gif  Re: Perl Script To Call A Simple Oracle Stored Procedure [message #279032 is a reply to message #203624] Tue, 06 November 2007 12:15 Go to previous messageGo to next message
bpropes
Messages: 2
Registered: November 2007
Location: Dallas, TX
Junior Member
Hi,

Did you ever find out how to do this? I am having similar issues trying to call a stored proc from a Perl script.
The DBI::Oracle thing wasn't too good of help unfortunately.

I've got the following stored proc, called DEFPROJ_FORM_INSERTION.

I was trying the following:

my $sth2 = $dbh->prepare(q{BEGIN DEFPROJ_FORM_INSERTION; END;});

I then try to execute on the variables like so:

$sth2->execute($projidltr,$ReqName,$Email,$Loc,$phoneno,$mgrname, $mgremail,$projname,$projloc,$spondept,$sponbiz,$projtype,$reglegal2,$netloss2,$expreduc2,$stratgoals2,$auditcomp2,$revgen2,$cacs2,$c ustit2,$custimpact2, $callmgt2, $calltrack2,$citilink2,$deskeng2,$dialer2,$dri2,$engineering2,$imaging2,$ipdt2,$mframe2, $miscoth2,$mortserv2,$mortweb2,$nonmortserv2,$origpltfrm2,$qualmap2,$dataware2,$servapp2,$sbdev2,$websvcg2, $probresol, $existproc, $busobj, $pendingproj, $impactareas, $regpenalty, $complydeadlinedt2, $expreducttext, $priorrating, $addlcommts2,$otherprojtype2,$miscothexpl2,)or do {


I got an error about trying to bind 53 variables but 0 are required.

Is my syntax totally wrong? How do you do this? I've created the SP within Oracle (SQL Plus) with no errors or issues.
How do you actually call it? Seems ludicrous.

Any feedback appreciated. Even tried to email that Eric Bartley guy out of Purdue and he's of course not there anymore.
Re: Perl Script To Call A Simple Oracle Stored Procedure [message #279461 is a reply to message #203624] Thu, 08 November 2007 10:40 Go to previous messageGo to next message
amcghie
Messages: 35
Registered: March 2005
Location: Sunny Dubai
Member
Hi,

Are you trying to execute the DEFPROJ_FORM_INSERTION Oracle Stored Procedure passing in 53 variables? If so, you have your syntax slightly wrong, try:
my $sql = qq{
    BEGIN 
      DEFPROJ_FORM_INSERTION( :projidltr
                            , :ReqName
                            , :Email
                            , :Loc,...);
    END;
};

my $sth2 = $dbh->prepare_cached($sql);

$sth2->bind_param( ":projidltr", $projidltr );
$sth2->bind_param( ":ReqName", $ReqName );
$sth2->bind_param( ":Email", $Email );
$sth2->bind_param( ":Loc", "$Loc );
...
$sth2->execute();

Rather than declaring 53 seperate bind variables you may be better passing in an array.

Hope this helps you

Andy
icon7.gif  Re: Perl Script To Call A Simple Oracle Stored Procedure [message #279463 is a reply to message #279461] Thu, 08 November 2007 10:46 Go to previous message
bpropes
Messages: 2
Registered: November 2007
Location: Dallas, TX
Junior Member
Hey! I got this working w/o all of that mess!

NOWHERE had I seen a valid example, and I think I'm going to submit my example to CPAN, but it simply needed CALL DEFPROJ_FORM_INSERTION in there and that was it!

There should NEVER be a need to put the actual syntax of the stored procedure within the script/page(perl in this case) itself.
That entirely defeats the purpose, and to me is completely unmanageable!
Previous Topic: Passing Tables from PHP to PLSQL
Next Topic: PSP help
Goto Forum:
  


Current Time: Thu Mar 28 15:29:52 CDT 2024