Input output binding using DBI

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

Hello Monks First of all I would like to thank all the people here who have been guiding me. Now here is a question. I have a stored procedure as

PACKAGE Par_Reports_Pos AS
PROCEDURE SUN_PRC_AT_I_POSM1D002(retCode OUT NUMBER, retMsg OUT VARCHA
+R2);
END Par_Reports_Pos;
[download]

. Now I connect to the database and try to execute the stored procedure as below

use DBI;   
my $dsn ="dbi:Oracle:$myDSN";
$dbh = DBI->connect ($dsn, $user, $password, {
        PrintError  =>1,
    RaiseError =>1}); 
if (!$dbh) {
print "Error connecting to database; $DBI::errstr\n";
exit(-1);
}
print "After Connection\n";
$sth = $dbh->prepare(q{BEGIN Par_Reports_Pos.sun_t_attm_temp_posm1d002
                    (:parameter1,
                    :parameter2
                    );
                    END;
                });

my $outretcode = $sth->bind_param_inout('retCode', 100);
my $outretmsg = $sth->bind_param_inout('retMsg', 100);
[download]

I am trying to get the return code and return message after $sth->Execute. But I think that I am passing the wrong parameter in output binding. Please correct me. Thanks

Comment on Input output binding using DBI Select or Download Code

Replies are listed 'Best First'.
Re: Input output binding using DBI by mifflin (Curate) on Apr 06, 2005 at 00:03 UTC
I dont see you executing a stored procedure. I see you executing a select statuement... `Select * from sun_t_attm_temp_posm1d002` [download] what you probably want to prepare is a PL/SQL block like... `BEGIN Par_Reports_Pos.SUN_PRC_AT_I_POSM1D002(?, ?); END;` [download] then your binds would change to... `my $retCode; my $retMsg; $sth->bind_param_inout(1, \$retCode, 100); $sth->bind_param_inout(2, \$retMsg, 100);` [download] $regCode and $regMsg would then be set with the results of the procedure call after you execute.	[reply] [d/l] [select]
Re^2: Input output binding using DBI by Nesh (Beadle) on Apr 06, 2005 at 00:46 UTC
Thanks for your reply. I had missed the required stored procedure call code. I have corrected that. Even after changing according to your advice I get the error as "bind_param_inout needs a reference to a scalar value at storedproc.pl line 23."	[reply]
Re^3: Input output binding using DBI by mifflin (Curate) on Apr 06, 2005 at 01:50 UTC
Note the difference between your bind_param_inout calls and mine. See the backslash before the variable names in mine? That says "pass a reference to this variable", not the varable itself.	[reply]
Re: Input output binding using DBI by Thilosophy (Curate) on Apr 06, 2005 at 01:32 UTC
If I may shamelessly advertise my DBIx::ProcedureCall module, which wraps stored procedures into Perl subroutines and takes care of issuing SQL and binding parameters: `use DBIx::ProcedureCall qw( Par_Reports_Pos:package:procedure ); my $outretcode; my $outretmsg; Par_Reports_Pos::sun_t_attm_temp_posm1d002 ( $dbh, \$outretcode, \$outretmsg);` [download] Note that you have to pass references (like `\$outretmsg`) if you bind OUT parameters (because the values have to go somewhere). Pure DBI code would look like: `$sth->bind_param_inout('retCode', \$outretcode, 100);` [download]	[reply] [d/l] [select]
Re^2: Input output binding using DBI by Nesh (Beadle) on Apr 06, 2005 at 19:24 UTC
Hi Its working with the Pure DBI code. But when I was trying to use : use DBIx::ProcedureCall qw( Par_Reports_Pos:package:procedure ); it could not be installed as I am using perl 5.6.1 Now my script takes a procedure name and accordingly executes it. Is there a way that the prepare statement can take any procedure name from the command line and execute it. Thanks	[reply]
Re^3: Input output binding using DBI by Thilosophy (Curate) on Apr 07, 2005 at 00:36 UTC
it could not be installed as I am using perl 5.6.1 Oh.. Sorry about that. The module does state that it needs 5.8, but that is just because I made it on 5.8. I do not think it really depends on 5.8. I will try to find out if it works on 5.6 and if so, fix the version requirement. Is there a way that the prepare statement can take any procedure name from the command line and execute it? It is not difficult to just take a procedure name and have your program write the SQL to invoke it dynamically (this is, after all, how DBIx::ProcedureCall works). The tricky part is figuring out how many parameters and return values you need, and how they need to be bound. Especially OUT parameters (which you seem to use) are difficult. Update: Version 0.08 of DBIx::ProcedureCall should now also work on Perl 5.6 and includes a command line tool that lets you do `perl -MDBIx::ProcedureCall::CLI -e procedure Par_Reports_Pos.sun_t_att +m_temp_posm1d002 :code :msg` [download]	[reply] [d/l]