Home » Open Source » Programming Interfaces » Perl Script To Call A Simple Oracle Stored Procedure
|
|
| Re: Perl Script To Call A Simple Oracle Stored Procedure [message #203674 is a reply to message #203624] |
Thu, 16 November 2006 00:30   |
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
|
|
|
|
 Re: Perl Script To Call A Simple Oracle Stored Procedure [message #279032 is a reply to message #203624] |
Tue, 06 November 2007 12:15 |
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 |
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
|
|
|
|
 Re: Perl Script To Call A Simple Oracle Stored Procedure [message #279463 is a reply to message #279461] |
Thu, 08 November 2007 10:46 |
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!
|
|
|
|
Goto Forum:
Current Time: Thu Jan 30 05:07:40 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
