Home » SQL & PL/SQL » SQL & PL/SQL » FORALL INSERT not supported on remote tables (Oracle 10g)
FORALL INSERT not supported on remote tables [message #570012] Sun, 04 November 2012 13:16 Go to next message
mape
Messages: 232
Registered: July 2006
Location: Slovakia
Senior Member
Hello

I need to figure out how to transfer a large amount of data from remote table by db link
into the local table.

I try to do that by plsq below but I got error:
PLS-00739: FORALL INSERT/UPDATE/DELETE not supported on remote tables

DECLARE

type t_varchar is table of varchar2(100); 
l_data  t_varchar ;

CURSOR r IS 
SELECT id
FROM TMP_MAPE_WEB_ID;

BEGIN
  OPEN r;
  LOOP
    FETCH r BULK COLLECT INTO l_data LIMIT 10000;

   
    FORALL i IN 1..l_data.COUNT
    INSERT INTO TMP_MAPE_WEB_REPORTING  (ID, VIEW_NAME) 
     select ID, VIEW_NAME
                     from WEBREPORTING.WEB_REPORTING@dbname  
                     where id=l_data(i);

    
    EXIT WHEN r%NOTFOUND;
  END LOOP;
  COMMIT;
  CLOSE r;
END ;


Does anybody have idea how to do that fast?

Thanks a lot

Regards
Re: FORALL INSERT not supported on remote tables [message #570013 is a reply to message #570012] Sun, 04 November 2012 13:21 Go to previous messageGo to next message
BlackSwan
Messages: 23151
Registered: January 2009
Senior Member
DECLARE
BEGIN
INSERT INTO tmp_mape_web_reporting 
            (id, 
             view_name) 
SELECT id, 
       view_name 
FROM   webreporting.web_reporting@dbname 
WHERE  id IN (SELECT id 
             FROM   tmp_mape_web_id); 
end;
Re: FORALL INSERT not supported on remote tables [message #570014 is a reply to message #570013] Sun, 04 November 2012 13:30 Go to previous messageGo to next message
mape
Messages: 232
Registered: July 2006
Location: Slovakia
Senior Member

I tried to do that as you have written too but unsuccessfully, plsq query does not want to finish.
Number of records is 12728129.
Re: FORALL INSERT not supported on remote tables [message #570015 is a reply to message #570014] Sun, 04 November 2012 13:37 Go to previous messageGo to next message
BlackSwan
Messages: 23151
Registered: January 2009
Senior Member
>Number of records is 12728129.
exactly which number of records?

more often than not "TEMP" table, such as TMP_MAPE_WEB_ID & TMP_MAPE_REPORTING, are really not required by Oracle.
Are statistics current on TMP_MAPE_WEB_ID?
does webreporting.web_reporting@dbname have INDEX on ID column?
post results from following SQL
SELECT COUNT(*) FROM webreporting.web_reporting@dbname ;
SELECT COUNT(*) FROM webreporting.web_reporting@dbname WHERE id IN (SELECT id FROM tmp_mape_web_id);
Re: FORALL INSERT not supported on remote tables [message #570016 is a reply to message #570015] Sun, 04 November 2012 13:39 Go to previous messageGo to next message
mape
Messages: 232
Registered: July 2006
Location: Slovakia
Senior Member

Remote table webreporting.web_reporting@dbname have INDEX on ID column

SELECT id FROM tmp_mape_web_id => 12728129
Re: FORALL INSERT not supported on remote tables [message #570017 is a reply to message #570016] Sun, 04 November 2012 13:45 Go to previous messageGo to next message
BlackSwan
Messages: 23151
Registered: January 2009
Senior Member
since you choose to ignore Posting Guideline & choose to not post requested details, I choose to cease wasting my time trying to assist you.
You're On Your Own (YOYO)!
Re: FORALL INSERT not supported on remote tables [message #570019 is a reply to message #570017] Sun, 04 November 2012 14:05 Go to previous message
mape
Messages: 232
Registered: July 2006
Location: Slovakia
Senior Member
SELECT count(*) FROM tmp_mape_web_id => 12728129
Previous Topic: Get all possible paths
Next Topic: ora 00001 unique contraint hr.jhist_emp_id_st_date_pk violated
Goto Forum:
  


Current Time: Sat Dec 20 05:55:02 CST 2014

Total time taken to generate the page: 0.08165 seconds