One, Two, Many Implicit Conversions (5986)

A player brought to our attention some rather strange code in the 25 August quiz on TRUNC.

We asked you to use this block to determine your answer:

DECLARE
   multiplier   INTEGER := /*VALUE*/;
BEGIN
   FOR indx IN 1 .. 5
   LOOP
      DBMS_OUTPUT.put_line (
            'Trunc by '
         || NVL (TO_CHAR (multiplier * indx), 'NULL')
         || ' = '
         || TO_NUMBER (TRUNC (123456.123456, multiplier * indx)));
   END LOOP;
END;

And this is what he wrote:

"I just hope your construct is meant to illustrate "bad practice"? You have this line: "|| TO_NUMBER (TRUNC (123456.123456, multiplier * indx))". You are doing TO_NUMBER on a number which will implicitly convert the result of TRUNC to a string, which TO_NUMBER converts back. Then the result of TO_NUMBER is a number which you are concatenating to your output string, so that must also be an implicit conversion? Have I read it correctly or am I just not awake yet ? I guess it might be meant as a distraction or a way to force ORA-06502? Anyway - you may have your reasons, I just wanted to let you know in case it happened to be a typo and you meant TO_CHAR instead of TO_NUMBER..."

Well, I must admit: that was a typo. And a lucky typo....it did not affect the outcome of the quiz, because Oracle ending up "doing the right thing" for us through implicit conversions.

So I am going to change the quiz to use TO_CHAR. Thanks for pointing this out, Kim!

SF

APEX Support in Toad

Quest is exploring whether to and how best to add support for Oracle Application Express in Toad for Oracle.

More and more Oracle technologists are building websites in APEX technology. It is moving rapidly from the sidelines of departmental applications to the mainstream of robust, public and private websites implementing mission critical applications involving many users. For example, the PL/SQL Challenge website is built on APEX.

Hundreds of thousands of these same technologists also spend lots of their time in Toad for Oracle, building out the backend elements (tables, packages, etc.). Quest Software believes that Toad for Oracle should be able to help APEX developers understand, analyze, maintain and even enhance their applications from within Toad.

We also, realize, however, that there is an enormous range and depth of experience with APEX "out there." So rather than just come up with a list of features we think would be helpful to APEX developers, we'd like to hear from you, the APEX developer and also development DBAs supporting APEX developers, about how you think Toad for Oracle can best support your work with APEX.

You can do so by responding to this blog post. You can also email me directly at steven.feuerstein@quest.com. I will be compiling requests and requirements for consideration by the Toad for Oracle Product Manager and development team.

Many thanks in advance,
Steven Feuerstein
PL/SQL Evangelist, Quest Software

Daily Quiz Activity for 15 August 2011 - 19 August 2011

In the past week (15 August - 19 August), 790 Oracle technologists were busy submitting 2,376 answers to quizzes and exploring the library of past quizzes. Here are the PL/SQL quizzes played in that period:

15 August 2011: When you want to declare an associative array with an integer index, the datatype of the INDEX BY clause must be BINARY_INTEGER or any of its subtypes. You should, as a rule, always use PLS_INTEGER unless you have some specialized requirement.
              528 Players ♦ Avg. Correct: 64% ♦ Avg. Time: 221 seconds ♦ Rating: 4 stars

16 August 2011: Make sure that you are both aware of all possible built-in functions and that you use them in a way that optimizes both performance and maintainability.
              634 Players ♦ Avg. Correct: 73% ♦ Avg. Time: 444 seconds ♦ Rating: 4 stars

17 August 2011: ALL_PROCEDURES gives you information about the subprograms (procedures and functions, both schema-level and within packages) on which you have EXECUTE authority.
              633 Players ♦ Avg. Correct: 80% ♦ Avg. Time: 203 seconds ♦ Rating: 4 stars

18 August 2011: Use SAVEPOINT to define a point in your session's transaction to which you can rollback. This feature allows you to only partially rollback changes in your session.
              624 Players ♦ Avg. Correct: 70% ♦ Avg. Time: 273 seconds ♦ Rating: 4 stars

19 August 2011: You can compare a collection to NULL, but keep in mind that associative arrays will always evaluate to NOT NULL - even if they do not contain any data. Nested tables and varrays are NULL until initialized.
              591 Players ♦ Avg. Correct: 68% ♦ Avg. Time: 213 seconds ♦ Rating: 4 stars