Usage is like this

use Oracle::Procedures qw(sysdate);
my $conn = DBI->connect(..);
print sysdate($conn);
[download]

Like I said, it works. But what do you think about the interface? Would you use it? There is also DBIx::Procedures::Oracles, which does a similar task.

Also, when binding parameters, my module does not specify datatypes. I never do that. Seems to work just fine. Is there a problem with this approach?

Are there any obvious situations where it would not work? One thing that comes to mind are functions that return cursors.

And of course: Is the module name (Oracle::Procedures) any good?

Here is the module

package Oracle::Procedures;

use strict;
use warnings;
use Carp qw(croak);


our $VERSION = '0.01';

sub __run_procedure{
        my $dbh = shift;
        croak "expected a database handle as first parameter, but got 
+nothing" unless $dbh;
        croak "expected an Oracle database handle as first parameter, 
+but got $dbh" unless ref $dbh;
        my $name = shift;
        croak "expected a procedure name to run against the Oracle dat
+abase, but got nothing" unless $name;
        my $params;
        # if there is one more arg and it is a hashref, then we use na
+med parameters
        if (@_ == 1 and ref $_[0] eq 'HASH') {
            return __run_procedure_named($dbh, $name, $_[0]);
        }
        # otherwise they are positional parameters
        my $sql = "begin $name";
        if (@_){
        $sql .= '(' . join (',' , map ({ '?'} @_  )) . ')';
        }
        $sql .= '; end;';
        # print $sql;
        # prepare
        $sql = $dbh->prepare($sql);
        # bind
        my $i = 1;
        foreach (@_){
            $sql->bind_param($i++, $_);
        }
        # execute
        $sql->execute;
}

sub __run_procedure_named{
        my ($dbh, $name, $params) = @_;
        my $sql = "begin  $name";
        my @p = sort keys %$params;
        if (@p){
            @p = map { "$_ => :$_" } @p;
            $sql .= '(' . join (',', @p) . ')';
        }
        $sql .= '; end;';
        # print $sql;
        # prepare
        $sql = $dbh->prepare($sql);
        # bind
        foreach (keys %$params){
                $sql->bind_param(":$_", $params->{$_});
        }
        # execute
        $sql->execute;
}

sub __run_function{
        my $dbh = shift;
        croak "expected a database handle as first parameter, but got 
+nothing" unless $dbh;
        croak "expected an Oracle database handle as first parameter, 
+but got $dbh" unless ref $dbh;
        my $name = shift;
        croak "expected a function name to run against the Oracle data
+base, but got nothing" unless $name;
        my $params;
        # if there is one more arg and it is a hashref , then we use w
+ith named parameters
        if (@_ == 1 and ref $_[0] eq 'HASH') {
            return __run_function_named($dbh, $name, $_[0]);
        }
        # otherwise they are positional parameters
        my $sql = "begin ? := $name";
        if (@_){
        $sql .= '(' . join (',' , map ({ '?'} @_  )) . ')';
        }
        $sql .= '; end;';
        # print $sql;
        # prepare
        $sql = $dbh->prepare($sql);
        # bind
        my $r;
        my $i = 1;
        $sql->bind_param_inout($i++, \$r, 100);
        foreach (@_){
            $sql->bind_param($i++, $_);
        }
        #execute
        $sql->execute;
        return $r;
}

sub __run_function_named{
        my ($dbh, $name, $params) = @_;
        my $sql = "begin :perl_oracle_procedures_ret := $name";
        my @p = sort keys %$params;
        if (@p){
            @p = map { "$_ => :$_" } @p;
            $sql .= '(' . join (',', @p) . ')';
        }
        $sql .= '; end;';
        # print $sql;
        # prepare
        $sql = $dbh->prepare($sql);
        # bind
        my $r;
        $sql->bind_param_inout(':perl_oracle_procedures_ret', \$r, 100
+);
        foreach (keys %$params){
                $sql->bind_param(":$_", $params->{$_});
        }
        # execute
        $sql->execute;
        return $r;
}

sub run{
    my $w = wantarray;
    # in void context run a procedure
    return __run_procedure(@_) unless defined $w;
    # in non-void context run a function
    return __run_function( @_);
}

