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

Hello,
I have a few different PLSQL packages stored on an ORACLE DB. For each of these, I have a seperate perl program to call each one similiar to this following statement(snipped for simplicity):

-- begin code segment --
# Statement handler to call database package
my $sth = $dbh->prepare(q{
BEGIN
upload_validation.FILE_FORMAT_VALIDATION(?,?);
END;
});

$sth->bind_param(1,$load_type);
$sth->bind_param(2,$product);
$sth->execute();
-- end code segment --
The issue I am having, the different stored procedures have a different number of parameters. Upload_validation has 2, while check_employee has 5 etc etc.

I want to have a generic perl program that will take in the procedure name and the parameters the stored procedure will take. I having issues because of I can not get around the ? part of the prepare statment. If I send too few args or too many, it dies with a PLSQL error saying wrong number or types of arguments to call FILE_FORMAT_VALIDATION.

Just wondering if this is possible to make a sort of generic driver, or should we stick to making a seperate perl program for each stored procedure PLSQL we need to call.

-------------------------------
Just Your Friendly Neighborhood
_SPIDEY_

Replies are listed 'Best First'.
Re: Generic PLSQL Launcher
by Transient (Hermit) on May 12, 2005 at 20:15 UTC
    Just build your statement based upon the arguments... the prepare statement is just a string. e.g. 
    sub run_plsql_statement {
  my $package_name = shift;
  my $procedure_name = shift;
  my @params = @_;

  my $statement = "BEGIN\n";
  $statement .= $package_name.'.' if $package_name;
  $statement .= $procedure_name;

  # probably have a better way to do the map...
  $statement .= '('.( join(',',( map { '?' } @params )) ).')' if @para
+ms;

  $statement .= ";\nEND;\n";
}

    [download]
    (code untested - but hopefully gets you started)

    I'll let you finish the rest (bindings) :)
[reply]
[d/l]
Re: Generic PLSQL Launcher
by kirbyk (Friar) on May 12, 2005 at 20:58 UTC
    Sure, we use a similar wrapper to call our plsql. 
    # This is for calling stored procedures by just passing in a dbh, the 
+procedure name and a param hash
sub call_plsql {
      my ( $self, $dbh, $name, $params ) = @_;

      my @vars;
      foreach $key ( keys %$params ) {
            push( @vars, "$key => :$key" );
      }

      my $call = "BEGIN $name( " . join( ', ', @vars ) . ' ); END;';
      warn "$call\n" if ( grep(/-v/, @ARGV) );

      my $sth = $dbh->prepare( $call );

      foreach $key ( keys %$params ) {

            if ( ref($params->{$key}) eq 'ARRAY' ) { 
                  $sth->bind_param_inout( ":$key", @{ $params->{$key} 
+} );
            }
            else {
                  $sth->bind_param( ":$key", $params->{$key} );
            }
      }

      $sth->execute();

      return;
}

    [download]
    This is then called like (from an arbitrary example): 
     my $params = {
                          p_PromoID             => $promo_id,
                          p_NumCodes          => $cnt,
                          o_NewCodes        => [ \$sth, 0, { ora_type 
+=> 116 } ],
                          o_Status          => [ \$error, 250 ],
                         };

      call_plsql( $dbh, 'wp.create_unique_codes', $params );

    [download]
    Note that, in order to use plsql like this, you have to put default values on your variables: 
    create or replace procedure insert_profile_survey(
    p_primary_usage          VARCHAR DEFAULT NULL,
    p_gender                 VARCHAR DEFAULT NULL,
...

    [download]

    -- Kirby, WhitePages.com

[reply]
[d/l]
[select]
Re: Generic PLSQL Launcher
by Thilosophy (Curate) on May 13, 2005 at 09:43 UTC
    I think you want DBIx::ProcedureCall. 
    use DBIx::ProcedureCall qw(
   update_validation:package:procedure
);

update_validation::file_format_validation($dbh,$load_type, $product);

    [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom