DBIx::Call - Perl extension to make database stored procedures look like Perl subroutines
use DBIx::Call qw(sysdate); my $conn = DBI->connect(.....); print sysdate($conn);
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.
While the this module's interface is database-independent, only Oracle is currently supported.
DBIx::Call 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 DBIx::Call 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.
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.
use DBIx::Call qw(
sysdate
dbms_random.random
hh\$\$uu
);
# gives you
sysdate(); # no change
dbms_random_random(); # note the underscore
hh__uu(); # dollar signs removed
You can request stored procedures that do not exist. This will not be detected by DBIx::Call, but results in a database error when you try to call them.
DBIx::Call 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.
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.
If you do not want to import wrapper functions, you can still use the SQL generation and parameter binding mechanism of DBIx::Call:
DBIx::Call::run($conn, 'dbms_random.initialize', 12345);
print DBIx::Call::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 DBIx::Call::run in the appropriate context.
This module is built on top of DBI, and you need to use that module to establish a database connection.
DBIx::Procedures::Oracle offers similar functionality. Unlike DBIx::Call, it takes the additional step of checking in the data dictionary if the procedures you want exist, and what parameters they need.
cannot mix named and positional parameters
only IN parameters (this is expected to be fixed in a future release)
Oracle
cursors, LOB
OUT parameters
Cursors
Thilo Planz, <thilo@cpan.org>
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.