This answer should be marked "correct" : it says nothing about what will or won't happen when the code is *executed*. It only says "can appear anywhere in your PL/SQL block", which I take to mean "the PL/SQL block will compile without error regardless of where (in the executable section) the statement appears".
I will ask the others to post their concerns on this blog. Then I will offer my response later.
Cheers, SF
The PL/SQL Challenge (www.plsqlchallenge.com) offers a daily quiz on the PL/SQL language, through which thousands of Oracle technologists demonstrate and deepen their knowledge of PL/SQL. This blog contains posts by the PL/SQL Challenge founder, Steven Feuerstein, as well as comments from players.
27 October 2010
26 October quiz: objections to marking a choice as incorrect (1583)
We are about to kick off the OPP/APEXposed conference in Brussels, so I will post this note to get the discussion going, and going in a very lively manner. Then I will come back to it later.
In this quiz on read-only transactions, I marked the following choice as incorrect:
"The SET TRANSACTION READ ONLY statement can appear anywhere in your PL/SQL block, regardless of the location of SQL statements in that block."
Here is one of the objections:
Subscribe to:
Post Comments (Atom)
My own opinion:
ReplyDeleteThe objector is painstakingly nitpickingly right, that the set transaction "can appear" anywhere - but it won't work.
Problem is to differentiate between when a choice is correct merely because it can compile, or when a choice has to be able to execute without fail.
Either Steven has to write very longwinded explanations, so every choice is absolutely clear - but then the quiz will become as hard to read as a legal contract ;-)
Or we the participants have to use our common sense and understand the "spirit" of the question/answers from the context. (That should fit in with Stevens announced intentions of focusing the challenge on more education and less competition for grand prizes :-)
My own rule of thumb is, that if in doubt then I do *not* assume that Steven has made a trick answer. I assume that generally Steven plays fair with us and the correctness of an answer generally does not hinge on somewhat farfetched speculations.
In this case I personally consider it reasonably obvious, that even if "can appear" could be interpreted as just being able to compile, it would not make sense - and Steven very rarely makes a nonsense answer correct just to be tricky :-)
So my vote: No rescoring :-)
Choice says "in block", explanation says "in session". When
ReplyDeletea place of SET TRANSACTION in BLOCK or in SESSION has nothing to do with place in TRANSACTION. RTFM.
SET TRANSACTION can even fail as first line of block if there is existing transaction.
So the choice must be marked as CORRECT and it's score INVERTED.
declare
x int;
procedure SetReadOnly(DoIt boolean, "Place in 'Block'" varchar2)
is
begin
if DoIt then
dbms_output.put_line('Setting read only after '||"Place in 'Block'"||' ...');
set transaction read only;
end if;
end SetReadOnly;
begin
for i in 1 .. 4 loop
SetReadOnly(i = 1, 'very beginning');
select count(*) into x from dual;
SetReadOnly(i = 2, 'select');
execute immediate 'alter session set sql_trace=false';
SetReadOnly(i = 3, 'select and alter session');
execute immediate 'alter system set sql_trace=false';
SetReadOnly(i = 4, 'select and alter session and alter system');
commit;
end loop;
end;
/
Setting read only after very beginning ...
Setting read only after select ...
Setting read only after select and alter session ...
Setting read only after select and alter session and alter system ...
PL/SQL procedure successfully completed.
I was a bit confused when you said "SET TRANSACTION must be the first statement before you execute any SQL Statements in your session". Below is a test case where it is not first statement, still the SQL statement / PL/SQL block is executed successfully.
ReplyDeleteSQL> conn riaz@test-server
Enter password:
Connected.
SQL> declare
2 a number;
3 begin
4 select 1 into a from dual;
5 execute immediate 'SET TRANSACTION read only';
6 select 1 into a from dual;
7 end;
8
/
PL/SQL procedure successfully completed.
SQL> conn riaz@test-server
Enter password: Connected.
SQL> SELECT * FROM dual;
DUM
---
X
SQL> SET TRANSACTION read only;
Transaction set.
SQL> SELECT * FROM Dual;
DUM
---
X
This is 10gR2 database. Could you please clarify that?
regards
This comment has been removed by the author.
ReplyDeleteThe question is about behavior inside a program unit. To answer such a question, it is a fair assumption that the code compiles, I think.
ReplyDeleteMike
Hello,
ReplyDeleteThe objection seems to be not justified enough for 2 reasons:
- all other choices clearly deal with the execution, not the compilation thus defining the "question context"
- "can appear anywhere in your PL/SQL block" definitely includes anonymous blocks for which execution and separation cannot be separated. That means that any attempt to use an anonymous block with the misplaced SET TRANSACTION will fail.
For these reasons the choice in question was properly deemed as incorrect.
I think this is just another playing with words. On the other hand I agree that to make the choice bulletproof it should say "to execute without error".
ReplyDeleteMy comment is not very helpful, is it?
Well, in construction of the question and the choice I think it should be marked as correct.
Regards
Ludo
Hi Steven,
ReplyDeleteI knew this part of the question would become a troublemaker. Instantly after submitting my answer, I thought "hopefully this is not getting another language proficiency issue"...
It is clear that the SET TRANSACTION READ ONLY statement has to be the first in the sequence of statements comprising the transaction. Anyway, I regard the objection you've quoted as valid.
Furthermore, if you construct a case where only SELECTS are used, the location of the statements indeed doesn't matter, e.g.:
DECLARE
x NUMBER;
BEGIN
SELECT 1 INTO x FROM DUAL;
SET TRANSACTION READ ONLY;
SELECT 2 INTO x FROM DUAL;
END;
/
works fine without throwing ORA-01453.
So - yes, "SQL" does imply DML and then it matters where SET TRANSACTION is located. Which means you are right. Nevertheless, I still see a bit of ambiguity in this answer.
Anyway, I've learnt something from this quiz, and that is what counts for me!
Best regards,
Uwe
Steven,
ReplyDeleteI agree with you on this one. The choice is incorrect.
I got it wrong because I also at first thought that it doesn't matter where you put SET TRANSACTION READ ONLY in the context of PL/SQL block, but when I think about it in the context of a transaction (and we can have more than one transaction in a block) it does matter where we put it - we have to put it before any SQL statement in that transaction.
Dalibor Kovač
The question is about "read only transactions" not the "set transaction" statement. Therefore to have a read only transaction the "set transaction" statement MUST come first. Qouting from doc "Establishes the current transaction as read-only, so that SUBSEQUENT [caps mine] queries .."
ReplyDeleteI think Steven is right on this one. But, I say that because I had a red flag waving around the "statement" in question but put on my uber-literal hat and got the correct answers.
Ahh, come on, we're programmers, not attorneys. You don't really want a several pages fine print of assumptions and questions stated in an unbreakable form, but awfully hard to understand.
ReplyDeleteObviously compilable is not sufficient, it needs to be executable as well.
Hi Steven, I have an objection to the tuesday, 26th October Quiz:
ReplyDeleteI think that the answer is correct, too - but for different reasons:
SET TRANSACTION don't have to be the first statement.
The SQL Statements can be anywhere located in your BLOCK, as long as all transactions are closed before the SET TRANSACTION.
Look at this:
DROP TABLE plch_parts purge
/
CREATE TABLE plch_parts
(
partnum INTEGER
, partname VARCHAR2 (100)
)
/
BEGIN
INSERT INTO plch_parts
VALUES (1, 'Mouse');
INSERT INTO plch_parts
VALUES (100, 'Keyboard');
INSERT INTO plch_parts
VALUES (500, 'Monitor');
COMMIT;
END;
/
declare
vx varchar2(10);
begin
SELECT dummy
into vx
from dual;
UPDATE plch_parts set partname = upper(partname);
ROLLBACK;
SET TRANSACTION READ ONLY;
select partname
into vx
from plch_parts
where partnum=100;
end;
/
Regards
Robert
The objection has merit. The requirement is that "SET TRANSACTION READ ONLY" must be the first statement in a read-only transaction; there are no requirements specific to location within a PL/SQL block. A PL/SQL block could have more than one transaction, some of which might not be read-only. It is also possible that multiple read-only transactions could appear within the same block which would require multiple "SET TRANSACTION READ ONLY" statements. It is even possible that a PL/SQL block might have no queries and yet still include "SET TRANSACTION READ ONLY" (in fact, it is possible for that to be the only statement within the block since the scope is a transaction not a block). Perhaps none of these cases have practical merit, but all are technically valid and none are excluded by the wording of the quiz or choice.
ReplyDeleteAlthough the intent of the "location" choice seems to have been clear to most players, that intent is not clearly expressed in its wording. Had the phrase been "regardless of the locations or types of SQL statements in that block" then the choice would have been unambiguously incorrect. Some have stated that the meaning of the choice should have been discernable from the focus of the other choices; however, that argument is invalid because the list of assumptions includes the following:
"You should not assume there is any kind of relationship between the multiple choice answers. Specifically, information in one choice does not have any bearing on the correctness of another choice."
Players who correctly inferred the intent of the choice should keep their scores; those who responded based on a literal interpretation should be given credit.
Hi, i believe the marking is wrong.
ReplyDeleteset transaction needs to be the first statement in a transaction, however, transaction boundaries have nothing to do at all with block boundaries.
Even if set transcation was the first statement in a block, it can still fail if there is an uncommited pending transaction, as others have noted.
Karsten
To answer Riaz's question, the following will fail:
ReplyDeletedeclare
a number;
begin
commit;
select 1 into a from dual for update;
execute immediate 'SET TRANSACTION read only';
select 1 into a from dual;
end;
The "for update" starts a transaction and the SET READ ONLY must be the first statement in a transaction.
I thought that any select would start a transaction but that doesn't seem to be the case. Maybe that was true in older versions of Oracle.
Hello All,
ReplyDeleteThe last comment of Karsten just put it right,
transaction boundaries and pl/sql block boundaries are completely distinct things,
The topic of this question was a little bit
"forced" into being a PL/SQL topic.
The READ ONLY transactions are a purely SQL
notion (except, well, the case of an autonomous transaction that only exists under the "auspices" of a PL/SQL program unit).
But my feeling is that the basic intention of Steven was here to check whether we are aware
of the need to place SET TRANSACTION to be the first in its transaction, regardless of any PL/SQL block structure, maybe the most characteristic feature of a READ ONLY transaction.
Yes, the wording of the choice was maybe not the "happiest" one to carry this intention.
I even remember that back in Oracle 6 you could not even execute a SELECT before this statement, so, if ANY DML was executed at all
in a session, then a COMMIT or ROLLBACK
was required before the SET TRANSACTION.
On the other hand, strictly logically speaking, the sentence of this choice, just like any other afirmative sentence expressing a rule or a theorem may be interpreted like this: a rule is true if there is NO single exception possible to it. With this interpretation, it is obvious that the number of counter-examples (pl/sql blocks) that make this sentence FALSE is endless.
So, again, an ambiguous question, in which both possible answers can be credited with
a certain amount/part of the truth.
... Do we still consider computer science a precise science ???
Best Regards to All,
Iudith Mentzel
Let's not split hairs here. I think it is a fair assumption that "appear" equates to compile and execute.
ReplyDeleteI didn't participate in yesterday's quiz but if I had I probably would have marked it as CORRECT.
ReplyDeleteWhy? Because, to make it not work, you have to assume extra steps not stated in the question to make it incorrect. This violates the rules and an ongoing theme of previous objections.
For those arguing about the general idea of the question and that it's "obvious" it must be at the beginning of a transaction. Who says the procedure must called at the beginning of a transaction? If the SET is the first line of the procedure that doesn't necessarily make it functional. But, even this shouldn't be taken as a counter argument for INCORRECT, because I'm imposing restrictions outside the scope of the question itself
I believe the phrase 'location' was ambiguous and should be fixed in the quiz history. There are some PL/SQL constructs where the 'location' of a statement is restricted (eg a PIPE ROW can only appear in the body of the relevant function) but apart from those circumstances the execution sequence can be independent of the physical sequence of statements in a block (especially with local procedures in the define block). Indeed, there's nothing there indicating whether the
ReplyDeleteThinking harder after the results, I also came up with the scenario when you code a procedure with a SET TRANSACTION READ ONLY and an exception handler as a test to determine whether the session is currently in a transaction. As pseudo-code this might be :
BEGIN
other_pkg.other_proc;
BEGIN
SET TRANSACTION READ ONLY;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'You must explicitly commit or rollback your change');
END;
END;
I'd prefer to use DBMS_TRANSACTION.LOCAL_TRANSACTION_ID for this sort of test, but the SET TRANSACTION approach would work.
So ideally, I'd like to see the question rephrased in the historical log so that it is phrased in lines with "The SET TRANSACTION READ ONLY statement can be successfully executed regardless of any preceding SQL statements in the transaction." The use of 'block' seems to be trying to put a PL/SQL 'spin' on a purely transactional phenomena.
A very thoughtful and interesting thread of comments. Thanks, everyone, for contributing.
ReplyDeleteAnd now I offer some comments.
It seems that there were two objections regarding my decision to score this choice as incorrect: "The SET TRANSACTION READ ONLY statement can appear anywhere in your PL/SQL block, regardless of the location of SQL statements in that block."
1. I only say "appear anywhere". I do not say that it must compile or must execute without error. So, sure, the argument goes, you can put the statement "anywhere." If I made the above statement as a "stand alone" claim and asked you to decide "true or false" on the basis of that statement alone, I could definitely see why you might argue that it is incomplete or ambiguous. But this is not a "stand alone" statement. It is proposed as an answer to a question. The question is:
"Which of these statements regarding read-only transactions in PL/SQL are correct?"
A "read-only transaction" only exists when you run a piece of code that includes the SET TRANSACTION READ ONLY statement. So I feel that the context of this question (in which you must evaluate each of the choices) is quite clear: the block of code containing SET TRANSACTION READ ONLY must compile and execute to be a read-only transaction.
2. I say "anywhere in your PL/SQL block", but transactions are connected to a session and not a block. In addition, many players offers scenarios in which the SET TRANSACTION was not the first statement in the block. Great points, but they do not mean that the choice should be marked as correct. This choice can only be correct if I can put the SET TRANSACTION READ ONLY anywhere in my block no matter where my SQL statements are and still have a valid read-only transaction. This is clearly NOT TRUE. If I put SET TRANSACTION READ ONLY after an update statement, Oracle will raise an error when I try to execute the block. So this very broad claim of "can appear anywhere" is clearly false.
In fact, now that I think about it, I don't even say "can appear anywhere in the execution section of the PL/SQL block," so this choice can easily be rejected as correct, since you certainly can't put the statement in the declaration section of the block.
To conclude: I don't see any way to justify marking this choice as correct. And I did not. It is incorrect. There is no reason for me to change the way I scored this quiz
There were many comments in this thread; it is late (12:30 AM) and I am exhausted from a full day of training (and waking up at 4 AM yesterday). So I may have missed something. Please let me know me if you made a comment that you think is left unaddressed.
Warm regards, Steven
A very thoughtful and interesting thread of comments. Thanks, everyone, for contributing.
ReplyDeleteAnd now I offer some comments (in two parts, as Blogger seems to have a limit on the size. VERY irritating)....
PART 1
It seems that there were two objections regarding my decision to score this choice as incorrect: "The SET TRANSACTION READ ONLY statement can appear anywhere in your PL/SQL block, regardless of the location of SQL statements in that block."
1. I only say "appear anywhere". I do not say that it must compile or must execute without error. So, sure, the argument goes, you can put the statement "anywhere." If I made the above statement as a "stand alone" claim and asked you to decide "true or false" on the basis of that statement alone, I could definitely see why you might argue that it is incomplete or ambiguous. But this is not a "stand alone" statement. It is proposed as an answer to a question. The question is:
"Which of these statements regarding read-only transactions in PL/SQL are correct?"
A "read-only transaction" only exists when you run a piece of code that includes the SET TRANSACTION READ ONLY statement. So I feel that the context of this question (in which you must evaluate each of the choices) is quite clear: the block of code containing SET TRANSACTION READ ONLY must compile and execute to be a read-only transaction.
PART 2
ReplyDelete2. I say "anywhere in your PL/SQL block", but transactions are connected to a session and not a block. In addition, many players offers scenarios in which the SET TRANSACTION was not the first statement in the block. Great points, but they do not mean that the choice should be marked as correct. This choice can only be correct if I can put the SET TRANSACTION READ ONLY anywhere in my block no matter where my SQL statements are and still have a valid read-only transaction. This is clearly NOT TRUE. If I put SET TRANSACTION READ ONLY after an update statement, Oracle will raise an error when I try to execute the block. So this very broad claim of "can appear anywhere" is clearly false.
In fact, now that I think about it, I don't even say "can appear anywhere in the execution section of the PL/SQL block," so this choice can easily be rejected as correct, since you certainly can't put the statement in the declaration section of the block.
To conclude: I don't see any way to justify marking this choice as correct. And I did not. It is incorrect. There is no reason for me to change the way I scored this quiz
There were many comments in this thread; it is late (12:30 AM) and I am exhausted from a full day of training (and waking up at 4 AM yesterday). So I may have missed something. Please let me know me if you made a comment that you think is left unaddressed.
Warm regards, Steven
I know I'm late on here, but if this wording was put into question, then you need to question the entire documentation library - because it's exactly how it reads. There is no issue!
ReplyDeleteIudith - this topic is found in Ch6 of the PL/SQL user's guide, so I don't think we're crossing boundaries.
> "I don't see any way to justify marking this choice as correct"
ReplyDeleteKnowledge doesn't count. Guessing of mood counts. It's a lotto. The further the more uninteresting.
Vitaliy,
ReplyDelete> Knowledge doesn't count. Guessing of mood counts.
Most probably neither will your employer fire you nor will your customers disregard you because you weren't 100% correct in the PL/SQL Challenge. Most probably (hopefully) you weren't planning to put a "One-time monthly winner in the weekly PL/SQL Challenge" in your CV.
So - why be so dogged about this?
It may be a matter of attitude, but when I got an answer wrong (like I did yesterday) because of a possible ambiguity, I can tell myself "I know what I know and I know I understood the topic completely". And when I see that I WAS wrong, then I'll be happy because I've gained experience. And then I get back to work, where knowledge that counts is more than just checking the right box.
This quiz is a win/win for me. A lotto isn't.
So far for my .02 €,
Uwe
Steven, although I understand that it is not the reason you scored this choice as you did, arguing that this choice is incorrect because the phrase “statement can appear anywhere in your PL/SQL block” does not exclude the declaration section is disingenuous and scoring responses on such a basis would take the PL/SQL Challenge into the realm of trick questions. If discussions of executable statements are not implicitly limited to the executable portions of PL/SQL blocks then I am certain that there are other quizzes that should have been scored differently.
ReplyDeleteThe original choice was stated as follows (emphasis mine):
"The SET TRANSACTION READ ONLY statement can appear anywhere in your PL/SQL block, regardless of the location of SQL statements in that block."
There is absolutely nothing in the wording that limits the type of SQL statement; therefore, the statement is true if there is at least one case in which the location of SET TRANSACTION READ ONLY is independent of the location of some SQL statement. Previous comments in this thread have already demonstrated that a plain SELECT (without FOR UPDATE) can be positioned anywhere relative to SET TRANSACTION READ ONLY and will not raise an exception or a compile error so long as SET TRANSACTION READ ONLY does not occur within an open transaction.
I understood the intent of the choice, and chose not to select it; however, it is possible (even likely) others may have evaluated it as I have just described. The question asked "which of these statements…are correct?" No other criteria were stated (although the implicit requirement that it compile and execute without raising an exception should be apparent to everyone). From a strictly literal and logical perspective, those who selected this choice are correct.
Dennis, SET TRANSACTION READ ONLY, and any other statements that can be directly executed in PL/SQL, should not be wrapped inside EXECUTE IMMEDIATE. There are several reasons for this, chief among them being that it prevents the compiler from identifying errors in the wrapped statement. EXECUTE IMMEDIATE should be reserved for dynamic SQL or SQL that cannot be handled directly in PL/SQL, such as DDL.
I think, Jhall62, that we will have to agree to disagree. I do not interpret the statement the way that you do. I do not see how identifying a single scenario where the STRO statement can appear a SQL statement, like a select, makes that choice correct.
ReplyDeleteSF
Hello All,
ReplyDeleteRelative to Scott's remark:
Yes, the READ ONLY transactions are treated in the PL/SQL User Guide, just like most of the DML statements are presented, but it is still not a "pure" (or typical) PL/SQL topic.
I understand the arguments of both parts,
but I think that maybe a final argument for considering this choice as INCORRECT is the following simple one:
There are countless scenarios (pl/sql blocks)
where you CAN really put SET TRANSACTION anywhere in the executable part (ex.blocks running in a session that issues SELECT-s only), as well as there are countless scenarios in which putting SET TRANSACTION anywhere will NOT work.
The "spirit" of the choice was probably this:
"SET TRANSACTION will ALWAYS work in ANY pl/sql block, wherever in the block I place it."
This is clearly NOT true, so I think that ultimately scoring it as INCORRECT was the right thing to do.
Best Regards,
Iudith Mentzel