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.

Oracle - you so crazy!

One of the tasks I was given recently was to check the credit card retention data and see how the data looks and I discovered a very interesting thing with the Case statement and NULL in Oracle. My initial query seemed to indicate that the retention job was not running. Here's the statement I ran:

select case card_number when null then 'NULL' else 'NOT NULL' end as card, count(*)
from credit_card
group by case card_number when null then 'NULL' else 'NOT NULL' end
order by 1;

This looks pretty straightforward. The case is on the card_number when the value is null change the output to the string 'NULL' else change the output to 'NOT NULL' and count the occurrences of each.
Every time I ran this in every database, it showed all records as 'NOT NULL' which shouldn't be correct. I circled back and changed the case to a decode to see what it would look like. That statement was:

select decode(card_number,null,'NULL','NOT NULL') as card, count(*)
from credit_card
group by decode(card_number,null,'NULL','NOT NULL')
order by 1;

The logic is the same but we use an oracle standard decode function instead of the CASE keyword. And our results look more like we'd expect. Some are null and some are not null. So, what went wrong with the CASE statement? Some of you might have already picked up on what I did wrong. The null in the ...Case card_number when null... part was not checking if card_number was null, it was saying when there is nothing to do change the variable to 'NULL'. If we look to the NULL statement in PL/SQL that might help us understand.

create or replace procedure validnullproc as
Begin
Null;
End;

This is a valid procedure that does fuck all. So basically I was inserting a null chunk of code into my Case statement. Here is a statement that shows a couple of other options:

SELECT
  CASE card_number
    WHEN NULL
    THEN 'NULL'
    ELSE 'NOT NULL'
  END AS card,
  DECODE(card_number,NULL,'NULL','NOT NULL') as decodecard,
  nvl2(card_number, 'NOT NULL', 'NULL') as nvl2card
FROM credit_card
WHERE card_number IS NULL
AND rownum         < 11
UNION
SELECT
  CASE card_number
    WHEN NULL
    THEN 'NULL'
    ELSE 'NOT NULL'
  END AS card,
  DECODE(card_number,NULL,'NULL','NOT NULL'),
  nvl2(card_number, 'NOT NULL', 'NULL')
FROM credit_card
WHERE card_number IS NOT NULL
AND rownum         < 11;

Results:
"CARD"                        "DECODECARD""NVL2CARD"
"NOT NULL"                    "NOT NULL"                    "NOT NULL"                  
"NOT NULL"                    "NULL"                        "NULL"                      

So, my fancy Case statement to pull the CARD column above is basically just saying always Print 'NOT NULL'. However, both the DECODE and the NVL2 functions resolve to the correct values and I think NVL2 is the better option in this case. And I must admit that NVL2 is new to me, personally.

 In my investigations into the odd behavior of the CASE and NULL statement, I did uncover this lovely new function called NVL2. It is basically DECODE for NULLs. Most of you are probably familiar with the standard NVL function. You might have seen something like NVL(status_code, 'X') which will resolve to the status_code value unless it is NULL in which case it replaces the NULL with 'X'. You can do it with DECODE but it is more typing: DECODE(status_code, null, 'X', status_code). DECODE can come in real handy when you are displaying coded data, especially in older somewhat denormalized or poorly designed enterprise applications. Say you have a status column on some table that stores single letter status codes but you did not store the corresponding word values in a reference table. You can use DECODE in your report SQL to do this for you: DECODE(status, 'A', 'Active', 'I', 'Inactive', 'C', 'Closed', 'W', 'Whiskey', T', 'Tarngo', 'F', 'Foxtrot'). As you can see from the above example, you can handle NULL/NOT NULL with decode as well but NVL2 is designed specifically to allow you to change the value of not null data. As you can probably tell from the above example, the syntax is the function name NVL2 and the first parameter is the field or value you are changing, the second parameter is the value you want to output if the first parameter is not null, and the final parameter is the value you want to output if the first parameter is null.

Well, thanks for tuning into my adventure's with Oracle. Now, go forth and use Oracle wisely.