Schema looping in PL/SQL

I was asked by a co-worker to look at a task that had been given to her to see if I could write some SQL to make it easier. A lot of the one-off tasks around here can be simple but very time consuming. And it just so happens that I like writing SQL so look forward to these little diversions. The task was to add a property key/value pair for each record that matched a certain set of criteria in every database instance that we have for a certain application we support. We have three financial database service names in Oracle at the moment that divide the country up mostly by geographic location. We'll call these East, West and Yeehaw. I needed a block of code that could be executed once on each of these services but update each of the schemas housed on these service names that needed the new property.                    

I had done some recent investigation regarding aliasing a schema name and found that Oracle doesn't directly support this. My bright idea was to use the data dictionary and see if I could work something out. I wrote a quick block to loop through a cursor and print out the schema names that I would be interested in that exist on that service name. That block looks something like this:

Declare

  CURSOR schemacur
  IS
    SELECT owner
    FROM all_tables
    WHERE owner LIKE '%MYAPP%'
    AND table_name = 'PROPERTIES'
    ORDER BY 1;
  schemarec schemacur%rowtype;
  schemaval   VARCHAR2(64);

begin

   open schemacur;
   loop
     fetch schemacur into schemarec;
     exit when schemacur%notfound;
 
     schemaval := schemarec.owner;
     dbms_output.put_line(schemaval);
 
   end loop;
   close schemacur;

end;                                                                                                                                                                                                                                                                                                                              
Refresher:
A cursor is basically an array filled up by the results of a select statement. Generally, you interact with a cursor by opening it, fetching it into a record, and eventually closing it. A PL/SQL loop is similar to any other loop in any other language. The exit statement is how you get out of the loop.

In this case, I have a variable declared as a record that matches the ROWTYPE from the the cursor. In this particular case, I could  have fetched it directly into a varchar2 variable since the select statement only gets one column. However, I wanted to show the more complex structure. First, I open the cursor. This executes the select statement for the cursor and holds the results in memory with the index on the first record. The index is implied here. I go into a loop and fetch the first record into my record variable and specify that when no more records are found that I should exit the loop. I assign the record value to the variable and print it out in the dbms output window. Of course, you need to turn on the dbms output window within sqldeveloper or whatever tool you are using. GUI tools will have a button to click or some such thing, while sql*plus has the SET SERVEROUTPUT ON; command.

So, the output in the window of this is an alphabetically sorted list of schemas that exist on the current service name that are like '%MYAPP%' and own a table called 'PROPERTIES';

Now, I can tweak this block a little by adding another cursor and loop to check records within each of those schemas. What we have now is:

Declare

  CURSOR schemacur
  IS
    SELECT owner
    FROM all_tables
    WHERE owner LIKE '%MYAPP%'
    AND table_name = 'PROPERTIES'
    ORDER BY 1;

  schemarec schemacur%rowtype;
  schemaval   VARCHAR2(64);
  schemacount INTEGER;
  i           INTEGER;

  CURSOR proccur
  IS
    SELECT processor_id
    FROM processor
    WHERE description LIKE '%STYLE1%'
    AND description LIKE '%STYLE2%';

  procrec proccur%rowtype;
  procid    NUMBER;
  proccount INTEGER;
  j         INTEGER;
  prockey   CONSTANT VARCHAR2(16) := 'KEY';
  procval   CONSTANT VARCHAR2(64) := 'VALUE';

  sqlstmt   VARCHAR2(4000);

BEGIN

 i := 0;
 select count(*) into schemacount from all_tables where owner LIKE '%MYAPP%'
    AND table_name = 'PROPERTIES';
   dbms_output.put_line(schemacount || ' schemas to loop through');
 
   open schemacur;
   loop
     fetch schemacur into schemarec;
     exit when schemacur%notfound;
       i := i + 1;
    schemaval := schemarec.owner;
    --dbms_output.put_line(schemaval);
    sqlstmt := 'alter session set current_schema=' || schemaval;
    EXECUTE immediate sqlstmt;
 
    select count(*) into proccount from processor
    WHERE description LIKE '%STYLE1%'
    AND description LIKE '%STYLE2%';
    dbms_output.put_line(proccount || ' processors to loop through');
    j := 0;
 
    OPEN proccur;
    LOOP
      FETCH proccur INTO procrec;
      EXIT
    WHEN proccur%notfound;
      j := j + 1;
      procid := procrec.processor_id;
      dbms_output.put_line(procid);
   
    END LOOP; -- proccur
    dbms_output.put_line('looped through ' || j || ' processors');
 
    CLOSE proccur;
  END LOOP; -- schemacur
  dbms_output.put_line('looped through ' || i || ' schemas');

  CLOSE schemacur;
  sqlstmt := 'alter session set current_schema=myschema';
  EXECUTE immediate sqlstmt;

EXCEPTION
WHEN OTHERS THEN

  raise;
END;

Now, this one adds a lot more meat but in essence just adds another loop and a few more display items. I wanted to confirm that I looped through the expected number of records for both cursors so I get the count of the query and count each iteration and visually compare results. This also serves to show me how the schema redirect works. In the first block, I just output the schema names. In this block, I alter my session and change the current schema to each of these targets in turn and subsequently execute the proccur loop as if I were connected as those schemas. Well, not exactly - Oracle interprets the lack of a schema alias as "Oh, they must mean this object is owned by the current schema, let me check what that is" and I repointed the current schema to a new one over and over and at the very end I set it back.

Now, all I need to do is to insert my new property instead of print each id in the inner cursor loop. This worked like a charm. I executed the block 3 times on each of the aforementioned service names and every affected schema received the new property and I saved my coworker a lot of time and work. It felt really nice.

Well, that does it for this installment. See you all next time around.