Exception Handling and Future Posts

I'm not sure how, but I managed to forget that I had started this blog. It's possible that this is because LaTeX has not been working correctly on my preview - it's quite annoying to not be able to double check your work. Anyways, I thought, for the sake of keeping this blog alive, I'd post where I plan to go from here and a bit about a mishap at work.

*NOTE* Actually, as I was typing this, I realized that none of my JavaScript is working in preview. Not sure if this is my fault or blogger is just not working correctly...

For now, those posts on calculus are being put on hold indefinitely. I did review some of the material; however, it's a lot to type up and I'm not sure if that's the direction I want to continue going with this blog. I'll try to post anywhere from one to two times a month and, for now, the topics will be random (not in series like the calculus posts attempted to be).

That being said, the I'll be sure to try to be more active from now on.

Moving on to my SQL troubles.

Now, I know better than what I did here but, I guess we all make mistakes...I guess.

At work, I primarily work in Oracle. When I'm not working in Java or some scripting language, it's stored procedures and triggers till I can no longer type. Recently, I had to make a simple update - to catch a certain value as it passed through our system and update some logs and data based on the value. For those who are not familiar, there is an Oracle function called, "NVL". Usage is as follows, "nvl([valueToCheck], [valueToReplaceWith])", this means that when this function is called, if the 'valueToCheck' is null, then return the 'valueToReplaceWith'.  For example:

set serveroutput on size unlimited;
declare
  some_string varchar2(64);
  test varchar2(64);
begin
  some_string := null; -- null value for illustrative purposes
  select nvl(some_string, 'better value') into test from dual;
  dbms_output.put_line(test);
end;
/
show err

This little PL/SQL block should output "better value" because 'some_string' is null.

Moving on, I built a new procedure and came up with the flawed reasoning that if the table doesn't have a row for a value it will return a null value on a select query. With this reasoning, I figured, "exceptions will just clutter the code, I'll just use "NVL" and check the value one time," which lead me to code like this:

-- ...other parts of the procedure
-- bad idea follows:
  select nvl(value_i_need, '?') into value_to_check 
  from some_table 
  where condition = 'condition_to_be_met';
  if value_to_check != '?' then
     -- do stuff
  else
     -- do other stuff
  end if;
-- ...more procedure logic...but logic that worked... 
end;
/
show err

Looking back, I realize that there are other flaws with this but, I'm just gonna call it a bad day. I proceded to push out this code to our working set and, only hours later, everything is broken. It was at this point I realized that my "select into" was causing a no data found exception because no rows existed for some of the conditions. Again, looking back there were plenty of better ways to handle this but, more or less, the moral of this story is, even if you don't think you need it, you always need exception handling. Just doing this alone, would have saved the errors that followed:

-- ...other parts of the procedure
-- begin end for exceptions...
  begin
    select nvl(value_i_need, '?') into value_to_check 
    from some_table 
    where condition = 'condition_to_be_met';
    if value_to_check != '?' then
       -- do stuff
    else
       -- do other stuff
    end if;
  exception when no_data_found then
    value_to_check := '?';
    -- problems solved...
  end;
-- ...more procedure logic... 
end;
/
show err

I thought this was worth writing about, if for nothing else then the fact that it will help me remember to not make that mistake again. Oddly enough, I never forget exceptions in Java, possibly because I'm more comfortable coding in it. Anyways, maybe sometime I'll look into the time complexities of SQL fetch queries depending on joins and things like that; with all the time I spend in Oracle, I've been curious lately.

Any questions, comments and/or suggestions are appreciated.