Qualified identifiers and error messages - 29 December quiz (1823)

The 29 December quiz tested your knowledge of how you can qualify the names of PL/SQL elements with their scope name (procedure, function, block).

Iudith wrote the following commentary regarding the kinds of errors that are raised across different versions of Oracle:

Regarding the Quiz of 29-dec, Choice 2:

<<plch_employees>>
DECLARE
employee_id plch_employees.employee_id%TYPE;
BEGIN
SELECT plch_employees.employee_id
INTO plch_employees.employee_id
FROM plch_employees
WHERE plch_employees.employee_id = plch_employees.employee_id;
DBMS_OUTPUT.PUT_LINE(plch_employees.employee_id);
END plch_employees;
/

The choice is anyway incorrect, but maybe it is worth to remark that the PL/SQL compiler treats it differently in the different database versions:

1. For Oracle 10.2.0.3.0, the full compiler errors are as follows:

ERROR at line 3:
ORA-06550: line 3, column 17:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 3, column 17:
PL/SQL: Item ignored
ORA-06550: line 6, column 12:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 7, column 7:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 5, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 10, column 42:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 10, column 5:
PL/SQL: Statement ignored

2. For Oracle 11.1.0.7.0, the compiler errors are somewhat different:

INTO plch_employees.employee_id
*
ERROR at line 5:
ORA-06550: line 5, column 12:
PLS-00403: expression 'PLCH_EMPLOYEES.EMPLOYEE_ID' cannot be used as an 
INTO-target of a SELECT/FETCH statement
ORA-06550: line 6, column 7:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 4, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 9, column 42:
PLS-00357: Table,View Or Sequence reference 'PLCH_EMPLOYEES.EMPLOYEE_ID' not allowed in this context
ORA-06550: line 9, column 5:
PL/SQL: Statement ignored

That is, for Oracle11gR1 the PLS-00403 and PLS-00357 errors appear, while in Oracle10gR2 we saw the PLS-00320 error.

So, things change over time, and, in this case, 11g looks more explicit.

Also, the "hiding" of the %TYPE anchoring caused by using a table name as a label can be worked around not only by defining the variable as INTEGER, but also by qualifying the table name with the schema owner in the variable definition, and thus a %TYPE anchoring can still be used.

"Bounds" for associative arrays - correcting an ambiguity - 28 December (239)

The 28 December quiz on associative arrays scored the following as incorrect:

There are no upper or lower bounds on the integer values you can use as index values.

Several players wrote to complain about this scoring, from two angles:

1. "If the array is indexed by binary_integer then there is upper and lower bounds. (-2147483647 .. +2147483647) However if the table is indexed by varchar2, then there are no bounds on the 'integer values'"

2. "There are no upper or lower bounds on the integer values you can use as index values.Actually, there is a limit on the index values, but it is defined by the limit on the BINARY_INTEGER. So I think there is NO actual limit on the index values, just the limit on the BINARY_INTEGER value."

3. One player quoted from my book, Oracle PL/SQL Programming, as follows: ""Unbounded versus bounded A collection is said to be bounded if there are predetermined limits to the possible values for row numbers in that collection. It is unbounded if there are no upper or lower limits on those row numbers. VARRAYs or variable-sized arrays are always bounded; when you define them, you specify the maximum number of rows allowed in that collection (the first row number is always 1). Nested tables and associative arrays are only theoretically bounded. We describe them as unbounded, because from a theoretical standpoint there is no limit to the number of rows you can define in them."

I agree with point 1 (that is, I accept that I was not explicit enough in my phrasing) and disagree with points 2 and 3. My explanations follow:

1. The argument here is that if the associative array is indexed by VARCHAR2, then you can run code like this without any error (provided by one of the players):

SQL>declare
  2     type ty is table of number index by varchar2(100);
  3     tb ty;
  4  begin
  5     tb(2147483648) := 1;
  6  end;
  7  /

while this code fails:

SQL>declare
  2    type ty is table of number index by binary_integer;
  3    tb ty;
  4  begin
  5    tb(2147483648) := 1;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 5

Now, I could argue that if you index by VARCHAR2, then the values used as index values are not integers; they are strings. So I think I could stand firm and insist that this statement is correct, but the bottom line is that from the perspective of a developer taking advantage of this "workaround" she is using "integer values" as the index values.

So I am going to change the wording of this choice to be more explicit, give everyone who select incorrect credit, and rescore.

2. I find this argument to be "hair splitting". The simple fact of the matter is that if an associative array is indexed by BINARY_INTEGER or one of its subtypes, there are upper and lower bounds (minimum and maximum values) that can be used as index values. So what if those bounds are defined, indirectly, through the use of BINARY_INTEGER?

3. I love having my book quoted at me. I conclude two things from this quote: (a) I need to change the wording. Associative arrays are unbounded only from a practical, not theoretical standpoint. It is precisely from a theoretical perspective that they are bounded; and (b) we need to distinguish between the idea of an upper bound on the number of elements in a collection and on the index values of that collection. With associative arrays, there is an upper bound on the integer values that can be used as index values, but there is no practical bound on the number of elements that can be defined in the collection.

