PL/SQL substitution question

Hello...

I have a PL/SQL problem which I was hoping someone could help me with. It's like this...

I have two different application sites running the same application on the same machine. Some tables are shared between the sites, most aren't. The sites are specified by connecting as the user name concatenated with an underscore  and the site number (in this specific case, 'NATO_031' and 'NATO_061').

So, I want to update a non-shared table in each database with a common data. Fer instance, article price. The price of the article is the same at each site. So, if NATO_061 has this article number in its database, update the price. If NATO_031 does, update it. Blah. So, what if 3 DB sites are on the same machine? How about 'n' instances?

So, I have a SITEUSER table with the name of all the (machine) local sites. The data comes out of the SITETEST2 table. SITETEST & SITETEST2 have the same format, val1 number, val2 char(20). On each site, sitetest.val2 is null. On all three tables, val1 is an unique integer. sitetest2.val2 is a string value.

Here is my problem...

What do I need to replace my brackets with to get this test procedure to work?

By the way, access has been granted as needed.

Thanks in advance.  

/********************************************************
SITETEST.SQL       
J. O. PAYNE        
29 APRIL 1992      
********************************************************/

declare

• get all site usernames cursor sites is select user_name from siteusers;
• get all data cursor data is select val1 , val2 from sitetest2;

begin

     for data_row in data loop
          exit when data%notfound;

          for site in sites loop
               exit when sites%notfound;

               -- do update for all sites
               begin                      
                    update [site.user_name].sitetest
                    set    val2 = datarow.val2
                    where  val1 = datarow.val1;
               exception
                    when others then
                         null;
               end;

          end loop;

          delete 
          from   sitetest2
          where  val1 = datarow.val1;
    
          commit;

     end loop;

     commit;

exception
     when others then
          rollback;

end;

/*******************************************************/

| James O. Payne, Jr.                                                     |
| IKEA NA Information Systems                                             |
| Plymouth Meeting, PA 19462 USA                        215/834-0180 x343 |
 ===========================================================================
|          "It's a big country.  Someone's got to furnish it."            |
|     (IKEA is much too busy selling furniture to pay attention to my     |
|             drivel, let alone to be responsible for it.)                |
===========================================================================