Skip to Main Content
Oracle PL/SQL Programming: A Developer's Workbook
book

Oracle PL/SQL Programming: A Developer's Workbook

by Steven Feuerstein, Andrew Odewahn
May 2000
Intermediate to advanced content levelIntermediate to advanced
594 pages
11h 32m
English
O'Reilly Media, Inc.
Content preview from Oracle PL/SQL Programming: A Developer's Workbook

Chapter 7. Nested Tables

Beginner

Q:

7-1.

The syntax for creating a nested table is similar to that for an index-by table:

TYPE type name IS TABLE OF element datatype [NOT NULL];

For example, here’s the command to create a nested table type called a_simple_udt_t; its elements consist of numeric data, each with no more than 10 digits:

CREATE TYPE a_simple_udt_t IS TABLE OF NUMBER(10);

Q:

7-2.

The statements are:

  1. True. Nested table types can indeed be used as datatypes for Oracle tables as in this DDL:

    SQL> CREATE TABLE a_table (
      2    col1 number,
      3    col2 a_simple_udt_t )
      4  NESTED TABLE col2 STORE AS a_table_col2;
    
    Table created.

    The NESTED TABLE clause specifies the name of the table used to store the data in the nested column.

  2. True. Whether a nested table type is used as a column in a table or in PL/SQL itself, there is no limit to the number of rows it can store.

  3. False. All rows in a nested table must contain the same type of data.

  4. True. Nested table types are stored in the database. To view information about the nested table types in a database including their structure, use the data dictionary views ALL_TYPES and ALL_TYPE_ATTRS.

  5. False. Nested table types can be based on user-defined data types as well as Oracle’s native data types. For example, you can create an object as:

    SQL> CREATE TYPE an_object_t AS OBJECT (
      2     column_one VARCHAR2(30),
      3     column_two NUMBER(10,2) );
    
    Type created.

    And then create a nested table type based on the object as:

    SQL> CREATE TYPE a_nested_table_t AS TABLE OF an_object_t; ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle Database 12c PL/SQL Programming

Oracle Database 12c PL/SQL Programming

Michael McLaughlin
Oracle PL/SQL for DBAs

Oracle PL/SQL for DBAs

Arup Nanda, Steven Feuerstein
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 9781449324070Supplemental ContentErrata Page