Tuesday, February 24, 2015

Reflections from RMOUG Training Days 2015 (part 1) - enough with the shoulds, already!?

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 DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, no more than 10 hands were raised. Now, it is certainly possible that people just don’t like to raise their hands, but it seems more likely that this is just one more feature (added in 10.2) among many that developers have not integrated into their standard toolkit.

And the list of valuable but largely neglected features goes on and on….
  • Compile time warnings
  • Conditional compilation
  • PL/Scope
  • Collections, especially nested tables 
  • MULTISET operators
  • Profilers (DBMS_HPROF and DBMS_PROFILER)
  • ...
And then there is the issue of best practices. Tom Kyte, Bryn Llewellyn and I have whined (that’s how I tend see it now, explained more below) for decades (!) about how developers should do this or do that, as in:
  • Don’t swallow up errors with WHEN OTHERS THEN NULL
  • Hide your SQL statements behind a PL/SQL API
  • Unit test your code
  • Avoid row by row processing
  • ….
And yet (again, from a very unscientific feeling about your world, based on admittedly lmiited datasets), many developers just keep on doing the same old thing, unable to jump out of their well-worn ruts.

Why? What’s wrong with these developers?

Wait a minute - did I really say that? Do I really think there is something “wrong” with the hundreds of thousands of developers who don’t follow best practices, who don’t utilize non-basic features, old and new?

No, I do not. In fact, I think there is something wrong with me, with Tom, with Bryn (all right, fine, I won’t make any claims about Tom and Bryn. There’s something wrong with me). We keep expecting all of you to change the way you do your work. We expect that you, in other words, will change to meet our construction of the ideal PL/SQL progammer.

And if we lived in an ideal world, this expectation might not be quite so futile.

But in the real world, you face enormous pressure to produce code quickly. Many dev managers are not willing to or cannot give you the time needed to learn about new features, play around with them, integrate them into your daily workflow.

In this world, our constant refrain of “Do this, do that, c’mon people!” might not really be doing all that much good, and could even generate some bad feelings that make it hard for developers with the best of intentions to change their habits.

So after many years of singing the same tune, I find myself thinking: we (the experts, the gurus, the fonts of wisdom) need to take a different approach.

Perhaps it is time to accept that most people simply will not change. Rather than lecturing people (whatever the form of the lecture: book, blog, video, etc.), maybe we should look at ways to make it much easier to use these features or be reminded of them.

And I don’t mean “just” doing that in our products, like SQL Developer and PL/SQL itself. I mean what can be offered to users outside of the product path (which, as you well know, means a long, slow path - in terms of development, testing, release and then uptake)? And what could be worked on not only or primarily by Oracle employees, but by expert users “out there”?

I have one idea, which I will publish in a separate post. But I would love to hear if any of you have ideas along these lines or if you think I am off base in my conclusions.

Friday, February 20, 2015

Is the Function Result Cache operative on my database?

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? */

   RETURN VARCHAR2 result_cache
   dbms_output.put_line ('Ran FRC_TEST');

   RETURN 'Return Value';

   dbms_output.put_line (frc_test());
   dbms_output.put_line (frc_test());


If you see this:

Return Value
Return Value

then the result cache feature is enabled and working.

If you see this:

Return Value
Return Value

then you know that it is not. Alternatively, if you have EXECUTE authority on the DBMS_RESULT_CACHE package, you can simply run:

   dbms_output.put_line (dbms_result_cache.status);

"ENABLED" means, well, you get the idea.

So: are you running Enterprise Edition? Find out with this query:

select * from v$version;
Beyond that, you can check for availability of specific features of the Oracle Database 11g release here. For Oracle Database 12c, check out this page.

Monday, February 16, 2015

Sample code for 6th edition of Oracle PL/SQL Programming

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.

Never open up access to code protected by ACCESSIBLE BY?

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 ACCESSIBLE BY clause could be applied to these packages as well.  For a colleague of mine, this is carrying things too far. He thinks you see no problem if newly written code accesses the packages em_central_a and em_central_b directly. If you agree to the opinion of my colleague, the cited excerpt from the article should be revised. 
Revise what I wrote? Accept that I was wrong? What is Arne thinking?

He is thinking that I am arational human being. And I deeply appreciate that. In fact, I have no (minimal, anyway) problem admitting I am wrong and fixing what I have written. I do that a lot. :-)

But I think in this case I will fight the good fight and provide a more thorough explanation.

First, Arne, regarding use of ACCESSIBLE BY with "these packages as well." I think that you must be referring to the packages containing the new subprograms that want to use em_central_a or _b? If so, yes, you can certainly expand the list of program units in ACCESSIBLE BY, which brings me to my main point:

The default position you should take regarding previously private code that was made "public" solely to help re-org your chubby packages is:

Don't let anyone/any other program access that code.

