Home » SQL & PL/SQL » SQL & PL/SQL » MEMBER OF condition across DB Link (11g2)
MEMBER OF condition across DB Link [message #651411] Tue, 17 May 2016 09:32 Go to next message
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 #651412 is a reply to message #651411] Tue, 17 May 2016 09:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
NEVER do in PL/SQL that which can be done in plain SQL
It is foolish & wasteful to move data into PL/SQL table only to move it into another heap table.


INSERT INTO item_temp
SELECT item_id
FROM item_values
WHERE item_id < 500;
Re: MEMBER OF condition across DB Link [message #651418 is a reply to message #651412] Tue, 17 May 2016 10:07 Go to previous messageGo to next message
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 #651420 is a reply to message #651418] Tue, 17 May 2016 10:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
blah, blah, blah

you post fake problem, then you get wrong answer

While you may be able to achieve what you desire, it will be inefficient & won't scale.

More often than not, Oracle rarely requires "temp" table; either in SQL or PL/SQL

When you decide to post valid test case, then you might get useful response.
Re: MEMBER OF condition across DB Link [message #651423 is a reply to message #651420] Tue, 17 May 2016 10:31 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
That's the problem with types. One type is not equal to another type with the exact same definition.
Re: MEMBER OF condition across DB Link [message #651430 is a reply to message #651423] Tue, 17 May 2016 13:51 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
cookiemonster wrote on Tue, 17 May 2016 11:31
That'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 #651454 is a reply to message #651430] Wed, 18 May 2016 04:11 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
Very cool stuff - learned something new today.
Thanks for sharing SY!
Re: MEMBER OF condition across DB Link [message #651456 is a reply to message #651430] Wed, 18 May 2016 04:32 Go to previous message
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
Previous Topic: Not a group by expression (but not in sqlplus)
Next Topic: timestamp question
Goto Forum:
  


Current Time: Thu Mar 28 17:52:43 CDT 2024