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.

Unit Tests and Oracle

Our organization has relatively recently jumped on the bandwagon and started testing our code. Fucking weird, I know. Why test your code, you say? Aren't we all super brilliant and everything we right comes out the write way? Well, as you can tell by me switching the right and write there, sometimes shit happens. Even the best authors have typos now and then and even the best coders misplace a semi-colon from time to time. Yeah, the compiler will probably catch the bogus semi-colon but sometimes we have errors in logic as well. I know, I know. It sucks to say. Coders are human and humans are great and terrible and everything in between. And sometimes those errors in logic are so subtle that you might not find it until it is live, and you might not find it until some customer points it out and then you have angry managers and egg on your face and have to endure the snickers and giggles of your co-workers and not just when they catch a glimpse at the urinal. So, some genius somewhere invented the idea of testing your code. This happens in java and dot net by faking a picture of the application and supplying everything that is needed to run a scenario through and compare the output against your expected results. In the database, it is a little bit different. I can not always substitute something fake for something real. Often times, I have to actually enter data into the tables and then delete it afterwards.

So, anyway, all of the other fucktard coders here are implementing unit tests or at least faking it. I didn't want to be left out and didn't really have a choice in the matter. There are at least three ways to do this. One is to write everything yourself. Code setup procedures and tear down procedures and test procedures and run the tests. Two is to find some product to assist you. Three is to do a little bit of both. I have settled on the third way at this point. I wrote my own tests for the first couple of objects before I found the UTPLSQL package written by Steven Feuerstein. It was freeware so I grabbed it and took a look. It is a pretty interesting set of code but since most of the objects I am testing are stored procedures for reporting, I found the asserts in the UTPLSQL a bit limiting. So, naturally, I figured I would use as much as I could and make up the rest on my own. It has worked okay so far.

Unit tests are self-contained objects that mimic everything but the subject of the test and then compare results received from the test subject against expected results. Let's imagine our program is a calculator. It would be relatively easy to test that 1 + 2 = 3 and such. In reality, I work with a transaction processing system and the reports can be quite complicated to test. I insert a new record for every table involved in the report query and compare the results of the stored procedure. For our calculator program we might issue something like: assertTrue(calc(1,2,'+'), 3); however, my report procedures return the data set in a cursor. It looked like the UTPLSQL had an assert that would handle cursors but I had a looming deadline and a few minutes with it wasn't enough for me to figure it out so I panicked and wrote my own cursor assert. I write a package for each report I need to test that contains a setup procedure ut_setup, a tear down procedure ut_teardown, a test procedure ut_rpt_to_test where the name has ut_prepended to the report stored procedure name, and a shit ton of constants. The constants are the string literals that I insert during setup and delete during tear down and compare against the results during the test.

It is still a little bit foreign to me and I'm still learning new and better ways to test. Each one gets a little easier. If you have to work with Oracle and are looking to bulletproof your code, check out UTPLSQL. There may be others out there as well. There are probably suites for other popular databases as well, but you may prefer to home-cook your own. So, go ahead you fucking rebel! Be a Tom Petty! You won't back down from unit testing! But, if HB 499 passes you might get another dance with Mary Jane. Am I right? Nudge nudge wink wink, say no more Squire!

I may get into the nuts and bolts of the tests in a future post and some of you fucking rock star coders might show this humble database halfwit a thing or two with your fancy hipster unit testing. You probably did it before it was cool. Goddamn hipsters! Get off my lawn!

Until next time.

Accepting multiple values in a parameter

I had an interesting case recently where I had to modify a report to accept a parameter that could be a comma separated list of values and I needed to filter on the value(s). Someone on the system before me created a function called stringsplit that helps this occur. Her is the form of stringsplit:

CREATE OR REPLACE FUNCTION STRINGSPLIT (expression varchar2, delimiter varchar2 := ',')

return SPLIT_TABLE pipelined

IS

    idx    pls_integer;

    listTmp    varchar2(32767) := expression;

BEGIN

    loop

        idx := instr(listTmp,delimiter);

        

        if idx > 0 then

            pipe row(substr(listTmp, 1, idx-1));

            listTmp := substr(listTmp, idx+length(delimiter));

        else

            pipe row(listTmp);

            exit;

        end if;

    end loop;

    return;

END STRINGSPLIT;

As you can see, this function returns a SPLIT_TABLE type. The form of the Split_Table:

CREATE OR REPLACE TYPE SPLIT_TABLE AS TABLE OF VARCHAR2(32767);

So, our type is a table of large varchar2 values and our function takes a comma separated list and returns a table of those values. Now, we need to reference and conditionally filter on this in our report query. The condition is that if it is null, do not filter, otherwise filter. It would look something like:

