Home » SQL & PL/SQL » SQL & PL/SQL » Remote Pipeline (split)
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>
Previous Topic: Archieving and deleting
Next Topic: Update Nested Table
Goto Forum:
  


Current Time: Tue Feb 18 01:43:23 CST 2025