Obsessed with Oracle PL/SQL

This post was inspired by a Twitter conversation doing the Twitter version of shaking heads over the kind of code developers write with Booleans. Keep it simple and native and intuitive: Booleans are TRUE or FALSE (ok, also maybe NULL). So you don't have to write code like "IF my_boolean = TRUE". Suppose that I needed to implement a function IS_A_FACT so that I can compile and run the following block: BEGIN IF is_a_fact ('Steven says: The sun revolves around the earth.') THEN DBMS_OUTPUT.put_line ('Fact!'); ELSE DBMS_OUTPUT.put_line ('Opinion!'); END IF; END; Here are four different ways of getting the job done. They all work. Which would you prefer? 1. Lay it all out there, Steven FUNCTION is_a_fact (statement_in IN VARCHAR2) RETURN BOOLEAN AUTHID DEFINER IS l_is_a_fact BOOLEAN; BEGIN IF statement_in LIKE 'Steven says:%' THEN l_is_a_fact := TRUE; ELSE l_is_a_fact := FALSE;

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

It can be incredibly painful to make changes to an existing program that has the markings of spaghetti code . And isn't it positively terrifying to make those changes when you don't have a regression test for the program that you can run afterward to ensure that no bugs were inadvertently introduced? Yet that is what we are often called upon to do. When faced with this situation, the smart thing to do is to make the smallest, most isolated change possible, thereby minimizing the ripple effect. Suppose the code you have to modify looks like this: PROCEDURE someone_elses_mess /* || Author: Long-Gone Consultant || Maintained by: Terrified Employee */ IS BEGIN ... lots and lots of convoluted code FOR index IN 1 .. my_collection.COUNT LOOP ... hard-to-understand logic here ... more of the same here END LOOP; END; and you need to add some code between "hard-to-understand logic here" and "more of the same here." If a ce