Your thoughts as we play the last few quizzes of 2010?

Happy new year,
Steven

So which error is raised in 24 December quiz? (1805)

The 24 December quiz tested your knowledge of what happens when a CASE statement does not contain an ELSE clause and at runtime, none of the WHEN clauses are executed. The answer is that Oracle raises the "ORA-06592: CASE not found while executing CASE statement" error.

Several players wrote, however, to note that the local variable, l_text, is declared as VARCHAR2(20). So this assignment:

the_text := 'Santa is coming (for some, sort of, maybe)';

will raise a VALUE_ERROR exception. In other words, they chose the correct answer, but for the wrong reasons.

It is true that if statement assignment was executed, VALUE_ERROR would be raised and the text "Today is the 24th" would be displayed on the screen.

But that statement is never executed because of the lack of an ELSE clause.

I did not intend to introduce this issue into the quiz; the original quiz submitted by Michael used just "Santa is coming". But I "softened" the text - after all, many do not celebrate, do not believe in Santa, etc. - and neglected to increase the size of the string.

I will correct this in the question text. There will be no re-scoring of quiz answers.

Merry Christmas to those who celebrate,
Steven

Is a CLOB a string? A question raised about the 22 December quiz (1803)

The 22 December quiz tested your knowledge of the capabilities of both EXECUTE IMMEDIATE and DBMS_SQL to parse very long strings. We scored the following statement as incorrect:

"You cannot execute a string of more than 32,767 bytes as a dynamic SQL statement."

Oracle offers various mechanisms, especially in Oracle11g to bypass this limitation (the maximum size, that is, of a VARCHAR2 variable or literal).

One player emailed the following concern:

One of the choices for today's quiz looks somewhat ambiguous, namely the choice that says: "You cannot execute a string of more than 32,767 bytes as a dynamic SQL statement". If we take this literally as it is worded, then we could object that there is no such thing at all as a "string of more than 32,767 bytes", because this is the maximum length allowed to a VARCHAR2 string. If so, then this choice is supposed to be marked as correct. On the other hand, if string means "anything that is character data", including a CLOB, then this choice (though apparently in need to be marked as incorrect) falls over 2 other choices, namely: a) the choice that says "You can use a subprogram of the DBMS_SQL package to parse a SQL statement whose length exceeds 32767 bytes ( by the way, the wording of this choice ellegantly avoids speaking of a "string whose length exceeds 32767, but speaks of a STATEMENT whose lengths exceeds 32767, thus avoiding the above problem). and b) the choice that says "In Oracle11g you can pass a CLOB to EXECUTE IMMEDIATE. These 2 choices in fact do cover in entirety the 2 cases of using a statement contents as a CLOB. I think that the wording of the choice that says "You can use a subprogram of the DBMS_SQL package to parse a SQL statement whose length exceeds 32767 bytes" is excellent, covering (even without specifying it explicitly) ALL the cases by which a statemnent longer than 32767 can be made "to fit into" the requirements of DBMS_SQL, be it by using a CLOB or by "breaking" the statement's contents into elements of a DBMS_SQL.VARCHAR2A array. So, in my opinion, the problematic choice recommends itself to be rescored due to the ambiguous wording.

The SQL Language Reference says the following about CLOBs: "The CLOB data type stores single-byte and multibyte character data." CLOBs are, therefore, character data, which is another general term for string. VARCHAR2s are also character data, with a maximum length of 32767. But I believe that it is correct to interpret string as a general category of data including CHAR, VARCHAR2, NCHAR, NVARCHAR, CLOB and NCLOB (any others?). Others also feel this way; see:  http://www.orafaq.com/forum/t/98334/2/.

You are correct that two other choices covered the correct descriptions of ways to parse very long strings. I do not see, however, what bearing that fact has on the other choice, which is incorrect.

So I do not see the need to rescore. What do you think?

Release 1.8 of PL/SQL Challenge in Production

We have successfully upgraded PL/SQL Challenge to release 1.8, which includes these significant new features and changes:

* Remember Me: a long-requested enhancement, you can check the "Remember me" box before you login. We will then automatically log you in to the PL/SQL Challenge on subsequent visits to the website. If you do not visit the site for seven days, you will be prompted to login again.

* Public Profile: you can now record much more information about yourself and your career as a PL/SQL developer. This information is then made available on a public player profile page - but only if you explicitly approve the publication of that content. All player names on the site are hyperlinked to this page. We also provide you with a public URL so that this profile can be accessed from outside of the PL/SQL Challenge website (such as from your blog).

* Streamlined "Take the Quiz" process: You no longer have to scroll down through assumptions and advice. You can choose to view the information or simply press the Play Now button to get right to the quiz.

* Post-Quiz Survey and Player Notes: after you take the quiz, we now invite you to provide us with feedback on the quality of the quiz. You can also record notes regarding that quiz for future reference. The notes will appear on the Past Quiz page. You can also take the survey at any time after you take the quiz.

