CODEWITHSUNDEEP
sqloracle-databaseplsql
perrohunter
perrohunter

Reputation: 3526

Evaluate a varchar2 string into a condition for a SQL statement

I'm trying to find which rows are missing from 1 database to another, I already have link to the both DBs and I already found out that I can't just join separate tables so what I'm trying right now is select the ID's from one table and paste them into the select statement for the other DB however I don't know how to parse a clob into a condition.

let me explain further:

I got this collection of varchar2's with all the ID's i need to check on the other DB, and I can iterate through that collection so I get a clob with form: 'id1','id2','id3'

I want to run this query on the other DB

SELECT * FROM atable@db2 
WHERE id NOT IN (clob_with_ids)

but I don't know how to tell PL/SQL to evaluate that clob as part of the statement.

id field on atable@db2 is an integer and the varchar2 id's I got are from runnning a regex on a clob

edit:

I've been ask to add the example I was trying to run:

SELECT *   
  FROM myTable@db1   
  WHERE ( (creation_date BETWEEN to_date('14-JUN-2011 00:00:00','DD-MON-YYYY HH24:MI:SS') AND to_date('14-JUN-2011 23:59:59','DD-MON-YYYY HH24:MI:SS')) ) 
   AND acertain_id NOT IN (    SELECT to_number(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_SUBSTR(payload,'<xmlTag>([[:alnum:]]+)-'),'<xmlTag>',''),'-','')) as sameIDasOtherTable   
                                 FROM anotherTable@db2   
                                WHERE condition1 ='bla'   
                                  AND condition2 ='blabla'   
                                  AND ( (creation_date BETWEEN to_date('14-JUN-2011 00:00:00','DD-MON-YYYY HH24:MI:SS') AND to_date('14-JUN-2011 23:59:59','DD-MON-YYYY HH24:MI:SS')) ) )   
  ORDER BY TO_CHAR(creation_date, 'MM/DD/YYYY') ASC;

I get error ORA-22992

Any suggestiongs?

Upvotes: 0

Views: 1673

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

I don't believe you can do that, but I've been proved wrong on many occasions... Even if you could find a way to get it to treat the contents of the CLOB as individual values for the IN you'd probably hit the 1000-item limit (ORA-01795) fairly quickly.

I'm not sure what you mean by 'I already found out that I can't just join separate tables'. Why can't you do something like:

SELECT * FROM atable@db2 WHERE id NOT IN (SELECT id FROM atable@db1)

Or:

SELECT * from atable@db2 WHERE id IN (
    SELECT id FROM atable@db2 MINUS SELECT id FROM atable@db1)

(Or use @APC's anti-join, which is probably more performant!)

There may be performance issues with joining large tables on remote databases, but it looks like you have to do that at some point, and if it's a one-off task then it might be bearable.

Edited after question updated with join error

The ORA-22992 is because you're trying to pull a CLOB from the the remote database, which doesn't seem to work. From this I assume your reference to not being able to join is because you're joining two remote tables.

The simple option is not to pull all the columns - specify which you need rather than doing a select *. If you do need the CLOB value, the only thing I can suggest trying is using a CTE (WITH tmp_ids AS (SELECT <regex> FROM anotherTable@db2) ...), but I really have no idea if that avoids the two-link restriction. Or pull the IDs into a local temporary table; or run the query on one of the remote databases.

Upvotes: 3

APC
APC

Reputation: 146229

It seems to me you have invested a lot of time in developing the wrong solution. A way simpler solution would be to use a SET operator. This query retrieves all the IDs in the local instance of ATABLE which are missing in the remote instance of the same table:

select id from  atable
minus
select id from  atable@db2

If your heart is set on retrieving the whole local row, you could try an anti-join:

select loc.*
from   atable loc
       left join  atable@db2 rem
           on (loc.id = rem.id )
where rem.id is null
/

Upvotes: 4

Related Questions