Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Cursor Over a DBLink

Cursor Over a DBLink

From: <marcoandres7_at_tutopia.com>
Date: 12 Sep 2005 15:15:46 -0700
Message-ID: <1126563346.040611.10630@g43g2000cwa.googlegroups.com>


Hi,
I'm having trouble with a routine to get all rows from a remote table using a cursor and a DBLink.

The cursor is defined like:
CURSOR param IS SELECT * FROM table_at_dblink And the operation is like follows:

FOR row IN param LOOP
  UPDATE table t1 SET t1.desc=row.desc WHERE t1.key=row.key   IF param%NOTFOUND THEN
    INSERT INTO table VALUES (row.key, row.desc)   END IF
END LOOP The process was simplified.
The problem is that it takes too long for the 14,000 rows, and other tables involved with this trouble have 250,000 rows...

I 've keep on working, so I had the partial solution:

CURSOR param IS SELECT * FROM temptable_at_dblink

INSERT INTO temptable SELECT * FROM table_at_dblink FOR row IN param LOOP
  UPDATE temptable t1 SET t1.desc=row.desc WHERE t1.key=row.key   IF param%NOTFOUND THEN
    INSERT INTO table VALUES (row.key, row.desc)   END IF
END LOOP
DELETE FROM temptable

This operation is a lot faster, the main difference is that the cursor is on a local table, instead the remote in the previous case.

But we need this operation on several tables, and creating one "temptable" for all, or many "temptables"s it tsn't a good solution.

What is the problem with the cursor?, and how can we fixed it?

Thanks in advance,

Marco Received on Mon Sep 12 2005 - 17:15:46 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US