How many of you are familiar with K.I.S.S? Lick it up? God of Thunder? Cold gin? Any of these sound familiar? If so, I applaud your good taste in bad music. But, I am talking about the Keep It Simple Stupid mantra many of those in IT have forgotten or eschew in favor of overly complex systems that they can gloat about designing and generally fuck up in one way or another. I am reminded of a guy I used to work with that loved to design and implement overly complex systems because he sounded smart. His white board designs invariably resembled His Holiness The Flying Spaghetti Monster. This is the guy that didn't trust the native windows management stuff so wrote his own which resulted in an application that docked in the upper left corner and could not be moved. Yeah, that is way better than a regular Windows window dude! Not.
The new systems we write and the old systems we maintain and improve grow in complexity naturally. Some of them are needfully complex from day one, but some can start quite simple and grow complex over time as features are added and new systems come up that need to be utilized. For the love of everything holy, do not artificially create complexity. Make stuff as stupid simple as you can from the beginning. In all that you do, this will pay dividends in the future.
I was tasked recently with comparing databases for an application we have with a couple dozen separate instances. Ideally, each of these instances would be identical in structure (or nearly so) and differ on data mainly (Customer 1 in Database A is not the same as Customer 1 in Database B). Unfortunately, we have only recently gotten into structured deployments using Hudson, not to mention a rogue DB Developer we had on staff back in the day (holla!). Over the years, there has been divergence. Some of them are easy to identify as useless or no longer useful items that we can clean up, but some, oh, some are hard to identify as such. In mature systems in mature organizations, the keys to the databases are heavily guarded and formal processes are in place to keep most people out. Some start ups even into their adolescent phase have thrown the doors wide open. How this starts is small businesses or business units that have either no formal DBA or only a couple of coders and a DBA that is wearing a few different hats. In strange cases you may have a boss that is making decisions that are poorly thought out. Whichever happens, what you end up with is objects that you have no idea who created, why they were created, if they are still in use, or what other objects might depend on those. What you end up with is a crazy Jenga game where the wrong table drop can mean long night spent bailing the system out, grumbles and finger pointing from your peers and maybe even the loss of gainful employment. Tread carefully.
This little statement can be a lifesaver in this situation:
SELECT name, line, text
FROM dba_source
WHERE upper(text) like upper('%<Object name to search>%');
Replace <Object name to search> with the table or procedure name you want to check. If you do this and get a table or view does not exist error, you need to get your DBA involved to grant you the appropriate data dictionary privileges. This statement will search stored procedures, functions, packages and the like for a reference to the searched text. If the table is one-off for a specific reason, probably due diligence was not used during its creation so the dependencies tab in a DB browsing tool or the dependency data dictionary table would not have any information to assist you in making a decision regarding the object's worth. You may be able to deduce after some inspection whether it is a useful and still actively used object. But you may not, and individual inspection of items doesn't scale. What if there are 50 objects that do not match? Individual inspection would be a month long task if not longer.
Now, consider table constraints for a moment. Let's say when we built our recipe table from earlier posts we made sure the recipe id could never be null. One way, and a very popular way of doing this is directly in the table definition:
Create Table RECIPE (
RECIPE_ID NUMBER NOT NULL
.
.
.
);
Now, let's say you want to monetize your recipe application. Every new subscriber gets their own database. A year down the road you have 50 subscribers so you have 50 database schemas with roughly identical copies of the recipe database structure. Now, you hire a neighborhood kid to do some light tech work that is beneath you as the King of the new Recipe Database Empire FaceCookBook or CookFaceBook or CookBook? You are too busy with discussions of your impending IPO and talks with Bono and what not to compare your databases. Your new hire compares everything, including constraints. What would you say if I told you that your 50 databases have 50 different constraints for each original constraint? Surprised? When you think about it, it makes sense but I never took the time to think about it until it was too late. My comparison of 27 databases have 120,000+ collisions on constraints. Every table that had an in line NOT NULL declaration on a column or a DEFAULT value declaration had a different name in each database. Oracle has an automatic sequence that is behind the scenes and when you declare a column to be NOT NULL or DEFAULT 0 or whatever, the way Oracle implements and enforces that is by creating a system named constraint in the form of SYS_C00000000001 or something like that where the 1 is the value from the system constraint sequence. Since you are letting each individual system name the constraints whatever it wants, there is no guarantee that SCHEMA1.SYS_C0000000001 is identical to SCHEMA2.SYS_C0000000001. Basically, all hell breaks loose. You get these false positives or probable false positives that muddy the waters so you cannot see immediately that TABLE C in SCHEMA 48 is missing a constraint on COLUMN X.
Let's consider an alternative. Try this instead:
CREATE TABLE recipe
(
recipe_id NUMBER CONSTRAINT recipe_recipe_id_nn NOT NULL
.
.
.
);
Now, the naming convention lacks something but IMHO it is far superior to SYS_C0001869645. If you ever tried to insert a recipe with a recipe id, instead of an error message that says SYS_C000689345 violated you would see RECIPE_RECIPE_ID_NN violated. You immediately know that the recipe id column in the recipe table should be not null. And, when the neighborhood kid does the compare for you, he doesn't interrupt your IPO meeting with Jon Bon Jovi and Curt Schilling to tell you that there are 120,000+ discrepancies between databases and WTF is going on!?!!?! It could get ugly.
For more on constraints, check out docs.oracle.com, www.oraclefaq.com, http://jonathanlewis.wordpress.com (I like this guy), http://asktom.oracle.com, or Burleson at http://www.dba-oracle.com/. If you find a page with Cowboy Burleson on it you have to take a shot but if you find a page with Uncle Touchy Pedo Burleson on it you have to put yourself on a list and seek therapy - extra points if you take three shots, do the tuck and exclaim, "It puts the lotion on its skin, or it gets the hose again!"
Friday, December 14, 2012
Friday, December 7, 2012
The Decode function in Oracle
Oracle has a function called decode that allows you to decode coded values or even encode values depending on your needs. The function takes a column, a series of if-then pairs and a default value. An example is in order. Let's think back to our Recipe database example from earlier posts. You have a Table called Recipe and it has a column called Type to show if the recipe is an Appetizer or a Soup or a Dessert for example. And let's say that in Version 1 of our recipe database we just made RECIPE.TYPE a CHAR(1) column. So, we store 'A' for Appetizer, 'S' for Soup and 'D' for Dessert, etc. Now, let's say your Mom/SO/Daughter/Client is confused by the 'S' on the screen and why Salads show an 'L' (for obvious reasons we cannot have both Soup and Salad designated with an S unless we want to combine them so the 'S' designates a Soup or a Salad. For this example, we want to keep them separate, and 'A' was taken for Appetizer so the next available letter in Salad is the 'L', but you don't want to explain every one of them. You can write a SQL statement with a Decode and list out all of them one time and use that where you need it. Our statement would look something like:
SELECT NAME, DECODE(TYPE, 'A', 'Appetizer', 'S', 'Soup', 'D', 'Dessert', 'L', 'Salad', 'Other') AS Decoded_Type FROM RECIPE;
This is pretty handy. Now, let's say you want to run a report and see how many different type recipes you have. One possible query, using multiple decodes within an aggregate would be:
SELECT
SUM(DECODE(TYPE, 'A', 1, 0)) as NumberOfAppetizers,
SUM(DECODE(TYPE, 'S', 1, 0)) as NumberOfSoups,
SELECT NAME, DECODE(TYPE, 'A', 'Appetizer', 'S', 'Soup', 'D', 'Dessert', 'L', 'Salad', 'Other') AS Decoded_Type FROM RECIPE;
This is pretty handy. Now, let's say you want to run a report and see how many different type recipes you have. One possible query, using multiple decodes within an aggregate would be:
SELECT
SUM(DECODE(TYPE, 'A', 1, 0)) as NumberOfAppetizers,
SUM(DECODE(TYPE, 'S', 1, 0)) as NumberOfSoups,
SUM(DECODE(TYPE, 'D', 1, 0)) as NumberOfDesserts,
SUM(DECODE(TYPE, 'L', 1, 0)) as NumberOfSalads,
SUM(DECODE(TYPE, 'A', 0, 'S', 0, 'D', 0, 'L', 0, 1)) as NumberOfOthers
FROM RECIPE;
Now, the way SUM(DECODE(TYPE, 'A', 1, 0)) as NumberOfAppetizers, reads is: for every record, I want to select 1 if the TYPE = 'A' and 0 Otherwise, Oh, and, please sum up the total of that for me and call it NumberOfAppetizers. This might not be the best way to do this for our example, but I ran across a real world case where someone had written a huge SQL statement where the calculated those values in sub-selects, and then joined all of those sub-selects together to display the desired information. Although the above is a much simplified version it could be expanded upon to utilize DECODE, alleviate the multiple sub-select join, and improve readability and performance.
A couple of other things we hit upon this time that might not have seen before is the nested function calls and the column aliasing. Functions, whether SQL Native or After-Market, depending on what they do and your use-case or needs, can be nested where the inner function executes and returns result to the outer function which then executes. We see that with the decode statements and the sum functions. The decode converts the type to either a 1 or 0 for each row and then the results are summed for the entire table. Pretty neat stuff. I have used this a lot with dates - ADD_MONTHS(TRUNC(SYSDATE), 1) or something like that. The column aliasing lets you rename columns in a select statement. SELECT TYPE AS MY_TYPE, SUM(AMOUNT) AS PROFIT FROM RECIPE GROUP BY TYPE; etc.
I feel this might be a slippery slope here because my explanations keep introducing new things. ADD_MONTHS is a function that takes a date and returns the passed value moved n months into the future or past depending on sign. Yes, ADD_MONTHS('01-DEC-2012', -12) will return '01-DEC-2011'. Sysdate, I believe we have covered before but just in case we haven't that is the system date from the oracle server. TRUNC is a function that truncates part of the date value that you pass depending on what you pass. TRUNC(SYSDATE) drops the time portion and returns the current day month and year. Other options exist, like TRUNC(SYSDATE, 'MM') returns the first day of the current month, etc. Another new item is the GROUP BY statement. If you notice the absence of GROUP BY in the earlier example, that is because I did not select anything that was not being aggregated. That is the rule with GROUP BY in Oracle. You must group by everything that you are not aggregating. If you are aggregating everything you do not need the GROUP BY. So, in the PROFIT example, I am selecting TYPE and the SUM of AMOUNT so I must GROUP BY TYPE or Oracle tells me TYPE is not a group by expression.
Check out the Oracle Function Docs for more information.
I'll dig into more interesting Oracle SQL statements, problems and/or solutions in the future. And, I might even lose my shit and go off on a rant. Toodles.
FROM RECIPE;
Now, the way SUM(DECODE(TYPE, 'A', 1, 0)) as NumberOfAppetizers, reads is: for every record, I want to select 1 if the TYPE = 'A' and 0 Otherwise, Oh, and, please sum up the total of that for me and call it NumberOfAppetizers. This might not be the best way to do this for our example, but I ran across a real world case where someone had written a huge SQL statement where the calculated those values in sub-selects, and then joined all of those sub-selects together to display the desired information. Although the above is a much simplified version it could be expanded upon to utilize DECODE, alleviate the multiple sub-select join, and improve readability and performance.
A couple of other things we hit upon this time that might not have seen before is the nested function calls and the column aliasing. Functions, whether SQL Native or After-Market, depending on what they do and your use-case or needs, can be nested where the inner function executes and returns result to the outer function which then executes. We see that with the decode statements and the sum functions. The decode converts the type to either a 1 or 0 for each row and then the results are summed for the entire table. Pretty neat stuff. I have used this a lot with dates - ADD_MONTHS(TRUNC(SYSDATE), 1) or something like that. The column aliasing lets you rename columns in a select statement. SELECT TYPE AS MY_TYPE, SUM(AMOUNT) AS PROFIT FROM RECIPE GROUP BY TYPE; etc.
I feel this might be a slippery slope here because my explanations keep introducing new things. ADD_MONTHS is a function that takes a date and returns the passed value moved n months into the future or past depending on sign. Yes, ADD_MONTHS('01-DEC-2012', -12) will return '01-DEC-2011'. Sysdate, I believe we have covered before but just in case we haven't that is the system date from the oracle server. TRUNC is a function that truncates part of the date value that you pass depending on what you pass. TRUNC(SYSDATE) drops the time portion and returns the current day month and year. Other options exist, like TRUNC(SYSDATE, 'MM') returns the first day of the current month, etc. Another new item is the GROUP BY statement. If you notice the absence of GROUP BY in the earlier example, that is because I did not select anything that was not being aggregated. That is the rule with GROUP BY in Oracle. You must group by everything that you are not aggregating. If you are aggregating everything you do not need the GROUP BY. So, in the PROFIT example, I am selecting TYPE and the SUM of AMOUNT so I must GROUP BY TYPE or Oracle tells me TYPE is not a group by expression.
Check out the Oracle Function Docs for more information.
I'll dig into more interesting Oracle SQL statements, problems and/or solutions in the future. And, I might even lose my shit and go off on a rant. Toodles.
Friday, November 30, 2012
A Really Interesting Real World Data Issue
I was approached this week by a colleague that had a data issue. A column was showing duplicates but visually the values had to be different. How to figure out what was different? We would eventually want to consolidate all of the values to the same value but finding out the differences would be interesting and important - we wouldn't want to propagate the incorrect values.
Think about a table with a column and when you select distinct or select unique() or group by the values look alike but are different. How do you figure out what is different? The standard functions for strings in Oracle are okay but stripped down. There isn't a Left, Right or Mid like there might be in SQL Server or Access. There is substr(value, initial position, length). You could loop through and compare each character and exit the loop when something is different and that might give you where they are different. Then you could compare the ascii values of the characters at those positions to see what is different and decide upon a a good value and then update that character. But that is pretty complex and it takes a lot of manual intervention and you would not see an interesting issue this way.
I did some research and discovered a function called DUMP. Now, Oracle has taken a dump on my life on a daily basis for 12 years now. I finally get to take a DUMP on Oracle. Ah, revenge is sweet! Taking a dump of a column will give the type, length and ascii value of each character. This is brilliant! And native! I have obfuscated the data for an example that I can provide to you all. Imagine a table called dump_example:
create table dump_example (
dump_column varchar2(4000));
After adding in the values and we get this:
select * from dump_example;
"DUMP_COLUMN"
"20121130�EXAMPLE�TEXT�VALUE"
"20121130 EXAMPLE TEXT VALUE"
"20121130 EXAMPLE TEXT VALUE"
Think about a table with a column and when you select distinct or select unique() or group by the values look alike but are different. How do you figure out what is different? The standard functions for strings in Oracle are okay but stripped down. There isn't a Left, Right or Mid like there might be in SQL Server or Access. There is substr(value, initial position, length). You could loop through and compare each character and exit the loop when something is different and that might give you where they are different. Then you could compare the ascii values of the characters at those positions to see what is different and decide upon a a good value and then update that character. But that is pretty complex and it takes a lot of manual intervention and you would not see an interesting issue this way.
I did some research and discovered a function called DUMP. Now, Oracle has taken a dump on my life on a daily basis for 12 years now. I finally get to take a DUMP on Oracle. Ah, revenge is sweet! Taking a dump of a column will give the type, length and ascii value of each character. This is brilliant! And native! I have obfuscated the data for an example that I can provide to you all. Imagine a table called dump_example:
create table dump_example (
dump_column varchar2(4000));
After adding in the values and we get this:
select * from dump_example;
"DUMP_COLUMN"
"20121130�EXAMPLE�TEXT�VALUE"
"20121130 EXAMPLE TEXT VALUE"
"20121130 EXAMPLE TEXT VALUE"
An interesting issue I ran into is the Length function and Dump diverging on the length for the given column:
If we select the length of the dump column from the dump example table
select dump_column, length(dump_column) len_dump_col from dump_example;
"DUMP_COLUMN" "LEN_DUMP_COL"
"20121130�EXAMPLE�TEXT�VALUE" 27
"20121130 EXAMPLE TEXT VALUE" 27
"20121130 EXAMPLE TEXT VALUE" 27
...we see that the length of all three values is 27. Now let's take a dump! Once done with that, we wipe and wash our hands with soap and hot water for a few minutes. And then, we do this:
select dump_column, dump(dump_column) len_dump_col from dump_example;
"DUMP_COLUMN" "DUMP_DUMP_COL"
"20121130�EXAMPLE�TEXT�VALUE" "Typ=1 Len=33: 50,48,49,50,49,49,51,48,239,191,189,69,88,65,77,80,76,69,239,191,189,84,69,88,84,239,191,189,86,65,76,85,69"
"20121130 EXAMPLE TEXT VALUE" "Typ=1 Len=27: 50,48,49,50,49,49,51,48,32,69,88,65,77,80,76,69,32,84,69,88,84,32,86,65,76,85,69"
"20121130 EXAMPLE TEXT VALUE" "Typ=1 Len=30: 50,48,49,50,49,49,51,48,194,160,69,88,65,77,80,76,69,194,160,84,69,88,84,194,160,86,65,76,85,69"
Notice the divergent length values. So, something in the selecting and displaying of the values suppresses the multiple white space and somehow the Length function ignores those extras as well. It is the ninth character that begins the divergence in actual value so we need to figure out what the character is related to 32, 194, and 239. If we go to http://www.asciitable.com/ we can see that 32 is the space and the others are on the extended character map and we should probably go with the space as the good character. We can update the other values to consolidate the table but that doesn't explain how two native functions in Oracle can get divergent results like they did. I still haven't quite figured out how or why that is happening. I've been considering posting this out on some expert sites and see if I get any information.
I was really pleased that this issue popped up this week. I was intrigued by the issue and enjoyed the investigation process and the new function that I learned about. Has anything happened recently in your daily work load that caused you to learn something new and exciting?
I plan on getting back to the introductory stuff in the next blog.
Thursday, November 8, 2012
How to add, delete, and change data in a database.
I recently went into a little bit of detail about databases and the SELECT statement. That is useful when you want to see what is in your database and do reporting, etc. But, this presumes that the data already exists. What if you have a new database with tables and things but have no data? What if you want to change some data? What if you want to remove data from your table(s)? Where did your mom learn that little trick with her tongue? These are all compelling questions.
There are utilities out there that can load data in bulk into a table but let us focus on the native SQL statements that achieve our desired results. INSERT, UPDATE, DELETE and Tijuana, I'm guessing. Let's say we have a table called RECIPE and this table has these columns:
RECIPE_ID, NAME, TYPE, SOURCE, DATE_CREATED, DATE_MODIFIED
We want to add a record to this table. There are some intricacies here but let's keep it simple.
Your insert statement might look something like this:
INSERT INTO RECIPE VALUES (1, 'Chocolate Chip Cookies', 'Dessert', 'Grandma Betty', sysdate, sysdate);
NOTE: Sysdate is a mechanism to get the current date from the Oracle database. Many systems use it to keep track of when something happens by selecting it and storing it as we have done.
If you run this and commit it, you will have one record in your table. If you have auto commit turned on you are a maniac and an unsavory sort and I want nothing to do with you. Auto commit is a feature that, you guessed it, commits everything you run automatically. You might be thinking this saves you from forgetting to commit or it saves you a step. In my experience, it adds several steps when you have to unfuck whatever you just fucked up. Use with extreme caution.
Now, let's say you are on the phone with Grandma Betty and tell her that you added her cookie recipe and she says, "Well, you know, Dear, that is your Great Great Grandmother Ester's recipe." Oh Noes! What do? Calm the fuck down! I got this. You need to update your record.
UPDATE RECIPE SET SOURCE = 'Great Great Grandma Ester', DATE_MODIFIED = sysdate WHERE NAME = 'Chocolate Chip Cookies';
Commit;
You may actually use the RECIPE_ID in the where clause in place of the NAME but I wanted to keep it understandable. Hopefully this is understandable...
Now, let's say you try these cookies and they taste like Satan's taint. Great Great Grandma Ester went full retard and her recipe is awful and must be exterminated with extreme prejudice. No worries, Brah.
DELETE FROM RECIPE WHERE NAME = 'Chocolate Chip Cookies' AND SOURCE = 'Great Great Grandma Ester';
Commit that shit, yo.
With these tools, you can add, remove and modify data from your database as you desire. Now, I'm off to your mom's house. Toodles.
There are utilities out there that can load data in bulk into a table but let us focus on the native SQL statements that achieve our desired results. INSERT, UPDATE, DELETE and Tijuana, I'm guessing. Let's say we have a table called RECIPE and this table has these columns:
RECIPE_ID, NAME, TYPE, SOURCE, DATE_CREATED, DATE_MODIFIED
We want to add a record to this table. There are some intricacies here but let's keep it simple.
Your insert statement might look something like this:
INSERT INTO RECIPE VALUES (1, 'Chocolate Chip Cookies', 'Dessert', 'Grandma Betty', sysdate, sysdate);
NOTE: Sysdate is a mechanism to get the current date from the Oracle database. Many systems use it to keep track of when something happens by selecting it and storing it as we have done.
If you run this and commit it, you will have one record in your table. If you have auto commit turned on you are a maniac and an unsavory sort and I want nothing to do with you. Auto commit is a feature that, you guessed it, commits everything you run automatically. You might be thinking this saves you from forgetting to commit or it saves you a step. In my experience, it adds several steps when you have to unfuck whatever you just fucked up. Use with extreme caution.
Now, let's say you are on the phone with Grandma Betty and tell her that you added her cookie recipe and she says, "Well, you know, Dear, that is your Great Great Grandmother Ester's recipe." Oh Noes! What do? Calm the fuck down! I got this. You need to update your record.
UPDATE RECIPE SET SOURCE = 'Great Great Grandma Ester', DATE_MODIFIED = sysdate WHERE NAME = 'Chocolate Chip Cookies';
Commit;
You may actually use the RECIPE_ID in the where clause in place of the NAME but I wanted to keep it understandable. Hopefully this is understandable...
Now, let's say you try these cookies and they taste like Satan's taint. Great Great Grandma Ester went full retard and her recipe is awful and must be exterminated with extreme prejudice. No worries, Brah.
DELETE FROM RECIPE WHERE NAME = 'Chocolate Chip Cookies' AND SOURCE = 'Great Great Grandma Ester';
Commit that shit, yo.
With these tools, you can add, remove and modify data from your database as you desire. Now, I'm off to your mom's house. Toodles.
Friday, October 26, 2012
The Importance of Early Investigation
I will get back to the SQL for dummies stuff next time. This bit me in the ass this week in my home life. The concept here can be applied to pretty much every aspect of your life. Two words: Do your fucking homework!
I got a frantic and exasperated holler from the kitchen this week. It seems the water was backed up in the dishwasher and what do we do! OMG! WTF! BBQ? Damn. So being the dutiful spouse (Ha! Whatevs...) I hop to it, grab some tools and pull the dishwasher apart and proceed to find nothing wrong. I double and then triple check everything. All is kosher. Spent quite a lot of time bailing water and looking for clogs and scratching my head and teaching the kids fun new cuss words. Ever heard a 4 year old shout "motherfucking bitch ass dishwasher!" and "what the actual fuck!". Did that really happen, you ask? No, no it didn't. But you can imagine, right? Nudge Nudge Wink Wink.
So, my exasperated and profanity laden evening would not have been wasted had I took a brief minute to check the simplest things first. Turns out my daughter was looking for a specific cup and it happened to be in the dishwasher, mid-cycle. A dishwasher that is humming and churning and leaking steam might deter the weaker spirited children and, perhaps, the brighter ones too. Not my daughter. She promptly unlocked the dishwasher and opened it mid-cycle to get her precious. Now, most of us would set things back to how we found them so whatever was happening and causing the aforementioned steam and churning business could continue. Alas, dreamy thoughts of iCarly, horses, milk and cookies, world domination, and "why is my brother such a douche?" were no doubt overwhelming and the washer remained unlocked and the poor cycle remained unfinished.
All of this effort, pain, suffering, effort, 'innocence bunker'-busting f-bombs, and effort could have been easily avoided. Experience is a bitch. It gives us bloated heads and fat honey boo boo's mama-sized egos. A complete novice might take some time to map out how things should work, what the normal process is, the happy path, and then devise a strategy of checking all of these in order of importance. However, it would be super easy for someone that has ever worked on a clogged dishwasher to assume that standing water means clogged drain and some of the time that would be correct. But only some. And some of the time ain't all of the time. 60% of the time it works every time is fine for Sex Panther cologne, Axe body spray, and Curtis's Valtrex but you don't want a 40% fail rate on your code or your condoms. You will have a bad time. So, do your Uncle DBDeveloper a favor and think just a smidge before diving in. Check the easiest shit first. That's why Service Desk asks you to turn it off and back on. It's easy. And sometimes it fixes whatever shit the bed. Start with the simplest, easiest or quickest thing. Even if your gut is telling you it is super complex and what elegant solutions you might devise to resolve the problem. If your problem is an 8 year old girl with a penchant for obliviousness, how is an elegant solution for a mis-perceived root cause going to help? Two words: it fucking ain't.
I don't care how long you've been in the industry and how many lines of code you have written, If 60% of that was for the wrong reason, then I award you no points and may god have mercy on your soul. Seriously, check your goddamn ego at the door. Your boss doesn't give two shits how elegant a solution you can come up with for Problem B when Problem A is the one losing them money. Don't be stupid twat. Well, be as small a stupid twat as you can be. Not all of us can completely avoid the natural twattiness that is inside of us. I am ashamed to admit that even I, yes, I, have a twatty streak, perhaps not quite a mile wide but certainly somewhere between Bree Olsen and the Octomom. I struggle everyday against this cursed twat gene. I am not sure which side I get the twat gene from - both sides exhibit such traits. Perhaps this is a curse of humanity. Perhaps there is a little stupid twat in all of us. Maybe if we were less concerned with skin color and income level and more concerned with reading books and staying fresh, the world would be a better place. Lik dis eff u cry evrityme.
But seriously, do your homework. When a problem comes along you must whip it, but first you must think what is the simplest thing it could be? Check that shit first. I could have had a lovely evening with video games, beer and internet porn but instead I was up to my elbows in half cleaned dishes and dishwasher parts.
I plan to get back on the SQL for stupid twats series again next week. Stay tuned.
I got a frantic and exasperated holler from the kitchen this week. It seems the water was backed up in the dishwasher and what do we do! OMG! WTF! BBQ? Damn. So being the dutiful spouse (Ha! Whatevs...) I hop to it, grab some tools and pull the dishwasher apart and proceed to find nothing wrong. I double and then triple check everything. All is kosher. Spent quite a lot of time bailing water and looking for clogs and scratching my head and teaching the kids fun new cuss words. Ever heard a 4 year old shout "motherfucking bitch ass dishwasher!" and "what the actual fuck!". Did that really happen, you ask? No, no it didn't. But you can imagine, right? Nudge Nudge Wink Wink.
So, my exasperated and profanity laden evening would not have been wasted had I took a brief minute to check the simplest things first. Turns out my daughter was looking for a specific cup and it happened to be in the dishwasher, mid-cycle. A dishwasher that is humming and churning and leaking steam might deter the weaker spirited children and, perhaps, the brighter ones too. Not my daughter. She promptly unlocked the dishwasher and opened it mid-cycle to get her precious. Now, most of us would set things back to how we found them so whatever was happening and causing the aforementioned steam and churning business could continue. Alas, dreamy thoughts of iCarly, horses, milk and cookies, world domination, and "why is my brother such a douche?" were no doubt overwhelming and the washer remained unlocked and the poor cycle remained unfinished.
All of this effort, pain, suffering, effort, 'innocence bunker'-busting f-bombs, and effort could have been easily avoided. Experience is a bitch. It gives us bloated heads and fat honey boo boo's mama-sized egos. A complete novice might take some time to map out how things should work, what the normal process is, the happy path, and then devise a strategy of checking all of these in order of importance. However, it would be super easy for someone that has ever worked on a clogged dishwasher to assume that standing water means clogged drain and some of the time that would be correct. But only some. And some of the time ain't all of the time. 60% of the time it works every time is fine for Sex Panther cologne, Axe body spray, and Curtis's Valtrex but you don't want a 40% fail rate on your code or your condoms. You will have a bad time. So, do your Uncle DBDeveloper a favor and think just a smidge before diving in. Check the easiest shit first. That's why Service Desk asks you to turn it off and back on. It's easy. And sometimes it fixes whatever shit the bed. Start with the simplest, easiest or quickest thing. Even if your gut is telling you it is super complex and what elegant solutions you might devise to resolve the problem. If your problem is an 8 year old girl with a penchant for obliviousness, how is an elegant solution for a mis-perceived root cause going to help? Two words: it fucking ain't.
I don't care how long you've been in the industry and how many lines of code you have written, If 60% of that was for the wrong reason, then I award you no points and may god have mercy on your soul. Seriously, check your goddamn ego at the door. Your boss doesn't give two shits how elegant a solution you can come up with for Problem B when Problem A is the one losing them money. Don't be stupid twat. Well, be as small a stupid twat as you can be. Not all of us can completely avoid the natural twattiness that is inside of us. I am ashamed to admit that even I, yes, I, have a twatty streak, perhaps not quite a mile wide but certainly somewhere between Bree Olsen and the Octomom. I struggle everyday against this cursed twat gene. I am not sure which side I get the twat gene from - both sides exhibit such traits. Perhaps this is a curse of humanity. Perhaps there is a little stupid twat in all of us. Maybe if we were less concerned with skin color and income level and more concerned with reading books and staying fresh, the world would be a better place. Lik dis eff u cry evrityme.
But seriously, do your homework. When a problem comes along you must whip it, but first you must think what is the simplest thing it could be? Check that shit first. I could have had a lovely evening with video games, beer and internet porn but instead I was up to my elbows in half cleaned dishes and dishwasher parts.
I plan to get back on the SQL for stupid twats series again next week. Stay tuned.
Friday, October 12, 2012
What is a database and what is SQL?
What is a database? Well, it simply is a collection of data. How many of you are old enough to remember card catalogs in libraries? I know I am, but I am getting up there. How many of you have mothers? A few, I am guessing. How many of those mothers have recipe cards in a box or cookbooks on a shelf? Those are all databases, in essence. The IT version of a database is a little more complicated and can house shit tons of data. Every store, restaurant, gas station and school you have seen or been in, apart from some little mom and pop shops that are relics from a by-gone era, have databases that store information about the items they sell, the customers that buy them, the employees that work, etc.
Let's go back to the card catalog for a moment. A card catalog is a wooden dresser with lots of little drawers. Each drawer is filled with lots of little cards. Each card is filled with information about a book. Likewise, a database is filled with tables (drawers) and each table is filled with records (cards) and each record is filled with information about a specific thing (book). The way to get the information out of the card catalog is largely manual, although modern libraries have a computerized version of this that simplifies it a bit. You still have to get a piece of paper, jot down the Dewey Decimal number which is the location of the book and then walk around and find it. In a database, the method of retrieval is a bit different. Which brings us to our second question...
What is SQL? SQL or Structured Query Language is a method of inserting, modifying, or retrieving information from IT databases. ANSI-SQL is the industry-wide agreed upon terminology and methodology for interacting with data in a database. Each company has to make slight additions or change implementations a bit to provide value and/or differentiate themselves from the competition but the core is the same across all brands.
There are several different ways of getting data into a database and modifying data in a database, but the bread and butter of SQL is the SELECT statement. Let's say you are an IT guy and you built a recipe database for dear old mom. There will be a period of time where you, she or the whole family enters the data into the database. Afterwards, when mom wants to bake a cake, she will write a select statement. Now, depending on how advanced of an IT guy you are or how much you love your mother, you may mask the select statement behind a search box on a web page but the underlying mechanism is a SELECT. That statement might look like this:
SELECT * FROM Recipes WHERE recipe_type = 'CAKE';
This would return every record in the Recipes table that is for a cake. Simple, right? Sure, for Mom's Recipes, yeah, it is simple. Now think about a store like Home Depot? What might their database look like? What might a SELECT statement look like there? Well, it can get pretty complicated. What if your database was designed by a drunken water-head with extra chromosomes, parents that are cousins and no idea what a good database looks like? Queries in this behemoth make modern politics look sane and straightforward. I can only surmise that our DB architects mother was a willing participant because as we all know, if it was a legitimate rape, her body would have "shut that whole thing down". What a whore. I blame her for everything. With all the drinking she must have done in those nine months, I can't believe it went full term. It kind of makes me wish for a time traveling retroactive sterilization program that could nip that shit in the bud. The downside of that is, I'm pretty sure my Ex-wife would partake of that and my blogs would go poof into nonexistence. But, I digress.
SQL can be simple and can be very complex. But if it were always easy, everyone would do it, you know, like your mom.
Well, there you have it. Another installment from the angry database dude. I had so hoped I would avoid the anger and do a legitimate post, but people are stupid and they piss me off and my anger shut that whole legit thing down. Eh, maybe next time. And, as John Henson always says, Good night and big balls.
Let's go back to the card catalog for a moment. A card catalog is a wooden dresser with lots of little drawers. Each drawer is filled with lots of little cards. Each card is filled with information about a book. Likewise, a database is filled with tables (drawers) and each table is filled with records (cards) and each record is filled with information about a specific thing (book). The way to get the information out of the card catalog is largely manual, although modern libraries have a computerized version of this that simplifies it a bit. You still have to get a piece of paper, jot down the Dewey Decimal number which is the location of the book and then walk around and find it. In a database, the method of retrieval is a bit different. Which brings us to our second question...
What is SQL? SQL or Structured Query Language is a method of inserting, modifying, or retrieving information from IT databases. ANSI-SQL is the industry-wide agreed upon terminology and methodology for interacting with data in a database. Each company has to make slight additions or change implementations a bit to provide value and/or differentiate themselves from the competition but the core is the same across all brands.
There are several different ways of getting data into a database and modifying data in a database, but the bread and butter of SQL is the SELECT statement. Let's say you are an IT guy and you built a recipe database for dear old mom. There will be a period of time where you, she or the whole family enters the data into the database. Afterwards, when mom wants to bake a cake, she will write a select statement. Now, depending on how advanced of an IT guy you are or how much you love your mother, you may mask the select statement behind a search box on a web page but the underlying mechanism is a SELECT. That statement might look like this:
SELECT * FROM Recipes WHERE recipe_type = 'CAKE';
This would return every record in the Recipes table that is for a cake. Simple, right? Sure, for Mom's Recipes, yeah, it is simple. Now think about a store like Home Depot? What might their database look like? What might a SELECT statement look like there? Well, it can get pretty complicated. What if your database was designed by a drunken water-head with extra chromosomes, parents that are cousins and no idea what a good database looks like? Queries in this behemoth make modern politics look sane and straightforward. I can only surmise that our DB architects mother was a willing participant because as we all know, if it was a legitimate rape, her body would have "shut that whole thing down". What a whore. I blame her for everything. With all the drinking she must have done in those nine months, I can't believe it went full term. It kind of makes me wish for a time traveling retroactive sterilization program that could nip that shit in the bud. The downside of that is, I'm pretty sure my Ex-wife would partake of that and my blogs would go poof into nonexistence. But, I digress.
SQL can be simple and can be very complex. But if it were always easy, everyone would do it, you know, like your mom.
Well, there you have it. Another installment from the angry database dude. I had so hoped I would avoid the anger and do a legitimate post, but people are stupid and they piss me off and my anger shut that whole legit thing down. Eh, maybe next time. And, as John Henson always says, Good night and big balls.
Thursday, September 27, 2012
The Pain of doing things the Right way after having done them Wrong for oh, so long.
If you are human and you've written something and are reasonably intelligent - say somewhere between a door stop and Fox News Anchor, chances are good that if you come back to whatever it was that you wrote after a bit of time, you are going to see things that could be improved or written differently. Those that fail the reasonably intelligent bit think their shit is golden and keep making the same damn mistakes. If you are writing novels or editorials or blogs, the impact of your poor writing is probably negligible. But, if you are writing code and scripts and doing tasks that other people's code and scripts are based on, as the Ski Instructor says...You are gonna have a bad time. The mishmash of half-assed code and fucktard scripts snowballs into a leviathan of suck. It is an epic undertaking to unfuck the fuckeduppedness.
All of the places I have written code for, yeah, both of them, have been somewhere in the midst of gosh we have done some shitty things and we really need to do things in a sane way...maybe follow some best practices and not consult the magic eight ball or other eight ball before making crucial architectural decisions or implementing some homegrown, inbred, failed abortion of a thing with Patrick Duffy for a leg and a celery hand. Goddamn it - unraveling this FSM-looking colossal mess will test your testicular or ovarian fortitude. You better wear a cup. Servers may crash, skies may fall - rest assured tears will be shed. Suck it up and forge ahead.
There is an old proverb (Mongolian maybe) that states: No matter how far down the wrong road you go, turn back. This definitely applies to technology and diabeetus - depending where you are on 435 in KC or 270 in STL it might by shorter to keep going.
So, after years of being told, get it done, of changing code during a deploy - not right before a deploy, but during a goddamn deploy, of we'll come back to documentation at a later date, of he who screams the loudest gets his feature added, we are attempting to put in structure and do things sanely. Let's test our shit before we deploy it. Wat? Document your inputs and outputs - make it easier for someone to code against your stuff. Da fuq?
Being the only database and sql dude on the app side, I was forgotten or picked last. I had heard some bitching from the full on coders about having to write tests for something that has worked for years when there are other things breaking and new fun shit to do and thought, "hahaha - fuck you, cry about it you baby girl" *in my best Vince Vaughn* - well this baby girl is crying about it now - jokes on me. I had a 7 second change to a stored procedure - literally 7 seconds - not literally almost about 7 seconds - well, no, really more 10-ish - fucking 7 Mississippi's and I was done with the code change. It took me days to figure out all of the inputs and how to appropriately build out the data picture "because you cannot assume the data exists - you have to create it and then clean up after yourself" and our system is pretty fucked up. If there was a God and he was a heroin addict with Schizophrenia, Priapism, Parkinson's and Tourette's - not to mention a spastic colon - He could not eat tainted sushi from a sidewalk vendor and shit something worse that this. Or maybe I am just a little bitter.
This process is going to make you want to shoot some people or at least pistol whip them. The sad truth is that most of the time, you had a hand in creating this candidate for a really really late term abortion. You may want to pistol whip yourself, as well. Don't waste time doing that however because there is a mountain of work ahead of you. It is going to suck. It is going to drain your soul and make you question your choice in profession. I have been thinking about how I used to tend bar. I was pretty goddamn good at it too. Bars are fun. Sometimes you can drink. Sometimes a girl will go wild and that's always fun. When my eyes are crossing and I am cussing my mother for not swallowing me instead, I consider going back to that life. Not one single person has come back to the bar after I made them a drink and asked me to rollback. That sounds pretty good. But then I remember the bad parts of it, getting shot at, tires slashed, ass kicked in monolithic brawl, having to socialize with mouth-breathing knuckle-draggers and scumbag Steve's as far as the eye can see. Fuck that. Benefits are nice too. Paid holidays and occasional free pizza make me happy. I could use a nice pair of tittays once in a while though. I'll have to settle for CTRL-SHIFT-N.
Anyway, virtually nobody does it right the first time. You have to pay your dues. Things that are difficult to accomplish make you feel like a fucking badass when you triumph over them. And fuck it, it all pays the same. But, someday - some fine day - you will be sitting atop the Everest of code done the right way. Shit actually works as expected. Changes that fix one thing but break something else are caught quicker than Mexican food through a goose. You can install things like Chaos Monkey and break shit without impacting user and losing money. And ultimately, your end-users are served better, quality is high, output is fast, everyone gets raises and you get to live the good life. Or at least that's what these motherfuckers keep telling me. Maybe I'll get there one day. Maybe I'll see the bright shining lights of Utopia. But for now, its all asses and elbows and "who in their right fucking mind would think that inserting values without checking them is a good idea?" and "step away from the firearm, sir."
In closing, I feel like I just brain-skeeted all over this page but catharsis is good for me but potentially puzzling for you Dear Reader. Console yourself with the fact that "We lib a fyt anuda day". I'm off to drink some Gatorade, or maybe H2O. Toodles.
All of the places I have written code for, yeah, both of them, have been somewhere in the midst of gosh we have done some shitty things and we really need to do things in a sane way...maybe follow some best practices and not consult the magic eight ball or other eight ball before making crucial architectural decisions or implementing some homegrown, inbred, failed abortion of a thing with Patrick Duffy for a leg and a celery hand. Goddamn it - unraveling this FSM-looking colossal mess will test your testicular or ovarian fortitude. You better wear a cup. Servers may crash, skies may fall - rest assured tears will be shed. Suck it up and forge ahead.
There is an old proverb (Mongolian maybe) that states: No matter how far down the wrong road you go, turn back. This definitely applies to technology and diabeetus - depending where you are on 435 in KC or 270 in STL it might by shorter to keep going.
So, after years of being told, get it done, of changing code during a deploy - not right before a deploy, but during a goddamn deploy, of we'll come back to documentation at a later date, of he who screams the loudest gets his feature added, we are attempting to put in structure and do things sanely. Let's test our shit before we deploy it. Wat? Document your inputs and outputs - make it easier for someone to code against your stuff. Da fuq?
Being the only database and sql dude on the app side, I was forgotten or picked last. I had heard some bitching from the full on coders about having to write tests for something that has worked for years when there are other things breaking and new fun shit to do and thought, "hahaha - fuck you, cry about it you baby girl" *in my best Vince Vaughn* - well this baby girl is crying about it now - jokes on me. I had a 7 second change to a stored procedure - literally 7 seconds - not literally almost about 7 seconds - well, no, really more 10-ish - fucking 7 Mississippi's and I was done with the code change. It took me days to figure out all of the inputs and how to appropriately build out the data picture "because you cannot assume the data exists - you have to create it and then clean up after yourself" and our system is pretty fucked up. If there was a God and he was a heroin addict with Schizophrenia, Priapism, Parkinson's and Tourette's - not to mention a spastic colon - He could not eat tainted sushi from a sidewalk vendor and shit something worse that this. Or maybe I am just a little bitter.
This process is going to make you want to shoot some people or at least pistol whip them. The sad truth is that most of the time, you had a hand in creating this candidate for a really really late term abortion. You may want to pistol whip yourself, as well. Don't waste time doing that however because there is a mountain of work ahead of you. It is going to suck. It is going to drain your soul and make you question your choice in profession. I have been thinking about how I used to tend bar. I was pretty goddamn good at it too. Bars are fun. Sometimes you can drink. Sometimes a girl will go wild and that's always fun. When my eyes are crossing and I am cussing my mother for not swallowing me instead, I consider going back to that life. Not one single person has come back to the bar after I made them a drink and asked me to rollback. That sounds pretty good. But then I remember the bad parts of it, getting shot at, tires slashed, ass kicked in monolithic brawl, having to socialize with mouth-breathing knuckle-draggers and scumbag Steve's as far as the eye can see. Fuck that. Benefits are nice too. Paid holidays and occasional free pizza make me happy. I could use a nice pair of tittays once in a while though. I'll have to settle for CTRL-SHIFT-N.
Anyway, virtually nobody does it right the first time. You have to pay your dues. Things that are difficult to accomplish make you feel like a fucking badass when you triumph over them. And fuck it, it all pays the same. But, someday - some fine day - you will be sitting atop the Everest of code done the right way. Shit actually works as expected. Changes that fix one thing but break something else are caught quicker than Mexican food through a goose. You can install things like Chaos Monkey and break shit without impacting user and losing money. And ultimately, your end-users are served better, quality is high, output is fast, everyone gets raises and you get to live the good life. Or at least that's what these motherfuckers keep telling me. Maybe I'll get there one day. Maybe I'll see the bright shining lights of Utopia. But for now, its all asses and elbows and "who in their right fucking mind would think that inserting values without checking them is a good idea?" and "step away from the firearm, sir."
In closing, I feel like I just brain-skeeted all over this page but catharsis is good for me but potentially puzzling for you Dear Reader. Console yourself with the fact that "We lib a fyt anuda day". I'm off to drink some Gatorade, or maybe H2O. Toodles.
Tuesday, September 4, 2012
Adventures in the Fog
Disclaimer: I am not positive of what guidelines, etiquette, etc. I should be following here so I am going to write this like I would tell someone that I bumped into at the water cooler. It may be bitchy at times. Some stuff is really aggravating and I've heard tell that Blogs can be cathartic. Take everything with a grain of salt and don't be such a bitch.
I had an interesting ulcer inducing morning this morning. I received a request for a report that was timing out in the Admin, which is Standard Operating Procedure for us because we report off of the transaction system because why would you want to do things the right way. That is so boring and it has totally been done before.
Anyway, I get the request and it is for data from the old system that was cobbled together by steam punk artists and Katrina survivors. The queries for the reports are built in the java code, are pulled together by loading temp tables and massaging data in the application. It is horseshit. We had a project a few months back that allows for pulling old system data from the new system so I checked and we had converted the requested report to a stored procedure. One would think this would be a fantastic thing. Turns out, not so much.
I convert the parameters to bind variables so I can just run the query and export the data. I get an Invalid Number error on the service code field...which is alpha-numeric. WTF, right? How can Oracle throw a number error on a character column with no conversion functions on it? I know! It shouldn't! Does not compute!
For those of you who don't know...a good way to figure out what is wrong with an extensive query is to strip things out by commenting column lists, joins, etc until you get to something you know works right. I begin the process by dropping some tables from the query (NOTE: I did NOT drop tables from the schema - that's just a phrase for commenting things out) until I get something that works. Problem is, I never got something that worked.
Another good method for fixing a broken query is rewriting it from scratch - adding in more columns and tables until you get the desired result or recreate the issue. Hopefully by this time we are getting zeroed in on the problem and can formulate a plan of attack to fix the goddamn thing.
I start out with a single table and the query runs as expected. I add in a few more columns and joins and stuff and everything seems to be working. I get to the last little bit and boom. No dice. It doesn't make any sense. Service Code can contain numbers but should not be restricted to numeric only. And why the sometimes issue? Not sure.
I take another tack and create a few temp tables to simulate the joins in the query. It is basically the idea that if there is a problem when you join table a, table b and table c all at once, how about create table ab and then joining that to c. It may shine light on whatever the ultimate issue is.
So, after a few hours of investigation, false starts and curses hurled at a world that would birth an abomination like this, I discover the true issue. The start and end date parameters are passed as timestamp and in the query, a to-char wraps the end date but not the start date. When you convert a parameter in a query to a bind variable (by adding a ':' in front) and are passing date or timestamp, you can write the value in the default format to short circuit conversion. In this case the varchar2 value'01-AUG-2012' will implicitly convert to timestamp when the query runs. Since we are doing an inclusive range, we are converting the end date to character and adding in the time component of ' 23:59:59' and since the incoming parameter is timestamp when the procedure runs as normal we need to convert that timestamp to varchar2 to add in the time component. There was a superfluous to_char() on end date. Oracle somehow flagged service_code as the problem column. Wild goose chases ensues. Fun is had by all.
I blame: Oracle for misleading errors, the system for not working appropriately, all of the developers and managers that are no longer here to defend themselves, users that have the gall to use the system and expect results, the liberal media and right-wing bible-thumping fascists. Oh, and Apple. Steve Jobs was a dick.
So, the ultimate lesson to learn here is that the error you see may not be the real error. You could have a ninja error - pops in, fucks shit up. poof - disappears. You could also have a Fox News error. This is an error that points to other things that are fine as the issue. Fox News errors are totally oblivious of their own culpability in the matter and will argue against all evidence to the contrary. The bible says God exists. Problem, Athiests?
And so concludes my virginal foray into blogging. I hope to continue blogging but let's be realistic. All of that girl on girl action is not going to watch itself, now is it. We all have our priorities.
I had an interesting ulcer inducing morning this morning. I received a request for a report that was timing out in the Admin, which is Standard Operating Procedure for us because we report off of the transaction system because why would you want to do things the right way. That is so boring and it has totally been done before.
Anyway, I get the request and it is for data from the old system that was cobbled together by steam punk artists and Katrina survivors. The queries for the reports are built in the java code, are pulled together by loading temp tables and massaging data in the application. It is horseshit. We had a project a few months back that allows for pulling old system data from the new system so I checked and we had converted the requested report to a stored procedure. One would think this would be a fantastic thing. Turns out, not so much.
I convert the parameters to bind variables so I can just run the query and export the data. I get an Invalid Number error on the service code field...which is alpha-numeric. WTF, right? How can Oracle throw a number error on a character column with no conversion functions on it? I know! It shouldn't! Does not compute!
For those of you who don't know...a good way to figure out what is wrong with an extensive query is to strip things out by commenting column lists, joins, etc until you get to something you know works right. I begin the process by dropping some tables from the query (NOTE: I did NOT drop tables from the schema - that's just a phrase for commenting things out) until I get something that works. Problem is, I never got something that worked.
Another good method for fixing a broken query is rewriting it from scratch - adding in more columns and tables until you get the desired result or recreate the issue. Hopefully by this time we are getting zeroed in on the problem and can formulate a plan of attack to fix the goddamn thing.
I start out with a single table and the query runs as expected. I add in a few more columns and joins and stuff and everything seems to be working. I get to the last little bit and boom. No dice. It doesn't make any sense. Service Code can contain numbers but should not be restricted to numeric only. And why the sometimes issue? Not sure.
I take another tack and create a few temp tables to simulate the joins in the query. It is basically the idea that if there is a problem when you join table a, table b and table c all at once, how about create table ab and then joining that to c. It may shine light on whatever the ultimate issue is.
So, after a few hours of investigation, false starts and curses hurled at a world that would birth an abomination like this, I discover the true issue. The start and end date parameters are passed as timestamp and in the query, a to-char wraps the end date but not the start date. When you convert a parameter in a query to a bind variable (by adding a ':' in front) and are passing date or timestamp, you can write the value in the default format to short circuit conversion. In this case the varchar2 value'01-AUG-2012' will implicitly convert to timestamp when the query runs. Since we are doing an inclusive range, we are converting the end date to character and adding in the time component of ' 23:59:59' and since the incoming parameter is timestamp when the procedure runs as normal we need to convert that timestamp to varchar2 to add in the time component. There was a superfluous to_char() on end date. Oracle somehow flagged service_code as the problem column. Wild goose chases ensues. Fun is had by all.
I blame: Oracle for misleading errors, the system for not working appropriately, all of the developers and managers that are no longer here to defend themselves, users that have the gall to use the system and expect results, the liberal media and right-wing bible-thumping fascists. Oh, and Apple. Steve Jobs was a dick.
So, the ultimate lesson to learn here is that the error you see may not be the real error. You could have a ninja error - pops in, fucks shit up. poof - disappears. You could also have a Fox News error. This is an error that points to other things that are fine as the issue. Fox News errors are totally oblivious of their own culpability in the matter and will argue against all evidence to the contrary. The bible says God exists. Problem, Athiests?
And so concludes my virginal foray into blogging. I hope to continue blogging but let's be realistic. All of that girl on girl action is not going to watch itself, now is it. We all have our priorities.
Subscribe to:
Comments (Atom)