Solved

Insert over db-link - transactions?

Posted on 2007-11-28
28
637 Views
Last Modified: 2013-12-07
I want replicate data from a schema to same schema in a remote db. Only new data should be inserted, duplicates ignored.

The insert statements look fine but no data is inserted, and I get no error. My guess is that I need to set up a transaction somehow. Or...?
procedure synchronize_acdata is

sync_string varchar2(4000);

begin  

    /*Cursor fetches all tables in schema*/

    for tbl_row in c_tables ('SCHEMANAME') loop     begin

        sync_string := 'insert into '||tbl_row.owner||'.'||tbl_row.table_name||'@master_dblink select * from '||tbl_row.owner||'.'||tbl_row.table_name;

        dbms_output.put_line (sync_string);

        execute immediate sync_string; 

    exception

    when dup_val_on_index then

        null;

    end;

    end loop;

end;

Open in new window

0
Comment
Question by:Mamarazzi
  • 10
  • 8
  • 4
  • +3
28 Comments
 
LVL 18

Expert Comment

by:Jinesh Kamdar
Comment Utility
U forgot to put the COMMIT; statement :)
0
 
LVL 13

Expert Comment

by:sonicefu
Comment Utility
procedure synchronize_acdata is
sync_string varchar2(4000);
begin  
    /*Cursor fetches all tables in schema*/
    for tbl_row in c_tables ('SCHEMANAME') loop     begin
        sync_string := 'insert into '||tbl_row.owner||'.'||tbl_row.table_name||'@master_dblink select * from '||tbl_row.owner||'.'||tbl_row.table_name;
        dbms_output.put_line (sync_string);
        execute immediate sync_string;
        commit;
    exception
    when dup_val_on_index then
        null;
    end;
    end loop;
end;
0
 
LVL 27

Accepted Solution

by:
sujith80 earned 75 total points
Comment Utility
If your "sync_string " is prepared correctly as an insert into ...select statement; then the
problem is:
- It is a single DML statement, and it rolls back all the records when it hits a dup_val_on_index. I.e. The insertion is not happening row-by-row, and it will not preserve the rows inserted upto the uniqueness violation.

See the below illustration.
SQL> truncate table tbl2;
 

Table truncated.
 

SQL> select * from tbl1;
 

        ID DT

---------- ---------

         3 28-NOV-07

         4 29-NOV-07

         2 27-NOV-07

         1 26-NOV-07
 

SQL> insert into tbl2 select * from tbl1 where rownum < 3;
 

2 rows created.
 

SQL> select * from tbl2;
 

        ID DT

---------- ---------

         3 28-NOV-07

         4 29-NOV-07
 

SQL> insert into tbl2 select * from tbl1;

insert into tbl2 select * from tbl1

*

ERROR at line 1:

ORA-00001: unique constraint (SITRUS.TEST_IDX) violated
 
 

SQL> select * from tbl2;
 

        ID DT

---------- ---------

         3 28-NOV-07

         4 29-NOV-07

Open in new window

0
 
LVL 13

Assisted Solution

by:sonicefu
sonicefu earned 50 total points
Comment Utility
You can optionally write your code as follows
sync_string varchar2(4000);

begin  

    /*Cursor fetches all tables in schema*/

    for tbl_row in c_tables ('SCHEMANAME') loop     begin

        sync_string := 'insert into '||tbl_row.owner||'.'||tbl_row.table_name||'@master_dblink select * from '||tbl_row.owner||'.'||tbl_row.table_name||' minus  select * from '||tbl_row.owner||'.'||tbl_row.table_name||'@master_dblink';

        dbms_output.put_line (sync_string);

        execute immediate sync_string;

        commit;

      end;

    end loop;

end;

Open in new window

0
 

Author Comment

by:Mamarazzi
Comment Utility
Thanks sujith!
How can I avoid that? I want to do the inserts and skip the duplicates.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
when dup_val_on_index then    
    var_sqlcode := SQLCODE;   ---> instead of null we should have something like this.
    var_sqlerrm := SQLERRM;
   --  insert into debug_tbl .... these variables
   or use dbms_output.put_line(..)
   -- dbms_output.put_line('dup_val_on_index exception raised');

Never say null because you will never get to know what is happening actually in the
sense whether code got executed successfully or an exception got raised.

Thanks
0
 
LVL 27

Expert Comment

by:sujith80
Comment Utility
The solution is already provided by sonicefu.

citing my example pls follow.
SQL> insert into tbl2 select * from tbl1

  2  minus

  3  select * from tbl2;
 

2 rows created.
 

SQL> select * from tbl2;
 

        ID DT

