First quiz on user-defined aggregate functions draws comments (2124)

On Friday, 24 March, the PL/SQL Challenge offered its first quiz, by Randy Gettman, on user-defined aggregate functions. This topic, certainly an advanced one, presents its own challenge: how to offer a relatively brief quiz on a most complicated topic.

It drew these two comments:

morkCallingOracle(comeIn.oracle).... I mean what has "the world" become? Seriously, most non SF-quizzes need multiple times the number of code lines SF ones need. Then if you managed to work your way through the code - in fact this time I gave up - they often seem far fetched or even trivial. Ofc OO is not my thing at all. I think re-adjusting my expertise to Intermediate is appropriate here. How important is OO to the quiz' audience or strategically to PL/SQL? Keep it up people! :)

Thanks for yesterday's quiz. It might have been more fun if there was an answer using only built-ins "select exp(sum(ln(partnum))) from plch_parts;" (even though it will work only when partnum is always > 0) ..and even though the purpose of quiz was to teach custom aggregate. :)

I will respond to the first comment and leave the other for players to discuss.

This was a very code-heavy quiz (gee, maybe I really shouldn't do this sort of thing on a Friday!). In fact, when Randy first submitted it, my initial reaction was "This is just too much." Then I took a closer look and realized that so far as I could tell, this is just about the minimal amount of code you could possibly write to create such a function. So my choice became: never do a quiz on this topic (involving code) or give it a try. I am glad we "tried."

The first commenter might well be correct that on average player-contributed quizzes have more code than my own. The most likely explanation for that, however, is that quizzes provided by players often deal with more complex and edgy features, on which I am not an expert. If I were, I expect that my quizzes on such topics would have a similar volume of code.

Finally, in terms of "far fetched" or "trivial" or "how important is OO" - remember: we offer a new, fresh, delightful quiz every single weekday. That's a whole lot of quizzes, folks. And if we only covered "core" or "fundamental" features, well....we'd end up with lots of repetition or lots of trivial, boring quizzes. Plus, you wouldn't stretch and expand your knowledge of PL/SQL-related technologies.

The first player says "in fact this time I gave up" - and I can understand that. Sometimes a quiz will serve primarily as an introduction for you to a new area of technology. So you have to accept the fact that you are (temporarily) ignorant and delight in the opportunity to learn something new.

But go ahead and take the quiz anyway! "Give up," but still submit an answer! Unless it's one of those "only once choice correct:", you are bound to get some credit for the time spent realizing you have no idea what is going on in the quiz ( :-) )! And, by the way, we strive to avoid setting a quiz to "one choice correct" when it is advanced.

Cheers, Steven

Buggy behavior on some 11.1 versions with INTEGER variables (2123)

The 23 March quiz explored nuanced differences between a PL/SQL INTEGER type and a SQL INTEGER type.

In particular, inserts into a table of an integer with more than 38 digits does not cause an error to be raised, as one might have thought.

Several players wrote, however, that when they tried to run the verification code, they did, in fact, get the"ORA-01438: value larger than specified precision allowed for this column" error.

After further testing, it looks like this script works (and thus demonstrates the point of the quiz properly) on database instances of versions 10.2 and 11.2. On some sub-releases of 11.1, however, such as 11.1.0.6, the script fails with the error noted above. On 11.1.0.7, this bug is apparently fixed and the script runs without error.

The author of this quiz, "_Nikotin", also found that "it's possible to reproduce other bug-like behaviour with INTEGER (on 10.2, 11.1, 11.2):

SQL> drop table t;

Table dropped.

SQL> create table t (a integer);

Table created.

SQL> insert into t values (1E125);

1 row created.

SQL> alter table t modify a number(38);

Table altered.

SQL> insert into t select a from t;

1 row created.

SQL> insert into t values (1E125);
insert into t values (1E125)
                      *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


SQL> select a from t;

         A
----------
1.000E+125
1.000E+125

SQL> select dbms_metadata.get_ddl('TABLE', 'T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------

  CREATE TABLE "TEST_USER"."T"
   (  "A" NUMBER(38,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"