SQL & PL/SQL

I'm trying to use a pipelined table function defined in a remote DB (DB_A) from my local DB (DB_B) via a DB Link (DB_A_REMOTE).

The pipelined table function is defined in a package with all the type specifications it needs and works fine when called locally but when called remotely it fails

Here's a sample setup in DB_A:

connect scott/tiger
create or replace
package pkg as
  type rec is record (
    dte date
  );
  type rec_set is table of rec;
  
  function dts(p_eff_date date) return rec_set pipelined;
  function dt(p_eff_date date) return date;
end;
/
create or replace
PACKAGE BODY pkg AS

  function dts(p_eff_date date) return rec_set pipelined AS
    r rec;
  BEGIN
    r.dte := p_eff_date;
    pipe row(r);
    r.dte := r.dte+1;
    pipe row(r);
    RETURN;
  END dts;

  function dt(p_eff_date date) return date as
  begin
    return p_eff_date;
  end;

END pkg;
/

In DB_B I have the following setup:

create database link DB_A_REMOTE connect to Scott identified by tiger using 'DB_A';
create or replace synonym RPKG for PKG@DB_A_REMOTE;

In DB_A I can access both functions from PKG just fine

SQL> select pkg.dt(sysdate) from dual
DJ.DT(SYSDATE)       
----------------------
21-SEP-2012 11:26:31   

SQL> select * from table(pkg.dts(sysdate))
DTE                  
----------------------
21-SEP-2012 11:26:31   
22-SEP-2012 11:26:31   
23-SEP-2012 11:26:31   
24-SEP-2012 11:26:31   

However in DB_B the I get the following:

SQL> select rpkg.dt(sysdate) from dual
RPKG.DT(SYSDATE)     
----------------------
21-SEP-2012 11:29:05   

SQL> select * from table(rpkg.dts(sysdate))

Error starting at line 2 in command:
select * from table(rpkg.dts(sysdate))
Error at Command Line:2 Column:20
Error report:
SQL Error: ORA-06553: PLS-752: Table function DTS is in an inconsistent state.
06553. 00000 -  "PLS-%s: %s"
*Cause:    
*Action:

selecting rpkg.dt shows that I can get to the remote package and execute functions in it, but the second line is where my problem is.

Why is the table function in an inconsistent state and How can I fix it so that it will work accross the database linlk?

Edited by: Sentinel on Sep 21, 2012 11:35 AM