Oracle Collections

Page 176

Other New PL/SQL Packages

Oracle Database 10g comes with a number of other new PL/SQL packages, listed next, many of which are discussed in other chapters of this book:

dbms_advanced_rewrite	dbms_advisor	dbms_datapump
dbms_dimension	dbms_file_transfer	dbms_frequent_itemset
dbms_frequent_itemset	dbms_monitor	dbms_scheduler
dbms_server_alert	dbms_service	dbms_sqltune
dbms_stat_funcs	dbms_streams_auth	dbms_streams_messaging
dbms_streams_tablespace_adm	dbms_warning	dbms_xmlstore
utl_dbws	utl_i18n	utl_lms
utl_recomp

Oracle collections consist of two different structures, nested tables and VARRAY columns. Oracle Database 10g offers the following new functionality for Oracle collections:

The ability to create temporary tables with VARRAY columns

The ability to change the element size of a VARRAY column

The ability to define the tablespace of a nested table

New ANSI-related functionality within nested tables

Creating Temporary Tables with VARRAY Columns

In Oracle Database 10g, you can now include VARRAY columns in a temporary table. Here is an example of such an operation:

 Create type ty_cust_info as object ( first_name  varchar2(30),  last_name   varchar2(30),   address     varchar2(30),  city        varchar2(30),   state       varchar2(2),  zip         varchar2(5) ) / create type nt_cust_info as varray(10) of ty_cust_info; / create global temporary table t_cust_info