[Find below the text of the September 2012 e-newsletter sent out to PL/SQL Challenge subscribers.]
Another summer gone...and a hot one it was. Let's just hope the ice in the
Arctic circle returns next year. In the meantime, eyes turn towards Oracle Open
World, the annual extravaganza of All Things Oracle (and that is an
awful lot of "things", compared to the old days).
I'll be presenting twice on Wednesday, 3 October, and I will have more
PL/SQL
Challenge ribbons. So I hope to see many Challengers there and ready to add
that ribbon to their conference badge!
Speaking of the old days, the 2012 OOW will be my
twentieth
consecutive presenting at OOW or IOUW (International Oracle User Week, the
precursor to OOW that was organized by the International Oracle User Group). I
just came across the acceptance letter for my 1992 submission: "An Interactive
Debugger for SQL*Forms." Yes, that's right. A debugger for SQL*Forms 3.0, which
I
built in SQL*Forms itself. I called it XRay Vision and it was a
very cool utility - and a testimony to the foolishness of building
tools for products at the end of their lifecycle. I didn't sell too many copies
of XRay Vision. Sigh...
We've now passed 550,000 answers submitted to quizzes since April 2012 - the
PL/SQL Challenge is still going strong and making Oracle technologists around
the world stronger PL/SQL developers. If you haven't been playing lately (or
taking advantage of our Practice feature - more on that below), I encourage you
to pay us a
visit.
Dynamic SQL: Never use to avoid code repetition?
The
12
September 2012 PL/SQL quiz asked players to pick the choice that would allow
a developer to add an entirely new table to the application schema, and not have
to change the procedure that will be used to insert into this table. This is
accomplished through the use of dynamic SQL, and several players objected to the
use of dynamic SQL in this kind of situation. This quiz was drawn from our own
experience at the PL/SQL Challenge. We are implementing support for Favorites
and we have at this point seven "kinds" of favorites: quiz, author, player,
roundtable discussion, quiz commentary, resource, feature. We have a distinct
table for each kind of favorite, whose names follow the convention
"qdb_fav_[type]s", as in qdb_fav_authors and qdb_fav_players.
I really like to minimize code volume and strip out any repetition, so I
chose this dynamic SQL approach to inserting a now into the appropriate
favorites table:
PROCEDURE add_favorite_dynamic (user_id_in IN INTEGER,
favorite_id_in IN INTEGER,
favorite_type_in IN VARCHAR2)
IS
BEGIN
EXECUTE IMMEDIATE
'INSERT INTO qdb_fav_'
|| favorite_type_in
|| 's (user_id, '
|| favorite_type_in
|| '_id, notify_user) VALUES (:user_id, :favorite_id)'
USING user_id_in, favorite_id_in;
END add_favorite_dynamic;
Notice that even if we start keeping track of another kind of favorite, this
procedure would not have to be modified, as long at the naming conventions are
followed when creating the new table.
If I were to use static SQL, my procedure would look like the one below, and
would have to be changed each time we add a new favorites table:
PROCEDURE add_favorite_static (user_id_in IN INTEGER,
favorite_id_in IN INTEGER,
favorite_type_in IN VARCHAR2)
IS
BEGIN
CASE favorite_type_in
WHEN c_fav_author
THEN
INSERT INTO qdb_fav_authors (user_id, author_id)
VALUES (user_id_in, favorite_id_in);
WHEN c_fav_comp_event
THEN
INSERT INTO qdb_fav_comp_events (user_id, comp_event_id)
VALUES (user_id_in, favorite_id_in);
WHEN c_fav_discussion
THEN
INSERT INTO qdb_fav_discussions (user_id, discussion_id)
VALUES (user_id_in, favorite_id_in);
WHEN c_fav_player
THEN
INSERT INTO qdb_fav_players (user_id, player_id)
VALUES (user_id_in, favorite_id_in);
WHEN c_fav_question
THEN
INSERT INTO qdb_fav_questions (user_id, question_id)
VALUES (user_id_in, favorite_id_in);
WHEN c_fav_resource
THEN
INSERT INTO qdb_fav_resources (user_id, resource_id)
VALUES (user_id_in, favorite_id_in);
WHEN c_fav_thread
THEN
INSERT INTO qdb_fav_threads (user_id, thread_id)
VALUES (user_id_in, favorite_id_in);
WHEN c_fav_topic
THEN
INSERT INTO qdb_fav_topics (user_id, topic_id)
VALUES (user_id_in, favorite_id_in);
END CASE;
END add_favorite_static;
Another static SQL approach: rather than having a distinct table for kind of
favorite, use a single favorites table (and thereby lose the ability to define a
foreign key on the "favorite ID" column):
PROCEDURE add_favorite_static (
user_id_in IN INTEGER,
favorite_id_in IN INTEGER,
favorite_type_in IN VARCHAR2)
IS
BEGIN
INSERT
INTO qdb_favorites (user_id, favorite_type, favorite_id)
VALUES (user_id_in, favorite_type_in, favorite_id_in);
END add_favorite_static;
Which approach would you take? We've set up a
poll to
get your feedback on this issue. I hope you can take a few minutes from your
busy schedule to "vote".
Practice Makes Expert!
Did you know that in addition to taking daily, weekly and monthly
scheduled quizzes, you can also (re)take any past quizzes through the
PL/SQL Challenge Practice feature?
Click on the Practice tab on the menu and you can then set up a practice
based on particular features of the technology you'd like to get more familiar
with or for quizzes drawn from favorite authors. You can also take advantage of
the Autotune feature: the PL/SQL Challenge will automatically add practices to
your queue based on either past low scores or missed quizzes -
you decide how many of and how often these practices should be
generated.
Since we added the Practice feature to the PL/SQL Challenge in April 2012,
nearly 450 Oracle technologists have completed over 2100 practice quizzes. One
player has taken 130 practice quizzes, and another twenty players have taken at
least 25 quizzes each.
Here's what one player, Ruslan, told us about how he uses this feature:
"I like to practice my PL/SQL on PL/SQL Challenge because
I can hone my knowledge in a wide range of themes and on different levels.
Constant practicing helps me to solve my tasks much better and more effective
and to prepare to be certified. For example when I started one of my first
projects I knew nothing about BULK operations and Dynamic SQL and packages that
hold constants. So I developed not really flexible and fast application. After I
learned all of that here I rewrite that application and made it more general and
fast enough. The PL/SQL Challenge site is very ergonomic. I like the Library
because it helps me to get back to my mistakes and learn what I didn't know
previously. And I like statistics. Statistics shows my progress in time. Thank
you for such a great place to learn PL/SQL!"
You, too, can use the PL/SQL Challenge site to learn more about PL/SQL (and
SQL and APEX....) and solve problems faster. Set up your Autotune practices
today and start down the path to PL/SQL expertise!
New Poll: Usability of the PL/SQL Challenge website
An on-again, off-again player of the PL/SQL Challenge wrote the following to
us:
"I tried to find how to play the PL/SQL quiz and just
couldn't find where to go to play the PL/SQL quiz which I've played on and off
for a few years now. The web pages have become so complicated that you seem to
have to go through several rules and pages to get to the quiz. Could you tell me
where I go to play the quiz? I learnt a lot from playing this quiz but now am
thinking of stopping because it's taking too long to get to it. Don't get me
wrong - I think it's a brilliant idea and so good for keeping on top of PL/SQL
changes. I wish the web pages were simpler."
We were surprised - and dismayed - to hear this; we didn't think it was hard
to play a quiz on the site. If it is, well, that is something we need to fix,
and fast.
So we thought we'd ask our players for some
feedback
on usability of the site. Please take a moment to help us improve your
experience at the PL/SQL Challenge.
High Performance PL/SQL Video
On 5 September, Quest Software hosted a webinar by yours truly on Higher
Performance PL/SQL. It was just an hour long, so I had to focus very closely on
just a few topics: BULK COLLECT, FORALL, Function Result Cache, NOCOPY and -
barely squeezed in - pipelined table functions. You can watch the recorded
session
here
. Enjoy!
Notice that you can request to have a daily digest emailed to you with the latest news for your Favorites. We have turned on this preference only if you are already receiving daily emails with quiz results.