---------- ---------

         3 28-NOV-07

         4 29-NOV-07

         1 26-NOV-07

         2 27-NOV-07

Open in new window

0
 

Author Comment

by:Mamarazzi
Comment Utility
Ehhh... I don't get it. What is the solution? The Commit? I already tried that.. and I still get no data inserted. And also if there is any other error  than the dup_val_on_index, I want to rollback the whole thing...
0
 
LVL 27

Expert Comment

by:sujith80
Comment Utility
>> What is the solution?
Solution is to use a MINUS with your target table in the "insert into select" statemet. (See the code snippet I have posted). That ensures that only the extra records are inserted.

>> if there is any other error  than the dup_val_on_index, I want to rollback the whole thing
That is already being done in the original code you posted. It means that if your target table has any of the records from the source table; NO records will be inserted. Is that what you want?
0
 
LVL 13

Expert Comment

by:sonicefu
Comment Utility
If still not working the following code, send output of dbms_output.put_line (sync_string);
procedure synchronize_acdata is

sync_string varchar2(4000);

begin  

    /*Cursor fetches all tables in schema*/

    for tbl_row in c_tables ('SCHEMANAME') loop     begin

        sync_string := 'insert into '||tbl_row.owner||'.'||tbl_row.table_name||'@master_dblink select * from '||tbl_row.owner||'.'||tbl_row.table_name;

        dbms_output.put_line (sync_string);

        execute immediate sync_string;

        commit; 

    exception

    when dup_val_on_index then

        rollback;

        dbms_output.put_line ("rollback ......");

    when others then

        dbms_output.put_line ("other exception ......");

    end;

    end loop;

end;

Open in new window

0
 

Author Comment

by:Mamarazzi
Comment Utility
Now I get it! Thank you all...
Regarding the exception handling, I want to IGNORE the dup_val_on_index, but rollback if there is any OTHER exception
0
 
LVL 27

Expert Comment

by:sujith80
Comment Utility
>> I want to IGNORE the dup_val_on_index
Yes, you can. But unless you change the code as instructed in the above posts, you cant prevent raising the exception.

See the code, how to handle the exception.
.

.

exception

    when dup_val_on_index then

     null;

    when others then

     raise;

end;

.

.

Open in new window

0
 

Author Comment

by:Mamarazzi
Comment Utility
The statement with "minus select *" does not work... I tried this select:
select * from schemaname.tblname MINUS select * from schemaname.tblname@master_dblink
that returns all rows from local db - the MINUS had no effect

But if do:
select id from schemaname.tblname MINUS select id from schemaname.tblname@master_dblink
I get the proper amouint of rows, that is all rows in local that don't exist in master.

My problem is that I dynamically want to loop through all tables in the schema and move all rows that don't exist in master, TO master... without knowing the tablenames or primary keys.

Any ideas?
0
 
LVL 27

Expert Comment

by:sujith80
Comment Utility
What is your definition of c_tables ('SCHEMANAME')?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Mamarazzi
Comment Utility
See cursor code!

Some of the tables don't have a PK, otherwise I could get that from DD...
cursor  c_tables(

        cp_username in varchar2, --Schema name

        cp_excluded_tables in varchar2 default '*') is /*List of excluded tables if any, comma separated */

select  owner, 

        table_name

from    sys.dba_tables

where   upper(owner) = upper(cp_username)

and     table_name not in upper(cp_excluded_tables);

Open in new window

0
 
LVL 1

Expert Comment

by:michaeljoneill
Comment Utility
Have you examined using Advanced Replication?  Your are re-inventing the wheel.  Here's the link for 10gR2 AR ==> http://download.oracle.com/docs/cd/B19306_01/server.102/b14226/repoverview.htm#REPLN001
0
 

Author Comment

by:Mamarazzi
Comment Utility
I know that I'm reinventing the wheel, but this is kind of a special situation that demands manual handling. I just need a quick fix for now!
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
cursor  c_tables(
        cp_username in varchar2, --Schema name
        cp_excluded_tables in varchar2 default '*') is /*List of excluded tables if any, comma separated */
select  owner,
        table_name
from    sys.dba_tables
where   upper(owner) = upper(cp_username)
and     table_name not in upper(cp_excluded_tables);  -- you cannot use a variable here.

if you variable contains 'EMP1,EMP2,EMP3' which you want to exclude then the above will not work because the whole thing will be treated as a single variable and it will try to exclude a table name which is 'EMP1,EMP2,EMP3' and it is obvious that no one will have a table name
like that which is commas inside and so excluding tables will not work as you expect.

create some dummy table and then store all the table names in that which you want to exclude and use something like below :

