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 cursors which hold the select data. These cursors must be declared as out parameters of the called procedure. Then from the Perl application side these cursor parameters are bound to perl variables which then after execute can iterated trough from Perl to fetch the rows. Although the Oracle cursor mechanism is very powerful, this power comes with a cost of added complexity of implementation.

When you just want to retrieve a number of selects from a procedure. It seems cumbersome compared to DBD::Sybase where you just can select without fuzz from tables inside procedures. Then these select results magically appear in the result set, same as if you would do a plain select. This mechanism is called implicit results. Implicit results are an easier way to pass "select" result sets from stored procedures to Perl.

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 refcursors trough 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;
}