/ 
DBIx-ProcedureCall-0.07
River stage zero No dependents
/ DBIx::ProcedureCall::Oracle

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 for versions prior to 9.2.0.5.0

There seems to be a bug in Oracle that prevents the use of bind variables for parameters to table functions prior to version 9.2.0.5.0 (it will fail with an ORA-22905 error -- "cannot access rows from a non-nested table item"). Therefore, 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).

:fetch

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

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/05 by Thilo Planz

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