NAME

DBIx::ProcedureCall::Oracle - Oracle driver for DBIx::ProcedureCall

DESCRIPTION

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.

Procedures and functions

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.

Returning result sets

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.

Oracle-specific attributes

Currently known attributes are:

:procedure / :function

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.

:packaged

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

        schema::package::procedure

:package

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
                ];              

:cursor

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.

:table

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 [ [1], [2], [3] ]

No named parameters for table functions

The syntax to call table functions does not supported named parameters. You have to use positional parameters.

Caveat ( ORA-22905 )

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 9.2.0.5.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.

:fetch

Unless you also specify :table, :fetch assumes that you return the result set using a refcursor (:cursor).

:boolean

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.

SEE ALSO

DBIx::ProcedureCall for information about this module that is not Oracle-specific.

DBD::Oracle

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.

AUTHOR

Thilo Planz, <thilo@cpan.org>

COPYRIGHT AND LICENSE

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.

cpanm DBIx::ProcedureCall

perl -MCPAN -e shell
install DBIx::ProcedureCall