* Submit Your Own Quiz Idea: you can now submit your own idea for a quiz directly from the website, both from the "Submit Quiz Idea" on the home page and from several other places on the site as well. If your quiz is accepted, your name will be posted the day the quiz is taken. Get creative and share your expertise with us and players from around the world!

* Reorganization of Rules and Assumptions: the Rules page is gone, long live the Rules page! Most of the content of that page is now available in the FAQ. In addition, rules, assumptions and advice for the quiz is provided on the Take the Quiz page.


Warm regards and happy holidays,
Steven Feuerstein

Beta Test of Release 1.8 from 13 December to 17 December

We invite all PL/SQL Challenge players to visit test.plsqlchallenge.com and help us test the beta release of PL/SQL Challenge 1.8. You can log in at this site using your usual email/password combo. The quizzes shown for this week are from the first week of the PL/SQL Challenge. All past quiz data should be available to you for ranking and reporting.

This version of the PL/SQL Challenge includes these significant new features:

* Remember Me: a long-requested enhancement, you can check the "Remember me" box before you login. We will then automatically log you in to the PL/SQL Challenge on subsequent visits to the website. If you do not visit the site for seven days, you will be prompted to login again.

* Public Profile: you can now record much more information about yourself and your career as a PL/SQL developer. This information is then made available on a public player profile page - but only if you explicitly approve the publication of that content. All player names on the site are hyperlinked to this page. We also provide you with a public URL so that this profile can be accessed from outside of the PL/SQL Challenge website (such as from your blog).

* Streamlined "Take the Quiz" process: You no longer have to scroll down through assumptions and advice. You can choose to view the information or simply press the Play Now button to get right to the quiz.

* Post-Quiz Survey and Player Notes: after you take the quiz, we now invite you to provide us with feedback on the quality of the quiz. You can also record notes regarding that quiz for future reference. The notes will appear on the Past Quiz page. You can also take the survey at any time after you take the quiz.

* Submit Your Own Quiz Idea: you can now submit your own idea for a quiz directly from the website, both from the "Submit Quiz Idea" on the home page and from several other places on the site as well. If your quiz is accepted, your name will be posted the day the quiz is taken. Get creative and share your expertise with us and players from around the world!

* Reorganization of Rules and Assumptions: the Rules page is gone, long live the Rules page! Most of the content of that page is now available in the FAQ. In addition, rules, assumptions and advice for the quiz is provided on the Take the Quiz page.

Lots of great stuff, I hope you will agree! Now we need your help this week to uncover bugs and fine tune the flow on the website. If you have a few spare moments, please play around with setting up a comprehensive player profile. Are we missing any critical information you'd like to record about your career as a PL/SQL developer? And take the quiz survey. Are the questions asked appropriate to your experience with the quiz and the kind of feedback you'd like to provide?

Thanks in advance for your assistance and remember: any data entered during the beta test will NOT be carried over to production.

Many thanks in advance,
Steven Feuerstein

Every block will fail with an error? Not so, say players. (1764)

In the 9 December quiz, I test your knowledge of subprogram overloading and the problem of ambiguous overloading. One choice, marked as correct, stated: "Any block of code that includes a call to salespkg.calc_total will result in Oracle raising an error when executed." I received two objections to this statement: 1. What if, a player asked, my block looked like this:

BEGIN
   IF 1 = 2
   THEN
      EXECUTE IMMEDIATE 'call salespkg.calc_total(''A'')';
   END IF;
END;

Then this block will not raise an error, even though it "calls" the program. 2. "I do not agree with the answer "Any block of code that includes a call to salespkg.calc_total will result in Oracle raising an error when executed", especially the words "any" and "when executed". Of course, an anonymous block will give a runtime error. But when I include this call in a procedure or package (a "block of code" as well), I will never be able to execute this, because it won't even compile (PLS-00307)! So, in this case, the block of code will NEVER result in an Oracle (runtime) error, because it will never be executed. Because of that, I scored this answer as incorrect" Here is my response: 1. Well, look at that! A player found a "hole" in my statement. I said "includes a call" but I never said that the block had to actually execute the subprogram. Further, he hides the call to the ambiguously overloaded subprogram inside a dynamic PL/SQL block so that the static block with compile. Very ingenious - and irritating. :-) It is so ingenious, in fact, that I am entirely loath to rescore everyone's answers as correct on this point. I will grant that I had a "hole" in my statement and fix that in the text. I will give this player credit for a correct answer on this statement - and I will do so for anyone else who chose "incorrect" for this option because of this - you will need to submit a request through Feedback on the website. Finally, this player (_Nikotin) will receive an O'Reilly Media ebook as a reward for finding a way to maneuver around the language of this statement. 2. I do not agree with this objection. The bottom line is that you cannot execute a block that contains a call to (and that executes) any of these subprograms. Either you cannot run that block because it fails to compile due to an ambiguous overloading or because it is calling a subprogram that is invalid (due to an ambiguous overloading). Either way, you cannot execute that block. Your thoughts?