Obsessed with Oracle PL/SQL

I spent most of last week in Denver at RMOUG’s Training Days 2015. Some highlights and reflections follow. Love Those Two Hour Deep Dives I signed up to do a two-hour deep dive on error management features in PL/SQL. Wow, that was great. Great as in: it’s hard for me to cover much of anything in just one hour (and even more challenging at OOW, in which sessions are now 45 minutes!). Whatever my strengths, brevity is not one of them. So having two hours to explore a constrained set of PL/SQL features amde it more of, well, an exploration instead of a mad dash I hope the attendees felt that way, too. I still ran out of time, but I’d like to think that at least attendees could absorb a little bit more of what I presented. Thinking about Utilization That same deep dive got me thinking once again about the relatively low level of utilization of non-basic features of the PL/SQL. For example, of the 100 or so Oracle techies in attendance, when I asked how many were using D...

At a very fine RMOUG Training Days 2015 conference this week (about which I will post later...soon...promise!), an attendee complained that: 1. He'd learned about the function result cache and was really excited about it. 2. Spent two days trying to figure out why it wasn't working. 3. Then discovered that the feature is only enabled for Enterprise Edition . He wondered if there was a way to have determined this from the start, and saved himself a couple of frustrating days. So, first of all, if you want to quickly determine if this feature is working, run the following script: /* Does the function result cache work in my database? */ CREATE OR REPLACE FUNCTION frc_test    RETURN VARCHAR2 result_cache IS BEGIN    dbms_output.put_line ('Ran FRC_TEST');    RETURN 'Return Value'; END; / BEGIN    dbms_output.put_line (frc_test());    dbms_output.put_line (frc_test()); END; / If you see this: Ran FRC_TEST Ret...

O'Reilly Media published the 6th edition of my favorite book in the whole world: Oracle PL/SQL Programming It's my favorite because it has had more (and more positive) impact on my life than any other book I've ever written or read. Fortunately, lots of other people like it, too. To make it as useful as possible, we include lots of scripts demonstrating features of PL/SQL. You can access those scripts by going to the above link and clicking on Download Example Code or click here for the direct link.

In my Oracle Magazine article, When Packages Need to Lose Weight , I step through the process of breaking up a large package body into "sub" packages whose access is restricted through use of the new-to- Oracle Database 12c ACCESSIBLE BY feature. The idea, to sum it all up, is that once I move code from my original too-large-to-manage package body to another package, the header moves to the spec of that package. This means that formerly-private functionality is now accessible to anyone with execute authority on that package. For that reason, I stated: The body of em_central shrinks to a fraction of its former self, because the body of each procedure is simply a redirect into the em_central_a and em_central_b packages. The subprograms in these packages   should   be invoked only by em_central. A reader contacted me with this question: For me this means that newly written subprograms should not invoke the new packages em_central_a and em_central_b directly. So the AC...

Let this post serve as the official, groundbreaking, ground-shaking launch of (drum roll)..... Practically Perfect PL/SQL This is a new Youtube/Oracle Learning Library channel that will feature my latest and greatest videos on Oracle PL/SQL and more.  Within the P3 channel, I will set up a number and fill a number of different playlists.  First up: Get Rid of Hard-Coding in PL/SQL Four videos in the series are up with more to follow over the next week. So....you might be wondering: why is this channel called "Practically Perfect PL/SQL"? Let me count the ways: 1. Most important of all, triple alliteration : P3. Humans love alliteration and I like to give them what they want. 2. Sure, I want to talk about the features of PL/SQL, but mostly what I want to do is help developers write the best PL/SQL code they (we) can .   We all want to be perfect, but since that is not possible, mostly what we will do is  strive for perfection. W...

I received this note from a reader from Brazil: I am a PL/SQL developer for 6 years now. I recently received a job offer to develop in Oracle Forms 6.0, Reports and HTMLDB. I think these are older technologies. I don't know if it is a step back to work on these products, but I do know that I want to develop in Oracle Database 12c, Application Express, and other new technologies. One thing I have found is that it is more and more difficult to find a job in Brazil that only requires PL/SQL experience.   And this is what I said: No doubt about it, Oracle Forms 6.0 and HTMLDB are not the leading edge of PL/SQL-based Oracle technologies! You would be much better off working at a company that has upgraded to Oracle Database 12c, has a strong commitment to fully leveraging SQL and PL/SQL, uses Application Express for website and intranet development, etc. But keep the following in mind: There are not a whole lot of companies who have upgraded to 12.1. Don't be like me. I...

The PLS-306 compile error can be a real pain in the you know what. There are many possible causes, including typo in name with named notation, wrong datatype, wrong number of arguments....you know: just like the error message says. :-) But one of the most puzzling situations occurs when you need to execute a subprogram that is defined in another schema of your instance, or in another instance entirely, and the type of at least one parameter in the subprogram is "user-defined" (not a built-in datatype). So let's first state the rule that should guide you in this scenario, and then I will offer up  some code so that you can verify it for yourself. The rule: Even if a user-defined type in one schema has the same name and same structure as a type in another schema, they are different types. And now some code to drive the point home. Assuming you have two schemas defined, schema1 and schema2: CONNECT schema1/schema1 CREATE OR REPLACE TYPE number_nt IS ...

Over the past several months, I have put together a team of expert technologists whose job is to…well…back to that in a moment. I have been calling this team Oracle Database Evangelists . And now we are really close to launching ourselves into a flurry of activity and fun. Which has gotten us focused for the moment on what to call ourselves: both our team and each person. Do we really want to be evangelists , for example? My fine and very smart friend, That Jeff Smith, suggests that we not overthink it. I agree. But a little thought could go a long way in this case. And since I like the idea of engaging our user community in what we will be doing, I thought I would share with you our various ideas. Including our current favorite alternative to Evangelist. Hopefully you will give us some feedback. Before you can judge a name, though, you should have some idea of what we are naming. In other words: Why have I assembled a team of Oracle Database experts who are also really g...