DBIx::ProcedureCall::Oracle - Oracle driver for DBIx::ProcedureCall
This is an internal module used by DBIx::ProcedureCall. You do not need to access it directly. However, you should read the following documentation, because it explains how to use DBIx::ProcedureCall with Oracle databases.
DBIx::ProcedureCall 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 (or you can optionally declare the correct type, see below)
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.
If you do not want to rely on this mechanism, you can declare the correct type using the attributes :procedure and :function:
use DBIx::ProcedureCall qw[ sysdate:function dbms_random.initialize:procedure ];
If you use these attributes, the calling context will be ignored and the call will be dispatched according to your declaration.
In addition to "normal" stored procedures and functions, you can also use table functions, which again need a different kind of SQL statement. You use table functions with the :table attribute.
There are two types of functions that return result sets, table functions and ref cursors. To use either, you have to use the special attributes :table or :cursor when declaring the function to DBIx::ProcedureCall. The attributes are explained in detail below.
Currently known attributes are:
Declares the stored procedure to be a function or a procedure, so that the context in which you call the subroutine is of no importance any more.
Rather than importing the generated wrapper subroutine into your own module's namespace, you can request to create it in another package, whose name will be derived from the name of the stored procedure by replacing any dots (".") with the Perl namespace seperator "::".
use DBIx::ProcedureCall qw[ schema.package.procedure:packaged ];
will create a subroutine called
When working with PL/SQL packages, you can declare the whole package instead of the individual procedures inside. This will set up a Perl package with an AUTOLOAD function, which automatically creates wrappers for the procedures in the package when you call them.
use DBIx::ProcedureCall qw[ schema.package:package ]; my $a = schema::package::a_function($conn, 1,2,3); schema::package::a_procedure($conn);
If you declare additional attributes, these attributes will be used for the AUTOLOADed wrappers.
If you need special attributes for individual parts of the package, you can mix in the :packaged style explained above:
# create a package of functions # with the odd procedure use DBIx::ProcedureCall qw[ schema.package:package:function schema.package.a_procedure:packaged:procedure ];
This attribute declares a function (it includes an implicit :function) that returns a refcursor, like this one:
create function test_cursor return sys_refcursor is c_result sys_refcursor; begin open c_result for select * from dual; return c_result; end;
Using :cursor, the wrapper function will give you that cursor. Check the DBD::Oracle documentation about what you can do with that cursor.
Chances are that what you want to do with the cursor is fetch all its data and then close it. You can use one of the various :fetch attributes for just that. If you do, the wrapper function takes care of the cursor and returns the data.
A table function also returns a result set:
create or replace type str2tblType as table of varchar2(100); / create or replace function str2tbl( p_str in varchar2, p_delim in varchar2 default ',' ) return str2tblType PIPELINED as l_str long default p_str || p_delim; l_n number; begin loop l_n := instr( l_str, p_delim ); exit when (nvl(l_n,0) = 0); pipe row( ltrim(rtrim(substr(l_str,1,l_n-1))) ); l_str := substr( l_str, l_n+1 ); end loop; return; end; / select * from str2tbl ('1,2,3');
Similar to :cursor, you can either fetch from that result set yourself (by just declaring :table), or you can use one of the fetch methods (by declaring :fetch IN ADDITION to :table).
use DBIx::ProcedureCall qw( str2tbl:table:fetch[] ); my $data = str2tbl($conn, '1,2,3'); # $data will be like [ , ,  ]
The syntax to call table functions does not supported named parameters. You have to use positional parameters.
There seems to be a bug in Oracle that prevents the use of bind variables for parameters to table functions (it will fail with an ORA-22905 error -- "cannot access rows from a non-nested table item"). This appears to affect all versions prior to 220.127.116.11.0, but has also been seen on later releases on some systems.
Therefore, on affected systems DBIx::ProcedureCall will pass in the parameters literally (not using bind variables) when connected to older Oracle versions. This does not scale very well, so you should consider an upgrade. (Table functions without parameters are not affected).
A system is considered affected if a :table function results in ORA-22905. From that point on, the workaround described above is put in effect for all subsequent queries.
Unless you also specify :table, :fetch assumes that you return the result set using a refcursor (:cursor).
Unfortunately, Oracle does not automatically convert from BOOLEAN to strings. You can specify :boolean to declare a function that returns a BOOLEAN. This will create wrapper code to convert it to 1/0/undef for true/false/NULL.
BOOLEAN values as arguments to procedure calls are currently not supported.
DBIx::ProcedureCall for information about this module that is not Oracle-specific.
DBIx::Procedures::Oracle offers similar functionality. Unlike DBIx::ProcedureCall, it takes the additional step of checking in the data dictionary if the procedures you want exist, and what parameters they need.
Thilo Planz, <email@example.com>
Copyright 2004-06 by Thilo Planz
This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.