11 November 2010

Ambgiuous reference to "storage clause" in 8 November quiz (1641)

In the November 8 quiz, we scored the following statement as correct: "When you use a nested table type as a column datatype, you must also include a storage clause for that column." P.H. wrote to say that he disagreed, as he could create a nested table as follows:
create table t_test_nt_3
          col1    arr_numbers,
          col2    arr_numbers
 nested table col1 store as t_test_nt_3$col1
    storage (initial 1m next 1m),
 nested table col2 store as t_test_nt_3$col2;
Well, yes, you can do that. But what I meant by "storage clause" was the entire STORE AS...[STORAGE ...] clause. I can certainly see, however, why this term was not clear enough, and left the quiz (and me) vulnerable to a charge of ambiguity- unless Oracle itself was very clear on this topic in its documentation. Unfortunately, it is not. I found two references to "storage clause" as the entire STORE AS ... STORAGE part of the column declaration: http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28371/adobjcol.htm#sthref461 http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28371/adobjdes.htm#sthref834 These pages support my use of the term "storage clause" and my scoring of the choice as correct. This link, http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28371/adobjcol.htm#sthref450, however, states the following: "Elements of a nested table are actually stored in a separate storage table.... Oracle stores nested table data in a single storage table associated with the object table for both nested table types that are columns in a relational table or attributes in an object table. The storage table contains a column that identifies the parent table row or object that each element of the nested table belongs to....The NESTED TABLE..STORE AS clause specifies storage names for nested tables. Storage names are used to create an index on a nested table." If this last page had definitively referred to the whole "NESTED TABLE...STORE AS" as the "storage clause," I would reject P.H.'s claim of ambiguity. But I cannot do this. So I will give everyone credit for a correct answer on this choice, and change the text to make it unambiguous. I am currently in Dallas for the Oracle PL/SQL Programming/APEXposed conferences, so I may not update scores and ranks until I get back home on Friday. Congratulations to P.H., the only player who noticed this ambiguity and who wins an O'Reilly Media ebook.


  1. It is funny that you refer to the 11g documentation while discussing 10g database. 10g documentation sounds somewhat differently:
    "In Example 3-4, the NESTED TABLE clause specifies the storage name for the nested table. The storage name is used when creating an index on a nested table. ".

  2. This one nearly threw me; however, because "storage" was not in uppercase I took it to refer to the general requirement to declare how a nested table is stored and not a specific requirement to include a STORAGE clause. Without this visual clue I would likely have rejected this answer.

  3. Hello All,
    Nice to hear that others also raised objections related to this question.
    I naturally thought of storage as "physical storage", as this term is usually used for
    and then, of course, it IS NOT mandatory.

    On the other hand, I find it strange enough that Oracle is NOT consistent with its usual practice of using system-generated default
    names for such entities that "should always exist" in a given context.
    It does this for some hidden columns in the context of using nested tables, when naming constraints, table partitions, a.s.o.
    So, since the "separate storage table" for the nested column should always exist,
    I was almost sure that Oracle automatically generates a table name for it, if one is not explicitly specified by the statement.
    Anyway, a term other than "storage" table
    for this entity would have been welcome .

    Best Regards,
    Iudith Mentzel

  4. I haven't marked it as correct, because in the 10g SQL Reference under the common ddl clauses there is a clause call "storage_clause", which only referres to the "STORAGE (...)".

  5. I think Steven will always have an uphill battle with these vague ambiguities. A few times this week (like with the trigger question) I erred on the wording - unfortunately I guessed the wrong direction. Oh well, I'm learning.