Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask

executing a OSP with CGI

by chuleto1 (Beadle)
on Jul 15, 2003 at 19:15 UTC ( #274533=perlquestion: print w/replies, xml ) Need Help??

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

I am trying to execute an Oracle Stored Procedure. This is what I am attempting:
$SQLString = "exec OSP_FOO_BAR( '".$xvar."', ".$yvar.", ".$zvar.")"; $dbh->do($SQLString); $dbh->commit();
OSP_FOO_BAR is supposed to update a table with x,y,z variables. Does anyone see anything wrong with my code? I do not get the resutls I want. And I am SURE the problem is not the OSP. Many Thanks

Replies are listed 'Best First'.
Re: executing a OSP with CGI
by Ovid (Cardinal) on Jul 15, 2003 at 19:30 UTC

    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();

    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();


    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
Re: executing a OSP with CGI
by blue_cowdawg (Monsignor) on Jul 15, 2003 at 19:24 UTC

    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;
    This way you can capture any errors you generate and you are allowing the DBI interface to do what it does very well and handle any necessary type coversions for your variables.

    Hope this helps.

    Peter L. BergholdBrewer of Belgian Ales
    Unix Professional
      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";
      The RaiseError argument will allow you to avoid the
      or die $dbh->errstr
      on each of your database actions.


Re: executing a OSP with CGI
by arturo (Vicar) on Jul 15, 2003 at 20:32 UTC

    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();

    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"

string assignment.
by roju (Friar) on Jul 15, 2003 at 19:23 UTC
    First, rewrite that assignment using the more perlish variable interpolation, instead of concatination. So you go

    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" ); } }
      (this code not tested, and yes, I know the same thing could be acheived with less code.)
Re: executing a OSP with CGI
by LanceDeeply (Chaplain) on Jul 15, 2003 at 20:00 UTC
    did you test that exact sql interactively?

    i'm not sure how it is in Oracle, but in Sybase and MSSQL you dont use parens around the parameters to exec a stored proc.

Re: executing a OSP with CGI
by cjcollier (Novice) on Jul 15, 2003 at 21:17 UTC
    Hmm.. For substitutions, I usually do this:
    my $query = "exec OSP_FOO_BAR(?, ?, ?)"; my $sth = $dbh->prepare($query); $sth->execute($xvar, $yvar, $zvar);
    This should make sure that the variables are insert with correct escape codes. Perhaps this will be enough?


Re: executing a OSP with CGI
by Daruma (Curate) on Jul 16, 2003 at 15:47 UTC
    Greetings, chuleto1!

    When executing an existing stored procedure via DBI, I have often used a structure like the following:
    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; }
    I am basically creating an anonymous PL/SQL block to execute the desired stored procedure, (or function in this case.) Using the $pkgh->bind_param and $pkgh->bind_param_inout calls allows for much greater flexibility when working with some of the stored procedures I have.

    Hope this helps!


Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://274533]
Front-paged by diotalevi
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (3)
As of 2023-03-29 04:36 GMT
Find Nodes?
    Voting Booth?
    Which type of climate do you prefer to live in?

    Results (70 votes). Check out past polls.