11 March 2011

SELF parameter in user-defined constructor: ambiguity in quiz? (2085)

In the 10 March quiz on user-defined constructor functions, we scored as incorrect the following statement:

"The constructor must pass SELF as one of its parameters."

Several players felt that was ambiguous. Here is a typical comment:

I wasn't sure whether this should be marked correct or not, because the documentation says "A user-defined constructor has an implicit first parameter SELF. Specifying this parameter in the declaration of a user-defined constructor is optional.". Therefore, the constructor function *will always* pass SELF as one of its parameters, whether you explicitly declare it in the code or not. In this sense, the constructor function "must" pass SELF as one of its parameters - but in another sense, the programmer doesn't actually have to declare it explicitly.

While I can definitely see these players' point, the question clearly and explicitly refers to user-defined constructors:

Which of the following statements regarding user-defined constructor functions of object types are correct?

A user-defined function is a function that a developer writes. So when I state in a choice "the constructor must pass," I feel that it is valid to consider that equivalent to writing:

"The constructor function written by the developer must pass SELF as one of its parameters."

It is not true that the parameter list of the developer-written function must include SELF explicitly.

Finally, another player wrote with the following concern:

I have a doubt about wording of the "The constructor must pass SELF as one of its parameters." choice. It seems to me that a function (and a constructor is a function) does not pass its parameters, it accepts (or awaits) them. A calling code passes parameters to the function.

I suppose my verbiage is a little bit "loose," but I believe the intention and meaning was clear.

I do not believe that any re-scoring is required for this quiz.

Back to PL/SQL Challenge version 2 coding and testing!


10 March 2011

Help us beta test PL/SQL Challenge Version 2

Dear PL/SQL Challenge Players:

We plan to release version 2 of the PL/SQL Challenge in April 2011, around the one year anniversary of the launch of the PL/SQL Challenge (8 April 2010). Version 2 involves major changes to the PL/SQL Challenge, at all levels of the application code stack (database table design, PL/SQL API, APEX code), with (as one would hope and expect) significant new features for our players.

Here's a taste of what is coming:

* Select from and play multiple competitions from the home page. You can. for example, play the daily PL/SQL quiz, then check out the monthly Toad World quiz (also a PL/SQL quiz), sponsored by Quest Software. If and when you are eligible to participate in a quarterly championship playoff, you will simply pick it off the list on the home page.

* Much more quiz-taking flexibility: in a multi-quiz competition, for example, you can move back and forth between the quizzes (a feature long requested by those participating in the quarterly championship).

