MEMBER OF condition across DB Link [message #651411] |
Tue, 17 May 2016 09:32 |
mjm22
Messages: 54 Registered: January 2010 Location: Singapore
|
Member |
|
|
Hi,
I have come across a scenario that I am struggling to understand...
I am trying to insert to a table a subset of values selected form another table on a remote database. The selection is controlled via a MEMBER OF condition that limits the selection on the remote table. However this insert fails with invalid datatype but I am trying to understand exactly why. The type I am using to store the list is identical on both databases and the datatype on the table is the same as the datatype of the list.
The below code shows the issue..
First I set up the tables and types on each database
-- on database 1
CREATE TABLE item_values (item_id NUMBER(3), DESCRIPTION VARCHAR2(120));
INSERT INTO item_values VALUES (321,'A Thingy');
INSERT INTO item_values VALUES (421,'Another Thingy');
INSERT INTO item_values VALUES (521,'A What do ya call it');
CREATE OR REPLACE TYPE "NUM_TT" AS TABLE OF NUMBER;
CREATE TABLE item_temp AS SELECT DESCRIPTION FROM item_values WHERE 1 = 2;
-- on database 2 .. need to add own db link descriptor <remote db> for Database 1
CREATE TABLE item_values AS (SELECT * FROM item_values@<remote db>);
CREATE OR REPLACE TYPE "NUM_TT" AS TABLE OF NUMBER;
Now run the below on Database 1, replacing <remote db> with db link identifier for Database 2.
-- on database 1 run
DECLARE
v_list NUM_TT := NEW NUM_TT();
BEGIN
SELECT item_id
BULK COLLECT INTO v_list
FROM item_values
WHERE item_id < 500;
dbms_output.put_line('Insert into local table using data from local table');
INSERT INTO
item_temp
(DESCRIPTION)
( SELECT t.DESCRIPTION
--BULK COLLECT INTO v_text_tt
FROM item_values t
WHERE t.item_id MEMBER OF v_list
);
FOR item IN (
SELECT *
FROM item_temp
)
LOOP
dbms_output.put_line(' Item is ' || item.description);
END LOOP;
dbms_output.put_line('Insert into local table using data from dblink');
INSERT INTO
item_temp
(DESCRIPTION)
( SELECT t.DESCRIPTION
--BULK COLLECT INTO v_text_tt
FROM item_values@<remote db> t
WHERE t.item_id MEMBER OF v_list
);
FOR item IN (
SELECT *
FROM item_temp
)
LOOP
dbms_output.put_line(' Item is ' || item.description);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;
/
We can see from the output that the first insert works but the second version fails. When all types seem the same I am unsure exactly why this is.
Insert into local table using data from local table
Item is A Thingy
Item is Another Thingy
Insert into local table using data from dblink
ORA-00902: invalid datatype
ORA-02063: preceding line from ADM
|
|
|
|
Re: MEMBER OF condition across DB Link [message #651418 is a reply to message #651412] |
Tue, 17 May 2016 10:07 |
mjm22
Messages: 54 Registered: January 2010 Location: Singapore
|
Member |
|
|
Hi BlackSwan,
Thanks for the reply. My example is simplified for the purposes of this board and my question more about the concept.
When I use <500 here it is not something I am able to do in the application. In reality the list is a small sub-selection of a larger range of integers. The selected values are not easily grouped with < or > operators as they appear sporadically within the large range.
They are in fact stored in a separate parameters table (all with a same 'key' that allow me to retrieve them). The parameter table is updated by another process as new products are added which qualify for the sub-selection. If the Item Id has been added to the list of Item ID's in the parameters table then I need to include it, otherwise not. More realistically, rather than using <500, I should have specifically selected Item ID's 321 and 521 for the list and included other entries item_values that fall before, between and after these two.
Thanks,
Mike
|
|
|
|
|
Re: MEMBER OF condition across DB Link [message #651430 is a reply to message #651423] |
Tue, 17 May 2016 13:51 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
cookiemonster wrote on Tue, 17 May 2016 11:31That's the problem with types. One type is not equal to another type with the exact same definition.
Correct and unfortunately we can't make collections of built-in types the same type. But we can do it with collections of objects:
On local database:
a) Get SYS_GUID to be used as type OID:
SQL> select SYS_GUID() FROM DUAL
2 /
SYS_GUID()
--------------------------------
330E5501DCD92903E0530AC9D90AFE9E
SQL>
b) Create object & table types:
CREATE OR REPLACE
TYPE NUM_OBJ
OID '330E5501DCD92903E0530AC9D90AFE9E' -- obtained from SYS_GUID()
AS OBJECT(
N NUMBER
)
/
Type created.
CREATE OR REPLACE
TYPE NUM_TT
AS TABLE OF NUM_OBJ
/
Type created.
SQL>
c) Create table:
CREATE TABLE TBL
AS
SELECT LEVEL N
FROM DUAL
CONNECT BY LEVEL <= 5
/
Table created.
SQL>
Now on remote database:
a) Create object & table types with same object type OID as on local database:
SQL> CREATE OR REPLACE
2 TYPE NUM_OBJ
3 OID '330E5501DCD92903E0530AC9D90AFE9E' -- obtained from SYS_GUID()
4 AS OBJECT(
5 N NUMBER
6 )
7 /
Type created.
SQL> CREATE OR REPLACE
2 TYPE NUM_TT
3 AS TABLE OF NUM_OBJ
4 /
Type created.
SQL>
b) Now we can use MEMBER OF:
declare
v_tbl num_tt := num_tt(num_obj(1),num_obj(3),num_obj(7));
cursor v_cur
is
select *
from tbl@int
where num_obj(n) member of v_tbl;
begin
for v_rec in v_cur loop
dbms_output.put_line('N = ' || v_rec.n);
end loop;
end;
/
N = 1
N = 3
PL/SQL procedure successfully completed.
SQL>
SY.
|
|
|
|
Re: MEMBER OF condition across DB Link [message #651456 is a reply to message #651430] |
Wed, 18 May 2016 04:32 |
mjm22
Messages: 54 Registered: January 2010 Location: Singapore
|
Member |
|
|
Thanks Cookie
Quote:That's the problem with types. One type is not equal to another type with the exact same definition.
-- that was what I was interested in, an understanding of why I get the issue.
and Sy for taking the time to think about an example of how to circumvent the issue using a type based on a Global Unique ID.
They are the useful responses I am looking for.
BlackSwan, my example was simplified for the benefit of the understanding the problem I encountered regarding types of the same definition causing exceptions when used across DB Links. I tried to do this without all the noise of the real world limitations I am working within as they are of no consequence to the question I asked. In this regard it is not a 'fake' problem, even with every detail regarding my real world constraints or limitations included, my question was still the same... Why.
As far as I understand, this forum is for discussing issue or raising questions about SQL and PLSQL. So my question was valid, it was 'Why?' Your response answered a different question regardless of any 'real world' limitations I omitted. If you don't know the answer or chose not to answer the question I am asking then that's fine. But then I don't really see why you felt the need to reply .... but thanks for your time anyway.
M
|
|
|