CREATE OR REPLACE PROCEDURE myRecipeReport (

   recipeTypeList   IN     VARCHAR2 DEFAULT NULL,

   result_cursor       OUT SYS_REFCURSOR)

IS

BEGIN

   OPEN result_cursor FOR

      SELECT r.name

        FROM recipes r

       WHERE (   recipeTypeList IS NULL

              OR (EXISTS

                     (SELECT TO_NUMBER (COLUMN_VALUE)

                        FROM TABLE (STRINGSPLIT (recipeTypeList, ','))

                       WHERE TO_NUMBER (COLUMN_VALUE) = r.type)))

      ORDER BY 1;

END myRecipeReport;

This allows us to run the report for everything (list parameter is null) or filter on a subset of available types.

We are getting into several new things here: Types, Casting, etc. Google anything that you are interested in that I do not cover explicitly. 

Types:

A Type is a user defined method to model the structure and behavior of data in an application. You can create object types and static array types and as we've seen, table types. These are usually collections of data that are used somewhere in your application. You will need the Create Type privilege to create a type in your schema, or Create Any Type to create a type in a different schema. 

Casting:

As you may be aware from other coding ventures, Casting converts one datatype to another. 

Pipelined:

You specify Pipelined on a function when you want the function to be the source of data instead of a table. In our purpose here, we are calling the stringsplit function on a single variable and creating a table out of that.

Pipe row:

Pipe row is an Oracle function that is used in the pipelined function to return the results in a table format.

Select from Table:

This is how you treat the results of the pipelined function.

Pls_integer:

This is a pl/sql datatype in the NUMBER family that ranges from -2,147,483,647 to 2,147,483,647. It uses machine arithmetic and is usually faster in operations than the sql datatype NUMBER.

Column_value:

This is how you refer to the column returned from the pipelined function in the table format.

Exists:

This is a native oracle function that executes a subquery and returns true or false depending on whether or not rows were found. If the subquery refers to a column in the parent query it is referred to as a correlated subquery.

I feel I must apologize for the dearth of fucks and shits and goddamnits in this post. I must be off my game today. I am still rather disgruntled and somewhat riled up at the absurdities and idiocy that constantly surround me. I think I must have just focused on getting the information out this time. I will redouble my efforts to include references to coat-hanger abortions and liken my fucktard predecessors to Corky from Life Goes On, Client #9 and/or Justin Beiber's presumably tiny hairless nutsack. Until next time, keep fighting the good fight, stockpiling weaponry, practicing your tomahawk moves, preparing for the zombie apocalypse or civilization collapse and just whatever general badassery you are into. Thanks Obama! ;)

Oracle's NEXT_DAY function and How and Why to use it

I recently received an interesting request. How many transactions did we receive last year on a Friday, Saturday or Sunday? It is an interesting question depending on what kind of transactions you have and what reporting requirements may exist. So, I get this request and I'm all like...WTF? Bitches be trippin'. I have never received a question like this before. It is new ground for me. Ooh, I'm excited! Sorry Ma'am. You too sir.

I do a bit of googling and I don't really find much. Then I discovered NEXT_DAY in an Oracle Doc. So, NEXT_DAY takes a DATE argument and a CHAR argument. The CHAR argument we'll call DayOfWeek.   It is 'SUN'...'SATURDAY', meaning you use either the full name of the day of the week or the standard abbreviation and pass a date in and your results are the date of the next DayOfWeek that you supplied. For example:

select next_day('01-JAN-2013', 'SUNDAY') mydate from dual;

Gets:

MYDATE
1/6/2013

So, the first sunday after the first was the 6th. Ok. Now for the tricky part:

select next_day('06-JAN-2013', 'SUNDAY') mydate from dual;

Gets:

MYDATE
1/13/2013

At first, this threw me for a loop, but then it started to make more sense. It is called NEXT_DAY after all, so it would not return the same date. It would return the date of the next DayOfWeek. So, the way to short circuit that if you want to check the closest day is date - 1. So,

select next_day('05-JAN-2013', 'SUNDAY') mydate from dual;

Gets:

MYDATE
1/6/2013

So, now we come to the part where I slay the dragon, save the day and get the girl. Or keep struggling to restore the Thieve's Guild to its former glory. Anyway, I created a table to store a years worth of 3 day weekends, so to speak.


CREATE TABLE WEEKEND_DATES (
 
    WEEKEND_DATE DATE
   
);

I think looped through a year's worth of days (one for each week) and inserted the weekend dates for each week. Like so:

DECLARE

   l_i      NUMBER;

   mydate   DATE;