* Play quizzes for different technologies. We will, to be more specific, start offering SQL and APEX quizzes, in addition to the PL/SQL quizzes (don't worry - ranking of all such quizzes are kept separate!).

* Participate in polls (just another kind of "quiz", really), so that we can gather more and better feedback from our players on how to improve the website.

* See all news and private messages through a new Messages window.

* Improvements to the appearance of the website.

* Improved feedback page, showing recently submitted issues.

I think you will be just as excited as I am about the new features of the PL/SQL Challenge. I can certainly tell you that it has been both an intensive and exhilarating refactoring project. Perhaps I will find some time over the next month or two to write about our experience in making the foundation of the PL/SQL Challenge must more generic and flexible.

In the meantime, we invite you to check out the beta version of version 2 at


We plan roughly 3 weeks of beta testing, then a week of "acceptance" testing at test.plsqlchallenge.com, then upgrade to production. And soon after that, we will hold our next quarterly championship, using the new architecture (exciting!).

You can use your regular email address and password to log in. You will see all the past quiz data available on the production site, as of 4 March. Please concentrate your testing initially on the process of taking a quiz. What do you think of the way we are presenting the competitions? Do you see any problems with taking a quiz?

Use the Feedback feature to tell us what you think....and we will soon create polls to solicit your feedback as well.

Many thanks in advance for your assistance!
Steven Feuerstein

First Virtual Private Database quiz raises questions (2084)

On 9 March, we offered the first quiz on the Virtual Private Database feature (also known as "Row Level Security"). This is a very powerful and complex feature, allowing you to enforce row-level security (filtering the rows that a user may see) in a transparent and highly secure manner.

Given the complexity of VPD, I was very glad to receive this email from a player:

"Nice quiz! I always wondered how this topic could be covered in a quiz with code that fits in a page."

But the quiz also raised questions from a number of players, including:

1. I wondered why this is a INTERMEDIATE level quiz, not ADVANCED. I feel like more players will answer this correctly 'without really knowing what is going on' than players 'knowing all VPD concepts'. I think most of the players would know very little or nothing about VPD. Anyway, this was a nice topic to cover. Thanks!

2. GRANT EXECUTE ON plch_vpd TO SCOTT is not necessary for making VPD effective.

3. Is DBMS_RLS a default package? I generated an error when running this. I am running Oracle 10g release 2 Oracle Database 10g Enterprise Edition Release - Prod PLS-00201: identifier 'DBMS_RLS' must be declared

4. I think the today quiz on RLS policy could output 2 different results depending if the schema scott has got the system privilege EXEMPT ACCESS POLICY or not. The output will be "jobs" if the schema has been granted the mentioned privilege or "NDF" if the privilege has not been granted. I read the assumption and I couldn't find anything about system privileges, so I was wondering if this is what you wanted. Or am I missing something?

5. Wouldn't it be better to say "WHEN 'HR' THEN NULL ELSE...". I cannot test it on 11g right now, but at a production DB with 10R2 we could not use MV when we had policies on multiple tables, each adding a 1=1 instead of NULL.

My responses:

1. I chose to set this to intermediate because it was a very simple example, with a "black and white" security policy. As a result, a minimal knowledge of VPD or relatively quick and high level review of documentation on the feature would likely tell you all you need to know to get it right.

2. Good point! The package is executed by Oracle based on the security policy defined. A schema only needs authority on the table, not the security package. I will change the question text to remove this.

3. You do not, by default, have the authority to use DBMS_RLS. That's why I was sure to point out in the question that "all statements complete successfully (that is, all the necessary privileges have been granted to HR)." If, however, you want to use VPD in your application, you will definitely need to ask for help from your DBA.

4. That's very interesting (EXEMPT ACCESS POLICY). Since I do not explicitly tell you in the question that this privilege is granted to HR - and this privilege is not required to get the code to compile and run - you should not assume that it was granted. This is, however, good material for a future quiz!

5. Interesting point (NULL instead of 1=1). I have no experience with this (I assume that the reference to "MV" is for a materialized view). I hope to hear more about this from some of you!

Any other thoughts regarding VPD and the quiz? Would any players like to share their experiences with VPD and how it has helped them? And, best of all, would any of you like to submit quizzes on VPD based on your experiences and knowledge? I hope so!

Cheers, SF

08 March 2011

Why don't we show the difficulty level of the question?

For a while, we showed the level of difficulty of the question (beginner, intermediate, advanced) when you took the quiz. Then we removed that information.

A player recently asked that we restore this data to the page, saying: "I think that was useful for us to decide how much time we should spend on a particular quiz option. For example, it is not worth spending 3 minutes to figure out correct answer for a doubtful option for BEGINNER level quiz, but it is worth doing so for ADVANCED level quiz. Isn't that information a fair one? I didn't hear about this in your blogs on why this was taken away."

Actually, I decided to remove the difficulty level for precisely the kind of thinking this player offered to me. I don't want to give you any "clues" to guide your answering the question - besides the information in the question itself.

You should determine the worth of your time playing the quiz independent of what I perceive to be the difficulty level. The quiz is the quiz; objective as I can make it. The assigning of a difficulty level is a rather subjective process, one on which I am challenged regularly by players.

So suppose I say it is a beginner quiz. That changes your approach when answering the quiz; you don't spend the 3 minutes on a "doubtful" option. You answer quickly and you get it wrong. Because, as it turns out when you see the answer, you don't think it is a beginner quiz at all. It was advanced! So you then need to ask for a score adjustment, or just feel a bit resentful and negative about the quiz.

Who needs that, right?

So take the quiz on its own merits and I think we will all be better off.

Cheers, Steven

Confusion caused by reference to year in question (2082)

The 7 March quiz asked:

"Which of the choices define a function that accepts any date in 2011 and returns the date of the first day of the next quarter from that date? Note: quarters begin on the following dates: 1 January, 1 April, 1 July, and 1 October."

Two players raised a concern about my reference to "any date in 2011," as follows:

"I had a problem with todays quiz. I answered it the way I feel you intended it to be, on the merits of functionality. But by adding a specific year into the question, some might feel that you need to set up a check to see if it is that year that is coming in through the parameter. therefore, no answers would be correct."

"I found the "accepts any date in 2011" misleading - to me this suggests an exclusion of non-2011 dates which is not in any of the choices. The only implicit restriction that I could see was that the passed date needed to be less than 01-Oct-9999."

My response: I see what you mean and if the question constituted technical specifications for a program to be written for an application, then I would also agree. There is no validation in any of the solutions for this restriction on dates. This is, however, a quiz - and your job was to determine if an implementation would in fact accept any date in 2011 and...so on. It doesn't say anything about rejecting a date not in the year. So I don't see any reason to change the scoring on this turn.

You might be interested to know why I would add this "clause" to the question - it has lots to do with my effort to create interesting quizzes, but also with some odd aspects of Oracle's handling of dates.

First, I like to offer more than one correct solution for most of my quizzes. That keeps players "on their toes" and also often serves as a way to demonstrate that there is always multiple solutions to a given problem in programming, and all solutions are not equal.

Many times, we end up writing long, elaborate algorithms, all the time thinking to ourselves: "It shouldn't be this hard, it can't take all this code...surely there is a better way?" But we don't listen to that voice. I encourage you to listen, and be ready to back away from code you have written, and challenge your assumptions.

So I decided to include this choice as an option:
CREATE OR REPLACE FUNCTION plch_day1_nextq (date_in IN DATE)
   c_year    CONSTANT INTEGER := TO_CHAR (date_in, 'yyyy');
   c_date   CONSTANT DATE := TRUNC (date_in);

   FUNCTION date_in_year (part_in IN VARCHAR2)
      RETURN TO_DATE (c_year || '-' || part_in, 'YYYY-MM-DD');
   END date_in_year;
             WHEN c_date < date_in_year ('04-01')
                date_in_year ('04-01')
             WHEN c_date < date_in_year ('07-01')
                date_in_year ('07-01')
             WHEN c_date < date_in_year ('10-01')
                date_in_year ('10-01')
                TO_DATE (TO_CHAR (c_year + 1) || '-01-01', 'YYYY-MM-DD')
It is a great example of "over-engineering" a solution, doing it with brute force, rather than simply finding the best built-in(s) to do the job.

But I wanted it to be a valid solution and that immediately leads to questions like "Will it handle any date? What about valid date ranges for Oracle? Do those vary by version?"

It turns out, in fact, that if I want that monstrous function to be a valid choice, there is more to address than simply saying "Any date between" Oracle's supported date range. One of my reviewers noted that this function also will fail if a date with a year number of 0 is passed into it, raising this error:

ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Now, I suppose I could have continued my work on this function until it handled all scenarios, but quite honestly I am a bit busy these days and...who knows what other odd behavior a player would uncover...

So I "slapped on" the reference to dates in 2011. It is, in other words, an artificial constraint whose purpose is to simplify the choices and my life.