20 October 2010

Questions regarding DELETE from the 19 October quiz (1522)

This quiz explored the use of the DELETE method, which can remove one, multiple or all elements from a collection. It was also one of those rare quizzes in which all choices were correct, at least according to me. One player disagreed. He objected to my scoring on the following choice, in which I specify low and high values for DELETE that are not defined in the collection:
DECLARE
  TYPE numbers_t IS TABLE OF NUMBER
      INDEX BY PLS_INTEGER;l_numbers   numbers_t;
BEGIN
  l_numbers (1) := 3;
  l_numbers (2000) := 2;
  l_numbers (-70990) := 1;

  l_numbers.delete (-100000, 100000);

  DBMS_OUTPUT.put_line (l_numbers.COUNT);
END;
/
In other words, I specified the low and high values in the DELETE range to encompass the defined index values. This code does remove all elements, but a player wrote to say: "I'd like to question the validity of the second answer of the quiz for 10/19/2010 - l_numbers.DELETE (-100000, 100000); According to Oracle Documentation - 'DELETE(m,n) deletes all elements whose subscripts are in the range m..n, if both m and n exist and m <= n; otherwise, it does nothing.' Values -100000 and 100000 do not exist hence the DELETE method should do nothing." I have three replies to this: 1. The DELETE method clearly does not match that behavior and never did. Even if the low and high values do not point to defined index values, Oracle will remove elements within that range. 2. This behavior, and not what the player has found in the documentation, is consistent with the SQL DELETE behavior and a BETWEEN in the where clause. 3. I found the following in Oracle documentation:

Deleting Collection Elements (DELETE Method)

This procedure has various forms:
  • DELETE with no parameters removes all elements from a collection, setting COUNT to 0.
  • DELETE(n) removes the nth element from an associative array with a numeric key or a nested table. If the associative array has a string key, the element corresponding to the key value is deleted. If n is null, DELETE(n) does nothing.
  • DELETE(m,n) removes all elements in the range m..n from an associative array or nested table. If m is larger than n or if m or n is null, DELETE(m,n) does nothing.
This text says nothing about m and n having to be defined.

7 comments:

  1. Doesn't delete(n) remove the element with subscript n rather than the nth element?

    ReplyDelete
  2. Doesn't delete(n) remove the element with subscript n rather than the nth element?

    ReplyDelete
  3. I'd hazard the guess, that Oracle fixed a "doc bug"?

    Player quotes documentation:

    "DELETE(m,n) deletes all elements whose subscripts are in the range m..n, if both m and n exist and m <= n; otherwise, it does nothing."

    Steven quotes documentation:

    "DELETE(m,n) removes all elements in the range m..n from an associative array or nested table. If m is larger than n or if m or n is null, DELETE(m,n) does nothing."

    They seem to have changed the wording "m and n exist" to a wording about whether m and n is null. Probably the original documentation writer meant to say "is not null" when he wrote "exist" :-)

    ReplyDelete
  4. The way I read "If m and n exist" was "if you have specified values for m and n". I hadn't considered that it was an ambiguous wording until reading this blog entry!

    ReplyDelete
  5. Hi Steve,

    just from curiosity - does that player pointed to the exact place in the documentation that supports his point of view?

    Regards,
    Oleksandr

    ReplyDelete
  6. He read it in 11g Doc http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/composites.htm#CJAFGFIG
    Behaviour of DELETE doesn't changed. But 11g documentation states something strange.

    ReplyDelete
  7. H-m-m, yes. But it says "if both m and n exist", not "if both ELEMENTS WITH indexes m and n exist".
    Former likely to be interpreted "if both m and n are not null". So this is the most likely a pure editorial change - while bad one.

    BTW, it is present in 11.2 only - in 11.1 it is in the old way.

    ReplyDelete