DBD::Oracle implicit results

For more info on driver read DBD::Oracle

Implicit results are an easy way to pass "select" result sets from stored procedures to Perl DBI applications. Finally Oracle 12.2c supports implicit result sets like 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;
}