Which is what I am saying in that quote. Why? Well, because it wasn't designed for use anywhere else. It wasn't tested for use outside of the current execution path. I do think it is extremely important that the original intention of the original developer be respected until you find a good reason to change it.

Otherwise you are asking for trouble - unless that code is so transparent, so well-written and comes with an automated unit testing script that it can understood and used in a variety of ways with confidence.

Please think about this: how many packages have you written or seen from others for which this is true?

Which brings me to my final point:

Everything changes, and code reuse is critical to overall maintainability of code.

By which I mean: when you first break up the package and create pseudo-private packages for the previously private code, you should tightly restrict usage. But suppose a developer comes along, sees those inaccessible subprograms, investigates and decides: "Wow, I could really use that functionality!"

Am I suggesting you tell them to get lost? No, of course not! At that point, you:

  • do some analysis, verify that there really is a good fit;
  • sort out what would need to change in the existing restricted subprogram to be used elsewhere;
  • ensure that these changes would not affect the original use 
  • make the changes and test them
  • add the new package "user" to the ACCESSIBLE BY clause
So, sure, "no problem" - expand access to that now-possibly-usable code, but do with it intention and careful decision-making. 

Should I revise my article? After all, it's not so "black and white", is it? Well....I don't think I need to do that. After all, I don't say:


I say: "The subprograms in these packages should be invoked only by em_central."

For any and every programming feature you ever encounter, there will always be nuances. I'd rather use my articles to make developers aware of what is possible with new features, and the major motivations behind these features.

Good, clever, practical programmers will discover exceptions, interesting new ways to apply a feature, all the time. It's what we do. And my statement includes an implicit admission of that reality.

Wednesday, February 11, 2015

Practically Perfect PL/SQL - A new video channel for PL/SQL developers!

Let this post serve as the official, groundbreaking, ground-shaking launch of (drum roll).....

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. 

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. We will try to be "practically" or almost perfect.

It's also a reminder to myself and everyone else writing PL/SQL and reading/watching my stuff, that I sure am not perfect. I violate my own best practices on a regular basis, I make changes in production (oh, say it isn't so, Steven!), I take short-cuts. 

3. Celebrate the practicality of programmers. We are, fundamentally, problem solvers - problem solvers working under deadlines. 

This means that when we hit an obstacle (a bug, bad requirements, changed specifications), we:
  • Never whine.
  • Never criticize another developer, especially if they are no longer on the team.
  • Never complain about users.
That's right: Never. Not us programmers, no. 

We are too practical, too pragmatic, to waste our cycles like that. We simply figure out how to work around the obstacle and get the job done.

I hope you enjoy, and maybe even learn from, the P3 videos. 

But even if you don't, please watch every single one, multiple times, so I get my view counts up really high! 

And do not hesitate to give me feedback, directly on the video page, here on this post, or through an email to steven.feuerstein@oracle.com.

Thursday, February 5, 2015

Should I take that Oracle Forms 6.0 PL/SQL job, Steven?

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 "got away" with specializing very narrowly in PL/SQL. Normal humans don't have that luxury. I suggest that at a minimum you need a working knowledge of Java, Javascript, and regular expressions.
  • I probably don't have to tell you this, but you have to live in the world as it is, not how you want it to be.
So maybe you should take that old technology job. If you cannot afford to keep looking, that job might end up being a great platform for you to explore the newer technologies as part of planning their upgrade strategy.

In other words: take the interview and then find out what their future plans. You could end up being in the right place at the right time to take the lead in working with 12.1, APEX and more. You could become the in-house superstar and have a long, successful career right there.

And regardless of whether you take the job or hold off, continuing your search, there is absolutely no reason to put off learning about and using the latest technologies.

Download Oracle Database 12c here

Sign up for your free APEX workspace here

And then, most important of all, pick a project you'd like to work on. In other words, make it as real as possible.

Build an app to keep track of lost kittens in your neighborhood, or a simple to-do list manager or....you name it. Pick an app you really like and then replicate it. You don't have to come up with a wildly, new original idea to hone your skills.

You just need focus and motivation.

Hope this helps!

Dealing with "PLS-306: Wrong number or types of arguments" across schemas

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 PROCEDURE show_nt_count (n IN number_nt)
   DBMS_OUTPUT.put_line (n.COUNT);

GRANT EXECUTE ON show_nt_count TO schema2

GRANT EXECUTE ON number_nt TO schema2

And now I can successfully invoke show_nt_count from schema2 as follows:

CONNECT schema2/schema2

   n   schema1.number_nt := schema1.number_nt (1);
   schema1.show_nt_count (n);

But that's because I am defining a local variable based on the schema1 type.

If I try to use the "same" type that is defined in schema2, I get the PLS-00306 eorr:


   n   number_nt := number_nt (1);
   schema1.show_nt_count (n);

