Wednesday, March 25, 2015

Code cleanup: post-authentication in Application Express

Recently ran across this procedure, defined in the Post Authentication field in an Appliation Expression application. I encourage you to look it over and come up with your list of things you'd want to change about it. I leave a whole lot of white space below the code block so you can view the end before seeing what I have to say about it.

1:  procedure post_auth_7048783549465082709 is  
2:  begin  
5:  for c1 in (  
6:    select count(*) cnt  
7:     from app_users  
8:    where username = :APP_USER  
9:     and role_name = 'ADMINISTRATOR' )  
10:  loop  
11:    if c1.cnt > 0 then  
12:     :IS_ADMIN := 'TRUE';  
13:    else  
14:     :IS_ADMIN := 'FALSE';  
15:    end if;  
16:  end loop;  
18:  for c1 in (  
19:    select count(*) cnt  
20:     from app_users  
21:    where username = :APP_USER  
22:     and role_name = 'CONTRIBUTOR' )  
23:  loop  
24:    if c1.cnt > 0 then  
25:     :IS_CONTRIBUTOR := 'TRUE';  
26:    else  
27:     :IS_CONTRIBUTOR := 'FALSE';  
28:    end if;  
29:  end loop;  
31:  for c1 in (  
32:    select count(*) cnt  
33:     from app_users  
34:    where username = :APP_USER  
35:     and role_name = 'TESTER' )  
36:  loop  
37:    if c1.cnt > 0 then  
38:     :IS_TESTER := 'TRUE';  
39:    else  
40:     :IS_TESTER := 'FALSE';  
41:    end if;  
42:  end loop;  
44:  for c1 in (  
45:    select count(*) cnt  
46:     from app_users  
47:    where username = :APP_USER  
48:     and role_name = 'REPORTING' )  
49:  loop  
50:    if c1.cnt > 0 then  
51:     :IS_REPORTING := 'TRUE';  
52:    else  
53:     :IS_REPORTING := 'FALSE';  
54:    end if;  
55:  end loop;  
57:  for c1 in (  
58:    select substr(lower(:APP_USER), instr(:APP_USER, '@')+1) email  
59:     from dual  
60:  )  
61:  loop  
62:    if = '' then  
63:     :is_employee := 'TRUE';  
64:    else  
65:     :is_employee := 'FALSE';  
66:    end if;  
67:  end loop;  
69:  for c1 in (  
70:    select count(*) cnt  
71:     from app_users  
72:    where username = :APP_USER  
73:     and role_name = 'SUPER ADMINISTRATOR' )  
74:  loop  
75:    if c1.cnt > 0 then  
76:     :IS_SUPERADMIN := 'TRUE';  
77:    else  
78:     :IS_SUPERADMIN := 'FALSE';  
79:    end if;  
80:  end loop;  
82:  end;  

Don't look down below yet.

What do you think is wrong?

The more capable you are at analyzing this (and your own) code all by your lonesome, the more valuable you will be to your dev teammates and employer.

