Creative Date Filters to Meet Business Needs

Recently, I was tasked with providing an ad hoc query to a group of users. These users wanted this set of data to be delivered to them every year on July 1st. I co-opted our Jenkins box to meet this demand. If you unaware, Jenkins is a tool that assists you in deployment automation and monitoring. Learn more about Jenkins, I'll wait.

Okay, so now that you are familiar with this tool, you might realize that I'm using it to fill a need that it wasn't particularly designed to satisfy, but it is surprisingly useful at filling this need (and the precedent was set by others so if they didn't want me to use it for this, they shouldn't have opened that can of worms in the first place). But, I didn't come here to write about Jenkins, at least, not primarily. I wanted to tell you how to use SQL to manage date parameters in a oddly repetitive task.

So, initially the users wanted this data once a year so it was a simple thing to calculate the date range. Since we would be running this job on the first of July and the cutoff date would be midnight, we easily get that value and since the start date needs to be July 1st of last year, the date parameters are easy to achieve. Let's figure out the end date:

select trunc(sysdate) as end_date from dual;

Pretty damn simple right. You may remember that when the Trunc function is called on a date without any other parameters, it simply drops the time component and sets that to 00:00:00. Now, let's figure out the start date:

select add_months(trunc(sysdate),-12) as start_date from dual;

This is still not very complicated, really. You might remember us talking about the add_months function in the past. To recap, add_months does exactly what it sounds like, it adds months to the date that is passed and returns a date value. So, lets says sysdate is July 1st, 2014 10:07:35 AM when we run these two statements we get July 1st, 2013 00:00:00 and July 1st 2014 00:00:00. Problem solved, right?

Wrong. As users so often do, they changed the scope on this. In total, they want this report delivered 4 times per year. Here's the breakdown:

Fiscal Year Preview Report: Jul 1, LY (Last Year) - Jun 1, CY (Current Year) delivered on Jun 1, CY
Fiscal Year Full Report: Jul 1, Last Year - Jul 1, CY delivered on Jul 1, CY
Service Year Preview Report: Oct 1, LY - Sep 1, CY delivered on Sep 1, CY
Service Year Full Report: Oct 1, LY - Oct 1, CY delivered on Oct 1, CY

It looks a little sketchy. The add months won't work all of the time now because two of the reports are for 11 month periods but two are for 12 month periods. I should probably mention that the end dates are exclusive so the query will be greater than or equal to start date and less than end date. Well, I could probably change the number I pass to add months but there's no guarantee that they won't push back and change the schedule even further.

As a measure to allow for future expansion, I decided to use a series of sub-selects to get to what I wanted. Let's continue with the previous examples but try using sub-selects to get both of the values:

select add_months(end_date, -12) as start_date from (
    select trunc(sysdate) as end_date from dual);

