Obsessed with Oracle PL/SQL

As my 25th winter in Chicago approaches (and after the 2016 elections), I attempt to cheer myself up with jokes. Programmer jokes. Jokes that largely have to do with being too lazy to verify assumptions or prove claims before making decisions that turn out to be really bad decision.  Here's my second joke for your enjoyment. And just in case you have any doubts, this is a true story. As in not "based on a true story." True. All the way through. After I left Oracle Corporation back in 1992, I spent a few years consulting before I wrote the first edition of Oracle PL/SQL Programming (and my life changed big time). For about six months, I commuted to downtown Chicago to work at an insurance company. I was, as you might expect, part of the development team, writing PL/SQL packages and building user interfaces in Oracle's wonderful SQL*Forms 3. This particular insurance company had been acquired by another, even larger insurance company. And it tur...

As my 25th winter in Chicago approaches, I attempt to cheer myself up with jokes. Programmer jokes. Jokes that largely have to do with being too lazy to verify assumptions or prove claims before making decisions that turn out to be really bad decision. Here's my first joke for your enjoyment. No Packages for You! Packages should be the foundation, the building blocks of any PL/SQL-based applications. I believe that you should not create schema-level procedures and functions, but instead define them as subprograms in one or more packages. So you can imagine it came as quite a shock to me when an attendee at one my of trainings came up over the break and said to me: My DBAs won't let me put my code in packages. Everything has to be defined as a procedure or function at the schema level. They tell me that packages take up too much memory and so they cannot be used. Wow! Packages take up too much memory.... Who would've thought it? Now, as wit...

As my 25th winter in Chicago approaches (and after the 2016 elections),, I attempt to cheer myself up with jokes. Programmer jokes. Jokes that largely have to do with being too lazy to verify assumptions or prove claims before making decisions that turn out to be really bad decision.  Here's the most recent "joke" I heard from a developer, via an email back in July: We just encountered a PL/SQL performance problem after migrating a database from 10g to 11g and I tought it might interest you. I isolated the problem using    the 10046 traces and DBMS_PROFILER. So I was able to reproduce the problem with a very simple PL/SQL testcase, but I cannot explain it. He then pasted in 439 lines of code and output. So this is the "very simple" testcase? It might be, but still I wrote back: "It may be simple, but it's long and I'd appreciate it if you would summarize for me what you believe you have discovered." This way, at least, I ...

On the PL/SQL Challenge , all times are shown in the UTC timezone. Weekly quizzes end on Friday, midnight UTC. So I recently decided that when I display the time that the quiz starts and ends, I should add the string "UTC". Our quiz website is built in Oracle Application Express 5.0 , so I opened up the process that gets the date and found this: DECLARE l_play_date DATE := qdb_quiz_mgr.date_for_question_usage (:p46_question_id); BEGIN :p46_scheduled_to_play_on := TO_CHAR (l_play_date, 'YYYY-MM-DD HH24:MI'); "OK, then," says Steven the Fantastic Developer to himself. "I know exactly what to do." And I did it: DECLARE l_play_date DATE := qdb_quiz_mgr.date_for_question_usage (:p46_question_id); BEGIN :p46_scheduled_to_play_on := TO_CHAR (l_play_date, 'YYYY-MM-DD HH24:MI') || ' UTC'; Ah, PL/SQL and APEX - so easy to use! :-) Now, there are lots of things you could say about the ...

With programmers new to PL/SQL (and SQL), it is not uncommon to find that they overcomplicate things, writing more code than is necessary, and putting too much logic into PL/SQL.  That problem can then be compounded by accidentally getting the "right answer" based on inadequate testing and test data. So it is always good to be reminded:  Do as much work as you can in SQL, and then finish up in PL/SQL.  Where "finish up" means do whatever is appropriate within the database, and then make that available to whatever language is being used to write the UI! A recent quiz on the PL/SQL Challenge  explored this topic, and I offer it below as a learning exercise via blog post. Suppose I have a table and dataset as follows: CREATE TABLE plch_animals ( animal_id INTEGER PRIMARY KEY, animal_name VARCHAR2 (100) ) / BEGIN INSERT INTO plch_animals (animal_id, animal_name) VALUES (1, 'Bonobo'); INSERT INTO plch_animals (anima...

DBMS_OUTPUT. PUT_LINE is the built-in procedure that PL/SQL developers use to display output on the screen. Let's watch it do it's thing on LiveSQL : So I displayed a string, a date, a date converted to a string, and a number. Cool.  Now let's display a Boolean value (TRUE, FALSE or NULL): Ouch! It did not like a Boolean value, that's for sure. But why not? To figure that out, we need to take a look at the specification of the DBMS_OUTPUT package. That's easy in SQL Developer : just right-click and choose Popup Describe.  Searching for "procedure put_line", I see: create or replace package dbms_output authid definer as ... procedure put_line(a varchar2); Huh. That's weird. It only accepts a string. So how could it display a number and a date with no problem, but then choke on a Boolean? It all has to do with implicit conversions . Generally, Oracle Database in both SQL and PL/SQL will automatically and implic...