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 which hold the selected rows. For each SELECT a cursor must be declared as out parameters 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;
}

Implementation of DBD::Oracle implicit results

As of 12.2c, Oracle supports returning result sets like does DBD::Sybase does for ages. In Oracle terminology this mechanism is called implicit results. With implicit results its way easier to get access from Perl to SELECT result sets from stored procedures.

Implicit results pass ref cursors via the DBMS_SQL.RETURN_RESULT() function. With implicit results its possible to retrieve one (or more) result sets, functionally equal to Sybase result sets.

To make this result sets available from Oracle procedures, a few changes on the DBD::Oracle driver have to be made.

Lets explore an example Oracle implicit result block:

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:

$db -> {ora_implicit_prefetch} = 1; # Behave like DBD::Sybase
$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;
}

wrap up

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!

One way is to create for each ported procedure a wrapper function from which you can do SELECT. However this hack require you to create a wrapper function but also you need create types for returned table and rows to complete the solution. Also with this workaround you can only support one result set.

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.