This highlights that we are using the current day as the method to get the date to start last year. But, what happens if there are issues on the first and the report doesn't get executed? If the email server tips over and the report doesn't get delivered, the Jenkins work space will still have the file so we can export it and manually send. But if the Jenkins job doesn't run to create the file on the 1st and we run it on the second, what happens? Yep, that's right! Give yourself a cookie. The parameters will skew one day into the future and our report loses its integrity. The users will not be happy that the report generated on the 2nd grabbed the records from the 1st in the wrong year. Bogus! What do? Never fear. I got you.
(BTW, I wish I could say that the above concerns are fatalistic in nature, but every place I've ever worked has had brittle systems and shit happened - servers have tipped over, email has gone down, all hell has broken loose - we need to be aware of that and give our code as much bulletproofing as we can.)

I decided to write the queries such that they could be ran on any day of the month and they would calculate the appropriate days. If so much shit happens that we can't get this report ran and delivered within a month, we've got way bigger fish that we need to be frying. And resumes to be updating. So, here goes, let's get creative!

We need to get the current month, the current year, determine which month we are in, and calculate the appropriate start and end dates.

First, let's get the numeric value for current month and year.

select
    to_number(to_char(sysdate, 'mm')) as currmo,
    to_number(to_char(sysdate, 'yyyy')) as curryear
  from dual;

Muy bueno! Second, let's determine what the start month should be based on current day, get the start day (in this case this is the first but if they suddenly decide that this should run from the 15th to the 15th we just change one spot in the query), and select the current month and year variables like above:

select
    case
        when currmo in (6,7) then 'J'
        when currmo in (9,10) then 'O'
        else 'N'
    end startflag, -- 'J' means start july 1, 'O' means start oct 1, 'N' means use sysdate,
    currmo,
    curryear,
    to_char(curryear - 1) lastyear,
    '01' startday
  from (
    select
        to_number(to_char(sysdate, 'mm')) as currmo,
        to_number(to_char(sysdate, 'yyyy')) as curryear
      from dual);

Alright, that looks good. We should have enough here to calculate the start date and end date appropriately for our given criteria and if they schedule changes a bit, we should have done this in such a way that we'll be able to quickly update it to what we need. Let's put this altogether now.

Let's calculate the appropriate start date and end date.

select
    case
        when startflag = 'J' then to_date(startday || '-JUL-' || lastyear, 'dd-mon-yyyy')
        when startflag = 'O' then to_date(startday || '-OCT-' || lastyear, 'dd-mon-yyyy')
        when startflag = 'N' then trunc(sysdate)
    end startdate,
    to_date(startday||lpad(currmo, 2, '0')||curryear, 'ddmmyyyy') enddate
  from (
    select
        case
            when currmo in (6,7) then 'J'
            when currmo in (9,10) then 'O'
            else 'N'
        end startflag, -- 'J' means start july 1, 'O' means start oct 1, 'N' means use sysdate,
        currmo,
        curryear,
        to_char(curryear - 1) lastyear,
        '01' startday
      from (
        select
            to_number(to_char(sysdate, 'mm')) as currmo,
            to_number(to_char(sysdate, 'yyyy')) as curryear
          from dual));

This works beautifully! If we run this on any day in September, if figures out that the start date should be October 1st, LY, etc. To test this out, you can set aside four months and run this every day for those four months, or you can change your system date and rerun, but what I did was change which numbers were in the IN clauses in the start flag CASE statement. This will nicely simulate the different dates and you don't have to waste four months or change your system date.

Now you might be thinking, this sounds good and all but how do we utilize these dates in our report query. Good point. Let's consider an example where we have a table of transactions and they have a column called TRANS_DATE and that is going to be what we compare against our values above. Let's image that the users want a report of everything in the TRANS table within those date parameters (the actual report is a bit more complicated than this and I don't want to muddy the waters any further than they already are). Our report query will look like:

select
    *
  from trans t
 inner join (
  select
    case
        when startflag = 'J' then to_date(startday || '-JUL-' || lastyear, 'dd-mon-yyyy')
        when startflag = 'O' then to_date(startday || '-OCT-' || lastyear, 'dd-mon-yyyy')
        when startflag = 'N' then trunc(sysdate)
    end startdate,
    to_date(startday||lpad(currmo, 2, '0')||curryear, 'ddmmyyyy') enddate
  from (
    select
        case
            when currmo in (6,7) then 'J'
            when currmo in (9,10) then 'O'
            else 'N'
        end startflag, -- 'J' means start july 1, 'O' means start oct 1, 'N' means use sysdate,
        currmo,
        curryear,
        to_char(curryear - 1) lastyear,
        '01' startday
      from (
        select
            to_number(to_char(sysdate, 'mm')) as currmo,
            to_number(to_char(sysdate, 'yyyy')) as curryear
          from dual))
  ) datefilter
on 1=1
where t.trans_date >= (datefilter.start_date)
    and t.trans_date <   (datefilter.end_date)
order by 1;

Alright, there you have it. There are other ways we could have handled this, for instance, we could have change the add months, used trunc (sysdate, 'MM') to get to the first day of the month but I like what I have here. It seems to be easily expandable. If they decide that they want to do this quarterly, all we have to do is add those quarters to the start flag case statement and add the cases to handle those new start flag values. It shouldn't be heavy lifting to make those changes.

So endeth the lesson. Give this a shot of you feel like it or the need arises. Extra credit if you take the time to figure out another way to do this.

Until next time, Happy Querying.

A problem with Decode and a problem with people

A problem with Decode

I ran into an interesting behavior of decode today. The security team wanted a report to verify that data retention jobs were working properly. I wrote a query to find the oldest overall record, the oldest record still retaining the data the retention jobs would erase, and the counts of each. Well, the data looked pretty good at first glance and it seemed accurate when it highlighted a few instances where the retention jobs weren't scheduled correctly. However, one date kept surfacing over and over. I didn't think much of it, but the security team wanted an explanation. So, I dug in and they were right. The resurfacing date didn't match the data. My query wasn't correct. But why?

Well, first off, I'm a bit of a an old dog. I've been at this a while and I might be a tad stuck in my ways. Because of that, I have habits and one of those habits is relying on the decode function and eschewing the case function for most uses. This time it bit me. My decode looked something like this:

min(decode(retention_column, null, add_months(sysdate, 1), transaction_date))

So, if the retention column is null, use a dummy date, else consider the date of the transaction and once all that is done, get the earliest one. Seems relatively straightforward, right? Yet, the results didn't match the data. I just wasn't finding the issue so I called in a new set of eyes. He confirmed that I wasn't crazy and that it looked like it should work. He dismantled the statement a little more and tried some changes to find out what was going on. So, it turns out that the Decode statement automatically converts parameters to string values. Then the min was happening on the string, not the date. That is why dates of the first were coming before the real minimum date. Problem solved. So, what I had to do is convert the results of the decode function to date and then find the minimum. It looks like this:

min(to_date(decode(retention_column, null, add_months(sysdate, 1), transaction_date), 'dd-mon-yyyy')


A problem with people

So, I really just need to vent about something. There was a bit of a fiasco at work a couple weeks ago. Someone was tasked with clearing a test database and ended up being in the wrong one when they ran their scripts. I found out about it a few days after it happened and I was tasked with adding a few users in so that they could get in the system and do what they needed to do. Someone also mentioned that the latest backup was 6 months old. It only took me a couple of minutes to add in the users.

I took some time and figured out a way to pick a random number of customers, a random number of days and a random number of transactions for those days. I was able to generate 6 months worth of test transactions and effectively replace the data that had been lost. I've been super excited about this and telling everyone that I run into. However, I haven't heard jack shit from the people directly involved with this system. They couldn't give a shit less about my breakthrough.

Now, there is a general malaise of apathy that has infected probably 90% of the people at my work. Maybe that is a little harsh. It is probably more like 60% apathy and 50% incompetence. A few of you math wizards out there might have figured out that that covers more than the sum total of people. And I say nay. Those aren't mutually exclusive. There's 15 to 20% of people that both don't know shit and don't give a fuck about it.

It is difficult to keep a head of steam up when beset with the lazy and the stupid on almost all sides. Probably more than half of the few competent people that are left have one foot out the door. The others are bravely trying to stem the tide of meh, with varying success. I just don't get how people do not see the potential in the randomized data generator I created. In my mind's eye, I picture a system that can user docker and puppet to spin up a new test instance, kick off my script and simulate half a year's worth of activity all in a matter of moments. So, you are showing the application off to a new set of customers? How about a personalized demo in a personalized test environment spun up at a moment's notice? Maybe I should lay off the paint thinner. Or maybe I'm onto something here. Hell, I've had several of the brighter stars at work singing the praises of docker and puppet for months now. One of my illustrious colleagues just accepted an offer to go work for PuppetLabs in Portland. In a world where a foul-mouthed, halfwit database developer can see massive potential in this stuff, why can't everyone?

Alright, I get it, my feelings got hurt. For a guy who looks like I look: Sons of Anarchy meets Trailer Park Boys meets Cops meets Lizard Lick Towing, I can be rather sensitive. But for realz, maybe try giving a shit. Let's just start with one shit given per week. Baby steps, alright? Rome wasn't built in a day. Or perhaps I'm just pissy because my work wasn't appreciated enough. Perhaps I'm being too hard on everyone and I'm lashing out in anger and painting with too wide and negative of a brush. Perhaps everyone is so in awe of my randomizer that they've been struck dumb and are screaming praise silently in their own heads. Or perhaps not. Perhaps, the "meh, good enough" crowd really are fucking it up for the people that are on the streets.

Either way, I feel properly vented. I'm going to bed. See you all next time. In the meantime, be careful when using the Decode function and be extra careful when pissing excellence. The mediocre and unenlightened might just treat it as regular piss. Toodles.

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.

April foolishness

So, I've been working on Unit Tests in my schema. I have copied over some test data and built out the structure and written some code to setup and tear down as I have discussed previously. Well, as often happens at work, I had some shifting priorities and the Unit Tests fell to the back burner for a bit. Well, I was able to get back to them in full force today. The problem I ran into after several hours of writing code, running tests, etc. is that I had totally jacked up a couple of sequences. Sequences are number generators used for primary keys in tables. If your table is TAB and your primary key is TAB_ID then usually you will have a sequence called either SEQ_TAB_ID or TAB_ID_SEQ depending on your naming convention. My Unit Tests use the same method that the App does to enter records. In this case it is a shit ton of stored procedures. Almost every table, but not every table because that would be fucking stupid, has a procedure to insert a row and another procedure to delete a row. I imagine that the App does not use the delete procedures much or at all. But, I sure can use them to clean up after myself. Curtis knows what I'm talking about. Lulz.

Anyway, since this is all in my schema and I pulled some real test data, but not all and recreated the objects and what not, I did a silly thing. Now a normal well adjusted developer might have come up with a cohesive plan to do this, but I was feeling frisky and decided to hit the ground running so to speak. I jumped right in there and pulled over the data, then I manually created a couple of the sequences that I need for the testing. Here is the appropriate way to create the above mentioned sequence in a pristine environment:
CREATE SEQUENCE SEQ_TAB_ID START WITH 1 INCREMENT BY 1;
There are some other options but nobody gives a fuck about them so I'm moving on. These two are a must. You tell the sequence its first value and you tell it how many to add to the first value to get the the next value. This was a crucial mistake. What I should have done was find out the max id in the data that I had pulled over and given the sequences an incremented value of that to start with. So, let's say that the maximum value of my TAB_ID  is 698226. I may have pulled over the last month's worth of test transactions to play with or something like that. But, my sequence started at 1. So, the first few tests worked because those values were available in the table. (A big aside here: we have talked about this in the past, I believe and you may already know this but, a primary key in a table if it has a primary key constraint must be unique. So, what happened was I pulled over records starting at 1000 to 600,000 say and I set my sequence to start at 1. As soon as the sequence has been run 999 times, I'm going to have a bad time. That is what happened. I began to get unique constraint errors. Well, I figured out my mistake but I again failed to act appropriately. I'm not sure if I'm just a big giant fucktard or if I've been around them too much but either way, I need to step my fucking game up. If sequences were blocking a shot, I'd be that Ware kid.

An appropriate action at this point would have been to drop the existing sequence and recreate it as I should have done in the beginning. Ah, fuck that. I'm a bro-grammer, right? I do this shit my way. I'm from the streets, bitch. Fuck you and your 'industry standards'. I'll have none of it. I'm keeping it real. My solution might be a legitimate move if you are working in a database that might be getting live action. You know, testing in MOTHERFUCKING PROD as we so often do. Well, I bumped up the increment by to 13000 and I selected some motherfucking nextvals. Soon, enough I got to where I needed to be. And all is right with the world. Got my mind on my DB and my DB on my mind, yo. Not really, I had getting the fuck up out of here on my mind. I had shit to do. I bolted and left it the way it was. That's right...Left. It. The. Way. It. Was. So, the next day, I didn't even get logged in before my priorities changed yet again. Shit breaks. Other shit comes up from time to time and the shit just gets stirred around. It's like a big shitty pot of shitty spaghetti sauce that you have stir in a shitty manner with your shitty little wooden spoon every few shitty minutes. It's - well, it's shitty, I guess, is what I'm trying to say. I worked unhappily on my new priority for a week or more. Shit didn't go well. (Is anyone else sensing a theme here?) I eventually got that priority as far along as I could get it and got back to this one again. So, today, I'm testing merrily away. I'm like the little Unit Tester that could. I got this. Sadly, no. I got shit. (There it is again - it's like Bad Wolf but shittier.) I ran several tests and  wrote some code and ran some tests and wrote some code. Well, I eventually added a new insert and when I tested that one, it shit all over the place. TOAD was like the bathroom in Trainspotting. Ick. After a bit more than a bit of investigation, I'd say it was a squared bit, I discovered that my recently added table had the TAB_ID declared as a NUMBER(9,0); which means the largest available TAB_ID could be 999999999 whereas my main TAB table had the TAB_ID declared as NUMBER; which means there is no upper bound (technically there is but let's not get all tangential) so I had apparently been testing this thing like a MOFO because my MAX(TAB_ID) was 389548277499. My sequence had been adding 13000 to the id every time a test was ran. Did I mention that I test several records during each test. Why, that is a test within a test. Yo Dawg. I heard you like tests...It is TESTCEPTION. Every test I ran ended up inflating the TAB_ID by 65000.

MFW I realized what I had done. I chuckled a little. If I didn't laugh at myself I'd be all emo and that wouldn't work out well at all. I have no hair to die jet black, can't afford a complete wardrobe overhaul, and if I'm going to be cutting anybody it is going to be other people because fuck them, amiright? I have found that other people are three of the top three things wrong with the world today. Snooki, Mindy Kaling, and Dianne Feinstein are all other people. Thanks, Obama.

Well, I hope my pain may have served to amuse and perhaps enlighten you all a little bit. And to be honest, sometimes off the beaten path is where you want to go. You might find a change of perspective beneficial. Of course, you might find a ton of shit. You know what, I just realized why the nuns at my grade school were such ball-busting bitches. Because the painful lessons are the ones that teach you the most. Of course, those dumb cunts can't make every lesson the most painful of your life and reasonably expect it to work. If all you know is pain, it loses its meaning, its poignancy and its teaching ability.There has to be a little downtime from the punishment. You can only taint-punch a hooker so much before she gets all mouthy...with the 'when you untie me, I'm going to fuck you up', or 'please don't kill me', or my favorite 'you've just lost your family discount cuz'. But, I digress. Again. I digress all over the place. I even got some digress on the curtains once.  That was a trip. Literally. I actually tripped over the roll of duct tape, or maybe it was the shovel handle. Either way, I fell into the motel curtains mid digression and ended up with a mild abrasion on the People's Digressor.

Next time or at least coming soon, I'll cover some basic Oracle functions to make life easier. Apparently, I'm a lousy friend and kind of a dick for not doing that sooner. The REPLACE function would have been useful knowledge for someone.

Yo Dawg, I heard you like writing SQL & Goddamnit, that didn't go as planned.

Today was a roller coaster of a day. I had an interesting success and a stupid yet interesting rookie mistake. It started off my needing to make a change to someone else's script that I have to run during the next deploy.

Interesting Success

As a little background here, we are deploying changes to one of our multitenant applications that relies on one of our old silo'd applications. So we would be generating a few hundred update statements from one database and then applying them to several different databases. So, I would have to either gain access to each of these individual databases and parse out the output of the script and run only the applicable statements OR manually edit the results to add the appropriate schema designation to the resulting scripts, which is tedious and prone to error. Or, I could figure out a way to fix the statement at the time of generation.

 Most of my SQL Writing SQL efforts rely upon the data dictionary and its massive library of tables and views to make magic happen. The script that I was given needed to be run in a somewhat alien application but have the resulting SQL ran in several instances of one of the applications I am intimately familiar with (assuming you consider dry anal rape being intimately familiar).

The problem was that I was expecting to be able to pull the OWNER from the data dictionary ALL_TABLES view or some such thing but the script that I was given was just using Application data tables and was not relying on the data dictionary. This threw a wrench into my plans and the heat was getting turned up also because the deploy is this Sunday at the un-FSM-ly hour of 5 AM - did I mention the dry anal rape, yeah. So, in a panic I started investigating and gaining familiarity with the quasi-alien database. Running some select statements and looking very closely at the ones that were being used in the script I was given. The gentleman that provided the script was nice enough to add in a comment to break up the statements into groups based on their location such as --Portal1. However, I need the schema designation for Portal1 not the words Portal1. So, during my investigation I found that the table that holds the Portal Name Portal1, etc. also holds a URL for the API. Sure enough, the schema designation was mostly included in the URL.

For example: Portal1's URL would be http://foo-p1.xyz.com/blah/blah/woof/woof... and the schema designation would be foop1. So, I needed to parse the URL and pull out foo-p1...foo-pn, n times. I combined the REPLACE, SUBSTR and INSTR functions to do my work. The resulting column in the select statement turned out to be something like this:

replace( substr(portal_table.portal_url, 9, instr(portal_table.portal_url, '.') -9), '-') AS schema_designation

I was then able to modify the output to include || schema_designation || '.' || right before each table name is specified in the output. For example:

dbms_output('update ' || schema_designation ||'.foo set bar = 1;');

so the resulting statement becomes

update foop1.foo set bar = 1;

which can be executed from any schema on the same SID or Service Name as foop1 as long as the appropriate permissions are in place. This worked out really well and saved me a bunch of terrible data entry or logging into too many schemas to run the scripts.

Stupid but Interesting Rookie Mistake

So, did you know that you can join to the same table twice in a single statement? So, let's say we have a simple employee table called emp that has 3 columns: id, name, manager_id. Manager_id refers to the ID column of the emp record for that employee's manager. Now, lets say we want to run a report that lists each employee and their manager. We would have to join to emp twice, right? It would look something like this:

select employee.name as employee, manager.name as manager
from emp employee
join emp manager on employee.manager_id = manager.id;

If our table has data such as:

ID   NAME   MANAGER_ID
1     Tom       NULL
2     Dick       1
3     Harry      2
4     Peter       2
5     Paul        3
6     Mary      4

The results would look something like:

EMPLOYEE    MANAGER
Dick                  Tom
Harry                 Dick
Peter                  Dick
Paul                    Harry
Mary                  Peter

So, that is a good simple example of how to join to the same table twice. Interesting huh?

Did you know that you can join to the same table twice in different ways while aliasing the table the same way? Neither did I. Why would you ever want to do that? It just sounds bad.

So, anyway, I was recently tasked with fixing a report. It was joining to the table foo on Bar_id and it should have joined on Blah_id. Also, blah should have joined to bar and not woof. So, the statement looked like

select * from woof
join bar on woof.woof_id = bar.woof_id
join foo on foo.bar_id = bar.bar_id
join blah on woof.woof_id = blah.woof_id;

What it needed to look like was:
select * from woof
join bar on woof.woof_id = bar.woof_id
join blah on woof.bar_id = blah.bar_id;
join foo on foo.blah_id = blah.blah_id;

What I accidentally wrote was:

select * from woof
join bar on woof.woof_id = bar.woof_id
join foo foo on foo.bar_id = bar.bar_id
join blah on woof.bar_id = blah.bar_id;
join foo foo on foo.blah_id = blah.blah_id;

So, notice what I did there? It is pretty easy to spot in this simple example but in the convoluted query I was writing, it was a little harder to spot. So, I kept the bad join and added the good join. The results were subtly wrong. It just started showing twice the records that it should have. When you run for a large time frame, you get a healthy set of data back. It is easy to not notice that there are dupes in there. I mean, the bad join flew in production since the dawn of time, or at least since the report was initially built.

I screwed the pooch on this one. The deploy is happening Sunday so either broken stuff is getting pulled out of the deploy or broken stuff is getting deployed and we have to fix it later. Either way, feelsbadman.jpg.

I now, have to come up with a method to trap for this insanity in a Unit Test. The weird thing is that if a record is doubled, its values would all be legit values so, it passed my current test. The QA people assigned to test this were a bit inexperienced so the bug almost made all the way in unnoticed. The real kicker is that the only reason I found it was because some user was supposed to be testing this in UAT but was testing in production so said it wasn't fixed and pushed back. I was so happy when I found out that she was testing in prod that I started to gloat for a second, but then I noticed the output of the report I ran trying to recreate her results. Oh noes!

The good news is that the fix is super quick. The testing will be a little more involved from both the Unit Test perspective and from the QA perspective.

So, I guess it was a push today? My awesomeness is perfectly equaled by my derpity derp. And that concludes this installment. I just realized I didn't say fuck or cunt very much. I wonder if I'm coming down with something? Laters.


  

Do your Due Diligence

Warning: I was feeling super stabby when I was writing this. If you have delicate sensibilities enter at your own risk. As Corey Taylor says: if I offended you, you needed it.

     I might have posted on this topic once before or perhaps touched on it while covering another topic. I normally stick to SQL and PL/SQL goodness but I'm fucking pissed and I need to vent. I usually only post when I do something wicked awesome or some asshat pisses me off. What is the term for a collection of asshats? A Bing? A Sharepoint of Asshats, perhaps? Hmm. A douche maybe? Asshats are like Frat boys in that they run together in packs and they delight in ruining other people's days. Sometimes developers, analysts, project managers or other work teams do the same thing. Perhaps they don't realize they are being asshats and perhaps they even think they are being helpful or working hard. That is usually not the case.

     Virtually no one has 100% code coverage in their due diligence efforts. It if fucking difficult to stay vigilant in this. You see the signs for a certain condition that you've seen a dozen times before so you jump to the conclusion and don't fully investigate the issue. Snap judgments are fucking awful. They are almost never correct. They are often wildly off the mark. Look, I get it. You've answered tickets or investigate issue for 10 weeks or 10 years and you know what you are doing. Sure. We all feel that way some of the time. But calm the fuck down. If everyone you rush to talk to looks like they just stepped in a big pile of dog shit, slow your motherfucking roll. You are a problem. You are not adding value. You are derailing someones efforts on something. Every little interruption is a loss of several minutes trying to get back into the zone or trying to remember exactly where you were before someone tapped on your wall or your shoulder.

     Interruptions are a necessary evil and they are bound to happen to a certain degree. But make EVERY EFFORT to limit the times that you bring bullshit to someone. If you know how to fucking do it and you have the ability to fucking do it and you have the access to fucking do it, FUCKING DO IT YOURSELF! WTF? If you think something is wrong, how many times did you check it? Are you fucking sure? Are you confident enough to go to the CEO or the VP and tell them your thoughts? If not, then double or triple check it yourself. And then, send someone else an email or an IM that they can get to in their own time. Not every goddamn thing is an urgent thing. Not every goddamn thing has to be done right this motherfucking second.

     And this is one of my worst pet peeves. If everything you are working on is a fucking stop the presses all hands on deck kind of thing in your mind, yet every single time someone else needs to interrupt you, you are too busy and it'll be a minute or two, you've got delusions of grandeur or some shit like that. Not everything you fucking do is important. Not every time you feel the need to interrupt someone about something do you really need to do that. But for damn sure, if you do that, you best answer my fucking questions in a timely fucking manner when I come to you with something urgent. Goddammit!

     We are (or should be) all trying to add value to the organization, find and resolve issues quickly, and prevent bugs from going into production. Every time you interrupt someone, you are increasing the probability that some setting won't get set right or some task won't get 100% completion or the turnaround on that particular task is going to run long. Be very careful when doing this. And reciprocate. If you tell someone this has to been done right now, tell them fucking why. Not just because you are working on it right now. That's not a good enough reason to derail someone else's productivity. Manage your interactions with people and err on the side of caution.

     I've heard managers and people say they have an open door policy. I don't have a door but if I did it would be shut and have a do not disturb sign on it. I like to be left alone so that I can focus and dive  deep on things. Some of this shit is really complicated stuff. If I am just about to get to the bottom of some issue and someone taps me on the shoulder or on my wall, it better be fucking important. I like to think that I'm a friendly person and I am almost always willing to help someone but your lack of proper planning doesn't mean your problem is urgent for me. If you have triple checked your issue and have done all you can do, send me an IM or an email and let me know. I take periodic controlled breaks from my work to read email and I usually will read IM when I notice them and calculate the need for immediate response against how close I am to finishing something or getting to a decent break point. Allowing me to manage my interactions and interruptions is crucial to stabilizing my mood and minimizing the errors in my work.

     Some of you may be reading this and thinking, "This motherfucker is throwing stones in his glass house!" Well, there may be some truth to that. I don't always succeed in sussing out the next thing to to and I can rely on others a little too heavily sometimes. But most of you who know me personally have seen me stand around quietly until a conversation ends before asking a question and most of you have seen me apologize profusely for the need for the interruption. We all do it sometimes. Most of us endeavor to minimize the need for it and most of us struggle with finding a balance.

    You may be wondering why I am so stabby about this right now. Well, I was interrupted a few times the last two weeks because people forgot how the calendar works or some such bullshit. Maybe you need a sheet of paper and maybe you need to jot down notes and think about things a little bit. If you are asking someone how the calendar works, don't try to save face. Own up to the fact that you were dumb or hurried or just didn't think. Honesty is a good thing. I have used that. I told someone this week that if I had more time I could probably have found this thing I was looking up, but I was on the phone with a customer and knew that the people I was bothering could find it quicker and we wouldn't make the customer wait long for me to find it. Maybe that's not cool, but it makes sense to me. There's a reason for my urgency that is something more than that I just feel like I want to get this done and that's enough to make me interrupt someone else's productivity.

     Some of you may be thinking, well we are a team and that means I get to interrupt you whenever I want. I say that working in a team means that you need to respect your teammates feelings. Maybe not always putting them above your own, but giving them equal footing. Your teammates will notice your balanced approach and maybe not look like they stepped in dog shit every time you talk to them. If the dog shit face continues, check your shoes, perhaps shower more often or considering changing your soap.

    For those of you that need step by step instructions:

1. Check if everything you see is dark and smelly.
2. Remove your head from your ass.
3. Really think about what you are going to ask someone to do for you or to help you with.
4. Do as much of the task yourself that you are able to do.
5. How urgent is it really?
6. If it can wait, approach the teammate via electronic means. Approach directly only if really super urgent.
7. State your perceived urgency and when the deadline is.
8. Don't say ASAP unless it is a prod down situation or a customer waiting on the phone.
9. Remember all of the times you've asked for someone's urgent help.
10. Respond quickly when someone else approaches you with an urgent request.
11. Say thank you!

    If you can't figure out which person you work with is the worst offender here, either you work in a utopian environment or you are the biggest asshat at your office. Take immediate steps to stop wearing your own ass as a hat. For fuck's sake, we are all adults here. Act like it. When you fuck up, admit it, say sorry and move on. Excuses are for the weak. But above all else, pull your own weight and treat your teammates decent. This completes my psychotic ramblings for today.
   

Interesting behavior of the column default mechanism

This isn't going to blow anyone's socks off or stun anyone like a taser to the face, but this bit me in the ass this week so I am sharing while it is fresh in my mind and to hopefully save someone some pain in the future.

Oracle provides functionality to put in a default value in a column. This week, we had an issue where a partner was passing some information but missing a field that is relatively unimportant but a downstream process was depending on. A brilliant solution would be using the oracle column default mechanism...or so I thought. Let's say the Table involved is ADDRESS and the column is COUNTRY_CODE. Some new downstream process expects every row to have a country code filled in, but the table allows nulls in the column. Well, you can still use default. Here is how you configure a default.

alter table address modify (country_code default 'US');

Pretty straightforward really. It doesn't matter if the column is nullable or not. It works either way. Well, depending on how you access the table. And there lies the rub this time. You can insert a row and/or update a row and the mechanism works but you can insert a row and/or update a row and the mechanism does not work, depending on the syntax of the statement.

When inserting a row, one can specify the column list before the values statement to specify a subset of the overall table's column list or provide the values in a different order than how they are specified in the table. You do this such as:

insert into address( address_id, addr, addr2, city, state, zip, country_code) values (1, '123 Main', 'Apt B', 'Nowheresville', 'KS', '90210', null);

Or

insert into address( address_id, addr, addr2, city, state, zip) values (1, '123 Main', 'Apt B', 'Nowheresville', 'KS', '90210');

The kicker is that the first insert will not utilize the default mechanism while the second one will. In plainer terms, if your insert or update specifies a column with a default value on it, the default value will not get populated.

Similarly,

update address set addr2 = 'Apt C', country_code = null where address_id = 1;

...allows the null value to enter the table in the country_code column. But,

update address set addr2 = 'Apt C' where address_id = 1;

...would set the country_code to 'US' if it was previously null on the record.

If this behaved in a different manner, a tiny database script could have prevented changing application code and freed up application developers to work on other bugs and features. However, because the application code interacts with the table by including the country_code column in the insert and update whether or not there is a value specified for them, we cannot use the default mechanism.

I only had a little bit of egg on my face and frustration this week, after suggesting the default column value mechanism as a solution and subsequently finding out its application is dependent on the column list in the statement. If your application code is intelligent enough to dynamically list the columns being inserted or changed or your database activity is controlled by stored procedures so that the application would call I_ADDRESS or ADD_ADDRESS procedures to insert a record, you could dynamically choose your column list depending on the values passed to the procedure and utilize the default value mechanism.

Well, that is a short and sweet little post this week. I guess they can't all be earth shattering and brilliant. Use the default mechanism with care and it will help you.

More on the Data Dictionary and SQL Writing SQL

     It has been quite a while since I posted here.I guess you could say I took the summer off. I have had a lot going on over the last few months both at work and at home. Things seem to be starting to settle back down now. My daughter is back in school and the most of our summer plans have been completed. 

     So, shall we dig back in? The last few weeks at work I have had the need to use the Data Dictionary in new ways. As we have touched on before, you can use the Data Dictionary to manage large tasks and let the database do some of the work for you. And you can use SQL to write SQL. We will cover SQL Writing SQL first. A basic (and silly) example would be:

Select ‘select x from dual;’ from dual;

     Your output here would be:

'SELECTXFROMDUAL;'

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

select x from dual;

     A more practical example would be this: let’s say that we have a key/value table with some data in one schema and we want to put it in a different schema but those schemas reside on different hosts. Here is our statement:

SELECT 'insert into my_table select seq_my_table_id.nextval, my_source.my_source_id, '''
  || KEY
  || ''', '''
  || value
  || ''' from target_schema.my_source where description = ''My New Bad Ass my_source'';' mySqlWritingSql
FROM my_table
WHERE my_source_id = 1521;                                                                                                                   
What the above select statement returns is this:  

MYSQLWRITINGSQL                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
insert into my_table select seq_my_table_id.nextval, my_source.my_source_id, 'key1', 'value1' from target_schema.my_source where description = 'My New Bad Ass my_source';                                                                                                                                                                                                                                                       
insert into my_table select seq_my_table_id.nextval, my_source.my_source_id, 'key2', 'value2' from target_schema.my_source where description = 'My New Bad Ass my_source';                                                                                                                                                                                                                                                                        
insert into my_table select seq_my_table_id.nextval, my_source.my_source_id, 'key3', 'value3' from target_schema.my_source where description = 'My New Bad Ass my_source';                                                                                                                                                                                                                                                             
insert into my_table select seq_my_table_id.nextval, my_source.my_source_id, 'key4', 'value4' from target_schema.my_source where description = 'My New Bad Ass my_source';                                                                                                                                                                                                                                                                              
insert into my_table select seq_my_table_id.nextval, my_source.my_source_id, 'key5', 'value5' from target_schema.my_source where description = 'My New Bad Ass my_source';                       

As you can probably tell, the double pipe operator (||) is the concatenate operator in SQL. Each of these returned rows is a syntactically correct insert statement that we can run on the target host. When writing these for yourself, you can test an insert and rollback. If you do this and want to test, be sure you are not doing an autocommit type of transaction. DDL’s are autocommit, DML’s are not. DDL’s are Create, Alter, Drop, etc. DML’s are Insert, Update, Delete, etc.

Another recent scenario where this type of statement can help is this: I have been developing Unit Tests for some of our products, however one of them lacks the stored procedures to handle look ups and to insert and remove data since we use a tool for this. At the database level where my tests will be running, I need these so I have been developing a package that contains the add  and delete procedures and the functions to look up necessary values. I have also had to modify my schema to match this product's model since I do the unit testing in my own schema. Over the years I have acquired some of the structures that I need but not all. I could have dropped everything and pulled in the whole schema but I thought it would be a fun exercise to pull the delta. This touches on the Data Dictionary at this point, however, so let me give you some background on this. You may remember some of this from an earlier post.

The Oracle Data Dictionary is a set of views owned by SYS that is automatically installed with Oracle. These are views into the objects that store all of the database users and all of the objects owned by those users. There are three types of views and generally each view has a counterpart in the other two types. The types are:

USER_%

ALL_%

DBA_%

The distinction between these are: the USER_ views deal only with the objects owned by the logged on USER so if you are logged in as schema1 the objects in the USER_% views are all owned by schema1   whereas the ALL_% views deal with ALL of the objects that the logged on USER has access to see so if schema1  as granted SELECT to schema2 (a read only user) then when you are logged in as schema2  you should be able to access some details about schema1  objects from the ALL_% views; and finally, the DBA_% views are for use by DBA’s and those with higher levels of access – they show other system information that most users do not require.

So, let’s take a look at a real world example. My schema and the project schema are co-located on the host so it makes this a bit easier.  I had a portion of - let's call this project XYZ - the XYZ data model in my schema and needed to isolate the objects I was missing. I wrote the following:

SELECT    'create table '
       || table_name
       || ' as select * from xyzstage.'
       || table_name
       || ' where 1 = 2;'
          it
  FROM all_tables
 WHERE     owner = 'XYZSTAGE'
       AND table_name IN (SELECT table_name
                            FROM all_tables
                           WHERE owner = 'XYZSTAGE'
                          MINUS
                          SELECT table_name FROM user_tables);                                                                         

This statement uses ALL_TABLES and USER_TABLES. I select the table name from all tables where owner is xyzstage and subtract the tables that I own. Then I use sql writing sql to generate the statements to create all of the table objects I am missing. The results look like this:

create table table1 as select * from xyzstage.table1 where 1 = 2;
create table table2 as select * from xyzstage.table2 where 1 = 2;
create table table3 as select * from xyzstage.table3 where 1 = 2;
create table table4 as select * from xyzstage.table4 where 1 = 2;
create table table5 as select * from xyzstage.table5 where 1 = 2;                                                                    
I added the where 1 = 2 clause so that I don't migrate data. I really only need the seed data for the look up tables and I will acquire that later. This is just about grabbing the structures that are missing. Now, my schema looks almost exactly like the XYZStage schema. Let's call the one holdout I’ve found so far CONFIG. This table in XYZ has a counterpart in ABC also called CONFIG. However, these were developed in different silos and diverged on column names.  The CONFIG table in my schema is the one for ABC. My solution is to append the columns from XYZ onto the one for ABC. I use different Data Dictionary tables for this:

SELECT    'alter table '
       || table_name
       || ' add '
       || column_name
       || ' '
       || data_type
       || DECODE (data_type,
                  'NUMBER', ';',
                  'DATE', ';',
                  '(' || data_length || ');')
          it
  FROM all_tab_cols
 WHERE     owner = 'XYZSTAGE'
       AND table_name = 'CONFIG'
       AND column_name IN
              (SELECT column_name
                 FROM all_tab_cols
                WHERE owner = 'XYZSTAGE' AND table_name = 'CONFIG'
               MINUS
               SELECT column_name
                 FROM user_tab_cols
                WHERE table_name = 'CONFIG');                                                                                              
    As we have discussed before, the || is used to concatenate strings and column values and build a SQL statement. The DECODE function is a native SQL function that we have covered before but will revisit briefly. In this case, we look at the value of the DATA_TYPE column for each row and include the DATA_LENGTH information for those that need it. The dictionary views we use are ALL_TAB_COLS and USER_TAB_COLS. These list the column information for tables for the schema owner and for all of the other schemas that the schema owner has access to reach. The results look like this:

alter table CONFIG add CREATE_DATE DATE;
alter table CONFIG add LAST_UPDATE DATE;
alter table CONFIG add LAST_UPDATE_ID NUMBER;
alter table CONFIG add CONFIG_TYPE VARCHAR2(64);                                                                      

You might be thinking, if the output of the statements is an executable statement, can you execute the executable statements in an executable statement executable sort of executable way? Executably. Here is an anonymous block I wrote to do that very thing but for a different table. I had already ran the above to alter the table so I created CONFIG2 based off of the XYZSTAGE schema and ran the below. Here it is:

DECLARE

   CURSOR col_cur

   IS

      SELECT    'alter table '

             || table_name

             || '2 add '

             || column_name

             || ' '

             || data_type

             || DECODE (data_type,

                        'NUMBER', ' ',

                        'DATE', ' ',

                        '(' || data_length || ')')

                it

        FROM all_tab_cols

       WHERE     owner = 'XYZSTAGE'

             AND table_name = 'CONFIG'

             AND column_name IN

                    (SELECT column_name

                       FROM all_tab_cols

                      WHERE owner = 'XYZSTAGE' AND table_name = 'CONFIG'

                     MINUS

                     SELECT column_name

                       FROM user_tab_cols

                      WHERE table_name = 'CONFIG2');

BEGIN

   FOR fix_config2 IN col_cur

   LOOP

      EXECUTE IMMEDIATE fix_config2.it;

   END LOOP;

END;

The purpose of this particular solution is to effectively write and execute the fix all at the same time. The real lesson here is how to wrap the SQL Writing SQL statement in a Cursor in an anonymous block and execute the results in a loop. The one caveat here is that the semi-colon cannot be in the statement. This has a few things we haven’t covered yet or perhaps recently.

First, an anonymous block is like an un-stored and unnamed procedure. If you are just calling another procedure, you can write an anonymous block like BEGIN myproc(); END; or if you have to declare variables it looks like DECLARE var1 integer := 1; BEGIN myproc(var1); END; or something like that.

Next, we have the cursor. Most of us know the cursor as the little blinky thing when typing but in Oracle a cursor is a pointer to a private SQL area that stores information about the processing of a DML statement or in this case a DDL statement. In this case, we are going to access that area via a for loop. The for loop goes like this: for <row_name> in <cursor_name> loop <do your stuff> end loop; and in this case the stuff we are doing is executing the SQL returned for each row via an EXECUTE IMMEDIATE <row_name>.<cursor column alias>; which uses EXECUTE IMMEDIATE to implement native dynamic SQL.

All of this combines to let us do some really powerful stuff. I am not saying you can take over the world with this but I am not saying you can’t either. But, with great power comes great responsibility. You can do some serious damage with this stuff. Practice locally and copy tables as backups so you can revert to a working database if things go south. Cover your ass.

Alright, I think that is more than enough information for now. Take a stab at this stuff and see how it works for you.

Oracle Flashback

So, I overheard a conversation this week and discovered a really interesting and potentially useful feature called Oracle Flashback. Depending on how your DBA's have setup the database you may or may not be able to hit the ground running with this feature. Let's say we were updating or deleting records in our table and did something we really didn't want to do, such as update or delete the wrong record. Depending on the complexity of your database, you may be able to replace or manually undo the mistake but in an OLTP system more than likely you are pretty much hosed. I've done it. Many have done it. It's often viewed as a badge of courage. You learn to pay closer attention. 

But, let's say you've done something by accident and committed before you noticed your error and you really need to recreate the data the way it was prior to your little whoopsie. Well, in comes Oracle like a messiah - no, a superhero. It saves the day with Oracle Flashback. There is a system package called DBMS_FLASHBACK that you would need execute on from your DBA and they would have to have setup the UNDO_RETENTION and UNDO_MANAGEMENT properties accordingly ahead of time. If not, you are going to have a bad time.

Anyway, here's the scenario: you get tasked with deleting a record from a table called RECIPE. You tried grandma's recipe for potato salad and it tastes like ass and cat food. You want to get rid of that motherfucker before someone ends up dead. In your haste, you forget to qualify your delete statement and up deleting the whole table and you committed. Oh noes!?!?! What do?!?!?! Well, you would write something like:

CREATE TABLE ORACLE_SAVE_MY_ASS_PLS AS 

    SELECT * FROM (

        SELECT * FROM RECIPE

     ) 

     AS OF TIMESTAMP SYSDATE - 0.125;

This will create a table that is a copy of recipe as of 3 hours ago. If your UNDO_RETENTION is set for a day or more, you might be able to fix something you fucked up yesterday. But if you  need to fix something you just did a few moments ago and have made other changes previous to that that were legit, you'd have to alter the number you subtract from SYSDATE to zero in on your change. As a reminder, SYSDATE is the function that returns the time stamp from the database server for when it is run. You can subtract or add a number to SYSDATE to go back or forward in time. Now, obviously for our current purposes we cannot go forward in time, that doesn't make sense. But, often systems will add 30, 60 or 90 days to SYSDATE to set a reminder in the database or to control some future functionality, like password expiration.

So far, I have yet to use this little feature to save my bacon (mmm, bacon!) but I can really see it's usefulness.  A caveat here would still be TRUNCATE. This will not work if you TRUNCATE the table. TRUNCATE does not record the action in the UNDO logs and you will get dry anal raped by TRUNCATE. Use this wisely. Be cautious and use sparingly. You best know what you are about son.

I will leave you hear. I've slacked off a bit lately on posting and I'm trying to get back on the proverbial whore, err, horse or horse-faced whore. Whatever. I need to post more frequently is what I'm trying to say. And I have the sudden urge to watch Sex and the City with the sound off and my pants down. Ah, the Chris Noth is a handsome motherfucker.

Finally approaching warp speed with Unit Tests or More about this DBDeveloper's past than you probably care to know

I haven't posted for a bit. I've been busy at home and busy at work. The last few sprints at work have been grueling and ball-busting. Sprints are the Agile process's coding increments. Ours are currently broken into two weeks. Tasks are divided into small enough granules that can be completed in that two weeks or (the one that seems to be happening more and more) broken up in the the smallest pieces that are logically or easily done. My latest task is changing four reports and logically, any one of those could be completed and released independent of the others, but that would mean creating four separate issues to track and overhead for people so it is lumped together. I may or may not get these done in two weeks. I have recently had an issue drag out for several sprints. Let me tell you it fucking sucks. In no uncertain terms, it is a terrible feeling to go into a meeting and have people looking at you like what the fuck have you been doing? Why isn't this done? Well, my excuses have been, scope creep, under pointing stories ( issues are also sometimes called stories and they are pointed to give a weight of difficulty or time involved), stories still at an epic level, and the length of time and the monumental amount of work to arrive at a functional unit test at the database level. Some of these need to be addressed more in our planning meetings or retrospectives where we plan out what to work on and talk about what worked well and what didn't work well looking back. I will bust my hump on this current task and potentially get it done on time, but I need to bring up the self-defeating behavior to the group and discuss methods to minimize this.

The one thing that has seemed to be improving is the unit test portion. A year or more ago, I had not a fucking clue what a unit test was. I've been out of school for about 15 years now, and I must say that I haven't kept up with everything or much of anything really going on in the industry at large. I've had a lot of my personal time consumed by mitigating poor life choices and the like. I've gone through bankruptcy, divorce, the birth of my children, the betrayal of trust by once close friends, and the death of family and friends, just to name a few. Boo fucking hoo, amiright? Really, I was never very industrious and usually gravitated towards spending time doing things that weren't healthy for me. I'm not very career-centric, at least, historically speaking. I've just meandered through: should probably go to college (went to college), should probably graduate (graduated), should probably get a job in my field (got job in my field), should probably get a better job in my field (got better job in my field), etc.

So, now (and I realize I am digressing like a motherfucking boss - wait for it...) I find myself surrounded by people that are very cognizant of their skill sets growing stale and the industry moving forward in leaps and bounds. Since I am a very impressionable guy and easily bow to peer pressure, I have started branching out and learning more, etc. Of course, it helped that I finally started to get my personal shit together. I haven't had my ass kicked in 16 years or so and I haven't had a death threat in 7 or 8. I might be a real boy now, Geppetto! Anyway, since my personal life is doing good, my only distractions at home are hobbies, the family and the usual stuff that every "normal" person deals with, I have some time that I can safely devote to self-improvement (and not just on the tech side of things).

Anyway, back to not knowing what the fuck a unit test was. Given my penchant for doing only things that were at a minimum unproductive and usually detrimental to my health, sanity, employment, etc., I was completely clueless to the advancement of testing and quality control and what not. If my schooling had touched on any of this, I had quickly forgotten it in a booze and stripper fueled haze. I logically understand the need for good testing and find that unit tests and test based development in theory are wonderful things, but in real life, can be quite the undertaking for a database guy. In application code unit tests, programmers can stub out or easily simulate the tangential and/or dependent pieces that your current code needs to interact with to work correctly. This simulation is much more difficult at the database level. For one thing, how do you simulate a record existing in a table? If the procedure you are testing interacts with a record in a table, you pretty much have to have a record in the goddamn table. This, coupled with the advice of a non-database programmer that "you cannot depend on any record existing prior to your test" that I religiously followed because, like most religious zealots, I didn't think about it. Zing! I swallowed it hook line and sinker. My first unit test was a fucking mess. It was a Gary Busey in drag kind of ugly conglomeration of things that shouldn't be where they were and are just repulsive to think about. I quickly learned the uselessness of assuming that nothing exists prior and adding everything. Many of our supporting tables have a strictly controlled list of values, lacking even sequences to populate the primary keys. And, if my goal is to test how a certain construct affects data in a real world scenario, creating some of these type records from scratch simply do not make sense.

About, halfway through my second test, I figured out that I needed a hybrid approach. I needed to be able to assume that some things were in fact already present in the database to appropriately test certain constructs. I pursued the idea of this hybrid like a queer dog pursues a Prius, lightly skipping along with chapped lips and sore knees and humming a Justin Bieber tune. I think I failed to mention that my first test took 99% of the development time. It vastly dwarfed the real actual coding. The part that added benefit to the application and eventually eased the suffering of the end user, at least to a degree, was over quite quickly and I struggled with the "this test isn't going to help things" mindset for a while. Without the mandate that all code needed to have unit tests, I probably would have dropped it. After the hybrid approach was considered, that test still took quite a long time to develop but I felt pride that I improved the process a bit.

My next test resulted in even more improvement on the process. I wrapped the procedures with stripped down procedures and adding error handling and surfaced issues with more actionable information. I really felt it starting to gel. I have finally reached the point where the amount of work to write the unit test didn't dwarf the value adding portion of the work. It was still a 75/25 split or so but it seemed more logical this time around and I felt more capable of actually completing my work on time. I actually met a self imposed deadline today. Its been a while since I've hit one. It feels good man. Awwwww yeahhhhhhhh!

I am hopeful that I can shift the ratio more in my favor in the next iteration and gain even more speed in developing tests and confirming that shit works before it hits production. It is only a matter of time before the infrastructure side of the house has their ducks in some kind of arrangement that looks sort of row-ish and the focus of the issues we are having as a company becomes more application development centric. They probably will not give a shit about why it takes me so long to code these tests, they probably will just ignore any reasons whether valid or bullshit. I'd better get to where I can knock out a unit test in a much faster time. Maybe not premature ejaculation type times. Definitely not Miculek target shooting times. But, something better than 3 times the duration of the real development. I'll get there. With every one, I gain a further nuanced understanding of the inner workings of this thugged out crazy ass database. I don't know if you know this, but one of the database we have is completely fucked up. It is like Lindsay Lohan on Spring Break. It makes no sense, looks like shit, and probably forgot its Plan B. Someone is making a stop at the clinic.

In closing, Unit Tests are a good thing. You should make sure your shit works. Some of us are less perfect than others but none of us are infallible to typos and faulty logic. We cannot know our shit is bulletproof. It isn't. Even if it is, it is better to prove it by shooting at it than just making magnanimous claims like, "My Shit Doesn't Stink!" I say, "NO." and "FUCK YOU!" and "YOUR SHIT STINKS! ALONG WITH YOUR BREATH, YOUR FEET, MOST OF YOUR OPINIONS, AND YOUR MOTHER"S COOKING. SHE CAN SURE SUCK A DICK THOUGH..." But don't get mad, so does mine. I miss you mom.

Perhaps I've said too much. But, no matter. I came here to write a post and fuck bitches and my post is finished.