On Wed, 14 May 2008 10:05:22 -0700, Martin Evens wrote:

>  That is slightly more complicated than it looks. DBD::Oracle already  
>  magics a sth into existence for reference cursors but some databases  
>  can return more than one result-set from a procedure - e.g., SQL  
>  Server and the SQLMoreResults call to move to the next one.

Huh. How interesting.

I'm sure it's more complicated than it looks, to be sure, but I was  
just trying to provide an 80% solution for simple functions that  
return a scalar or a result set.

>  I have hundreds of functions and procedures in various packages in  
>  Oracle we use via DBD::Oracle. We have no SQL at all outside  
>  database functions/procedures/packages i.e., our Perl does not know  
>  anything at all about the tables or columns in the database and the  
>  only SQL executed is to prepare/execute procedures and functions.

Yes, this is exactly the sort of code I'm starting to write.

>  We wrap calls to functions and procedures like this:
> 
>  $h->callPkgFunc(\%options, $pkg, $func_name, \$ret, @args);
>  $h->callPkgProc(\%options, $pkg, $proc_name, @parameters);
> 
>  $pkg is the package name of synonym for the package.

Which package?

>  $func_name and $proc_name are the function or procedure name.
> 
>  $ret is the return value from a function - which may be a reference  
>  cursor for Oracle.

Could the call to callPkgFunc() just return a scalar, instead? I don't  
mean can you change all of your code; I just mean, could the method  
have been implemented that way?

>  @args is the list of scalar args for the function.
>  @parameters is the list of parameters for the procedure and if any  
>  is a
>  reference to a scalar it is assumed to be an output parameter.

Oh, that's interesting. I like that.

>  There are various %options for whether to die etc and ways of  
>  handling error output.
> 
>  The wrapper handles creating the SQL, preparing it, binding the  
>  parameters, executing the func/proc and returning the output bound  
>  parameters.

Yeah, great!

>  This works well for us. We were using the same wrapper for MySQL and  
>  DB2 but have since dropped use of MySQL and DB2. Of course, the  
>  innards of the wrapper were significantly different between DB2,  
>  MySQL and Oracle. For Oracle you end up with:
> 
>  begin :1 := pkg_name.function_name(:2,:3,:4...); end;
> 
>  begin pkg_name.proc_name(:1,:2,:3...); end;

Is that really the syntax for calling functions and procedures in  
Oracle? Sheesh!

>  The code to do this is fairly straight forward, the complexities lie  
>  in the differences between DBDs and databases.

It seems like it could be handled by the DBDs just setting a few  
variables, though, I should think.

Maybe something like this:

     sub call_proc {
         my $dbh    = shift;
         my $name   = shift;
         my $opts   = shift;
         my $places = $dbh-> create_places( \@_, $opts );
         return $dbh-> selectrow_array(
             $dbh-> proc_sql( $name, $places, $opts ),
             undef,
             @_
         );
     }

     sub call_func {
         my $dbh    = shift;
         my $name   = shift;
         my $opts   = shift;
         my $places = $dbh-> create_places( \@_, $opts );
         return $dbh-> selectrow_array(
             $dbh-> func_sql( $name, $places, $opts ),
             undef,
             @_
         );
     }


     sub create_places {
         my ($self, $params, $opts) = @_;
         join ', ', ('?') x @$params;
     }

     sub proc_sql {
         my ($self, $proc, $places, $opts) = @_;
         "CALL $proc( $places )";
     }

     sub func_sql {
         my ($self, $func, $places, $opts) = @_;
         "SELECT $func( $places )";
     }

     package DBD::Oracle;
     sub create_places {
         my ($self, $params, $opts) = @_;
         # XXX Do whatever needs to be done for in/out args here.
         join ', ', map { ":$_" } (1..@$params);
     }

     sub proc_sql {
         my ($self, $proc, $places, $opts) = @_;
         die "No package" unless $opts-> {package};
         "begin $opts-> {package}.$proc( $places ); end;"
     }

     sub func_sql {
         my ($self, $func, $places, $opts) = @_;
         die "No package" unless $opts-> {package};
         "begin :1 := $opts-> {package}.$func( $places ); end;";
     }

Note how I've overridden the default driver methods for creating a  
placeholder string for function and procedure arguments and SQL with  
different ones in the DBD::Oracle driver.

I just banged this out as an example of where we might go, of course;  
it's far from complete, and doesn't cover how to handle cursors or  
result sets, of course. But just as an idea of the sort of thing I  
think just might work.

>  A call-like method in DBI would save a little programming but for  
>  some DBDs it would be difficult - I'm of course thinking of  
>  DBD::ODBC. Although ODBC defines a {call xxx} syntax what actually  
>  happens when you you use it is very database dependent and I even  
>  know of ODBC drivers that expect you to ignore output bound  
>  reference cursors in the parameter list.

Sure, for databases that don't support outbound parameters. But that  
just means that you don't pass in a reference to a scalar as a param  
when you're using those databases, no?

Thanks,

David