DBD::Oracle implicit results

For more info on driver read DBD::Oracle

Traditionally Oracle pass SQL SELECT result sets within SQL 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.

Although the Oracle cursor mechanism is very powerful, this power comes with a cost of having to deal with cursor complexity on the Perl side.

Compared this cursor logic to DBD::Sybase, where you just can SELECT without fuzz from tables inside procedures. These select row magically appear in the result set, same as if you would do a plain SELECT.

On Oracle terminology this mechanism is called implicit results. Implicit results are way With implicit results its way easier to get access from Perl to SELECT result sets from stored procedures.

With 12.2c, Oracle supports returning result sets like does DBD::Sybase does for ages.

A look at DBD::Sybase implicit results

In a Sybase a procedure block you can invoke multiple select statements. The resulting implicit results (read rows sets) can iterated trough later from from Perl:

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

The first result set is available immediately after execute, with syb_more_results one iterate to the next (second, third ...) result set. As long there are more result sets syb_more_results will return 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

Oracle 12.2c newly supports implicit results by passing 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 - Sybase 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 change of SQL code for calling from Perl. No need to introduce cursors at Perl application level. Fetch code to process rows can remain as is.