Remote Pipeline (split) [message #322488] |
Fri, 23 May 2008 13:22 |
rdumont
Messages: 1 Registered: May 2008
|
Junior Member |
|
|
I work with 2 oracle instances on two different servers.
I'll like to query over the first instance from the second instance using some package functions and retrieve objects and collection of objects values.
I found many articles about this but none are giving a clear working solution.
Having it working locally is pretty easy.
Doing it over a dblink seems to be a different challenge...
Here is what I have done so far:
SQL_USER_A_SRV_1> show rel
release 1002000300
SQL_USER_A_SRV_1> SELECT SYS_OP_GUID() FROM DUAL;
SYS_OP_GUID()
--------------------------------
4DE61079BABE8848E040A8C03F01767E
SQL_USER_A_SRV_1> CREATE or replace type T_TESTOBJ
2 OID '4DE61079BABE8848E040A8C03F01767E'
3 as object
4 (
5 testobjno number,
6 testobjname varchar2(32)
7 );
8 /
Type created.
SQL_USER_A_SRV_1> SELECT SYS_OP_GUID() FROM DUAL;
SYS_OP_GUID()
--------------------------------
4DE61079BAC78848E040A8C03F01767E
SQL_USER_A_SRV_1> CREATE OR REPLACE type T_TESTOBJ_TAB
2 OID '4DE61079BAC78848E040A8C03F01767E'
3 as table of T_TESTOBJ;
4 /
Type created.
SQL_USER_A_SRV_1> create or replace package testobjpck as
2
3 function testobj( p_param number ) return T_TESTOBJ_TAB;
4
5 function test_one_obj( p_param number ) return t_testobj;
6
7 function testnum return number;
8
9 end;
10 /
Package created.
SQL_USER_A_SRV_1> create or replace package body testobjpck as
2
3 function testobj( p_param number ) return T_TESTOBJ_TAB is
4
5 l_collection t_testobj_tab;
6
7 begin
8
9 select
10 cast (
11 multiset (
12 select rownum as testobjno,
13 substr( 'abcdefghijklmnopqrstuvwxyz', rownum, 1 ) as testobjname
14 from all_objects
15 where rownum <= p_param
16 ) as t_testobj_tab
17 )
18 into l_collection
19 from dual;
20
21 return l_collection;
22
23 end;
24
25 function test_one_obj( p_param number ) return t_testobj is
26 begin
27
28 return t_testobj( p_param, substr( 'abcdefghijklmnopqrstuvwxyz', p_param, 1 ) );
29
30 end;
31
32 function testnum return number is
33 begin
34 return 8;
35 end;
36
37 end;
38 /
Package body created.
SQL_USER_A_SRV_1> select testobjpck.testnum from dual;
TESTNUM
----------
8
SQL_USER_A_SRV_1> select testobjpck.test_one_obj(10) from dual;
TESTOBJPCK.TEST_ONE_OBJ(10)(TESTOBJNO, TESTOBJNAME)
--------------------------------------------------------------------------------
T_TESTOBJ(10, 'j')
SQL_USER_A_SRV_1> select testobjpck.testobj(10) from dual;
TESTOBJPCK.TESTOBJ(10)(TESTOBJNO, TESTOBJNAME)
--------------------------------------------------------------------------------
T_TESTOBJ_TAB(T_TESTOBJ(1, 'a'), T_TESTOBJ(2, 'b'), T_TESTOBJ(3, 'c'), T_TESTOBJ
(4, 'd'), T_TESTOBJ(5, 'e'), T_TESTOBJ(6, 'f'), T_TESTOBJ(7, 'g'), T_TESTOBJ(8,
'h'), T_TESTOBJ(9, 'i'), T_TESTOBJ(10, 'j'))
SQL_USER_A_SRV_1> select * from TABLE ( cast( testobjpck.testobj(10) as t_testobj_tab ) );
TESTOBJNO TESTOBJNAME
---------- --------------------------------
1 a
2 b
3 c
4 d
5 e
6 f
7 g
8 h
9 i
10 j
10 rows selected.
SQL_USER_A_SRV_1> grant execute on testobjpck to user_b;
Grant succeeded.
SQL_USER_A_SRV_1>
Now, that I have something working on the first instance, let's move on the second one...
SQL_USER_B_SRV_2> show rel
release 902000700
SQL_USER_B_SRV_2> create synonym testobjpck for user_a.testobjpck@srv_1;
Synonym created.
SQL_USER_B_SRV_2> select testobjpck.testnum from dual;
TESTNUM
----------
8
Communication is fine... So, let's continue...
SQL_USER_B_SRV_2> select testobjpck.test_one_obj(10) from dual;
select testobjpck.test_one_obj(10) from dual
*
ERROR at line 1:
ORA-30626: function/procedure parameters of remote object types are not
supported
SQL_USER_B_SRV_2> select * from TABLE ( cast( testobjpck.testobj(10) as t_testobj_tab ) );
select * from TABLE ( cast( testobjpck.testobj(10) as t_testobj_tab ) )
*
ERROR at line 1:
ORA-30626: function/procedure parameters of remote object types are not
supported
Let's try to create the same types on the second instance...
SQL_USER_B_SRV_2> CREATE or replace type T_TESTOBJ
2 OID '4DE61079BABE8848E040A8C03F01767E'
3 as object
4 (
5 testobjno number,
6 testobjname varchar2(32)
7 );
8 /
Type created.
SQL_USER_B_SRV_2> CREATE OR REPLACE type T_TESTOBJ_TAB
2 OID '4DE61079BAC78848E040A8C03F01767E'
3 as table of T_TESTOBJ;
4 /
Type created.
Now, how these types (with same oid) work ?
Like this ?
How can we make these two queries work remotely ?
I read at lot of articles from different sources on the internet.
A few persons say that it is possible when object types are using the same oid.
But I still haven't found any code showing it.
Can you help...
SQL_USER_B_SRV_2> select testobjpck.test_one_obj(10) from dual;
select testobjpck.test_one_obj(10) from dual
*
ERROR at line 1:
ORA-30626: function/procedure parameters of remote object types are not
supported
SQL_USER_B_SRV_2> select * from TABLE ( cast( testobjpck.testobj(10) as t_testobj_tab ) );
select * from TABLE ( cast( testobjpck.testobj(10) as t_testobj_tab ) )
*
ERROR at line 1:
ORA-30626: function/procedure parameters of remote object types are not
supported
SQL_USER_B_SRV_2>
|
|
|