CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsqlcursor
Marco Aviles
Marco Aviles

Reputation: 5596

Update local database using remote database data

I got a bit of a problem with a Oracle query

create or replace
PROCEDURE       "LOAD_USERNAME" 
IS
   cursor usu is
      select userid
      from local_user;

BEGIN
   for usu_rec in usu
   loop
      update loc_user set username =(
         select cod_user 
            from (
               select cod_user, row_number() over (order by date_createad desc) r
               from user_table@DBLINK where cod_person=usu_rec.userid
            )
            where r = 1
         ) 
      where externaluserid=usu_rec.userid;

   end loop;
END;

Basically, trying to get code of a user(last one created) from other database and update a local table. This seems to work but I takes too much time. I can only check the remote database through a DBLINK.

Please, I want some help for a better way to do this.

I thank your help in advance.

Upvotes: 1

Views: 395

Answers (3)

APC
APC

Reputation: 146229

Oracle has provided built-in functionality for this sort of thing for several major versions. If you're on an older database you should use replication. In more recent versions this has been deprecated in favour of Streams.

Upvotes: 0

Sodved
Sodved

Reputation: 8588

You want to minimise the number of times you go over the network. So you should join to the remote table in your driving cursor and pull the username back there. This will be better as that query is only executed once (indexing/design will determine how well it goes). But your updates will then only be working with local data.

Edit: Removed my PL/SQL as @Aitor's was better

Upvotes: 4

Aitor
Aitor

Reputation: 3429

As Sodved said, is better to had the join in your cursor. You can try something like this:

create or replace
PROCEDURE       "LOAD_USERNAME" 
IS
   cursor usu is
select distinct local_user.userid,your_dblink_table.cod_user
      from local_user, user_table@bdlink your_dblink_table
where local_user.userid=your_dblink_table.codperson
and local_user.externaluserid=local_user.userid;
BEGIN
   for usu_rec in usu
   loop
      update loc_user set username =usu_rec.cod_user
where externauserid=usu_rec.userid;
 end loop;
commit;
END;

If you have to load massive updates, you can try a bulk collect/for all approach in the cursor.

Upvotes: 1

Related Questions