Are you not able to use placeholders with Oracle stored procedures? It's much safer as everything will be quoted correctly and you can avoid an SQL injection attack. (both of the following are untested and just here to show you how the methods work)

my $sql = 'exec OSP_FOO_BAR( ?, ?, ? )';
my $sth = $dbh->prepare($sql);
$sth->execute($xvar, $yvar, $zvar);
$dbh->commit();
[download]

If you can't use placeholders there, use the quote() function.

$_ = $dbh->quote($_) foreach $xvar, $yvar, $zvar; # foreach aliases $_
$SQLString = "exec OSP_FOO_BAR( $xvar, $yvar, $zvar )";
$dbh->do($SQLString);      
$dbh->commit();
[download]

Cheers,
Ovid

Looking for work. Here's my resume. Will work for food (plus salary).
New address of my CGI Course.

Or even

$dbh->do('exec OSP_FOO_BAR( ?, ?, ? )',undef,$xvar, $yvar, $zvar);

will work. At least i've used that style many times for inserts, or updates.
Eric Hodges

Perhaps a better way would be to:

my $sth=$dbh->prepare("exec OSP_FOO_BAR(?,?,?)")
   or die $dbh->errstr;
$sth->execute($xvar,$yvar,$zvar) 
   or die $sth->errstr;
[download]

Hope this helps.

Thanks for the input. I thougt $sth-execute() was used solely on Select statements

You might consider initializing your $dbh thus:

$dbh = DBI->connect($dsn, $db_user, $db_pass,
                    { RaiseError => 1 }
                   ) or croak "Couldn't connect to database";
[download]

The RaiseError argument will allow you to avoid the

or die $dbh->errstr
[download]

on each of your database actions.

Namaste

C.J.

This may have changed recently, but for a long time the recommended way to execute a stored procedure in Oracle through DBI is to essentially execute an anonymous procedure that calls it, like so:

$SQLString =<<END_OF_SQL;
BEGIN
    OSP_FOO_BAR( ?, ?, ? );
END;
END_OF_SQL

$db->do( $SQLString, undef, $xvar, $yvar, $zvar )
    or die "Can't execute $SQLString: " . $db->errstr();
[download]

In case you're unfamiliar with the notion, the ? are placeholders, whose values get replace by your x, y, and z vars in the do call. The undef in the do call is required to use placeholders with the DBI's do method (you probably don't need to know exactly why, but you do need something there).

Updated the code to fix one syntax error and misleading syntax.

This way of executing stored procedures is documented in DBD::Oracle

If not P, what? Q maybe?
"Sidney Morgenbesser"

from: $SQLString = "exec OSP_FOO_BAR( '".$xvar."', ".$yvar.", ".$zvar.")";

to: $SQLString = "exec OSP_FOO_BAR( '$xvar', $yvar, $zvar)";

Both pieces of code are equivalent. Should the second two variables have 's around them?

I highly reccomend using the DBI's ? substitution operator unless you know that $xvar, $yvar or $zvar don't contain any meta characters. But you should use them anyway, since that's what they're made for ;)

A short explanation:

When sending a query to the DBI, you can use the ? character and the arguments to C<exec()> to have the contents being substituted be checked for what may otherwise be considered metacharacters (characters that mean something other than themselves, eg ' (begin/end string), % (mysql's glob character), & (the conjunction character), etc.).

Here's an example:

my $query = "SELECT * FROM lala WHERE moomoo = ?";
my $sth = $dbh->prepare($query);
# at this point, the query string is parsed, and discovers
# that there is one substituion.  The DBI will require one
# argument to the C<exec()> function and complain if it
# doesn't see one.

foreach my $moomoo_val ( @vals ){

  # Note that the query is now cached, and can be
  # re-C<execute()>'d as many times as you wish.
  $sth->execute($moomoo_val);

  while(my $row = $sth->fetchrow_hashref){
    print( join("\t", vals %$row), "\n" );
  }
}
[download]

(this code not tested, and yes, I know the same thing could be acheived with less code.)

my $query = "exec OSP_FOO_BAR(?, ?, ?)";
my $sth = $dbh->prepare($query);

$sth->execute($xvar, $yvar, $zvar);
[download]

sub call_assign_value_func {
  my $some_num = shift;
  my $func_value = -1;
  my $pkgh = $dbh->prepare("BEGIN :result :=
    PKG_SIMULATOR.F_ASSIGN_VALUE(:some_num); END;");
  $pkgh->bind_param(":some_num", $some_num);
  $pkgh->bind_param_inout(":result", \$func_value, 15);
  $pkgh->execute();
  return $func_value;
}
[download]