Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

Calling a Stored Procedure (Oracle) from Perl

by Zo (Scribe)
on Oct 18, 2001 at 00:49 UTC ( #119562=perlquestion: print w/replies, xml ) Need Help??

Zo has asked for the wisdom of the Perl Monks concerning the following question:

Hello again...
Well with all the help earlier with learning about database connections, I've hit my last (hopefully) hurdle. All the help here setting up my server,etc.. has been great, now yet another question... I will post the code, then the error from the screen. This is just the basics, I have more functionality to it, but that will be added once this is up and working. here we go....

(Win 2000 platform) This is my code:
I will also note that this was not my original code, I did make an attempt and an online tutorial as well as using my books (oreillys Perl Cookbook and Perl in Nutshell)... so there might be something that I'm missing, should be changed, etc. I was thankful enough for the person who helped me out with this so far...
#!perl -w ######################################################### # This perl script will go to the oracle database, run the stored proc +edure # LINES_EXTRACT and update the ...... # This will also take each line data and run so that R.R. will exec +ute. # # October 17, 2001 ######################################################### use strict; use DBI; #make the oracle connection my $dbh = DBI->connect('dbi:Oracle:Db001','developer','developer2', { RaiseError => 1, AutoCommit => 0 } ) || die "Database connection not made: $DBI::errstr"; my $rv; eval { my $func = $dbh->prepare(q{ BEGIN :rv := jb_function( parameter1_in => :parameter1); END; }); $func->bind_param(":parameter1", 'pizza'); $func->bind_param_inout(":rv", \$rv, 256); $func->execute; $dbh->commit; }; if( $@ ) { warn "Execution of stored procedure failed: $DBI::errstr\n"; $dbh->rollback; } print "Execution of stored procedure returned $rv\n"; $dbh->disconnect;

Here is the error message...
C:\Perl>connect_4.pl Execution of stored procedure failed: ORA-06550: line 3, column 15: PLS-00201: identifier 'JB_FUNCTION' must be declared ORA-06550: line 3, column 8: PL/SQL: Statement ignored (DBD ERROR: OCIStmtExecute) Use of uninitialized value in concatenation (.) or string at C:\Perl\c +onnect_4.p l line 43. Execution of stored procedure returned

I have checked/searched this sight for past postings and now I hit a roadblock and I need those extra eyes to find my faults... and what is 'JB_FUNCTION'?
Thank you all in advance for any help... and I hope this posting will help others too and learn from my mistakes.
humble brother Zo.

Replies are listed 'Best First'.
Re: Calling a Stored Procedure (Oracle) from Perl
by tommyw (Hermit) on Oct 18, 2001 at 03:15 UTC

    Looks fine to me. Except for jb_function, of course.

    JB_FUNCTION is the function you're referencing in your prepared statement:

    my $func = $dbh->prepare(q{ BEGIN :rv := jb_function( parameter1_in => :parameter1); END; });
    Whatever this is, you don't have one in your database. I don't have one in mine, either, so the error message looks pretty much as I'd expect.

    To get a working, example, I'd try something simple, such as :rv:=length(:parameter1) and don't change anything else.

    In production, you'll probably want to change this to call the LINES_EXTRACT function/procedure that you reference in the initial comments.

Re: Calling a Stored Procedure (Oracle) from Perl
by rrwo (Friar) on Oct 18, 2001 at 02:45 UTC

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://119562]
Approved by root
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (3)
As of 2019-05-26 05:38 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Do you enjoy 3D movies?



    Results (153 votes). Check out past polls.

    Notices?
    • (Sep 10, 2018 at 22:53 UTC) Welcome new users!