[Hint] Insert records between 2 banks.

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Thu, 15 Sep 2005 11:39 am

See what interesting situation: I had to copy + or-800 thousand records HOT base to a base of tests. Great! Created between the two base DB_LINK and I see the INSERT script-that comitava of 1000 in 1000.

Of man, gave error of FK violated, i.e. This table relates to others and those others weren't populated as the hot base. In summary: in addition to the INSERT of the 800 thousand, I had to INSERT for each related table.

To do this, I created a PROCEDURE that generates the INSERT (I informing FK that gave error).
See:
Code: Select all
create or replace procedure thomas (con varchar2) is
  ban  varchar2(100):='@quente';  --> nome do DB LINK
  --
  cons varchar2(100);
  tab  varchar2(100);
  i    number;
  tmp  varchar2(100);
begin
  --busac nome da Constraint relacionada
  select r_constraint_name
  into cons
  from user_constraints
  where constraint_name=CON;
 
  --Busca o nome da tabela relacionada
  SELECT TABLE_NAME
  INTO TAB
  FROM USER_CONSTRAINTS
  WHERE CONSTRAINT_NAME = cons;
 
  --gera o INSERT !
  dbms_output.put_line('----------------------------------------------------');
  dbms_output.put_line('insert into '||tab||' (');
  dbms_output.put_line('SELECT * FROM '||tab||ban||' a');
  dbms_output.put_line('WHERE not exists');
  dbms_output.put_line('  (SELECT * FROM '||tab||' b');

  --para cada campo da PK faz o relacionamento.
  i:=0; 
  for vcur
  in (select *
      from user_cons_columns a
      where a.constraint_name=cons
     )
  loop
    i:=i+1;
    if i=1
    then tmp:='    where ';
    else tmp:='      and ';
    end if;
    dbms_output.put_line( tmp ||'b.'||vcur.column_name ||' = a.'||vcur.column_name);
   
  end loop;

  --fim 
  dbms_output.put_line(' ));');
  dbms_output.put_line('---------------------------------------------------- '); 
end;
/
IE, every time we give, FK error as follows: Put the procedure to run under the name of FK. The procedure returns the INSERT between the 2 banks, entering just what's missing! (a type of synchronize).

Code: Select all
SQL> exec thomas('CE_EMBARQUE6');
----------------------------------------------------
insert into TB_LOTE_EMBARQUE (
SELECT * FROM TB_LOTE_EMBARQUE@quente a
WHERE not exists
  (SELECT * FROM TB_LOTE_EMBARQUE b
    where b.CD_SEGURADO = a.CD_SEGURADO
      and b.NU_MES_EMBARQUE = a.NU_MES_EMBARQUE
      and b.NU_ANO_EMBARQUE = a.NU_ANO_EMBARQUE
      and b.CD_LOTE = a.CD_LOTE
));
----------------------------------------------------

PL/SQL procedure successfully completed.

SQL>
IE, simply run the generated INSERT and commit. This insert der a FK error again, simply rotate the procedure stating that new FK that gave error. SHow de bola!

:-o
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Tue, 15 Aug 2006 4:24 pm

Thanks for your help, Dr. Gori. :)
suelana
Location: Joinville-SC

Suelana BLU-JLLE


  • See also
    Replies
    Views
    Last Post


      Return to SQL

      Who is online

      Users browsing this forum: No registered users and 3 guests