Well, that was confusing - and many players wrote to tell us so. Our apologies!
The explanation was incomplete. We have now added the following text:
The reason....that the correct answer displays "NDF" is that Oracle also raises the NO_DATA_FOUND exception when you try to "read" an element in a collection at an undefined index value. As a result, when Oracle executes this line:
sys.DBMS_OUTPUT.put_line (tab (1).dummy);
NO_DATA_FOUND is raised, since the collection is empty (there is no element at index value 1).
Hopefully all is now clear. Thanks for "keeping us honest."
"if no data is fetched ..., then the value(s) of the variable(s) in the INTO clause are not changed."
ReplyDelete"not changed" is common wrong belief.
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/fetch_statement.htm#sthref2728
:
"If you FETCH past the last row in the result set, the values of the target fields or variables are INDETERMINATE"
So, again we played dirty quiz.
@Vitaliy
ReplyDeletedeclare
cursor e is
select ename from emp where 1 = 2;
v_name emp.ename%type;
begin
v_name := 'Oracle';
open e;
fetch e into v_name;
close e;
dbms_output.put_line(v_name);
end;
In the above example dbms_output will show Oracle
declare
cursor e is
select ename from emp where rownum = 1;
v_name emp.ename%type;
begin
v_name := 'Oracle';
open e;
Loop
fetch e into v_name;
dbms_output.put_line(v_name);
exit when e%notfound;
end loop;
dbms_output.put_line(v_name);
close e;
end;
In the above example here dbms_output will show SMITH
SMITH
SMITH
so we can use "not changed" and its good example only.... :-)
Vitaliy raises a good point. We can deduce from repeated testing under various conditions what state the variables are left in when fetching past the last row, but since the behaviour is technically undefined, good code should not rely on the variables having any particular state.
ReplyDeleteHowever, the quiz is good value because it's worth knowing what would happen in this situation.
@Gajendran
ReplyDelete"indeterminate" is "depend on implementation"
For example, implementation of select_into+to_many_rows was changed
in (approximately) 8i
In both cases documentation is not silent. It explicitly warn against relying on the value in both cases.
So why we should use unpredictable scenarios? And demonstrate it as proving anything?
Hi,
ReplyDeleteIt seems that the fact tabtype is indexed by binary_integer changes Oracle's bevahior at two points. Could sy point me to the reason for this?
I.e., if I omit "index by binary_integer", Oracle would:
a) raise
ORA-06531: Reference to uninitialized collection
at tab (1).dummy := 'Z'. That is, Oracle does not enforce us to initialize the nested table when indexed by binary_integer, but does enforce when indexing type is not explicitly defined.
b) After proper initialization of the collection, and "fetch bulk collect"ing from the empty cursor, Oracle would raise
ORA-06533: Subscript beyond count
at sys.DBMS_OUTPUT.put_line (tab (1).dummy);
Below is the code based on the quiz's code block I used on 11gR2 to demonstrate this.
DECLARE
CURSOR no_data
IS
SELECT dummy
FROM DUAL
WHERE 1 = 2;
var VARCHAR2 (1);
TYPE tabtype IS TABLE OF no_data%ROWTYPE ;
-- INDEX BY BINARY_INTEGER; -- removed for now
tab tabtype;
my_record no_data%rowtype;
BEGIN
var := 'Z';
OPEN no_data;
FETCH no_data INTO var;
CLOSE no_data;
sys.DBMS_OUTPUT.put_line (NVL (var, 'null'));
--The following line would raise
-- ORA-06531: Reference to uninitialized collection.
--tab (1).dummy := 'Z';
--So I initialize this collection in some other way
my_record.dummy := 'Z';
tab := tabtype(my_record);
--At this point, tab (1).dummy = 'Z' holds
OPEN no_data;
FETCH no_data BULK COLLECT INTO tab;
CLOSE no_data;
--As FETCH (from empty cursor) BULK COLLECT cleans the collection, this raises now
-- ORA-06533: Subscript beyond count
sys.DBMS_OUTPUT.put_line (tab (1).dummy);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
sys.DBMS_OUTPUT.put_line ('NDF');
END;
/
-- Thanks, Jozsef
Jozsef, when a TABLE OF collection is not indexed, it is a nested table. When you add an index, it is no longer a nested table but rather an associative array. These constructs operate quite differently, as you demonstrate.
ReplyDeleteYou may want to read up on the differences here (Oracle 10.2 documentation): http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/collections.htm#i26701
This difference in behaviour has also been the topic of several previous quizzes here.
"If you FETCH past the last row in the result set, the values of the target fields or variables are INDETERMINATE" disappeared from the 11.2 documentation.
ReplyDelete@al0
ReplyDeleteBut "variables are not changed" is still not documented. It is just belief in what we see.
I agree with Vitaliy. While we can test it in specific cases, there might be other cases with different behaviour (different OS or x86/Sparc, LOBs may behave differently, maybe a select through a db link to a non-Oracle DB...)
ReplyDeleteI have given this lots of thought and here's where I end up: no change to the scoring or ranking on this quiz.
ReplyDeleteHere is my rationale:
Yes, in Oracle 10g documentation, Oracle says that the value set in the variable is indeterminate. Yet, in the 11g documentation, no such statement. Did they remove it because in fact the value is and always has been determinate? Or, here's another question: does it really matter?
In all my years of working with Oracle, I have never seen a FETCH do anything but leave the value in the variable unchanged when I fetch past the end of the result set. Have any of you ever seen anything else? Can you now produce a block of code in any OS or with any datatype that does not follow this behavior?
One might argue (I am, in fact, quite certain that Vitaliy will do so) that such "anecdotal" stories do not "prove" anything. Perhaps...but what do I need to prove?
The quiz's question asks "What output is displayed after the following block of code is executed?" It does not ask "What does Oracle guarantee in its documentation that the code will display?" I believe that the output after the question's block is executed will always yield the answer that we scored as correct, regardless of what Oracle is willing to commit to in its documentation. So from the standpoint of a PL/SQL developer, working in the real world and not the theoretical world of documentation, this is, in fact, what will happen when we run our code.
And it is certainly worth knowing that, regardless of what Oracle says in its documentation, this is the behavior we will see. It could, for example, help a developer understand the behavior of, and more rapidly fix a bug in, her code. It is also worth knowing that we should not rely on that value. That is, after fetching, we should check the value of %NOTFOUND and if true, we do nothing with that variable (I will update the answer to point this out).
So until someone can come up with an example demonstrating the "indeterminate" nature of this action, I will leave the scoring as is. Since the quarter just started, you have close to 3 months to produce an example so that I can make an adjustment before the next playoff.