cursor  c_tables(
        cp_username in varchar2, --Schema name
        cp_excluded_tables in varchar2 default '*') is /*List of excluded tables if any, comma separated */
select  owner,
        table_name
from    sys.dba_tables
where   upper(owner) = upper(cp_username)
and     table_name not in (select upper(table_name) from not_required_tables);  

or you need to have a pl/sql block ( to split that comma delimited string into actual table names like
EMP1
EMP2
EMP3 to store them into pl/sql table and then use this pl/sql table which is having tables which you want to exclude in your loop code where you are inserting ... I mean before running the insert you should check whether that table name is in the array, if so do nothing otherwise execute the insert command.

Thanks
0
 
LVL 27

Expert Comment

by:sujith80
Comment Utility
>> Advanced Replication
Is not the option here. I believe it is a one-time task Mamarazzi trying to do. More over replication techniques requires a lot of configuration setups, which are tedious.
0
 

Author Comment

by:Mamarazzi
Comment Utility
nav_kum_v!
Thanks for the comment about cp_excluded_tables! Of course it doesn't work like I thought. My mistake!!!

I will close this thread now, since my original question about the updating and dup_val_on_index exception is answered by sujith80 and sonicefu. I will have to figure out another way of solving my problem with dynamically updating the tables.
0
 
LVL 1

Expert Comment

by:michaeljoneill
Comment Utility
>> More over replication techniques requires a lot of configuration setups, which are tedious.

Perhaps you should give Advanced Replication a whirl sometime; it has improved a great deal since Oracle 8.0

 Configuration is minimal and trivial.  There are so many things that can go wrong with the OP's posted strategy that it isn't even possible that this is for a production system that his job depends upon (which it may not).
0
 
LVL 27

Expert Comment

by:sujith80
Comment Utility
Mamarazzi,
i'll post a piece of code that can be used to dynamically do this. keep watching this thread. quite busy today. will keep posted.
0
 
LVL 1

Expert Comment

by:michaeljoneill
Comment Utility
The code as listed breaks if there are any objects named case-sensitively (i.e., "MyTableName" and "MYTableName", and "MYTABLENAME").  Also, depending on your versions of Oracle Server, you may need a different approach if the table has LOB columns.  Keep those facts in mind as you spoke up your wheel.
0
 

Author Comment

by:Mamarazzi
Comment Utility
michaeljoneill!
I'm interested to hear about the LOBs! We are running 10.2.0.3 and are moving BLOBS without problems! But maybe we haven't seen all the pitfalls yet!
You are right about case sesitive tablenames... as fas I know these are in upper case in DD, but you never know for sure...
0
 
LVL 1

Expert Comment

by:michaeljoneill
Comment Utility
Triggers and LOBs: Updating a lob through dbms_lob package will not cause the upate trigger to fire.  This may or may not be a problem for you, depending on your applications' uses of the data.

There are limitations on remote LOBs.  You definitely can update and insert locally by selecting remote LOBs, but I'm not sure about inserting and updating remote LOBs by selecting locally as you are doing.  The documentation wasn't clear to me on what you are trying in your code posted here.  You should definitely test to your satisfaction.

0
 
LVL 27

Expert Comment

by:sujith80
Comment Utility
Use a piece of code similar to the one below, here you dont have to hard code the columns on which you have the unique constraints enabled. Change the declaration of the cursor as what you want.
Hope the idea is clear to you. I have tested it and it works.

declare
 cursor c is
 select 'TBL1' table_name from dual
 union
 select 'TBL2' table_name from dual;

 l_stmt varchar2(4000) ;
begin
 for rec in c loop
  l_stmt :=
'declare '||
' l_cur sys_refcursor; '||
' l_rec '|| rec.table_name ||'@test_link%rowtype; '||
'begin '||
' open l_cur for select * from '|| rec.table_name ||'; '||
' loop '||
'  fetch l_cur into l_rec; '||
'  exit when l_cur%notfound; '||
'  begin '||
'   insert into '|| rec.table_name ||'@test_link values l_rec; '||
'  exception '||
'   when dup_val_on_index then null; '||
'   when others then raise; '||
'  end; '||
' end loop; '||
' close l_cur; '||
' commit; '||
'end; ';

 execute immediate l_stmt;
 end loop;
end;
/
0
 

Author Comment

by:Mamarazzi
Comment Utility
Thanks sujith80! I think this will work for me. Only problem is that som tables don't have PK or unique constraints at all... yes I know... I just inherited this database :-)
0
 

Author Comment

by:Mamarazzi
Comment Utility
sujith80!
Things have changed now and I need to do an update if record exists on master db. Any idea on how I can do that the best way?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now