select test.test@database_b('some input parameter') from dual;

orcl>
orcl> create database link l1 connect to scott identified by tiger using 'orcl';

Database link created.

orcl> create function dname (dno number) return varchar2 as
  2  vdname varchar2(20);
  3  begin
  4  select dname into vdname from dept where deptno=dno;
  5  return vdname;
  6  end;
  7  /

Function created.

orcl> select dname@l1(10) from dual;

DNAME@L1(10)
---------------------------------------------------------------------------------------

ACCOUNTING

orcl>

orcl> create or replace type num_tab as table of number;
  2
  3
  4  /

Type created.

orcl> create or replace function transform(depts in sys_refcursor)
  2  return num_tab
  3  pipelined
  4  is
  5  no number;
  6  begin
  7  loop
  8  fetch depts into no;
  9  exit when depts%notfound;
 10  no:=no * 10;
 11  pipe row(no);
 12  end loop;
 13  return;
 14  end;
 15  /

Function created.

orcl> select * from table(transform@l1(cursor(select deptno from scott.dept)));
select * from table(transform@l1(cursor(select deptno from scott.dept)))
*
ERROR at line 1:
ORA-30626: function/procedure parameters of remote object types are not supported


orcl>

select test.test@database_b('some input parameter') from dual;

orcl> create view dept_view as select * from dept@l1;

View created.

orcl> select * from table(transform@l1(cursor(select deptno from scott.dept)));
select * from table(transform@l1(cursor(select deptno from scott.dept)))
                    *
ERROR at line 1:
ORA-30626: function/procedure parameters of remote object types are not supported


orcl> select * from table(transform(cursor(select deptno from scott.dept_view)));

COLUMN_VALUE
------------
         100
         200
         300
         400

orcl>

MIKB2> create table t (id integer, val varchar2(10));

Table created.

MIKB2> insert into t select level, chr(ascii('A')+mod(level,3)) from dual connect by level <= 10;

10 rows created.

MIKB2> select * from t;
        ID VAL
---------- ----------
         1 B
         2 C
         3 A
         4 B
         5 C
         6 A
         7 B
         8 C
         9 A
        10 B

10 rows selected.

MIKB2> commit;

Commit complete.

MIKB2> create or replace package mypkg is
  2    myvalue varchar2(10);
  3    function get_value return varchar2;
  4    procedure set_value (p in varchar2);
  5  end;
  6  /

Package created.

MIKB2> create or replace package body  mypkg is
  2    function get_value return varchar2 is begin return myvalue; end;
  3    procedure set_value (p in varchar2) is begin myvalue := p; end;
  4  end;
  5  /

Package body created.

MIKB2> create or replace view v as select * from t where val = mypkg.get_value;

View created.

MIKB2> exec mypkg.set_value('A');

PL/SQL procedure successfully completed.

MIKB2> select * from v;
        ID VAL
---------- ----------
         3 A
         6 A
         9 A

3 rows selected.

MIKA> exec mypkg.set_value@mikb2('A');

PL/SQL procedure successfully completed.

MIKA> select * from v@mikb2;
        ID VAL
---------- ----------
         3 A
         6 A
         9 A

3 rows selected.

MIKA> exec mypkg.set_value@mikb2('B');

PL/SQL procedure successfully completed.

MIKA> select * from v@mikb2;
        ID VAL
---------- ----------
         1 B
         4 B
         7 B
        10 B

4 rows selected.

MIKB2> create table t (id integer, val varchar2(10));

Table created.

MIKB2> insert into t select level, chr(ascii('A')+mod(level,3)) from dual connect by level <= 10;

10 rows created.

MIKB2> select * from t;

        ID VAL
---------- ----------
         1 B
         2 C
         3 A
         4 B
         5 C
         6 A
         7 B
         8 C
         9 A
        10 B

10 rows selected.

MIKB2> commit;

Commit complete.

MIKA> create or replace package mypkg is
  2    myvalue varchar2(10);
  3    function get_value return varchar2;
  4    procedure set_value (p in varchar2);
  5  end;
  6  /

Package created.

MIKA> create or replace package body  mypkg is
  2    function get_value return varchar2 is begin return myvalue; end;
  3    procedure set_value (p in varchar2) is begin myvalue := p; end;
  4  end;
  5  /

Package body created.

MIKA> create or replace view v as select * from t@mikb2 where val = mypkg.get_value;

View created.

MIKA> exec mypkg.set_value('A');

PL/SQL procedure successfully completed.

MIKA> select * from v;

        ID VAL
---------- ----------
         3 A
         6 A
         9 A

MIKA> exec mypkg.set_value('B');

PL/SQL procedure successfully completed.

MIKA> select * from v;

        ID VAL
---------- ----------
         1 B
         4 B
         7 B
        10 B

MIKA> drop package body  mypkg ;

Package body dropped.

MIKA> create or replace package mypkg is
  2    procedure set_value (p in varchar2);
  3  end;
  4  /

Package created.

MIKA> create or replace package body  mypkg is
  2    procedure set_value (p in varchar2) is begin dbms_session.set_context ('MYAPPCTX','MYVAR',p); end;
  3  end;
  4  /

Package body created.

MIKA> create context myappctx using mypkg;

Context created.

MIKA> create or replace view v as select * from t@mikb2 where val = sys_context('MYAPPCTX','MYVAR');

View created.

MIKA> exec mypkg.set_value('A');

PL/SQL procedure successfully completed.

MIKA> select * from v;

        ID VAL
---------- ----------
         3 A
         6 A
         9 A

MIKA> exec mypkg.set_value('B');

PL/SQL procedure successfully completed.

MIKA> select * from v;

        ID VAL
---------- ----------
         1 B
         4 B
         7 B
        10 B

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> 
SQL> Create Or Replace Type t_scalaire As Object (
  2    id   integer,
  3    Val  varchar2(10)
  4  )
  5  /
 
Type created
SQL> Create Or Replace Type t_nt As Table Of t_scalaire
  2  /
 
Type created
SQL> Create Context t_ctx using aPkg
  2  /
 
Context created
SQL> Create Or Replace Package aPkg Is
  2    Procedure SetCtx (
  3      value   Varchar2
  4    );
  5  End;
  6  /
 
Package created
SQL> Create Or Replace Package Body aPkg Is
  2    Procedure SetCtx (
  3      value   Varchar2
  4    ) Is
  5    Begin
  6      DBMS_SESSION.set_context('t_ctx', 'val', value);
  7    End;
  8  End;
  9  /
 
Package body created
SQL> Create Or Replace Function Get_T_Data Return t_nt Pipelined Is
  2  Begin
  3    For rec In (Select * From t Where val = sys_context('t_ctx','val'))
  4    Loop
  5      Pipe row(t_scalaire(rec.id, rec.val));
  6    End Loop;
  7    --
  8    Return;
  9  End;
 10  /
 
Function created
SQL> Create View t_v (id, val) As
  2  Select *
  3    From table(get_t_data())
  4  /
 
View created
 
SQL> 

Connecté à :
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exec aPkg.SetCtx@adb('A');

Procédure PL/SQL terminée avec succès.

SQL> select * from t_v@adb
  2  /

        ID VAL
---------- ----------
         3 A
         6 A
         9 A

create or replace type nums as table of number;
/

create or replace function pipenums 
return nums
pipelined
as
begin
for i in 1..30 loop
pipe row(i);
dbms_lock.sleep(1);
end loop;
end;
/


set arraysize 1

select * from table(pipenums);