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.
No comments:
Post a Comment