BEGIN

   l_i := 0;

   LOOP

      SELECT NEXT_DAY (TRUNC(SYSDATE) - L_I, 'FRIDAY') INTO MYDATE FROM DUAL;

      INSERT INTO WEEKEND_DATES

           VALUES (MYDATE);

      SELECT NEXT_DAY (TRUNC(SYSDATE) - L_I, 'SATURDAY') INTO MYDATE FROM DUAL;

      INSERT INTO WEEKEND_DATES

           VALUES (MYDATE);

      SELECT NEXT_DAY (TRUNC(SYSDATE) - L_I, 'SUNDAY') INTO MYDATE FROM DUAL;

      INSERT INTO WEEKEND_DATES

           VALUES (MYDATE);

      L_I := L_I + 7;

      EXIT WHEN L_I >= 373;

   END LOOP;

   COMMIT;

END;

So, now we have the table WEEKEND_DATES filled with the dates for all of the Fridays, Saturdays and Sundays over the last year. Now we can join that to the transaction table or use an In Clause and a sub-select or an exists with a sub-select to get our desired results. Here is a final query:

SELECT

       CASE timestamp

          WHEN NEXT_DAY (timestamp - 1, 'FRIDAY') THEN 'FRIDAY'

          WHEN NEXT_DAY (timestamp - 1, 'SATURDAY') THEN 'SATURDAY'

          WHEN NEXT_DAY (timestamp - 1, 'SUNDAY') THEN 'SUNDAY'

          ELSE 'NOT A WEEKEND AND SHOULD BE IMPOSSIBRU!!!'

       END

          DAY_OF_WEEK,

       T.* 

FROM TRANSACTIONS T

WHERE TRUNC(timestamp) IN (

SELECT WEEKEND_DATE FROM WEEKEND_DATES);

So, you may be looking at this and going why are you TRUNCing everything? Well, the NEXT_DAY function retains the time portion of the date supplied. So, the first time though this process, the only results received were the ones that matched on the full time as well as date. When you TRUNC a date, it sets the time portion to midnight. This allows you to compare based on any time during the day. TRUNC does have other options so google that if you are interested and I have touched on it in past posts. Also, know that the IN clause might not be the best performing of the options available, but because this was a one-time ad hoc query and results returned quickly enough, I did not dwell on performance. Now if you sick fucks are wondering about the CASE statement above, I simply had the common decency to provide the knowledge of which DayOfWeek the given record actually fell on. I'm cool like that. Now, since we are limiting to days that are Friday, Saturday or Sunday, no other days of the week should be possible. If the results had any IMPOSSIBRU in it, then I rawdogged the pooch like a motherfucker somewhere along the line. I'll need to circle the wagons, go back to the beginning and figure out the failure point, after I kick the pooch down the stairs (just in case).

Alright, I think that is it for today. Class dismissed.

Fuckers.

Function-Based indexes

Indexes in Oracle are like indexes in books and documents. It is simply a way of finding things quicker than looking through the whole thing. But, there can be some tricky little gotchas when it comes to Oracle data and real world scenarios.

I was recently tasked with adding an index to a column in a table. At first glance this is a stupid simple task. Think of the dumbest, most clueless fucktard you know. They could create an index. It is that simple. The confusion and trickiness and why I have a job is knowing when to use an index and how to use an index.

CREATE INDEX index_name ON table_name (column_name [, another_column_name...]);

Not heavy lifting is it? Bro, do you even index?

So, as any good DB dude should do, I ran some queries to get to know the data I was tasked with indexing. So, it turns out that the column I was supposed to index was largely null. This is a problem. Nulls are not included in indexes. How do you index the absence of something? Now, I could go ahead and create an index on the column and all of the non-null values would be indexed appropriately. But, would that be enough? I needed to find out how the column is referenced in the system. I ran a query to look through all of the database objects for references to the column and/or table. I might cover this in another post. I was able to tell that the column was not used in any WHERE clauses in the DB code. I went to an App guy to get perspective on the application and how it interacted with the column. So, it turns out that most of the calls from the App were checking on the NULL status of the column. Well, that's no good. All of those calls would not use our shiny new index and the Application would continue to be dog-ass slow.

Now we get to the meat of this post. Recent versions of Oracle are able to create indexes on function calls on columns. I believe it was added in 8i but wouldn't swear to it. No matter, we are on 11g. The statement I wrote is:

CREATE INDEX FBIDX_MYTAB_NVL_MYCOLUMN ON MYTABLE( NVL(MYCOLUMN, 'some value'));