sub __run{
    my $w = shift;
    my $name = shift;
    my $dbh = shift;
    # in void context run a procedure
    return __run_procedure($dbh, $name, @_) unless defined $w;
    # in non-void context run a function
    return __run_function($dbh, $name, @_);
}


sub import {
    my $class = shift;
    my $caller = (caller)[0];
    no strict 'refs';
    foreach (@_) {
    my $name = $_;
    my $subname = $name;
    $subname =~ s/\W/_/g;
        *{"$caller\::$subname"} = sub { 
        Oracle::Procedures::__run(wantarray,$name,@_)
    };
    }
}


1;
__END__



=head1 NAME

Oracle::Procedures - Perl extension to make Oracle stored procedures l
+ook like Perl subroutines

=head1 SYNOPSIS

  use Oracle::Procedures qw(sysdate);
  
  my $conn = DBI->connect(.....);
  
  print sysdate($conn);
  

=head1 DESCRIPTION

When developing applications for an Oracle database, it is a good
idea to put all your database access code into stored procedures.
This module provides a convenient way to call these stored
procedures from Perl by creating wrapper subroutines that
produce the necessary SQL statements, bind parameters and run
the query.


=head2 EXPORT

Oracle::Procedures exports subroutines for any stored procedures
(and functions) that you ask it to. You specify the list of
procedures that you want when using the module:

    use Oracle::Procedures qw[ sysdate ]
    
    # gives you
    
    print sysdate($conn);
    

Calling such a subroutine will invoke the stored procedure.
The subroutines expect a DBI database handle as
their first parameter.

=head3 Subroutine names

The names of the subroutine is derived from the name
of the stored procedure. Because the procedure name can
contain characters that are not valid in a Perl procedure name,
it will be sanitized a little:

Everything that is not a letter or a number becomes underscores. 
This will happen for all
procedures that are part of a PL/SQL package, where
the package name and the procedure name are divided by a dot.


    sysdate    =>     sysdate
    dbms_random.random  => dbms_random_random
    hh$$uu    => hh__uu

You can request stored procedures that do not exist.
This will not be detected by Oracle::Procedures, but
results in a database error when you try to call them.


=head3 Procedures and functions

Oracle::Procedures needs to know if you are about
to call a function or a procedure (because the SQL is different).
You have to make sure you call the wrapper subroutines
in the right context.

You have to call procedures in void context.

    # works
    dbms_random_initialize($conn, 12345);
    # fails
    print dbms_random_initialize($conn, 12345);
    
You have to call functions in non-void context.

    # works
    print sysdate($conn);
    # fails
    sysdate($conn);
    
If you try to call a function as a procedure, you will get
a database error.

=head3 Parameters

You can pass parameters to the subroutines
(only IN parameters are supported at the moment)
You can use both positional and named parameters,
but cannot mix the two styles in the same call.

Positional parameters are passed in after the 
database handle, which is always the first parameter:

    dbms_random_initialize($conn, 12345);
    
Named parameters are passed as a hash reference:

    dbms_random_initialize($conn, { val => 12345678 } );

The parameters you use have to match the parameters
defined (in the database) for the stored procedure. 
If they do not, you 
will get a database error at runtime.

=head2 ALTERNATIVE INTERFACE

If you do not want to import wrapper functions, you can still
use the SQL generation and parameter binding mechanism
of Oracle::Procedures:

    Oracle::Procedures::run($conn, 'dbms_random.initialize', 12345);

    print Oracle::Procedures::run($conn, 'sysdate');

This can be useful if you do not know the names of the 
stored procedures at compilation time.
You still have to know if it is a function or a procedure, though,
because you need to call Oracle::Procedures::run in the appropriate
context.


=head1 SEE ALSO

This module is built on top of DBI and DBD::Oracle, and
you need to use these modules to establish a database connection.

DBIx::Procedures::Oracle offers similar functionality.
Unlike Oracle::Procedures, it takes the additional step of
checking in the data dictionary if the procedures you want
exist, and what parameters they need.

=head1 LIMITATIONS

cannot mix named and positional parameters

only IN parameters (this is expected to be fixed in a future release)

=head1 AUTHOR

Thilo Planz, E<lt>thilo@cpan.orgE<gt>

=head1 COPYRIGHT AND LICENSE

Copyright 2004 by Thilo Planz

This library is free software; you can redistribute it and/or modify
it under the same terms as Perl itself. 

=cut
[download]