Jeff Holoman's Blog

Archive for the ‘PL/SQL’ Category

Arrays over DB Link

Posted by jholoman on April 8, 2009

I recently had a problem come up where we needed to return a resultset via database link. I was aware that the data types allowed over DB link were somewhat restricted: no ref-cursors, no object types (including XMLType) to name a few.

I wasn’t aware that you could use PL/SQL Associative arrays (index-by tables) via db link. Here’s a small example:

On the remote db:

SQL> create or replace package aa_test
    as
    type my_emp_type is table of emp%rowtype index by pls_integer;
    function get_emp(p_empno in number) return my_emp_type;
    end;
    /

Package created.
SQL> create or replace package body aa_test
    as
    function get_emp(p_empno in number) return my_emp_type
    as
    l_emp_rec my_emp_type;
    begin
     select * bulk collect into l_emp_rec from emp where empno = p_empno;
    return l_emp_rec;
    exception when no_data_found then
    null;
   end get_emp;
   end aa_test;
  /

Package body created.

And Locally:

SQL> declare
    l_local_emp_rec aa_test.my_emp_type@holoman;
    begin
    l_local_emp_rec := aa_test.get_emp@holoman(7654);
    dbms_output.put_line(l_local_emp_rec(1).ename);
    dbms_output.put_line(l_local_emp_rec(1).job);
    end;
    /
MARTIN
SALESMAN

PL/SQL procedure successfully completed.

Always nice to learn something new.

Posted in Oracle, PL/SQL | Leave a Comment »