DBD::Oracle implicit results

For more info on driver read DBD::Oracle

Traditionally Oracle pass SQL SELECT result sets within SQL blocks or procedures back to the application via ref cursors. Each cursor hold selected rows and each SELECT a cursor must be declared as out parameter of the procedure. From the Perl side each ref cursor is bound to Perl variable and via this variable rows can be fetched.

my $sth = $db -> prepare(q{
  BEGIN
    OPEN c1: FOR SELECT 'banana','apple','orange' FROM DUAL;
    OPEN c2: FOR SELECT 'cat','dog' FROM DUAL;
  END;
});

# bind perl variables to cursors
my ($c1,$c2); # declare cursors
$sth->bind_param_inout( ":c1", \$c1, 0, { ora_type => ORA_RSET } );
$sth->bind_param_inout( ":c2", \$c2, 0, { ora_type => ORA_RSET } );

$sth->execute;

my $first_result_set = $c1->fetchall_arrayref;
my $second_result_set = $c2->fetchall_arrayref;

Although the Oracle cursor mechanism is provides a powerful cursor read/write mechanism, this power comes with a cost of always having to deal with cursor complexity on the Perl side.

Especially when you port a Perl application from Sybase to Oracle, introduction of cursor logic would drastically increase scope of migration by having to rewrite much Perl application logic. See wrap up at end of this page for more considerations.

Compare Oracle cursor logic to DBD::Sybase result sets. With Sybase you just can SELECT without fuzz from tables inside blocks/procedures. These select row magically appear in the result set, same as if you would do a plain SELECT:

my $sth = $db -> prepare(q{
    SELECT 'banana','apple','orange' -- first result set
    SELECT 'cat','dog'               -- second result set
});

The first result set is available immediately after execute, with syb_more_results one can iterate to the next (second, third ...) result set. As long there are more result sets syb_more_results will yield true:

# first result set is prefetched by DBD::Sybase
my $first_result_set = $sth->fetchall_arrayref;
if ($sth->{syb_more_results}) {  # fetch second second result set, if and check if there is one
  my $second_result_set = $sth->fetchall_arrayref;
}

DBD::Oracle implicit results

As of 12.2c, Oracle supports return of one or more ref cursors from a procedure using by calling for each result the DBMS_SQL.RETURN_RESULT(ref_cursor) function. In Oracle terminology this mechanism is called implicit results. By taking advantage of implicit results, DBD::Oracle can get same calling interface as DBD::Sybase.

To make implicit result functionality available within DBD::Oracle, Some changes on the driver source code have to be made. But first, lets explore a Perl usage example:

my $sth = $db -> prepare(q{
  DECLARE
    l1 SYS_REFCURSOR;
    l2 SYS_REFCURSOR;
  BEGIN
    OPEN l1 FOR SELECT 'banana','apple','orange' FROM DUAL;
    DBMS_SQL.RETURN_RESULT(l1);
    OPEN l2 FOR SELECT 'cat','dog' FROM DUAL;
    DBMS_SQL.RETURN_RESULT(l2);
  END;
});

With oracle 12.2c and new driver we can acquire result sets from procedures, similar to Sybase:

$sth->execute;
# the main $sth does not hold results until ora_next_result is called
if ($sth->ora_next_result) {  # fetch first result set into $sth
  # only after ora_next_result $sth will hold results
  my $first_result_set = $sth->fetchall_arrayref;
}
# fetch next result set into $sth
if ($sth->ora_next_result) {  # fetch second result set into $sth
  my $second_result_set = $sth->fetchall_arrayref;
}

DBD::Oracle - implicit prefetch mode

If you want to Migrate a big Perl5/SQL project from Sybase to Oracle, you like to have as few changes possible in Perl code. With two minor changes DBD::Oracle can mimic DBD::Sybase result set behaviour:

• implement option ora_implicit_prefetch on database handle. With this option set, execute will fetch first result set automatically if available.
• implement $sth->{syb_more_results} as alias of ora_next_result

These changes will further eliminated the need to change perl application code, because Oracle and Sybase Perl code can be equal:

# set up specific to Oracle
$db -> {ora_implicit_prefetch} = 1; # Behave like DBD::Sybase

# Same code for Sybase and Oracle
$sth->execute;
my $first_result_set = $sth->fetchall_arrayref;
if ($sth->{syb_more_results}) {  # iterate to second, if and check if there is one
  my $second_result_set = $sth->fetchall_arrayref;
}

Sybase to Oracle Migration

The cursor complexity is specially becomes prevalent when you port a Sybase or SQL Server application to Oracle. Then you are confronted having to rewrite each procedure returning SELECT result sets but also to find a workaround to handle the result sets someway differently in Perl!

In a mostly As-Is migration, in the desire to avoid to introduce cursors, a workaround is to create for each ported procedure a wrapper function from which you can SELECT. This workaround require you to create a wrapper function and corresponding return row- and table-type to complete the solution. Having to wrap all stored procedures being called from Perl. Having many stored procedure calls from Perl, this can quickly become a daunting task. And this workaround is you are limited to one result set per procedure call.

Using a modified DBD::Oracle driver with support of implicit results, porting Perl application code becomes way easier. Changes then can be limited to:

• set connection handle to use ora_implicit_prefetch
• change within Perl of SQL procedure invocation code from Sybase convention to Oracle convention

No need to introduce cursors at Perl application level. Fetch code to process rows can remain as is.