Right then. Let's take a look at how we might clean this up. Here are my concerns:
  1. Poor formatting: Even if you don't like my style of upper-casing all keywords, the lack of indentation is a significant impediment to reading code.
  2. Use of cursor FOR loop with a single row query (SELECT COUNT(*)): Cursor FOR loops are for fetching multiple rows of data. If you know for sure that there is just one row, using a cursor FOR loop just means you are being lazy, and not in a good way (you don't have to declare a record or variable to accept the fetched value).
  3. Use of COUNT(*) to determine if there is at least one row: COUNT(*) answers the question "How many have I got?" when the question being asked here is "Is there at least one row?".  What if there are 10,000,000 rows? The logic works, but you could spend a few extra CPU cycles getting to the answer. And, as with the use of the cursor FOR loop, it's misleading and confusing.
  4. Repetitive code: If you need the same functionality again and again, don't copy and paste. Ugh. Instead, apply your relational database normalization skills to your code: create a nested subprogram and call that multiple times.
  5. SELECT FROM dual: No, please, no! If you are running 11.1 or higher, there is almost no reason left to use a SELECT FROM dual instead of a native PL/SQL assignment. Certainly this is not necessary for an expression involving SUBSTR.
  6. Too much code inside Application Express: whenever possible move your PL/SQL code to packages compiled into the database. It is easier to manage from there.
Did I miss anything? Let me know!

Here's my first pass on a cleanup of this procedure:

PROCEDURE post_auth_7048783549465082709
   PROCEDURE set_value (role_in IN VARCHAR2, item_out OUT VARCHAR2)
      FUNCTION role_exists (role_in IN VARCHAR2)
         SELECT 'x'
           INTO l_dummy
           FROM app_users
          WHERE username = :app_user AND role_name = role_in;

         RETURN TRUE;
            RETURN FALSE;
      item_out := CASE WHEN role_exists (role_in) THEN 'TRUE' ELSE 'FALSE' END;
   set_value ('ADMINISTRATOR', :is_admin);
   set_value ('CONTRIBUTOR', :is_contributor);
   set_value ('TESTER', :is_tester);
   set_value ('REPORTING', :is_reporting);
   set_value ('SUPER ADMINISTRATOR', :is_superadmin);

   :is_employee :=
         WHEN SUBSTR (LOWER ( :app_user), INSTR ( :app_user, '@') + 1) =

This rewrite covers the first 5 concerns I listed. Gone are the cursor FOR loops, the COUNT(*), the repetitive code, the SELECT FROM dual. I shift from IF statements to CASE expressions, allowing for more concise and cleaner code.

Now let's take a look at how we might tackle concern #6 ("Whenever possible move your PL/SQL code to packages compiled into the database."). The app_post_authenticate procedure is a database object. It has pretty much the same code as my first rewrite, but instead of referencing host variables directly inside the procedure, I move them into the parameter list.

CREATE OR REPLACE PROCEDURE app_post_authenticate (
   app_user_in       IN     VARCHAR2,
   admin_out            OUT VARCHAR2,
   contributor_out      OUT VARCHAR2,
   tester_out           OUT VARCHAR2,
   reporting_out        OUT VARCHAR2,
   super_admin_out      OUT VARCHAR2,
   is_employee_out      OUT VARCHAR2)
   PROCEDURE set_value (role_in IN VARCHAR2, item_out OUT VARCHAR2)
      FUNCTION role_exists (role_in IN VARCHAR2)
         SELECT 'x'
           INTO l_dummy
           FROM app_users
          WHERE username = app_user_in AND role_name = role_in;

         RETURN TRUE;
            RETURN FALSE;
      item_out := CASE WHEN role_exists (role_in) THEN 'TRUE' ELSE 'FALSE' END;

   set_value ('ADMINISTRATOR', admin_out);
   set_value ('CONTRIBUTOR', contributor_out);
   set_value ('TESTER', tester_out);
   set_value ('REPORTING', reporting_out);
   set_value ('SUPER ADMINISTRATOR', superadmin_out);

   is_employee_out :=
         WHEN SUBSTR (LOWER (app_user_in), INSTR (app_user_in, '@') + 1) =

And then in Application Express, I have only this left:

PROCEDURE post_auth_7048783549465082709
   app_post_authenticate ( :app_user,

Sure, you could argue that by moving all the code to the database, you no longer can see what is going on in your application.

I would counter with: how often do you need to see this logic? Probably rarely. And most of the logic involved is already in the database: the app_users table, the literal values needed to "match up" with those rows in the table, etc. In addition, besides setting values of application items, there is no UI-specific logic in the procedure. That's another reason to move the code out of the application.

Monday, March 23, 2015

Don't Want to Remember No Stinking Error Codes

Programmers need to keep lots of information in their heads: about the language(s) with which they are writing code, the data model of their application, requirements, etc. So they tend to not remember (and even forget rather quickly) information they are pretty sure is not in their critical path.

I was reminded of this very forcefully at a two day course I gave in the Netherlands a few years ago. On the first day, I talked about FORALL and SAVE EXCEPTIONS, and how Oracle would raise ORA-24381 if at least one statement failed in the FORALL's execution.

Then at the end of the second day, when I discussed maintainability, I again talked about ORA-24381 (for reasons that will become clear below). And then, then it was time for the end-of-course ten-part quiz, with reputation and prizes on the line.

Lo and behold, when the dust settled, we had a tie for first place. So then it was time for a sudden death playoff. Whoever gave me the right answer first, wins.

I showed "-24381" on the screen and asked: "With what feature of PL/SQL is this number associated?"

Neither of the expert finalists had an answer and, amazingly, no one else in the class of 40 did, either.

It was very clear to me at that point that these developers....

Don't Want to Remember No Stinking Error Codes

OK, fine. It's a lot better to remember and work with meaningful names, than obscure numbers, right? As in:

   DBMS_OUTPUT.PUT_LINE (l_numbers (1));
         'Tried to read an undefined index value!');

And that's why Oracle gives names in the default STANDARD package to a whole bunch of the most commonly-occurring exceptions, such as NO_DATA_FOUND and VALUE_ERROR.

Sadly, no name was supplied for ORA-24381, so I guess that means we need to write code like this:

      IF SQLCODE = -24381
         /* Traverse SQL%BULK_EXCEPTIONS for DML errors */
      END IF;

Really? Is that really what we have to do? I sure hope not, because this is (confession: slight exaggeration coming) horrifying, nervous breakdown-inducing code.

OK, maybe it's not that bad. But think about it: you write code today and it goes into production tomorrow. You move on to another project, and the code lives on. Five years later, a bug surfaces in that program, and someone who has never looked at your code opens it up and sees:

IF SQLCODE = -24381

and you know how it makes them feel? Ignorant, stupid, diminished. Afraid to touch your code, because you are/were obviously much smarter (or at least more expert with PL/SQL) than they. You obviously knew all about those weird error codes and they have no clue.

Now, if one of your objectives in writing code is trying to make other people feel bad and thereby pump yourself up, it's not that hard to do. Just write code that is hard to understand, maintain and debug. Leave out comments, hard-code magic values left and right. Then express astonishment when someone has trouble understanding what you wrote.

There are many words for programmers like this. One of them likely starts with an "a".

But if that's not the kind of programmer you want to be, if you'd rather be the sort who goes out of their way to make people feel welcome in their code, in which the intention and the algorithm are evident, and these "next generation" developers can make changes with some degree of confidence, then do like Oracle does:

Reference those obscure error codes by name.

But, wait, you are thinking: "Steven just said that Oracle did not define a name for ORA-24381."

Yes, that is so. But nothing is stopping you from doing that yourself!

Create a package to hold all of your named exceptions, and then do the same thing that you will find in STANDARD: use the EXCEPTION_INIT pragma to associate a named exception with a number:

   forall_failure EXCEPTION;
   PRAGMA EXCEPTION_INIT (forall_failure, -24381);

You can then reference those obscure error codes by name.

   WHEN my_errors.forall_failure
      /* Traverse SQL%BULK_EXCEPTIONS for DML errors */

No more hard-coding of error codes, no more expecting others to have the same relatively random, highly specialized knowledge you acquired.

In short, no showing off.

Wednesday, March 11, 2015

Recommendations for unit testing PL/SQL programs

I have recently received a couple of requests for recommendations regarding unit testing of PL/SQL programs. I thought I would share with you what I told them.

First, some background: unit testing refers to the process of testing individual subprograms for correctness, as opposed to overall application testing (which, these days, almost always means visiting a website).

The basic idea behind unit testing is that if you verify that each individual subprogram works correctly, then you are much less likely to have bugs in higher-level programs that call those tested subprograms. And when you do, you know you can focus on the way the tested subprograms are used, and not the subprograms themselves.

The most important application of a unit test is to participate in a regression test, which can be run to verify one's code works today as well as it did yesterday. That will greatly reduce the chance of you upgrading the application and users complaining that a bunch of features that worked in V1.2 no longer work in the fancy, new and improved V1.3.

That's always embarrassing, right?

When developers talk about unit testing, they are usually not just talking about writing and running a test. They are talking about finding and using a framework that automates as much of the process as possible. Automation is key because testing always takes a lot of time, and manual testing takes the most time of all.

And most dev managers are not going to give you lots of time for testing. That's just the way it is.

Here are the main steps that could be automated:
  • Define the test cases: what are the various scenarios that need to be verified? These are driven by user requirements.
  • Build the code that implements the test cases.
  • Set up the data so that the test code will run properly.
  • Run the tests.
  • Record and examine the results.
There are a number of different tools available for automated or semi-automated unit testing of PL/SQL code. They include:
  1. utPLSQL: "Junit for PL/SQL", an open source framework that I originally built in 1999 and is now managed by Paul Walker.
  2. SQL Developer Unit Testing: Oracle's free PL/SQL IDE offers integrated unit testing.
  3. Dell Code Tester for Oracle: a commercial unit testing tool that I originally designed and helped to build, sold as part of the Toad Development Suite for Oracle.
and then a bunch of others, including DBFit, PL/unit, PLUTO and ruby-plsql, to name a few. I am not going to pretend that I have any great familiarity with this list of "others." I encourage you to check them out; I am going to focus on the Big Three. [Note: PL/SQL Developer, another popular PL/SQL IDE has some very basic unit testing features, but not enough to warrant review in this post.]

And even there, I am not going to offer a detailed comparison of features. You will need to do that yourself, based on your requirements. Instead, I will take a step back and offer some high level guidance to get you started on your journey.


First, it is important to correct a misunderstanding that some people have, and the creator of ruby-plsql repeats in his blog: "Steven stopped developing further utPLSQL and currently there are no other active maintainers of this project." Raimonds is absolutely correct that I stopped working on utPLSQL years ago, and for a while it did languish. 

Last year, however, Paul Walker stepped up to the plate and took over managing the project. He has been applying bug fixes, improving documentation, and encouraging discussion and collaboration.

Since 2006, utPLSQL has been downloaded over 76,000 times. Of course, there's no way of knowing how many dev teams use it, but I think it is safe to say there are hundreds of groups around the world actively relying on utPLSQL.

utPLSQL is free and gives you total control over your test code. But that's mostly because you have to write all the test code (SQL Developer and Code Tester generate test code for you). And then you have to maintain all that test code. But once you've written your tests, built around the utPLSQL API, you can execute your tests with a single command, and utPLSQL will tell you whether or not your code passed (with unit testing, passing comes with 100% correctness).

utPLSQL test scripts can be integrated into continuous integration scripting.

Code Tester for Oracle

I think that it is safe to say, even given my perhaps doubly-subjective perspective of (a) being an original designer of Code Tester and (b) now working for "the competition" - Oracle, that Code Tester is the most sophisticated automated testing tool for PL/SQL. It's packed full of features, handles lots of complex scenarios (for example, if your parameter is a collection of records and the record contains a collection of objects, you can right inside the UI provide values for that parameter), generates test code from your specifications, and offers limited integration with Toad.

That sophistication comes, however, at a steep price, namely $2854 for the Toad Development Suite for Oracle. You can't buy Code Tester by itself.

So here's my recommendation: if you are licensed to use the Toad Development Suite for Oracle (even if you only use Toad, you may have a license to TDSO via a site license, and not even know it), you owe it to yourself and your team to check out Code Tester. 

You will likely run into some frustrations and limitations, but it does an awful lot of the heavy lifting for you.

Code Tester tests can be integrated into continuous integration scripting via a PL/SQL API.

SQL Developer Unit Testing

Last and definitely not least, is the unit testing feature integrated directly into SQL Developer. Let's stop right there to make sure you appreciate what I just said:

The automated unit testing feature of SQL Developer is not a separate tool from the editor (which is the case with Code Tester - it is external to Toad, with only minimal integration points). It is a part of SQL Developer. This is, I believe a crucial point, because developers do not like to leave their editors.  Instead, they want all necessary functionality brought to them inside their editors.

Here is one screenshot from the doc to give you a sense of the integration:

In many ways, SQL Developer Unit Testing and Code Tester for Oracle are similar. They both aim to take the burden off of you for writing test code. Instead, you describe the test case: What are the input values? What data needs to be set up? Most importantly, what are the expected outcomes?

SQL Developer then generates code that implements your test case. If you need to change the test, you don't have to modify test code (as you would do with utPLSQL), you go into the UI, make a change, and run your test again.

SQL Dev unit tests can also be integrated into continuous integration scripting.

So here's my recommendation: 

If you are already using SQL Developer, the decision should be clear. Try the integrated unit testing features. See if it meets your needs (at least enough to get you going, building some tests, experiencing the positive feedback loop). 

If you are not a SQL Developer user, let's say you use Toad or PL/SQL Developer, and you are not likely to get access to Code Tester for Oracle, then I also suggest you install SQL Developer and try its unit testing features. Sure, it would be better to just use one IDE, but if the automated testing of SQL Dev works for you, given its infinitely low cost (as in, free), I would think you could justify having that second IDE around solely for testing.

Overall Conclusions

If you have access to an IDE that offers automated unit testing, including the generation of test code from test case specifications, try that first. The productivity gains of test code generation are so massive, it can easily be worth accepting some limitations.

If you are allergic to UI-based testing and/or worry about being constrained by an IDE's feature set (in other words, you are a command line fanatic), utPLSQL offers a powerful alternative. It's proven to be useful to hundreds and maybe thousands of dev teams over the years. It is an actively maintained and enhanced open source project.

Your Experience?

Do you use an automated testing framework for PL/SQL? Or have you built your own? Please tell us about your own experience.

Tuesday, March 10, 2015

27 Hours of Free PL/SQL Video Training at PL/SQL Channel

[June 2016 update: PL/SQL Channel videos have been moved to the new Practically Perfect PL/SQL channel on YouTube. The PL/SQL Channel has been disabled. Scripts referenced in the videos can be found at the PL/SQL Learning Library. Click on the download. Or check out all my LiveSQL scripts.]

A few years ago, I recorded roughly twenty-seven (27) hours of training on the Oracle PL/SQL language and made them available via subscription on the PL/SQL Channel.

Just a little under a year ago, I was re-hired by Oracle, and at the same time, Oracle purchased the PL/SQL Channel.

So I am very happy to announce that all training videos at the PL/SQL Channel are now available without subscription.

While these videos do not offer comprehensive coverage of all PL/SQL topics, as  you can see below, I certainly went into depth in a number of areas, notably PL/SQL collections:

I have moved these videos over to my new Practically Perfect PL/SQL channel.

Wednesday, March 4, 2015

Looking for Another Oracle Developer Advocate for SQL

The Oracle Developer Advocates team is responsible for helping users fully leverage their investment in Oracle Database and to promote the use of Oracle Database technologies for application developers, including SQL, PL/SQL, Application Express, Oracle REST Data Services, and more.

Each Oracle Developer Advocate (ODA) is responsible for the creation of high quality and highly entertaining resources for training and education; channeling user requirements back to Product Management and Product Development; building a vibrant, engaged global user community. 

The main focus for this member of the ODA team is, however, the SQL language.

To apply: Visit search for req 150009IK

The ideal ODA candidate:
  • Is proficient in Oracle SQL and has kept up with the latest and greatest features
  • Can explain things  (in particular, the relational model, set theory, etc.) in ways people understand and by which they are inspired
  • Enjoys performing in front of a crowd – and a camera (heads up: you will be auditioning for the job!)
  • Is easy to get along with; her or his ego fits through the doorway
Each Oracle Developer Advocate will:
  • Hold monthly webinars in their technology focus area
  • Write and publish through the PL/SQL Challenge a weekly quiz on their technology
  • Publish daily tips through Twitter and other social media outlets
  • Attend key User Group conferences, to present, listen and learn
  • Work closely with Product Management and Product Development to both deepen product understanding and more effectively communicate to users how to use those products.
Location and Travel

The ODA team is distributed geographically; you do not need to work out of Oracle Headquarters to do this job.

You should expect 25% travel, though the amount of travel will be largely up to you. The focus of our team is on building global communities, and this will be done mostly through the Internet, as opposed to via jet planes.

The ODA team is going to help Oracle Database developers utilize that language more fully, and to make it easier for all the experts "out there" (outside of Oracle) to contribute their knowledge to the global community. And along the way, we will utilize the latest multimedia and education technologies to create engaging, entertaining resources that will change the way our core application development technologies for Oracle Database are perceived and used.

If you've been around the Oracle technology community for a while and are looking for a way to contribute more, to do more, to have a greater impact, then consider the ODA position. If you want to help ensure that SQL is appreciated, leveraged and flourishes "in the wild", if you like to help others do what you have learned to do, then apply for this position.

Are You Really Excited About This Job?

I would be (I am). It's a "plum" position - you get paid to play and explore and share what you learn. But you must be an excellent and effective communicator. This means you will be auditioning for this job. 

So if you think you are a good fit and want to get a jumpstart on the process, by all means apply at the link above, but also feel free to record a video explaining why SQL is such an amazing and powerful language and why I should be excited and inspired to learn it. Send me a link and I will happily take time away from writing yet another PL/SQL quiz to watch you.

Note: when you check out the job requisition, ignore everything from this on down:

"Lead a team that acts as the central resource and driving force for the design, process, manufacturing, test, quality and marketing of product(s) as they move from conception to distribution. Organize interdepartmental activities ensuring completion of the project/product on schedule and within budget...."

Oh those tricky exception sections! Why won't it handle my error?

Fielded a question the other day from a frustrated PL/SQL developer. He included an exception handler for an exception that was being raised in his program, but the exception propagated out unhandled from the block anyway. What's with that?

To answer that question, why not test your own knowledge of PL/SQL: what will you see on the screen when you execute the following block (with server output enabled):

[Note, sure you could copy and paste the code and run it, but I urge you, I implore you, to just read the code and see if you can sort it out yourself. Reading code - verifying things logically - is a critical skill for any developer to, um, develop.]

   aname VARCHAR2(50);
      aname VARCHAR2(5) := 'Big String';
      DBMS_OUTPUT.PUT_LINE (aname);

         DBMS_OUTPUT.PUT_LINE ('Inner block');
   DBMS_OUTPUT.put_line (SQLCODE);
   DBMS_OUTPUT.PUT_LINE ('What error?');
      DBMS_OUTPUT.PUT_LINE ('Outer block');

I present this little quiz in my classes on error management and the answers usually cover the logical range:

"Big S"
"Inner block"
"Inner block then 0 then What error?"

and so on. Well, the answer is:

Outer block

and nothing else. Why? Because in the world of PL/SQL, the exception section can only possibly handle an exception raised in the executable section. When it's raised in the declaration section, the exception always propagates out unhanded.

In this block of code, I try to stuff "Big String" into the nested a name variable - and it's just too long. PL/SQL does not assign the first five characters. Instead it raises the VALUE_ERROR exception (ORA-06502). Since this happened in the declaration section, that inner block's exception section never comes into play. 

The exception propagates out unhandled, shutting down the outer block's executable section, and control is transferred to the exception section, where a match is found and "Outer block" is displayed.

Tuesday, March 3, 2015

Mining the Oracle Database for Cool Appdev Features

One of the key missions of the Oracle Developer Advocate (ODA) is to make sure our users realize how many great features for application developers are packed into Oracle Database.

Sure, there's SQL and PL/SQL - the "big" appdev features. But there are also many other, "smaller" features.

For example, Continuous Query Notification:
Continuous Query Notification (CQN) lets an application register queries with the database for either object change notification (the default) or query result change notification. . . . If a query is registered for query result change notification (QRCN), the database notifies the application whenever a transaction changes the result of the query and commits.
That's very cool, all by itself.

What's even more cool is that Dan McGhan on published a post exploring how to use CQN to implement real-time data delivery to JavaScript applications.

This is exactly the kind of research and resource I hoped the ODA team would do and produce, so kudos to Dan for helping make my vision a reality.

I look forward to lots more of the same from all members of the team.