NAME

DBIx::Procedure::Oracle - Call PL/SQL stored procedures and functions without writing SQL or needing to know about data types or bindings.

SYNOPSIS

 my $dbh = DBI->connect( 'DBI:Oracle:ORCL', 'scott', 'tiger'
                        ,{ PrintError => 0, RaiseError => 1 }
                       );

 $dbh->do( q{ CREATE FUNCTION test(days IN NUMBER) RETURN DATE AS
                  tmp_date DATE;
              BEGIN
                  SELECT sysdate - days INTO tmp_date FROM dual;
                  RETURN tmp_date;
              END;
           }
         );

 my $proc = DBIx::Procedure::Oracle->new( $dbh, object_name => 'test' );

 $date = $proc->execute(7); # 7 days ago

DESCRIPTION

This module allows the calling of Oracle PL/SQL functions and procedures without writing SQL statements to reference them. It queries the Oracle system table ALL_ARGUMENTS to resolve the procedure and determine the correct data types and bindings ( IN, OUT or INOUT ) of the procedure parameters. From this information an anonymous PL/SQL block is built and a database statement handle constructed. At present only positional binding of parameters is supported ( as opposed to named parameters ).

CONSTRUCTOR

new

$proc = DBIx::Procedure::Oracle->new( $dbh , [ owner => 'owner' ] , [ package_name => 'package' ] , object_name => 'procedure' );

Creates a new wrapped Oracle stored procedure. The first argument is a valid database handle. The other arguments are a flattened hash with flags that help to resolve the procedure. The keys are named directly after their column name counterparts in the Oracle system table ALL_ARGUMENTS. The owner and package_name flags are optional. If an owner is not provided then the logged on Oracle user is assumed. If a package_name is not provided then it is assumed that the function or procedure does not have a package. The object_name flag must be present since it gives the name of the function or procedure.

Future work might allow full PL/SQL name resolution, for example;

owner.package.procedure

owner.procedure

See http://download-west.oracle.com/otndoc/oracle9i/901_doc/appdev.901/a89856/d_names.htm

METHODS

execute

my $return_value = $proc->execute( [ PARAMS ] );

Executes the stored procedure. If it is a function then it will return a value and this is returned from the perl subroutine. If it is a procedure then this will not happen and as a default the execute method returns undef.

INOUT or OUT bound arguments to the execute method will have their values returned by reference.

To test for the successful execution of the procedure, either check DBI->err for the latest Oracle error, or set the DBI RaiseError flag to true and wrap the execute call in an eval block.

BUGS

Please report them!

ACKNOWLEDGEMENT

Special thanks to Andrew Theaker ( andrew.j.theaker@gsk.com ) for providing me with the rationalle behind this module back in the days when we used to work together!

AUTHOR

Mark Southern (mark_southern@merck.com)

COPYRIGHT

Copyright (c) 2002, Merck & Co. Inc. All Rights Reserved. This module is free software. It may be used, redistributed and/or modified under the terms of the Perl Artistic License (see http://www.perl.com/perl/misc/Artistic.html)