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.
