Obsessed with Oracle PL/SQL

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 3: APEX_UTIL.RESET_AUTHORIZATIONS; 4: 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; 17: 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...

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 "-24...

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 feature...

[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 demo.zip 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 .

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 http:// bit.ly/1zSXJGb   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 Enj...

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.] DECLARE aname VARCHAR2(50); BEGIN DECLARE aname VARCHAR2(5) := 'Big String'; BEGIN DBMS_OUTPUT.PUT_LINE (aname); EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE ('Inner block'); END; DBMS_OUTPUT.put_line (SQLCODE); DBMS_OUTPUT.PUT_LINE ('What er...

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 jsao.io 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 ...