I named the column FB to indicate Function-Based and IDX so I can know at a glance that it is an Index if I am looking at multiple objects in a list or something. Remember, meaningful names are never a bad idea. The interesting part of this is the NVL function on MYCOLUMN. NVL says, if MYCOLUMN is null, pretend it is 'some value' so I can compare it to other things. So, even when MYCOLUMN is null, NVL(MYCOLUMN, 'some value') is equal to 'some value'. And 'some value' can be indexed. Of course, to fully utilize the Funciton-based index, all of the calls that check the NULL status of the column have to be changed to NVL(MYCOLUMN, 'some value') = 'some value'. But, the bottom line is that the NULL values get indexed and interaction with the table is improved.

Letting the Data Dictionary do some of the leg work

Let's say that you have a column that is on several tables and you need to modify the length of that column. One method of doing that would be to figure out which tables have the column and manually write a shit pot load of alter statements to do what needs to be done. Now, let's suppose you are a shade or two brighter than Corky from Life Goes On. You are going to let the data dictionary do the work for you. Oracle comes with a plethora of tables, views, synonyms and what not that is chock fucking full of information. They are broken up based on item type and user access type. If you are curious, check public synonyms in your schema or if you have read access to SYS, check out the views that look like one of the following:

ALL_%
DBA_%
USER_%

Where the % is some string like TABLES, PROCEDURES, etc. Let's focus on USER_% first. You connect to Oracle as a user and the SYS views that begin with USER_ are the ones that you own. They exist in your schema - the one you logged in with anyway. So, USER_TABLES will list the tables you own, USER_PROCEDURES will list the procedures you own, and so on and so forth. Whereas, the SYS views with ALL_ are the ones that you have access to read, write, execute, etc. The DBA's and a lucky few with elevated privileges will have access to DBA_ views and can see everything in the database. Therefore, DBA_TABLES will list every stinking table in the entire goddamn database.

Now, here is an amazing little trick that will save you a Honey Boo Boo's mom sized chunk of work. You can write queries against those tables where the results are SQL commands. Let's say you have 3 procedures entitled MYPROC1, MYPROC2, and MYPROC3. Now let's suppose you want to compile all of them. You would write select 'ALTER PROCEDURE ' || procedure_name || ' COMPILE;'  as alterallmyprocs from USER_PROCEDURES; and execute that bad boy. Your results will look like:

ALTERALLMYPROCS
ALTER PROCEDURE MYPROC1 COMPILE;
ALTER PROCEDURE MYPROC2 COMPILE;
ALTER PROCEDURE MYPROC3 COMPILE;

So, you can paste those results into a sql window, execute as script and Oracle will compile all of your procedures. Pretty handy right? Well, imagine that you have 25 schemas with similar structures and there are 100 tables in each. Every one of those tables has a column called LAST_UPDATER that tracks via the application which user last updated the record. And now, let's suppose that the USERNAME column is getting enlarged so all of these LAST_UPDATER columns would need to expand to contain the enlarged USERNAME. Ignore for the moment that ideally you would be using the corresponding user id instead of the actual username. This is for instructional purposes, capisce? Would you really want to write the 2500 required statements by hand? Well, maybe you are a masochist. Or maybe you are a contractor. Or maybe you like to sandbag and appear industrious while actually slacking. In those cases, and for you Corky's out there, continue to work hard, not smart. I'll be using sql writing sql to get the same result in a fraction of the time. Just for fun, here is the sql writing sql statement to perform the above task:
select 'alter table ' || owner || '.' || table_name || ' modify ( ' || column_name || ' varchar2(256) );' it from all_tab_cols where column_name in ('USERNAME', 'LAST_UPDATER') and owner like '%MYAPP%';

I was recently tasked with some Year End reports. I needed to run a query in every production database for a specific app or set of apps and I used this technique to meet the narrow deadline. Of course the underlying requirement is that I am able to log into a schema created for me but given read access everywhere for purposes just like this.

Of course, with great power comes great responsibility. Use this for the good of all mankind. Do not fuck with people using this. Do not write something like select 'delete from ' || owner || '.' || table_name || ';' it from all_tables where owner = 'ThatDickThatAggravatesTheFuckOutOfMeAndFoolishlyGaveMePrivsOnHisTables';
Or even worse: select 'delete from ' || owner || '.' || table_name || ' where mod(' || column_name || ', 13) = 0;' from all_cons_columns where constraint_name like '%PK%' and column_name like '%ID%' and owner = 'ThatDickThatAggravatesTheFuckOutOfMeAndFoolishlyGaveMePrivsOnHisTables';
The former is going to delete everything from tables that do not get 'Child Record Found' errors but the latter is wicked nefarious. It will delete every row whose primary key id is evenly divisible by 13 and there are no child record conflicts. Sure, I could work out how to eliminate those conflicts but I don't want to make it that easy for you to really fuck with someone.

All kidding aside, properly used, this can save a ton of time and heartache. Start small, pay attention to syntax, and test, test, test!