Cursor Over a DBLink

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