ORA-06550: line 4, column 1:
PLS-00306: wrong number or types of arguments in call to 'PLCH_USE_N'
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.

This problem does not always occur with datatypes defined in built-in packages. For example, DBMS_SQL comes with several pre-defined collection types. If I use one of those with my two schemas, I will not get the PLS-00306 error:

CONNECT schema1/schema1

   n IN DBMS_SQL.number_table)
   DBMS_OUTPUT.put_line (n.COUNT);

GRANT EXECUTE ON show_nt_count TO schema2

CONNECT schema2/schema2

   n   DBMS_SQL.number_table;
   n(1) := 1;
   schema1.show_nt_count (n);

Ah, but why don't I get the error? As Kim Berg Hansen points out in the comments, it is not because there is any secret and mysterious action taken by the PL/SQL compiler ("Built-in packages are special."). Instead, it is because these are two schemas in the same instance, and each instance of the Oracle RDBMS has a single copy of DBMS_SQL defined in SYS.

In other words, both schema1 and schema2 are referencing the same type in the same package. So, of course, the compiler has nothing to complain about.

If, however, you try to do the same thing via a database link, the story changes (even if the database link ends up pointing to the same instance) . If I execute that last block, with the only change being to execute the schema1.show_nt_count as a remote stored procedure:

   n   DBMS_SQL.number_table;
   n(1) := 1;
   schema1.show_nt_count@my_db_link (n);

Then I will get a PLS-00306 error, since the DBMS_SQL package referenced in my block can no longer be resolved by the compiler to be the same DBMS_SQL package in the now-remote schema1.

To get rid of this problem, I can force the resolution of DBMS_SQL in the above block to be the same as that used by the remote procedure call:

   n   DBMS_SQL.number_table@my_db_link;
   n(1) := 1;
   schema1.show_nt_count@my_db_link (n);

And then no more PLS-00306 error!

Tuesday, February 3, 2015

What's in a name? As in, my team's name.

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 good at communicating ideas in a creative, inspiring way?

1. Make sure everyone (current and prospective users) know that Oracle Database is not just for storing and retrieving bits and bites. It also is a powerful foundation for application developers, packed with features that make it easier for us to build performant, scalable, maintainable applications. Think: SQL, PL/SQL, Oracle Spatial, Oracle Text and more, even “small” features like Query Change Notification.

2. Help our users fully leverage all these features by providing more training resources, more scripts, more tools, more how-tos.

As noted above, I had been thinking of calling our team and each of us individually “evangelists.”

That’s the title I had at Quest/Dell - PL/SQL Evangelist - and it’s a fairly common title in the software world these days.

But I was never really comfortable with it. Why not?

First, it tended to put me up on a pedestal: I am better than everyone else at this, you should all learn from me. Now, certainly I have lots of experience with PL/SQL. But so do lots and lots of others, and I am certain (really: I know for a fact) that some of them know as much about PL/SQL as I and are better developers than I am.

And even if I were the absolute best at PL/SQL in the whole, wide world, one of the things I want this team to do for other (non-employee) Oracle experts is make them more visible, give them a way to share their knowledge and build their reputations. How do all of my PL/SQL friends out there fit into my "evangelism"?

The second problem with “evangelist” is that it’s all about pushing and being pushy.  By pushing, I mean that when you evangelize you try to convince others that you are right, that they should follow you. It is an outward-directed process, from the vendor (Oracle) to the user (you). But I want our team to put users first, in every possible way.

I want many more of our users to become well-known experts in their own right. I want to make it as easy as possible for you to get answers to your questions, solutions to your problems. I want to help everyone learn about and take advantage of the many advanced (and some not so advanced) features of Oracle Database that are beneficial to developers, but rarely utilized.

Bottom line: I see us more as "inside partners" for our users than as promoters of Oracle Database technology. And I think we will be excellent partners with you.

Yeah, we could probably go with Evangelist and people wouldn’t blink an eye, think twice about it.

But maybe there is a name that better fits what we want to accomplish and how we want to work.

Last week, at our first meeting together at Oracle HQ last week, we bounced around a number of different naming ideas, including:


I must admit: I came up with Guide - and I really like the idea of it, that we are helping guide users through the vast jungle of Oracle products and features, to find the solution to their problem. But it sounded too much like Scouts and Girl Guides and also did not convey the sense of being a communication channel back in to Oracle.

As we tried on different names for the team and titles for the individual members, we kept circling back around to Advocate.

Advocate our software to our users.
Advocate for user needs back to Prod Dev.

Combine that word with a realization we are focusing (at least initially) on the application developer, and we have a current favorite amongst the team of:

Team name: Oracle Developer Advocates

Individual titles:

PL/SQL Advocate
SQL Advocate
Javascript Advocate

So...what do you think?

Thanks in advance,