22 January 2011

Seeing results from running SQL query (1926)

In the 21 January quiz, I asked you to select the choices that would display "Ellison". Two of the choices were PL/SQL blocks containing calls to DBMS_OUTPUT.PUT_LINE. Two were queries. One block and one query were scored as correct. I received the following objections.

1. "There were () at the end of function invocation. PL/SQL syntax would not allow that."

2."I think it is wrong as you actually ask for code that displays the given text and not which query only runs fine. Therefore only Option 2 is right and not Option 4 as it only selects something but has no attached DBMS_OUTPUT.PUT_LINE statement."

3. "You don't have semicolons at the end of the SQL query. Is it supposed to be correct? It doesn't seem so - that's why I haven't chosen the answer #4 though in general I know it is valid."

My responses:

1. In fact, you can provide "()" after a function call, without any argument values, and Oracle will accept it.

2. When a query is executed it returns data, which you view on your screen - just as you would by running a block with calls to DBMS_OUTPUT.PUT_LINE. One of the queries returned "Ellison" and was scored as correct. I do not believe it should be necessary to provide specific instructions on how to interpret the effect of running an SQL statement.

3. Ah, very interesting! Many developers do  believe that the ";" character is part of the SQL language - it is not. It is simply the default terminating character in SQL*Plus (causing immediate execution of the statement).

In conclusion, I do not believe it is necessary to change the way this quiz was scored.



  1. P.3 was mine. Yes, I agree that semicolon is not a part of the language. But for me the answer is correct if you can copy-paste it into SQL*Plus and it will run correctly. This one will not run at all because I need to add ';' or '/'.

  2. >>"But for me the answer is correct if you can copy-paste it into SQL*Plus"

    Why SQL*Plus, rather than dozens of other programs, including sql developer? Btw, if think so, sql*plus not executes query without pressing on "Enter" also. And may be write "Enter" or "\n" at the end of query for you?

  3. Oracle documentation explicitly puts semicolon at the end of the SQL clauses like 'alter table' for example. But 'Enter' or '\n' are not mentioned. And personally me - I strongly believe ';' should be present in SQL queries in the quiz. Or mentioned in the assumptions which I don't like but at least it will be fair.
    SQL*Plus is the only standard tool for executing SQL queries which goes together with the DB and can be used in any place at any time. That's why it is de-facto standard. I'm not sure SQL*Developer will handle calls to DBMS_OUTPUT at all (but really don't know - never used it - just saw others)

  4. Regardless of what any player believes a SQL statement should terminate with to ensure that it executes, in the context of a quiz, why wouldn't you ASSUME that the code is going to execute?

    I am not going to present you with code, say "after it executes", and then try to trick you by (even if it were true) leaving off a ";"
    or a "/" so that I could say later "Ha! It wouldn't even run!"

    That's not the intent of these quizzes and players should not approach them with that kind of concern or analysis.

  5. Shmyg, did you ever try to paste exactly any PL/SQL block of any quiz to SQL*Plus and execute it without typing any symbols?

  6. Myself marked the query as a correct choice, but shall to say that quizes should be present in an uniform way.

    If query does not require a semicolon or slash at the end then PL/SQL blocks shall be presented without a slash as their last line - and for the very same reasons, this slash is not a part of the PL/SQL syntax!

    Kind regards,

  7. Oleksandr,

    I definitely should be consistent and will try to be better at that.

    I am not, however, going to accept as an objection to any quiz that we left off a character that is used only to submit/execute the statement.