PL/SQL   «Prev 

Inserting a Row into a varray

  1. With SQL*Plus, connect to the database by using PETSTORE as the username, GREATPETS as the password, and MYDB as the host string.
  2. You are now connected to the database. Next, build an INSERT statement to insert a record into the varray within the CUSTOMER_OBJ_TABLE table. The values for the record are as follows: cust_id is 56, name of the person is Norma Grey residing at 100 South East Main Burbank CA 65443 having the following phone numbers: 1-800-876-3243 and 1-714-785-8763. Joan last updated the record on 12/22/99.
  3. SQL*Plus now displays the result of the DML statement.


Collection Types

The VARRAY and NESTED TABLE collections can be defined as both SQL and PL/SQL datatypes. As SQL datatypes, they are single-dimensional arrays of scalar values or object types. They can also define user-specified column datatypes. Both the VARRAY and NESTED TABLE datatypes are structures indexed by sequential integers (using 1-based integers). Sequentially indexed structures disallow gaps in the index values, and are also known as densely populated structures. While the VARRAY has a fixed number of elements when defined, the NESTED TABLE does not.
Oracle varray and nested table collections are indexed by 1-based numbering.

Associative array

The associative array, previously known as a PL/SQL table, is only a PL/SQL datatype. Associative array datatypes can only be referenced in a PL/SQL scope. They are typically defined in package specifications when they are to be used externally from an anonymous or named block program. Associative array datatypes support both numeric and string indexes. Numeric indexes for associative arrays do not need to be sequential and are non-sequential structures. Non-sequential structures can have gaps in index sequences and are known as sparsely populated structures. Associative arrays are dynamically sized and, like the NESTED TABLE datatype, have no fixed size. All three have access to the Oracle Collection API, but each uses a different set of methods. The recent changes to OCI8 enable it to support scalar, arrays of scalar, and reference cursor variables to external languages, like C, C++, C#, Java, and PHP. The VARRAY and NESTED TABLE datatypes require that you use the OCI-Collection class to access them externally from the SQL*Plus environment. OCI8 also has a new function that supports passing by reference a PL/SQL table.