Varchar2 Limit In Pl/Sql What Is The Assignment Operator

5 PL/SQL Collections and Records

A composite data type stores values that have internal components. You can pass entire composite variables to subprograms as parameters, and you can access internal components of composite variables individually. Internal components can be either scalar or composite. You can use scalar components wherever you can use scalar variables. PL/SQL lets you define two kinds of composite data types, collection and record. You can use composite components wherever you can use composite variables of the same type.

Note:

If you pass a composite variable as a parameter to a remote subprogram, then you must create a redundant loop-back , so that when the remote subprogram compiles, the type checker that verifies the source uses the same definition of the user-defined composite variable type as the invoker uses. For information about the statement, see Oracle Database SQL Language Reference.

In a collection, the internal components always have the same data type, and are called elements. You can access each element of a collection variable by its unique index, with this syntax: . To create a collection variable, you either define a collection type and then create a variable of that type or use .

In a record, the internal components can have different data types, and are called fields. You can access each field of a record variable by its name, with this syntax: . To create a record variable, you either define a type and then create a variable of that type or use or .

You can create a collection of records, and a record that contains collections.

Collection Topics

Record Topics

Note:

Several examples in this chapter define procedures that print their composite variables. Several of those procedures invoke this standalone procedure, which prints either its integer parameter (if it is not ) or the string : CREATE OR REPLACE PROCEDURE print (n INTEGER) IS BEGIN IF n IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE(n); ELSE DBMS_OUTPUT.PUT_LINE('NULL'); END IF; END print; /

Some examples in this chapter define functions that return values of composite types.

You can understand the examples in this chapter without completely understanding PL/SQL procedures and functions, which are explained in Chapter 8, "PL/SQL Subprograms".

Collection Types

PL/SQL has three collection types—associative array, (variable-size array), and nested table. Table 5-1 summarizes their similarities and differences.

Table 5-1 PL/SQL Collection Types

Collection TypeNumber of Elements
Index TypeDense or Sparse
Uninitialized Status
Where Defined
Can Be ADT Attribute Data Type

Associative array (or index-by table)

Unspecified

String or

Either

Empty

In PL/SQL block or package

No

(variable-size array)

Specified

Integer

Always dense

Null

In PL/SQL block or package or at schema level

Only if defined at schema level

Nested table

Unspecified

Integer

Starts dense, can become sparse

Null

In PL/SQL block or package or at schema level

Only if defined at schema level


Number of Elements

If the number of elements is specified, it is the maximum number of elements in the collection. If the number of elements is unspecified, the maximum number of elements in the collection is the upper limit of the index type.

Dense or Sparse

A dense collection has no gaps between elements—every element between the first and last element is defined and has a value (the value can be unless the element has a constraint). A sparse collection has gaps between elements.

Uninitialized Status

An empty collection exists but has no elements. To add elements to an empty collection, invoke the method (described in "EXTEND Collection Method").

A null collection (also called an atomically null collection) does not exist. To change a null collection to an existing collection, you must initialize it, either by making it empty or by assigning a non- value to it (for details, see "Collection Constructors" and "Assigning Values to Collection Variables"). You cannot use the method to initialize a null collection.

Where Defined

A collection type defined in a PL/SQL block is a local type. It is available only in the block, and is stored in the database only if the block is in a standalone or package subprogram. (Standalone and package subprograms are explained in "Nested, Package, and Standalone Subprograms".)

A collection type defined in a package specification is a public item. You can reference it from outside the package by qualifying it with the package name (). It is stored in the database until you drop the package. (Packages are explained in Chapter 10, "PL/SQL Packages.")

A collection type defined at schema level is a standalone type. You create it with the "CREATE TYPE Statement". It is stored in the database until you drop it with the "DROP TYPE Statement".

Note:

A collection type defined in a package specification is incompatible with an identically defined local or standalone collection type (see Example 5-31 and Example 5-32).

Can Be ADT Attribute Data Type

To be an ADT attribute data type, a collection type must be a standalone collection type. For other restrictions, see Restrictions on datatype.

Translating Non-PL/SQL Composite Types to PL/SQL Composite Types

If you have code or business logic that uses another language, you can usually translate the array and set types of that language directly to PL/SQL collection types. For example:

Non-PL/SQL Composite TypeEquivalent PL/SQL Composite Type
Hash tableAssociative array
Unordered tableAssociative array
SetNested table
BagNested table
Array

See Also:

Oracle Database SQL Language Reference for information about the function, which converts one SQL data type or collection-typed value into another SQL data type or collection-typed value.

Associative Arrays

An associative array (formerly called PL/SQL table or index-by table) is a set of key-value pairs. Each key is a unique index, used to locate the associated value with the syntax .

The data type of can be either a string type or . Indexes are stored in sort order, not creation order. For string types, sort order is determined by the initialization parameters and .

Like a database table, an associative array:

  • Is empty (but not null) until you populate it

  • Can hold an unspecified number of elements, which you can access without knowing their positions

Unlike a database table, an associative array:

  • Does not need disk space or network operations

  • Cannot be manipulated with DML statements

Example 5-1 defines a type of associative array indexed by string, declares a variable of that type, populates the variable with three elements, changes the value of one element, and prints the values (in sort order, not creation order). ( and are collection methods, described in "Collection Methods".)

Example 5-1 Associative Array Indexed by String

DECLARE -- Associative array indexed by string: TYPE population IS TABLE OF NUMBER -- Associative array type INDEX BY VARCHAR2(64); -- indexed by string city_population population; -- Associative array variable i VARCHAR2(64); -- Scalar variable BEGIN -- Add elements (key-value pairs) to associative array: city_population('Smallville') := 2000; city_population('Midland') := 750000; city_population('Megalopolis') := 1000000; -- Change value associated with key 'Smallville': city_population('Smallville') := 2001; -- Print associative array: i := city_population.FIRST; -- Get first element of array WHILE i IS NOT NULL LOOP DBMS_Output.PUT_LINE ('Population of ' || i || ' is ' || city_population(i)); i := city_population.NEXT(i); -- Get next element of array END LOOP; END; /

Result:

Population of Megalopolis is 1000000 Population of Midland is 750000 Population of Smallville is 2001

Example 5-2 defines a type of associative array indexed by and a function that returns an associative array of that type.

Example 5-2 Function Returns Associative Array Indexed by PLS_INTEGER

DECLARE TYPE sum_multiples IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER; n PLS_INTEGER := 5; -- number of multiples to sum for display sn PLS_INTEGER := 10; -- number of multiples to sum m PLS_INTEGER := 3; -- multiple FUNCTION get_sum_multiples ( multiple IN PLS_INTEGER, num IN PLS_INTEGER ) RETURN sum_multiples IS s sum_multiples; BEGIN FOR i IN 1..num LOOP s(i) := multiple * ((i * (i + 1)) / 2); -- sum of multiples END LOOP; RETURN s; END get_sum_multiples; BEGIN DBMS_OUTPUT.PUT_LINE ( 'Sum of the first ' || TO_CHAR(n) || ' multiples of ' || TO_CHAR(m) || ' is ' || TO_CHAR(get_sum_multiples (m, sn)(n)) ); END; /

Result:

Sum of the first 5 multiples of 3 is 45

Topics

Declaring Associative Array Constants

When declaring an associative array constant, you must create a function that populates the associative array with its initial value and then invoke the function in the constant declaration, as in Example 5-3. (The function does for the associative array what a constructor does for a varray or nested table. For information about constructors, see "Collection Constructors".)

Example 5-3 Declaring Associative Array Constant

CREATE OR REPLACE PACKAGE My_Types AUTHID DEFINER IS TYPE My_AA IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER; FUNCTION Init_My_AA RETURN My_AA; END My_Types; / CREATE OR REPLACE PACKAGE BODY My_Types IS FUNCTION Init_My_AA RETURN My_AA ISRet My_AA;BEGINRet(-10) := '-ten';Ret(0) := 'zero';Ret(1) := 'one';Ret(2) := 'two';Ret(3) := 'three';Ret(4) := 'four';Ret(9) := 'nine';RETURN Ret;END Init_My_AA; END My_Types; / DECLARE v CONSTANT My_Types.My_AA := My_Types.Init_My_AA(); BEGIN DECLARE Idx PLS_INTEGER := v.FIRST(); BEGIN WHILE Idx IS NOT NULL LOOP DBMS_OUTPUT.PUT_LINE(TO_CHAR(Idx, '999')||LPAD(v(Idx), 7)); Idx := v.NEXT(Idx); END LOOP; END; END; /

Result:

-10 -ten 0 zero 1 one 2 two 3 three 4 four 9 nine PL/SQL procedure successfully completed.

NLS Parameter Values Affect Associative Arrays Indexed by String

National Language Support (NLS) parameters such as , , and affect associative arrays indexed by string.

Topics

Changing NLS Parameter Values After Populating Associative Arrays

The initialization parameters and determine the storage order of string indexes of an associative array. If you change the value of either parameter after populating an associative array indexed by string, then the collection methods , , , and (described in "Collection Methods") might return unexpected values or raise exceptions. If you must change these parameter values during your session, restore their original values before operating on associative arrays indexed by string.

Indexes of Data Types Other Than VARCHAR2

In the declaration of an associative array indexed by string, the string type must be or one of its subtypes. However, you can populate the associative array with indexes of any data type that the function can convert to . (For information about , see Oracle Database SQL Language Reference.)

If your indexes have data types other than and its subtypes, ensure that these indexes remain consistent and unique if the values of initialization parameters change. For example:

  • Do not use as an index.

    If the value of changes, then the value of might also change.

  • Do not use different indexes that might be converted to the same value.

  • Do not use or indexes that differ only in case, accented characters, or punctuation characters.

    If the value of ends in (case-insensitive comparisons) or (accent- and case-insensitive comparisons), then indexes that differ only in case, accented characters, or punctuation characters might be converted to the same value.

Passing Associative Arrays to Remote Databases

If you pass an associative array as a parameter to a remote database, and the local and the remote databases have different or values, then:

  • The collection method , , or (described in "Collection Methods") might return unexpected values or raise exceptions.

  • Indexes that are unique on the local database might not be unique on the remote database, raising the predefined exception .

Appropriate Uses for Associative Arrays

An associative array is appropriate for:

  • A relatively small lookup table, which can be constructed in memory each time you invoke the subprogram or initialize the package that declares it

  • Passing collections to and from the database server

    Declare formal subprogram parameters of associative array types. With Oracle Call Interface (OCI) or an Oracle precompiler, bind the host arrays to the corresponding actual parameters. PL/SQL automatically converts between host arrays and associative arrays indexed by .

    Note:

    You cannot declare an associative array type at schema level. Therefore, to pass an associative array variable as a parameter to a standalone subprogram, you must declare the type of that variable in a package specification. Doing so makes the type available to both the invoked subprogram (which declares a formal parameter of that type) and the invoking subprogram or anonymous block (which declares and passes the variable of that type). See Example 10-2.

    Tip:

    The most efficient way to pass collections to and from the database server is to use associative arrays with the statement or clause. For details, see "FORALL Statement" and "BULK COLLECT Clause".

An associative array is intended for temporary data storage. To make an associative array persistent for the life of a database session, declare it in a package specification and populate it in the package body.

Varrays (Variable-Size Arrays)

A varray (variable-size array) is an array whose number of elements can vary from zero (empty) to the declared maximum size. To access an element of a varray variable, use the syntax . The lower bound of is 1; the upper bound is the current number of elements. The upper bound changes as you add or delete elements, but it cannot exceed the maximum size. When you store and retrieve a varray from the database, its indexes and element order remain stable.

Figure 5-1shows a varray variable named , which has maximum size 10 and contains seven elements. () references the nth element of . The upper bound of is 7, and it cannot exceed 10.

The database stores a varray variable as a single object. If a varray variable is less than 4 KB, it resides inside the table of which it is a column; otherwise, it resides outside the table but in the same tablespace.

An uninitialized varray variable is a null collection. You must initialize it, either by making it empty or by assigning a non- value to it. For details, see "Collection Constructors" and "Assigning Values to Collection Variables".

Example 5-4 defines a local type, declares a variable of that type (initializing it with a constructor), and defines a procedure that prints the varray. The example invokes the procedure three times: After initializing the variable, after changing the values of two elements individually, and after using a constructor to the change the values of all elements. (For an example of a procedure that prints a varray that might be null or empty, see Example 5-24.)

Example 5-4 Varray (Variable-Size Array)

DECLARE TYPE Foursome IS VARRAY(4) OF VARCHAR2(15); -- VARRAY type -- varray variable initialized with constructor: team Foursome := Foursome('John', 'Mary', 'Alberto', 'Juanita'); PROCEDURE print_team (heading VARCHAR2) IS BEGIN DBMS_OUTPUT.PUT_LINE(heading); FOR i IN 1..4 LOOP DBMS_OUTPUT.PUT_LINE(i || '.' || team(i)); END LOOP; DBMS_OUTPUT.PUT_LINE('---'); END; BEGIN print_team('2001 Team:'); team(3) := 'Pierre'; -- Change values of two elements team(4) := 'Yvonne'; print_team('2005 Team:'); -- Invoke constructor to assign new values to varray variable: team := Foursome('Arun', 'Amitha', 'Allan', 'Mae'); print_team('2009 Team:'); END; /

Result:

2001 Team: 1.John 2.Mary 3.Alberto 4.Juanita --- 2005 Team: 1.John 2.Mary 3.Pierre 4.Yvonne --- 2009 Team: 1.Arun 2.Amitha 3.Allan 4.Mae ---

Topics

Appropriate Uses for Varrays

A varray is appropriate when:

  • You know the maximum number of elements.

  • You usually access the elements sequentially.

Because you must store or retrieve all elements at the same time, a varray might be impractical for large numbers of elements.

Nested Tables

In the database, a nested table is a column type that stores an unspecified number of rows in no particular order. When you retrieve a nested table value from the database into a PL/SQL nested table variable, PL/SQL gives the rows consecutive indexes, starting at 1. Using these indexes, you can access the individual rows of the nested table variable. The syntax is . The indexes and row order of a nested table might not remain stable as you store and retrieve the nested table from the database.

The amount of memory that a nested table variable occupies can increase or decrease dynamically, as you add or delete elements.

An uninitialized nested table variable is a null collection. You must initialize it, either by making it empty or by assigning a non- value to it. For details, see "Collection Constructors" and "Assigning Values to Collection Variables".

Example 5-5 defines a local nested table type, declares a variable of that type (initializing it with a constructor), and defines a procedure that prints the nested table. (The procedure uses the collection methods and , described in "Collection Methods".) The example invokes the procedure three times: After initializing the variable, after changing the value of one element, and after using a constructor to the change the values of all elements. After the second constructor invocation, the nested table has only two elements. Referencing element 3 would raise error ORA-06533.

Example 5-5 Nested Table of Local Type

DECLARE TYPE Roster IS TABLE OF VARCHAR2(15); -- nested table type -- nested table variable initialized with constructor: names Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh'); PROCEDURE print_names (heading VARCHAR2) IS BEGIN DBMS_OUTPUT.PUT_LINE(heading); FOR i IN names.FIRST .. names.LAST LOOP -- For first to last element DBMS_OUTPUT.PUT_LINE(names(i)); END LOOP; DBMS_OUTPUT.PUT_LINE('---'); END; BEGIN print_names('Initial Values:'); names(3) := 'P Perez'; -- Change value of one element print_names('Current Values:'); names := Roster('A Jansen', 'B Gupta'); -- Change entire table print_names('Current Values:'); END; /

Result:

Initial Values: D Caruso J Hamil D Piro R Singh --- Current Values: D Caruso J Hamil P Perez R Singh --- Current Values: A Jansen B Gupta

Example 5-6 defines a standalone nested table type, , and a standalone procedure to print a variable of that type, . (The procedure uses the collection methods and , described in "Collection Methods".) An anonymous block declares a variable of type , initializing it to empty with a constructor, and invokes twice: After initializing the variable and after using a constructor to the change the values of all elements.

Example 5-6 Nested Table of Standalone Type

CREATE OR REPLACE TYPE nt_type IS TABLE OF NUMBER; / CREATE OR REPLACE PROCEDURE print_nt (nt nt_type) IS i NUMBER; BEGIN i := nt.FIRST; IF i IS NULL THEN DBMS_OUTPUT.PUT_LINE('nt is empty'); ELSE WHILE i IS NOT NULL LOOP DBMS_OUTPUT.PUT('nt.(' || i || ') = '); print(nt(i)); i := nt.NEXT(i); END LOOP; END IF; DBMS_OUTPUT.PUT_LINE('---'); END print_nt; / DECLARE nt nt_type := nt_type(); -- nested table variable initialized to empty BEGIN print_nt(nt); nt := nt_type(90, 9, 29, 58); print_nt(nt); END; /

Result:

nt is empty --- nt.(1) = 90 nt.(2) = 9 nt.(3) = 29 nt.(4) = 58 ---

Topics

Important Differences Between Nested Tables and Arrays

Conceptually, a nested table is like a one-dimensional array with an arbitrary number of elements. However, a nested table differs from an array in these important ways:

  • An array has a declared number of elements, but a nested table does not. The size of a nested table can increase dynamically.

  • An array is always dense. A nested array is dense initially, but it can become sparse, because you can delete elements from it.

Figure 5-2 shows the important differences between a nested table and an array.

Appropriate Uses for Nested Tables

A nested table is appropriate when:

  • The number of elements is not set.

  • Index values are not consecutive.

  • You must delete or update some elements, but not all elements simultaneously.

    Nested table data is stored in a separate store table, a system-generated database table. When you access a nested table, the database joins the nested table with its store table. This makes nested tables suitable for queries and updates that affect only some elements of the collection.

  • You would create a separate lookup table, with multiple entries for each row of the main table, and access it through join queries.

Collection Constructors

Note:

This topic applies only to varrays and nested tables. Associative arrays do not have constructors. In this topic, collection means varray or nested table.

A collection constructor (constructor) is a system-defined function with the same name as a collection type, which returns a collection of that type. The syntax of a constructor invocation is:

collection_type ( [ value [, value ]... ] )

If the parameter list is empty, the constructor returns an empty collection. Otherwise, the constructor returns a collection that contains the specified values. For semantic details, see "collection_constructor".

You can assign the returned collection to a collection variable (of the same type) in the variable declaration and in the executable part of a block.

Example 5-7 invokes a constructor twice: to initialize the varray variable to empty in its declaration, and to give it new values in the executable part of the block. The procedure shows the initial and final values of . To determine when is empty, uses the collection method , described in "Collection Methods". (For an example of a procedure that prints a varray that might be null, see Example 5-24.)

Example 5-7 Initializing Collection (Varray) Variable to Empty

DECLARE TYPE Foursome IS VARRAY(4) OF VARCHAR2(15); team Foursome := Foursome(); -- initialize to empty PROCEDURE print_team (heading VARCHAR2) IS BEGIN DBMS_OUTPUT.PUT_LINE(heading); IF team.COUNT = 0 THEN DBMS_OUTPUT.PUT_LINE('Empty'); ELSE FOR i IN 1..4 LOOP DBMS_OUTPUT.PUT_LINE(i || '.' || team(i)); END LOOP; END IF; DBMS_OUTPUT.PUT_LINE('---'); END; BEGIN print_team('Team:'); team := Foursome('John', 'Mary', 'Alberto', 'Juanita'); print_team('Team:'); END; /

Result:

Team: Empty --- Team: 1.John 2.Mary 3.Alberto 4.Juanita ---

Assigning Values to Collection Variables

You can assign a value to a collection variable in these ways:

  • Invoke a constructor to create a collection and assign it to the collection variable, as explained in "Collection Constructors".

  • Use the assignment statement (described in "Assignment Statement") to assign it the value of another existing collection variable.

  • Pass it to a subprogram as an or parameter, and then assign the value inside the subprogram.

To assign a value to a scalar element of a collection variable, reference the element as and assign it a value as instructed in "Assigning Values to Variables".

Topics

Data Type Compatibility

You can assign a collection to a collection variable only if they have the same data type. Having the same element type is not enough.

In Example 5-8, types and have the same element type, . Collection variables and have the same data type, , but collection variable has the data type . The assignment of to succeeds, but the assignment of to fails.

Example 5-8 Data Type Compatibility for Collection Assignment

DECLARE TYPE triplet IS VARRAY(3) OF VARCHAR2(15); TYPE trio IS VARRAY(3) OF VARCHAR2(15); group1 triplet := triplet('Jones', 'Wong', 'Marceau'); group2 triplet;group3 trio; BEGIN group2 := group1; -- succeeds group3 := group1; -- fails END; /

Result:

ERROR at line 10: ORA-06550: line 10, column 13: PLS-00382: expression is of wrong type ORA-06550: line 10, column 3: PL/SQL: Statement ignored

Assigning Null Values to Varray or Nested Table Variables

To a varray or nested table variable, you can assign the value or a null collection of the same data type. Either assignment makes the variable null.

Example 5-7 initializes the nested table variable to a non-null value; assigns a null collection to it, making it null; and re-initializes it to a different non-null value.

Example 5-9 Assigning Null Value to Nested Table Variable

DECLARE TYPE dnames_tab IS TABLE OF VARCHAR2(30); dept_names dnames_tab := dnames_tab( 'Shipping','Sales','Finance','Payroll'); -- Initialized to non-null value empty_set dnames_tab; -- Not initialized, therefore null PROCEDURE print_dept_names_status IS BEGIN IF dept_names IS NULL THEN DBMS_OUTPUT.PUT_LINE('dept_names is null.'); ELSE DBMS_OUTPUT.PUT_LINE('dept_names is not null.'); END IF; END print_dept_names_status; BEGIN print_dept_names_status; dept_names := empty_set; -- Assign null collection to dept_names. print_dept_names_status; dept_names := dnames_tab ( 'Shipping','Sales','Finance','Payroll'); -- Re-initialize dept_names print_dept_names_status; END; /

Result:

dept_names is not null. dept_names is null. dept_names is not null.

Assigning Set Operation Results to Nested Table Variables

To a nested table variable, you can assign the result of a SQL operation or SQL function invocation.

The SQL operators combine two nested tables into a single nested table. The elements of the two nested tables must have comparable data types. For information about the operators, see Oracle Database SQL Language Reference.

The SQL function takes a nested table argument and returns a nested table of the same data type whose elements are distinct (the function eliminates duplicate elements). For information about the function, see Oracle Database SQL Language Reference.

Example 5-10 assigns the results of several operations and one function invocation of the nested table variable , using the procedure to print after each assignment. The procedure use the collection methods and , described in "Collection Methods".

Example 5-10 Assigning Set Operation Results to Nested Table Variable

DECLARE TYPE nested_typ IS TABLE OF NUMBER; nt1 nested_typ := nested_typ(1,2,3); nt2 nested_typ := nested_typ(3,2,1); nt3 nested_typ := nested_typ(2,3,1,3); nt4 nested_typ := nested_typ(1,2,4); answer nested_typ; PROCEDURE print_nested_table (nt nested_typ) IS output VARCHAR2(128); BEGIN IF nt IS NULL THEN DBMS_OUTPUT.PUT_LINE('Result: null set'); ELSIF nt.COUNT = 0 THEN DBMS_OUTPUT.PUT_LINE('Result: empty set'); ELSE FOR i IN nt.FIRST .. nt.LAST LOOP -- For first to last element output := output || nt(i) || ' '; END LOOP; DBMS_OUTPUT.PUT_LINE('Result: ' || output); END IF; END print_nested_table; BEGIN answer := nt1 MULTISET UNION nt4; print_nested_table(answer); answer := nt1 MULTISET UNION nt3; print_nested_table(answer); answer := nt1 MULTISET UNION DISTINCT nt3; print_nested_table(answer); answer := nt2 MULTISET INTERSECT nt3; print_nested_table(answer); answer := nt2 MULTISET INTERSECT DISTINCT nt3; print_nested_table(answer); answer := SET(nt3); print_nested_table(answer); answer := nt3 MULTISET EXCEPT nt2; print_nested_table(answer); answer := nt3 MULTISET EXCEPT DISTINCT nt2; print_nested_table(answer); END; /

Result:

Result: 1 2 3 1 2 4 Result: 1 2 3 2 3 1 3 Result: 1 2 3 Result: 3 2 1 Result: 3 2 1 Result: 2 3 1 Result: 3 Result: empty set

Multidimensional Collections

Although a collection has only one dimension, you can model a multidimensional collection with a collection whose elements are collections.

In Example 5-11, is a two-dimensional varray—a varray of varrays of integers.

Example 5-11 Two-Dimensional Varray (Varray of Varrays)

DECLARE TYPE t1 IS VARRAY(10) OF INTEGER; -- varray of integer va t1 := t1(2,3,5); TYPE nt1 IS VARRAY(10) OF t1; -- varray of varray of integer nva nt1 := nt1(va, t1(55,6,73), t1(2,4), va); i INTEGER; va1 t1; BEGIN i := nva(2)(3); DBMS_OUTPUT.PUT_LINE('i = ' || i); nva.EXTEND; nva(5) := t1(56, 32); -- replace inner varray elements nva(4) := t1(45,43,67,43345); -- replace an inner integer element nva(4)(4) := 1; -- replace 43345 with 1 nva(4).EXTEND; -- add element to 4th varray element nva(4)(5) := 89; -- store integer 89 there END; /

Result:

i = 73

In Example 5-12, is a nested table of nested tables of strings, and is a nested table of varrays of integers.

Example 5-12 Nested Tables of Nested Tables and Varrays of Integers

DECLARE TYPE tb1 IS TABLE OF VARCHAR2(20); -- nested table of strings vtb1 tb1 := tb1('one', 'three'); TYPE ntb1 IS TABLE OF tb1; -- nested table of nested tables of strings vntb1 ntb1 := ntb1(vtb1); TYPE tv1 IS VARRAY(10) OF INTEGER; -- varray of integers TYPE ntb2 IS TABLE OF tv1; -- nested table of varrays of integers vntb2 ntb2 := ntb2(tv1(3,5), tv1(5,7,3)); BEGIN vntb1.EXTEND; vntb1(2) := vntb1(1); vntb1.DELETE(1); -- delete first element of vntb1 vntb1(2).DELETE(1); -- delete first string from second table in nested table END; /

In Example 5-13, is an associative array of associative arrays, and is a nested table of varrays of strings.

Example 5-13 Nested Tables of Associative Arrays and Varrays of Strings

DECLARE TYPE tb1 IS TABLE OF INTEGER INDEX BY PLS_INTEGER; -- associative arrays v4 tb1; v5 tb1; TYPE aa1 IS TABLE OF tb1 INDEX BY PLS_INTEGER; -- associative array of v2 aa1; -- associative arrays TYPE va1 IS VARRAY(10) OF VARCHAR2(20); -- varray of strings v1 va1 := va1('hello', 'world'); TYPE ntb2 IS TABLE OF va1 INDEX BY PLS_INTEGER; -- associative array of varrays v3 ntb2; BEGIN v4(1) := 34; -- populate associative array v4(2) := 46456; v4(456) := 343; v2(23) := v4; -- populate associative array of associative arrays v3(34) := va1(33, 456, 656, 343); -- populate associative array varrays v2(35) := v5; -- assign empty associative array to v2(35) v2(35)(2) := 78; END; /

Collection Comparisons

You cannot compare associative array variables to the value or to each other.

Except for Comparing Nested Tables for Equality and Inequality, you cannot natively compare two collection variables with relational operators (listed in Table 2-5). This restriction also applies to implicit comparisons. For example, a collection variable cannot appear in a , , or clause.

To determine if one collection variable is less than another (for example), you must define what less than means in that context and write a function that returns or . For information about writing functions, see Chapter 8, "PL/SQL Subprograms."

Topics

Comparing Varray and Nested Table Variables to NULL

You can compare varray and nested table variables to the value with the "IS [NOT] NULL Operator", but not with the relational operators equal () and not equal (, , , or ).

Example 5-14 compares a varray variable and a nested table variable to correctly.

Example 5-14 Comparing Varray and Nested Table Variables to NULL

DECLARE TYPE Foursome IS VARRAY(4) OF VARCHAR2(15); -- VARRAY type team Foursome; -- varray variable TYPE Roster IS TABLE OF VARCHAR2(15); -- nested table type names Roster := Roster('Adams', 'Patel'); -- nested table variable BEGIN IF team IS NULL THEN DBMS_OUTPUT.PUT_LINE('team IS NULL'); ELSE DBMS_OUTPUT.PUT_LINE('team IS NOT NULL'); END IF; IF names IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE('names IS NOT NULL'); ELSE DBMS_OUTPUT.PUT_LINE('names IS NULL'); END IF; END; /

Result:

team IS NULL names IS NOT NULL

Comparing Nested Tables for Equality and Inequality

If two nested table variables have the same nested table type, and that nested table type does not have elements of a record type, then you can compare the two variables for equality or inequality with the relational operators equal () and not equal (, , , ). Two nested table variables are equal if and only if they have the same set of elements (in any order).

Example 5-15 compares nested table variables for equality and inequality with relational operators.

Example 5-15 Comparing Nested Tables for Equality and Inequality

DECLARE TYPE dnames_tab IS TABLE OF VARCHAR2(30); -- element type is not record type dept_names1 dnames_tab := dnames_tab('Shipping','Sales','Finance','Payroll'); dept_names2 dnames_tab := dnames_tab('Sales','Finance','Shipping','Payroll'); dept_names3 dnames_tab := dnames_tab('Sales','Finance','Payroll'); BEGIN IF dept_names1 = dept_names2 THEN DBMS_OUTPUT.PUT_LINE('dept_names1 = dept_names2'); END IF; IF dept_names2 != dept_names3 THEN DBMS_OUTPUT.PUT_LINE('dept_names2 != dept_names3'); END IF; END; /

Result:

dept_names1 = dept_names2dept_names2 != dept_names3

Comparing Nested Tables with SQL Multiset Conditions

You can compare nested table variables, and test some of their properties, with SQL multiset conditions (described in Oracle Database SQL Language Reference).

Example 5-16 uses the SQL multiset conditions and two SQL functions that take nested table variable arguments, (described in Oracle Database SQL Language Reference) and (described in Oracle Database SQL Language Reference).

Example 5-16 Comparing Nested Tables with SQL Multiset Conditions

DECLARE TYPE nested_typ IS TABLE OF NUMBER; nt1 nested_typ := nested_typ(1,2,3); nt2 nested_typ := nested_typ(3,2,1); nt3 nested_typ := nested_typ(2,3,1,3); nt4 nested_typ := nested_typ(1,2,4); PROCEDURE testify ( truth BOOLEAN := NULL, quantity NUMBER := NULL ) IS BEGIN IF truth IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE ( CASE truth WHEN TRUE THEN 'True' WHEN FALSE THEN 'False' END ); END IF; IF quantity IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE(quantity); END IF; END; BEGIN testify(truth => (nt1 IN (nt2,nt3,nt4))); -- condition testify(truth => (nt1 SUBMULTISET OF nt3)); -- condition testify(truth => (nt1 NOT SUBMULTISET OF nt4)); -- condition testify(truth => (4 MEMBER OF nt1)); -- condition testify(truth => (nt3 IS A SET)); -- condition testify(truth => (nt3 IS NOT A SET)); -- condition testify(truth => (nt1 IS EMPTY)); -- condition testify(quantity => (CARDINALITY(nt3))); -- function testify(quantity => (CARDINALITY(SET(nt3)))); -- 2 functions END; /

Result:

True True True False False True False 4 3

Collection Methods

A collection method is a PL/SQL subprogram—either a function that returns information about a collection or a procedure that operates on a collection. Collection methods make collections easier to use and your applications easier to maintain. Table 5-2 summarizes the collection methods.

Note:

With a null collection, is the only collection method that does not raise the predefined exception .

Table 5-2 Collection Methods

MethodTypeDescription

Procedure

Deletes elements from collection.

Procedure

Deletes elements from end of varray or nested table.

Procedure

Adds elements to end of varray or nested table.

Function

Returns if and only if specified element of varray or nested table exists.

Function

Returns first index in collection.

Function

Returns last index in collection.

Function

Returns number of elements in collection.

Function

Returns maximum number of elements that collection can have.

Function

Returns index that precedes specified index.

Function

Returns index that succeeds specified index.


The basic syntax of a collection method invocation is:

collection_name.method

For detailed syntax, see "Collection Method Invocation".

A collection method invocation can appear anywhere that an invocation of a PL/SQL subprogram of its type (function or procedure) can appear, except in a SQL statement. (For general information about PL/SQL subprograms, see Chapter 8, "PL/SQL Subprograms.")

In a subprogram, a collection parameter assumes the properties of the argument bound to it. You can apply collection methods to such parameters. For varray parameters, the value of is always derived from the parameter type definition, regardless of the parameter mode.

Topics

DELETE Collection Method

is a procedure that deletes elements from a collection. This method has these forms:

  • deletes all elements from a collection of any type.

    This operation immediately frees the memory allocated to the deleted elements.

  • From an associative array or nested table (but not a varray):

    • deletes the element whose index is n, if that element exists; otherwise, it does nothing.

    • deletes all elements whose indexes are in the range m..n, if both m and n exist and m <= n; otherwise, it does nothing.

    For these two forms of , PL/SQL keeps placeholders for the deleted elements. Therefore, the deleted elements are included in the internal size of the collection, and you can restore a deleted element by assigning a valid value to it.

Example 5-17 declares a nested table variable, initializing it with six elements; deletes and then restores the second element; deletes a range of elements and then restores one of them; and then deletes all elements. The restored elements occupy the same memory as the corresponding deleted elements. The procedure prints the nested table variable after initialization and after each operation. The type and procedure are defined in Example 5-6.

Example 5-17 DELETE Method with Nested Table

DECLARE nt nt_type := nt_type(11, 22, 33, 44, 55, 66); BEGIN print_nt(nt); nt.DELETE(2); -- Delete second element print_nt(nt); nt(2) := 2222; -- Restore second element print_nt(nt); nt.DELETE(2, 4); -- Delete range of elements print_nt(nt); nt(3) := 3333; -- Restore third element print_nt(nt); nt.DELETE; -- Delete all elements print_nt(nt); END; /

Result:

nt.(1) = 11 nt.(2) = 22 nt.(3) = 33 nt.(4) = 44 nt.(5) = 55 nt.(6) = 66 --- nt.(1) = 11 nt.(3) = 33 nt.(4) = 44 nt.(5) = 55 nt.(6) = 66 --- nt.(1) = 11 nt.(2) = 2222 nt.(3) = 33 nt.(4) = 44 nt.(5) = 55 nt.(6) = 66 --- nt.(1) = 11 nt.(5) = 55 nt.(6) = 66 --- nt.(1) = 11 nt.(3) = 3333 nt.(5) = 55 nt.(6) = 66 --- nt is empty ---

Example 5-18 populates an associative array indexed by string and deletes all elements, which frees the memory allocated to them. Next, the example replaces the deleted elements—that is, adds new elements that have the same indexes as the deleted elements. The new replacement elements do not occupy the same memory as the corresponding deleted elements. Finally, the example deletes one element and then a range of elements. The procedure shows the effects of the operations.

Example 5-18 DELETE Method with Associative Array Indexed by String

DECLARE TYPE aa_type_str IS TABLE OF INTEGER INDEX BY VARCHAR2(10); aa_str aa_type_str; PROCEDURE print_aa_str IS i VARCHAR2(10); BEGIN i := aa_str.FIRST; IF i IS NULL THEN DBMS_OUTPUT.PUT_LINE('aa_str is empty'); ELSE WHILE i IS NOT NULL LOOP DBMS_OUTPUT.PUT('aa_str.(' || i || ') = '); print(aa_str(i)); i := aa_str.NEXT(i); END LOOP; END IF; DBMS_OUTPUT.PUT_LINE('---'); END print_aa_str; BEGIN aa_str('M') := 13; aa_str('Z') := 26; aa_str('C') := 3; print_aa_str; aa_str.DELETE; -- Delete all elements print_aa_str; aa_str('M') := 13; -- Replace deleted element with same valueaa_str('Z') := 260; -- Replace deleted element with new valueaa_str('C') := 30; -- Replace deleted element with new value aa_str('W') := 23; -- Add new element aa_str('J') := 10; -- Add new element aa_str('N') := 14; -- Add new element aa_str('P') := 16; -- Add new element aa_str('W') := 23; -- Add new element aa_str('J') := 10; -- Add new element print_aa_str; aa_str.DELETE('C'); -- Delete one element print_aa_str; aa_str.DELETE('N','W'); -- Delete range of elements print_aa_str; aa_str.DELETE('Z','M'); -- Does nothing print_aa_str; END; /

Result:

aa_str.(C) = 3 aa_str.(M) = 13 aa_str.(Z) = 26 --- aa_str is empty --- aa_str.(C) = 30 aa_str.(J) = 10 aa_str.(M) = 13 aa_str.(N) = 14 aa_str.(P) = 16 aa_str.(W) = 23 aa_str.(Z) = 260 --- aa_str.(J) = 10 aa_str.(M) = 13 aa_str.(N) = 14 aa_str.(P) = 16 aa_str.(W) = 23 aa_str.(Z) = 260 --- aa_str.(J) = 10 aa_str.(M) = 13 aa_str.(Z) = 260 --- aa_str.(J) = 10 aa_str.(M) = 13 aa_str.(Z) = 260 ---

TRIM Collection Method

is a procedure that deletes elements from the end of a varray or nested table. This method has these forms:

  • removes one element from the end of the collection, if the collection has at least one element; otherwise, it raises the predefined exception .

  • removes n elements from the end of the collection, if there are at least n elements at the end; otherwise, it raises the predefined exception .

operates on the internal size of a collection. That is, if deletes an element but keeps a placeholder for it, then considers the element to exist. Therefore, can delete a deleted element.

PL/SQL does not keep placeholders for trimmed elements. Therefore, trimmed elements are not included in the internal size of the collection, and you cannot restore a trimmed element by assigning a valid value to it.

Caution:

Do not depend on interaction between and . Treat nested tables like either fixed-size arrays (and use only ) or stacks (and use only and ).

Example 5-19 declares a nested table variable, initializing it with six elements; trims the last element; deletes the fourth element; and then trims the last two elements—one of which is the deleted fourth element. The procedure prints the nested table variable after initialization and after the and operations. The type and procedure are defined in Example 5-6.

Example 5-19 TRIM Method with Nested Table

DECLARE nt nt_type := nt_type(11, 22, 33, 44, 55, 66); BEGIN print_nt(nt); nt.TRIM; -- Trim last element print_nt(nt); nt.DELETE(4); -- Delete fourth element print_nt(nt); nt.TRIM(2); -- Trim last two elements print_nt(nt); END; /

Result:

nt.(1) = 11 nt.(2) = 22 nt.(3) = 33 nt.(4) = 44 nt.(5) = 55 nt.(6) = 66 --- nt.(1) = 11 nt.(2) = 22 nt.(3) = 33 nt.(4) = 44 nt.(5) = 55 --- nt.(1) = 11 nt.(2) = 22 nt.(3) = 33 nt.(5) = 55 --- nt.(1) = 11 nt.(2) = 22 nt.(3) = 33 ---

EXTEND Collection Method

is a procedure that adds elements to the end of a varray or nested table. The collection can be empty, but not null. (To make a collection empty or add elements to a null collection, use a constructor. For more information, see "Collection Constructors".)

The method has these forms:

  • appends one null element to the collection.

  • appends n null elements to the collection.

  • , appends n copies of the ith element to the collection.

    Note:

    , is the only form that you can use for a collection whose elements have the constraint.

operates on the internal size of a collection. That is, if deletes an element but keeps a placeholder for it, then considers the element to exist.

Example 5-20 declares a nested table variable, initializing it with three elements; appends two copies of the first element; deletes the fifth (last) element; and then appends one null element. Because considers the deleted fifth element to exist, the appended null element is the sixth element. The procedure prints the nested table variable after initialization and after the and operations. The type and procedure are defined in Example 5-6.

Example 5-20 EXTEND Method with Nested Table

DECLARE nt nt_type := nt_type(11, 22, 33); BEGIN print_nt(nt); nt.EXTEND(2,1); -- Append two copies of first element print_nt(nt); nt.DELETE(5); -- Delete fifth element print_nt(nt); nt.EXTEND; -- Append one null element print_nt(nt); END; /

Result:

nt.(1) = 11 nt.(2) = 22 nt.(3) = 33 --- nt.(1) = 11 nt.(2) = 22 nt.(3) = 33 nt.(4) = 11 nt.(5) = 11 --- nt.(1) = 11 nt.(2) = 22 nt.(3) = 33 nt.(4) = 11 --- nt.(1) = 11 nt.(2) = 22 nt.(3) = 33 nt.(4) = 11 nt.(6) = NULL ---

EXISTS Collection Method

is a function that tells you whether the specified element of a varray or nested table exists.

returns if the nth element of the collection exists and otherwise. If n is out of range, returns instead of raising the predefined exception .

For a deleted element, returns , even if kept a placeholder for it.

Example 5-21 initializes a nested table with four elements, deletes the second element, and prints either the value or status of elements 1 through 6.

Example 5-21 EXISTS Method with Nested Table

DECLARE TYPE NumList IS TABLE OF INTEGER; n NumList := NumList(1,3,5,7); BEGIN n.DELETE(2); -- Delete second element FOR i IN 1..6 LOOP IF n.EXISTS(i) THEN DBMS_OUTPUT.PUT_LINE('n(' || i || ') = ' || n(i)); ELSE DBMS_OUTPUT.PUT_LINE('n(' || i || ') does not exist'); END IF; END LOOP; END; /

Result:

n(1) = 1 n(2) does not exist n(3) = 5 n(4) = 7 n(5) does not exist n(6) does not exist

FIRST and LAST Collection Methods

and are functions. If the collection has at least one element, and return the indexes of the first and last elements, respectively (ignoring deleted elements, even if kept placeholders for them). If the collection has only one element, and return the same index. If the collection is empty, and return .

Topics

FIRST and LAST Methods for Associative Array

For an associative array indexed by , the first and last elements are those with the smallest and largest indexes, respectively.

Example 5-22 shows the values of and for an associative array indexed by , deletes the first and last elements, and shows the values of and again.

Example 5-22 FIRST and LAST Values for Associative Array Indexed by PLS_INTEGER

DECLARE TYPE aa_type_int IS TABLE OF INTEGER INDEX BY PLS_INTEGER; aa_int aa_type_int; PROCEDURE print_first_and_last IS BEGIN DBMS_OUTPUT.PUT_LINE('FIRST = ' || aa_int.FIRST); DBMS_OUTPUT.PUT_LINE('LAST = ' || aa_int.LAST); END print_first_and_last; BEGIN aa_int(1) := 3; aa_int(2) := 6; aa_int(3) := 9; aa_int(4) := 12; DBMS_OUTPUT.PUT_LINE('Before deletions:'); print_first_and_last; aa_int.DELETE(1);aa_int.DELETE(4); DBMS_OUTPUT.PUT_LINE('After deletions:'); print_first_and_last; END; /

Result:

Before deletions: FIRST = 1 LAST = 4 After deletions: FIRST = 2 LAST = 3

For an associative array indexed by string, the first and last elements are those with the lowest and highest key values, respectively. Key values are in sorted order (for more information, see "NLS Parameter Values Affect Associative Arrays Indexed by String").

Example 5-23 shows the values of and for an associative array indexed by string, deletes the first and last elements, and shows the values of and again.

Example 5-23 FIRST and LAST Values for Associative Array Indexed by String

DECLARE TYPE aa_type_str IS TABLE OF INTEGER INDEX BY VARCHAR2(10); aa_str aa_type_str; PROCEDURE print_first_and_last IS BEGIN DBMS_OUTPUT.PUT_LINE('FIRST = ' || aa_str.FIRST); DBMS_OUTPUT.PUT_LINE('LAST = ' || aa_str.LAST); END print_first_and_last; BEGIN aa_str('Z') := 26; aa_str('A') := 1; aa_str('K') := 11; aa_str('R') := 18; DBMS_OUTPUT.PUT_LINE('Before deletions:'); print_first_and_last; aa_str.DELETE('A');aa_str.DELETE('Z'); DBMS_OUTPUT.PUT_LINE('After deletions:'); print_first_and_last; END; /

Result:

Before deletions: FIRST = A LAST = Z After deletions: FIRST = K LAST = R

FIRST and LAST Methods for Varray

For a varray that is not empty, always returns 1. For every varray, always equals (see Example 5-26).

Example 5-24 prints the varray using a statement with the bounds . and .. Because a varray is always dense, inside the loop always exists.

Example 5-24 Printing Varray with FIRST and LAST in FOR LOOP

DECLARE TYPE team_type IS VARRAY(4) OF VARCHAR2(15); team team_type; PROCEDURE print_team (heading VARCHAR2) IS BEGIN DBMS_OUTPUT.PUT_LINE(heading); IF team IS NULL THEN DBMS_OUTPUT.PUT_LINE('Does not exist'); ELSIF team.FIRST IS NULL THEN DBMS_OUTPUT.PUT_LINE('Has no members'); ELSE FOR i IN team.FIRST..team.LAST LOOP DBMS_OUTPUT.PUT_LINE(i || '. ' || team(i)); END LOOP; END IF; DBMS_OUTPUT.PUT_LINE('---'); END; BEGIN print_team('Team Status:'); team := team_type(); -- Team is funded, but nobody is on it. print_team('Team Status:'); team := team_type('John', 'Mary'); -- Put 2 members on team. print_team('Initial Team:'); team := team_type('Arun', 'Amitha', 'Allan', 'Mae'); -- Change team. print_team('New Team:'); END; /

Result:

Team Status: Does not exist --- Team Status: Has no members --- Initial Team: 1. John 2. Mary --- New Team: 1. Arun 2. Amitha 3. Allan 4. Mae ---

FIRST and LAST Methods for Nested Table

For a nested table, equals unless you delete elements from its middle, in which case is larger than (see Example 5-27).

Example 5-25 prints the nested table using a statement with the bounds . and .. Because a nested table can be sparse, the statement prints only if . is .

Example 5-25 Printing Nested Table with FIRST and LAST in FOR LOOP

DECLARE TYPE team_type IS TABLE OF VARCHAR2(15); team team_type; PROCEDURE print_team (heading VARCHAR2) IS BEGIN DBMS_OUTPUT.PUT_LINE(heading); IF team IS NULL THEN DBMS_OUTPUT.PUT_LINE('Does not exist'); ELSIF team.FIRST IS NULL THEN DBMS_OUTPUT.PUT_LINE('Has no members'); ELSE FOR i IN team.FIRST..team.LAST LOOP DBMS_OUTPUT.PUT(i || '. '); IF team.EXISTS(i) THEN DBMS_OUTPUT.PUT_LINE(team(i)); ELSE DBMS_OUTPUT.PUT_LINE('(to be hired)'); END IF; END LOOP; END IF; DBMS_OUTPUT.PUT_LINE('---'); END; BEGIN print_team('Team Status:'); team := team_type(); -- Team is funded, but nobody is on it. print_team('Team Status:'); team := team_type('Arun', 'Amitha', 'Allan', 'Mae'); -- Add members. print_team('Initial Team:'); team.DELETE(2,3); -- Remove 2nd and 3rd members. print_team('Current Team:'); END; /

Result:

Team Status: Does not exist --- Team Status: Has no members --- Initial Team: 1. Arun 2. Amitha 3. Allan 4. Mae --- Current Team: 1. Arun 2. (to be hired) 3. (to be hired) 4. Mae ---

COUNT Collection Method

is a function that returns the number of elements in the collection (ignoring deleted elements, even if kept placeholders for them).

Topics

COUNT Method for Varray

For a varray, always equals . If you increase or decrease the size of a varray (with the or method), the value of changes.

Example 5-26 shows the values of and for a varray after initialization with four elements, after , and after .

Example 5-26 COUNT and LAST Values for Varray

DECLARE TYPE NumList IS VARRAY(10) OF INTEGER; n NumList := NumList(1,3,5,7); PROCEDURE print_count_and_last IS BEGIN DBMS_OUTPUT.PUT('n.COUNT = ' || n.COUNT || ', '); DBMS_OUTPUT.PUT_LINE('n.LAST = ' || n.LAST); END print_count_and_last; BEGIN print_count_and_last; n.EXTEND(3); print_count_and_last; n.TRIM(5); print_count_and_last; END; /

Result:

n.COUNT = 4, n.LAST = 4 n.COUNT = 7, n.LAST = 7 n.COUNT = 2, n.LAST = 2

COUNT Method for Nested Table

For a nested table, equals unless you delete elements from the middle of the nested table, in which case is smaller than .

Example 5-27 shows the values of and for a nested table after initialization with four elements, after deleting the third element, and after adding two null elements to the end. Finally, the example prints the status of elements 1 through 8.

Example 5-27 COUNT and LAST Values for Nested Table

DECLARE TYPE NumList IS TABLE OF INTEGER; n NumList := NumList(1,3,5,7); PROCEDURE print_count_and_last IS BEGIN DBMS_OUTPUT.PUT('n.COUNT = ' || n.COUNT || ', '); DBMS_OUTPUT.PUT_LINE('n.LAST = ' || n.LAST); END print_count_and_last; BEGIN print_count_and_last; n.DELETE(3); -- Delete third element print_count_and_last; n.EXTEND(2); -- Add two null elements to end print_count_and_last; FOR i IN 1..8 LOOP IF n.EXISTS(i) THEN IF n(i) IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE('n(' || i || ') = ' || n(i)); ELSE DBMS_OUTPUT.PUT_LINE('n(' || i || ') = NULL'); END IF; ELSE DBMS_OUTPUT.PUT_LINE('n(' || i || ') does not exist'); END IF; END LOOP; END; /

Result:

n.COUNT = 4, n.LAST = 4 n.COUNT = 3, n.LAST = 4 n.COUNT = 5, n.LAST = 6 n(1) = 1 n(2) = 3 n(3) does not exist n(4) = 7 n(5) = NULL n(6) = NULL n(7) does not exist n(8) does not exist

LIMIT Collection Method

is a function that returns the maximum number of elements that the collection can have. If the collection has no maximum number of elements, returns . Only a varray has a maximum size.

Example 5-28 and prints the values of and for an associative array with four elements, a varray with two elements, and a nested table with three elements.

Example 5-28 LIMIT and COUNT Values for Different Collection Types

DECLARE TYPE aa_type IS TABLE OF INTEGER INDEX BY PLS_INTEGER; aa aa_type; -- associative array TYPE va_type IS VARRAY(4) OF INTEGER; va va_type := va_type(2,4); -- varray TYPE nt_type IS TABLE OF INTEGER; nt nt_type := nt_type(1,3,5); -- nested table BEGIN aa(1):=3; aa(2):=6; aa(3):=9; aa(4):= 12; DBMS_OUTPUT.PUT('aa.COUNT = '); print(aa.COUNT); DBMS_OUTPUT.PUT('aa.LIMIT = '); print(aa.LIMIT); DBMS_OUTPUT.PUT('va.COUNT = '); print(va.COUNT); DBMS_OUTPUT.PUT('va.LIMIT = '); print(va.LIMIT); DBMS_OUTPUT.PUT('nt.COUNT = '); print(nt.COUNT); DBMS_OUTPUT.PUT('nt.LIMIT = '); print(nt.LIMIT); END; /

Result:

aa.COUNT = 4 aa.LIMIT = NULL va.COUNT = 2 va.LIMIT = 4 nt.COUNT = 3 nt.LIMIT = NULL

PRIOR and NEXT Collection Methods

and are functions that let you move backward and forward in the collection (ignoring deleted elements, even if kept placeholders for them). These methods are useful for traversing sparse collections.

Given an index:

  • returns the index of the preceding existing element of the collection, if one exists. Otherwise, returns .

    For any collection , returns .

  • returns the index of the succeeding existing element of the collection, if one exists. Otherwise, returns .

    For any collection , returns .

The given index need not exist. However, if the collection is a varray, and the index exceeds , then:

  • returns .

  • returns .

For example:

DECLARE TYPE Arr_Type IS VARRAY(10) OF NUMBER; v_Numbers Arr_Type := Arr_Type(); BEGIN v_Numbers.EXTEND(4); v_Numbers (1) := 10; v_Numbers (2) := 20; v_Numbers (3) := 30; v_Numbers (4) := 40; DBMS_OUTPUT.PUT_LINE(NVL(v_Numbers.prior (3400), -1)); DBMS_OUTPUT.PUT_LINE(NVL(v_Numbers.next (3400), -1)); END; /

Result:

4 -1

Example 5-29 initializes a nested table with six elements, deletes the fourth element, and then shows the values of and for elements 1 through 7. Elements 4 and 7 do not exist. Element 2 exists, despite its null value.

Example 5-29 PRIOR and NEXT Methods

DECLARE TYPE nt_type IS TABLE OF NUMBER; nt nt_type := nt_type(18, NULL, 36, 45, 54, 63); BEGIN nt.DELETE(4); DBMS_OUTPUT.PUT_LINE('nt(4) was deleted.'); FOR i IN 1..7 LOOP DBMS_OUTPUT.PUT('nt.PRIOR(' || i || ') = '); print(nt.PRIOR(i)); DBMS_OUTPUT.PUT('nt.NEXT(' || i || ') = '); print(nt.NEXT(i)); END LOOP; END; /

Result:

nt(4) was deleted. nt.PRIOR(1) = NULL nt.NEXT(1) = 2 nt.PRIOR(2) = 1 nt.NEXT(2) = 3 nt.PRIOR(3) = 2 nt.NEXT(3) = 5 nt.PRIOR(4) = 3 nt.NEXT(4) = 5 nt.PRIOR(5) = 3 nt.NEXT(5) = 6 nt.PRIOR(6) = 5 nt.NEXT(6) = NULL nt.PRIOR(7) = 6 nt.NEXT(7) = NULL

For an associative array indexed by string, the prior and next indexes are determined by key values, which are in sorted order (for more information, see "NLS Parameter Values Affect Associative Arrays Indexed by String"

Predefined PL/SQL datatypes are grouped into composite, , reference, and scalar type categories.

PL/SQL Number Types

Number types let you store numeric data (integers, real numbers, and floating-point numbers), represent quantities, and do calculations.

BINARY_INTEGER Datatype

The datatype is identical to . subtypes can be considered as subtypes. See "Change to the BINARY_INTEGER Datatype". To simplify the documentation, is primarily used throughout the book. See "PLS_INTEGER Datatype".

BINARY_INTEGER Subtypes

A base type is the datatype from which a subtype is derived. A subtype associates a base type with a constraint and so defines a subset of values. For your convenience, PL/SQL predefines the following subtypes:







The subtypes and let you restrict an integer variable to non-negative or positive values, respectively. and prevent the assigning of nulls to an integer variable. lets you restrict an integer variable to the values -1, 0, and 1, which is useful in programming tri-state logic.

BINARY_FLOAT and BINARY_DOUBLE Datatypes

Single-precision and double-precision IEEE 754-format single-precision floating-point numbers. These types are used primarily for high-speed scientific computation. For usage information, see "Writing Computation-Intensive Programs in PL/SQL". For information about writing math libraries that accept different numeric types, see "Guidelines for Overloading with Numeric Types".

Literals of these types end with (for ) or (for ). For example, or .

Computations involving these types produce special values that you need to check for, rather than raising exceptions. To help deal with overflow, underflow, and other conditions that can occur with these numbers, you can use several special predefined constants: , , , , , , and corresponding names starting with . The constants for (not a number) and infinity are also defined by SQL; the others are PL/SQL-only.

NUMBER Datatype

The datatype reliably stores fixed-point or floating-point numbers with absolute values in the range up to (but not including) . A variable can also represent . See Example 2-1.

Oracle recommends only using the value of a literal or result of a computation that falls within the specified range.

  • If the value of the literal or a computation is smaller than the range, the value is rounded to zero.

  • If the value of the literal exceeds the upper limit, a compilation error is raised.

  • If the value of a computation exceeds the upper limit, the result is undefined and leads to unreliable results and errors.

The syntax of a datatype is:

Precision is the total number of digits and scale is the number of digits to the right of the decimal point. You cannot use constants or variables to specify precision and scale; you must use integer literals.

To declare fixed-point numbers, for which you must specify scale, use the following form that includes both precision and scale:

To declare floating-point numbers, for which you cannot specify precision or scale because the decimal point can float to any position, use the following form without precision and scale:

To declare integers, which have no decimal point, use this form with precision only:

-- same as

The maximum precision that can be specified for a value is 38 decimal digits. If you do not specify precision, it defaults to 39 or 40, or the maximum supported by your system, whichever is less.

Scale, which can range from -84 to 127, determines where rounding occurs. For instance, a scale of 2 rounds to the nearest hundredth (3.4562 becomes 3.46). A negative scale rounds to the left of the decimal point. For example, a scale of -3 rounds to the nearest thousand (34562 becomes 34000). A scale of 0 rounds to the nearest whole number (3.4562 becomes 3). If you do not specify scale, it defaults to 0, as shown in the following example.

DECLARE x NUMBER(3); BEGIN x := 123.89; DBMS_OUTPUT.PUT_LINE('The value of x is ' || TO_CHAR(x)); END; /

The output is:

For more information on the datatype, see Oracle Database SQL Reference.

NUMBER Subtypes

You can use the following subtypes for compatibility with ANSI/ISO and IBM types or when you want a more descriptive name:











Use the subtypes , , and to declare fixed-point numbers with a maximum precision of 38 decimal digits.

Use the subtypes and to declare floating-point numbers with a maximum precision of 126 binary digits, which is roughly equivalent to 38 decimal digits. Or, use the subtype REAL to declare floating-point numbers with a maximum precision of 63 binary digits, which is roughly equivalent to 18 decimal digits.

Use the subtypes , , and to declare integers with a maximum precision of 38 decimal digits.

PLS_INTEGER Datatype

You use the datatype to store signed integers. Its magnitude range is -2147483648 to 2147483647, represented in 32 bits. values require less storage than values and subtypes. Also, operations use hardware arithmetic, so they are faster than operations, which use library arithmetic. For efficiency, use for all calculations that fall within its magnitude range. For calculations outside the range of , you can use the datatype.


Note:

  • The and datatypes are identical. See "Change to the BINARY_INTEGER Datatype".

  • When a calculation with two PLS_INTEGER datatypes overflows the magnitude range of , an overflow exception is raised even if the result is assigned to a datatype.


PL/SQL Character and String Types

Character types let you store alphanumeric data, represent words and text, and manipulate character strings.

CHAR Datatype

You use the datatype to store fixed-length character data. How the data is represented internally depends on the database character set. The datatype takes an optional parameter that lets you specify a maximum size up to 32767 bytes. You can specify the size in terms of bytes or characters, where each character contains one or more bytes, depending on the character set encoding. The syntax follows:

You cannot use a symbolic constant or variable to specify the maximum size; you must use an integer literal in the range 1 .. 32767.

If you do not specify a maximum size, it defaults to 1. If you specify the maximum size in bytes rather than characters, a variable might be too small to hold multibyte characters. To avoid this possibility, use the notation so that the variable can hold characters in the database character set, even if some of those characters contain multiple bytes. When you specify the length in characters, the upper limit is still 32767 bytes. So for double-byte and multibyte character sets, you can only specify 1/2 or 1/3 as many characters as with a single-byte character set.

Although PL/SQL character variables can be relatively long, you cannot insert values longer than 2000 bytes into a database column.

You can insert any value into a database column because the maximum width of a column is 2147483648 bytes or two gigabytes. However, you cannot retrieve a value longer than 32767 bytes from a column into a variable. Note that the datatype is supported only for backward compatibility; see "LONG and LONG RAW Datatypes" for more information.

When you do not use the or qualifiers, the default is determined by the setting of the initialization parameter. When a PL/SQL procedure is compiled, the setting of this parameter is recorded, so that the same setting is used when the procedure is recompiled after being invalidated.

For information on semantic differences between the and base types, see "Differences between the CHAR and VARCHAR2 Datatypes".

CHAR Subtype

The subtype has the same range of values as its base type. That is, is just another name for . You can use this subtype for compatibility with ANSI/ISO and IBM types or when you want an identifier more descriptive than .

LONG and LONG RAW Datatypes

You use the datatype to store variable-length character strings. The datatype is like the datatype, except that the maximum size of a value is 32760 bytes.

You use the datatype to store binary data or byte strings. data is like data, except that data is not interpreted by PL/SQL. The maximum size of a value is 32760 bytes.

You can insert any value into a database column because the maximum width of a column is 2147483648 bytes or two gigabytes. However, you cannot retrieve a value longer than 32760 bytes from a column into a variable.

Likewise, you can insert any value into a database column because the maximum width of a column is 2147483648 bytes. However, you cannot retrieve a value longer than 32760 bytes from a column into a variable.

columns can store text, arrays of characters, or even short documents. You can reference columns in , , and (most) statements, but not in expressions, SQL function calls, or certain SQL clauses such as , , and . For more information, see Oracle Database SQL Reference.

In SQL statements, PL/SQL binds values as , not as . However, if the length of the bound exceeds the maximum width of a column (4000 bytes), Oracle converts the bind type to automatically, then issues an error message because you cannot pass values to a SQL function.


Note:

The and datatypes are supported only for backward compatibility with existing applications. For new applications, use or in place of , and or in place of .

Oracle also recommends that you replace existing and datatypes with datatypes. datatypes are subject to far fewer restrictions than or datatypes. Further, functionality is enhanced in every release, whereas and functionality has been static for several releases. See "PL/SQL LOB Types".


RAW Datatype

You use the datatype to store binary data or byte strings. For example, a variable might store a sequence of graphics characters or a digitized picture. Raw data is like data, except that PL/SQL does not interpret raw data. Likewise, Oracle Net does no character set conversions when you transmit raw data from one system to another.

The datatype takes a required parameter that lets you specify a maximum size up to 32767 bytes. The syntax follows:

You cannot use a symbolic constant or variable to specify the maximum size; you must use an integer literal in the range 1 .. 32767.

You cannot insert values longer than 2000 bytes into a column. You can insert any value into a database column because the maximum width of a column is 2147483648 bytes or two gigabytes. However, you cannot retrieve a value longer than 32767 bytes from a column into a variable. Note that the datatype is supported only for backward compatibility; see "LONG and LONG RAW Datatypes" for more information.

ROWID and UROWID Datatype

Internally, every database table has a pseudocolumn, which stores binary values called rowids. Each rowid represents the storage address of a row. A physical rowid identifies a row in an ordinary table. A logical rowid identifies a row in an index-organized table. The datatype can store only physical rowids. However, the (universal rowid) datatype can store physical, logical, or foreign (non-Oracle) rowids.

When you select or fetch a rowid into a variable, you can use the built-in function , which converts the binary value into an 18-byte character string. Conversely, the function converts a character string into a rowid. If the conversion fails because the character string does not represent a valid rowid, PL/SQL raises the predefined exception . This also applies to implicit conversions.

To convert between variables and character strings, use regular assignment statements without any function call. The values are implicitly converted between and character types.

Physical Rowids

Physical rowids provide fast access to particular rows. As long as the row exists, its physical rowid does not change. Efficient and stable, physical rowids are useful for selecting a set of rows, operating on the whole set, and then updating a subset. For example, you can compare a variable with the pseudocolumn in the clause of an or statement to identify the latest row fetched from a cursor. See "Fetching Across Commits".

A physical rowid can have either of two formats. The 10-byte extended rowid format supports tablespace-relative block addresses and can identify rows in partitioned and non-partitioned tables. The 6-byte restricted rowid format is provided for backward compatibility.

Extended rowids use a base-64 encoding of the physical address for each row selected. For example, in SQL*Plus (which implicitly converts rowids into character strings), the query

SELECT rowid, last_name FROM employees WHERE employee_id = 120;

might return the following row:





The format, , has four parts:

  • : The data object number ( in the preceding example) identifies the database segment. Schema objects in the same segment, such as a cluster of tables, have the same data object number.

  • : The file number ( in the example) identifies the data file that contains the row. File numbers are unique within a database.

  • : The block number ( in the example) identifies the data block that contains the row. Because block numbers are relative to their data file, not their tablespace, two rows in the same tablespace but in different data files can have the same block number.

  • : The row number ( in the example) identifies the row in the block.

Logical Rowids

Logical rowids provide the fastest access to particular rows. Oracle uses them to construct secondary indexes on index-organized tables. Having no permanent physical address, a logical rowid can move across data blocks when new rows are inserted. However, if the physical location of a row changes, its logical rowid remains valid.

A logical rowid can include a guess, which identifies the block location of a row at the time the guess is made. Instead of doing a full key search, Oracle uses the guess to search the block directly. However, as new rows are inserted, guesses can become stale and slow down access to rows. To obtain fresh guesses, you can rebuild the secondary index.

You can use the pseudocolumn to select logical rowids (which are opaque values) from an index-organized table. Also, you can insert logical rowids into a column of type , which has a maximum size of 4000 bytes.

The statement helps you track the staleness of guesses. This is useful for applications that store rowids with guesses in a column, then use the rowids to fetch rows.

To manipulate rowids, you can use the supplied package . For more information, see Oracle Database PL/SQL Packages and Types Reference.


Note:

Use the datatype only for backward compatibility with old applications. For new applications, use the datatype.

VARCHAR2 Datatype

You use the datatype to store variable-length character data. How the data is represented internally depends on the database character set. The datatype takes a required parameter that specifies a maximum size up to 32767 bytes. The syntax follows:

You cannot use a symbolic constant or variable to specify the maximum size; you must use an integer literal in the range 1 .. 32767.

Small variables are optimized for performance, and larger ones are optimized for efficient memory use. The cutoff point is 2000 bytes. For a that is bytes or longer, PL/SQL dynamically allocates only enough memory to hold the actual value. For a variable that is shorter than 2000 bytes, PL/SQL preallocates the full declared length of the variable. For example, if you assign the same 500-byte value to a variable and to a variable, the former takes up 500 bytes and the latter takes up 1999 bytes.

If you specify the maximum size in bytes rather than characters, a variable might be too small to hold multibyte characters. To avoid this possibility, use the notation so that the variable can hold characters in the database character set, even if some of those characters contain multiple bytes. When you specify the length in characters, the upper limit is still 32767 bytes. So for double-byte and multibyte character sets, you can only specify 1/2 or 1/3 as many characters as with a single-byte character set.

Although PL/SQL character variables can be relatively long, you cannot insert values longer than 4000 bytes into a database column.

You can insert any value into a database column because the maximum width of a column is 2147483648 bytes or two gigabytes. However, you cannot retrieve a value longer than 32767 bytes from a column into a variable. Note that the datatype is supported only for backward compatibility; see "LONG and LONG RAW Datatypes" more information.

When you do not use the or qualifiers, the default is determined by the setting of the initialization parameter. When a PL/SQL procedure is compiled, the setting of this parameter is recorded, so that the same setting is used when the procedure is recompiled after being invalidated.

VARCHAR2 Subtypes

The subtypes and have the same range of values as their base type. For example, is just another name for .

You can use the subtypes for compatibility with ANSI/ISO and IBM types.

Currently, is synonymous with . However, in future releases of PL/SQL, to accommodate emerging SQL standards, might become a separate datatype with different comparison semantics. It is a good idea to use rather than .

PL/SQL National Character Types

The widely used one-byte ASCII and EBCDIC character sets are adequate to represent the Roman alphabet, but some Asian languages, such as Japanese, contain thousands of characters. These languages require two or three bytes to represent each character. To deal with such languages, Oracle provides globalization support, which lets you process single-byte and multi-byte character data and convert between character sets. It also lets your applications run in different language environments.

With globalization support, number and date formats adapt automatically to the language conventions specified for a user session. Thus, users around the world can interact with Oracle in their native languages.

PL/SQL supports two character sets called the database character set, which is used for identifiers and source code, and the national character set, which is used for national language data. The datatypes and store character strings formed from the national character set.

When converting or data between databases with different character sets, make sure the data consists of well-formed strings. For more information or data, see Oracle Database Globalization Support Guide.

Comparing UTF8 and AL16UTF16 Encodings

The national character set represents data as Unicode, using either the or encoding.

Each character in the encoding takes up 2 bytes. This makes it simple to calculate string lengths to avoid truncation errors when mixing different programming languages, but requires extra storage overhead to store strings made up mostly of ASCII characters.

Each character in the encoding takes up 1, 2, or 3 bytes. This lets you fit more characters into a variable or table column, but only if most characters can be represented in a single byte. It introduces the possibility of truncation errors when transferring the data to a buffer measured in bytes.

Oracle recommends that you use the default encoding wherever practical, for maximum runtime reliability. If you need to determine how many bytes are required to hold a Unicode string, use the function rather than .

NCHAR Datatype

You use the datatype to store fixed-length (blank-padded if necessary) national character data. How the data is represented internally depends on the national character set specified when the database was created, which might use a variable-width encoding () or a fixed-width encoding (). Because this type can always accommodate multibyte characters, you can use it to hold any Unicode character data.

The datatype takes an optional parameter that lets you specify a maximum size in characters. The syntax follows:

Because the physical limit is 32767 bytes, the maximum value you can specify for the length is 32767/2 in the encoding, and 32767/3 in the encoding.

You cannot use a symbolic constant or variable to specify the maximum size; you must use an integer literal.

If you do not specify a maximum size, it defaults to 1. The value always represents the number of characters, unlike which can be specified in either characters or bytes.

-- maximum size is 100 characters

You cannot insert values longer than 2000 bytes into an column.

If the value is shorter than the defined width of the column, Oracle blank-pads the value to the defined width.

You can interchange and values in statements and expressions. It is always safe to turn a value into an value, but turning an value into a value might cause data loss if the character set for the value cannot represent all the characters in the value. Such data loss can result in characters that usually look like question marks (?).

NVARCHAR2 Datatype

You use the datatype to store variable-length Unicode character data. How the data is represented internally depends on the national character set specified when the database was created, which might use a variable-width encoding () or a fixed-width encoding (). Because this type can always accommodate multibyte characters, you can use it to hold any Unicode character data.

The datatype takes a required parameter that specifies a maximum size in characters. The syntax follows:

Because the physical limit is 32767 bytes, the maximum value you can specify for the length is 32767/2 in the encoding, and 32767/3 in the encoding.

You cannot use a symbolic constant or variable to specify the maximum size; you must use an integer literal.

The maximum size always represents the number of characters, unlike which can be specified in either characters or bytes.

-- maximum size is 200 characters

The maximum width of a database column is 4000 bytes. Therefore, you cannot insert values longer than 4000 bytes into a column.

You can interchange and values in statements and expressions. It is always safe to turn a value into an value, but turning an value into a value might cause data loss if the character set for the value cannot represent all the characters in the value. Such data loss can result in characters that usually look like question marks (?).

PL/SQL LOB Types

The (large object) datatypes , , , and let you store blocks of unstructured data, such as text, graphic images, video clips, and sound waveforms. LOBs allow efficient, random, piece-wise access to the data. , , and are from 8 to 128 terabytes in size. The size of a is system dependent, but cannot exceed four gigabytes (4GB - 1 bytes).

The types differ from the and types in several ways. For example, s (except ) can be attributes of an object type, but s cannot. The maximum size of a , , or is 8 to 128 terabytes, but the maximum size of a is two gigabytes. Also, s support random access to data, but s support only sequential access. Note that the and datatypes are supported only for backward compatibility; see "LONG and LONG RAW Datatypes" for more information.

types store lob locators, which point to large objects stored in an external file, in-line (inside the row) or out-of-line (outside the row). Database columns of type , , , or store the locators. , , and data is stored in the database, in or outside the row. data is stored in operating system files outside the database.

PL/SQL operates on s through the locators. For example, when you select a column value, only a locator is returned. If you got it during a transaction, the locator includes a transaction ID, so you cannot use it to update that in another transaction. Likewise, you cannot save a locator during one session, then use it in another session.

You can also convert s to and types and vice versa, or s to and vice versa, which lets you use types in most SQL and PL/SQL statements and functions. To read, write, and do piecewise operations on s, you can use the supplied package .

For more information on LOBs, see Oracle Database Application Developer's Guide - Large Objects.

BFILE Datatype

You use the datatype to store large binary objects in operating system files outside the database. Every variable stores a file locator, which points to a large binary file on the server. The locator includes a directory alias, which specifies a full path name. Logical path names are not supported.

s are read-only, so you cannot modify them. Your DBA makes sure that a given exists and that Oracle has read permissions on it. The underlying operating system maintains file integrity.

s do not participate in transactions, are not recoverable, and cannot be replicated. The maximum number of open s is set by the Oracle initialization parameter , which is system dependent.

BLOB Datatype

You use the datatype to store large binary objects in the database, in-line or out-of-line. Every variable stores a locator, which points to a large binary object.

s participate fully in transactions, are recoverable, and can be replicated. Changes made by package can be committed or rolled back. locators can span transactions (for reads only), but they cannot span sessions.

CLOB Datatype

You use the datatype to store large blocks of character data in the database, in-line or out-of-line. Both fixed-width and variable-width character sets are supported. Every variable stores a locator, which points to a large block of character data.

s participate fully in transactions, are recoverable, and can be replicated. Changes made by package can be committed or rolled back. locators can span transactions (for reads only), but they cannot span sessions.

NCLOB Datatype

You use the datatype to store large blocks of data in the database, in-line or out-of-line. Both fixed-width and variable-width character sets are supported. Every variable stores a locator, which points to a large block of data.

s participate fully in transactions, are recoverable, and can be replicated. Changes made by package can be committed or rolled back. locators can span transactions (for reads only), but they cannot span sessions.

PL/SQL Boolean Types

PL/SQL has a type for representing Boolean values (true and false). Because SQL does not have an equivalent type, you can use variables and parameters in PL/SQL contexts but not inside SQL statements or queries.

BOOLEAN Datatype

You use the datatype to store the logical values , , and (which stands for a missing, unknown, or inapplicable value). Only logic operations are allowed on variables.

The datatype takes no parameters. Only the values , , and can be assigned to a variable.

You cannot insert the values and into a database column. You cannot select or fetch column values into a variable. Functions called from a SQL query cannot take any parameters. Neither can built-in SQL functions such as ; to represent values in output, you must use or constructs to translate values into some other type, such as 0 or 1, 'Y' or 'N', 'true' or 'false', and so on.

PL/SQL Date, Time, and Interval Types

The datatypes in this section let you store and manipulate dates, times, and intervals (periods of time). A variable that has a date and time datatype holds values called datetimes. A variable that has an interval datatype holds values called intervals. A datetime or interval consists of fields, which determine its value. The following list shows the valid values for each field:

Field NameValid Datetime ValuesValid Interval Values
-4712 to 9999 (excluding year 0)Any nonzero integer
01 to 120 to 11
01 to 31 (limited by the values of and , according to the rules of the calendar for the locale)Any nonzero integer
00 to 230 to 23
00 to 590 to 59
00 to 59.9(n), where 9(n) is the precision of time fractional seconds0 to 59.9(n), where 9(n) is the precision of interval fractional seconds
-12 to 14 (range accommodates daylight savings time changes)Not applicable
00 to 59Not applicable
Found in the view Not applicable
Found in the view Not applicable

Except for , these types are all part of the SQL92 standard. For information about datetime and interval format models, literals, time-zone names, and SQL functions, see Oracle Database SQL Reference.

DATE Datatype

You use the datatype to store fixed-length datetimes, which include the time of day in seconds since midnight. The date portion defaults to the first day of the current month; the time portion defaults to midnight. The date function returns the current date and time.

  • To compare dates for equality, regardless of the time portion of each date, use the function result in comparisons, operations, and so on.

  • To find just the time portion of a DATE variable, subtract the date portion: .

Valid dates range from January 1, 4712 BC to December 31, 9999 AD. A Julian date is the number of days since January 1, 4712 BC. Julian dates allow continuous dating from a common reference. You can use the date format model with the date functions and to convert between values and their Julian equivalents.

In date expressions, PL/SQL automatically converts character values in the default date format to values. The default date format is set by the Oracle initialization parameter . For example, the default might be , which includes a two-digit number for the day of the month, an abbreviation of the month name, and the last two digits of the year.

You can add and subtract dates. In arithmetic expressions, PL/SQL interprets integer literals as days. For instance, + 1 signifies the same time tomorrow.

TIMESTAMP Datatype

The datatype , which extends the datatype , stores the year, month, day, hour, minute, and second. The syntax is:

where the optional parameter specifies the number of digits in the fractional part of the seconds field. You cannot use a symbolic constant or variable to specify the precision; you must use an integer literal in the range 0 .. 9. The default is 6.

The default timestamp format is set by the Oracle initialization parameter .

In Example 3-1, you declare a variable of type , then assign a literal value to it. In the example, the fractional part of the seconds field is .

Example 3-1 Assigning a Literal Value to a TIMESTAMP Variable

DECLARE checkout TIMESTAMP(3); BEGIN checkout := '22-JUN-2004 07:48:53.275'; DBMS_OUTPUT.PUT_LINE( TO_CHAR(checkout)); END; /

In Example 3-2, the and functions are used to manipulate s.

Example 3-2 Using the SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN Functions

DECLARE right_now TIMESTAMP; yesterday TIMESTAMP; sometime TIMESTAMP; scn1 INTEGER; scn2 INTEGER; scn3 INTEGER; BEGIN right_now := SYSTIMESTAMP; -- Get the current SCN scn1 := TIMESTAMP_TO_SCN(right_now); DBMS_OUTPUT.PUT_LINE('Current SCN is ' || scn1); yesterday := right_now - 1; -- Get the SCN from exactly 1 day ago scn2 := TIMESTAMP_TO_SCN(yesterday); DBMS_OUTPUT.PUT_LINE('SCN from yesterday is ' || scn2); -- Find an arbitrary SCN somewhere between yesterday and today -- In a real program we would have stored the SCN at some significant moment scn3 := (scn1 + scn2) / 2; sometime := SCN_TO_TIMESTAMP(scn3); -- What time was that SCN was in effect? DBMS_OUTPUT.PUT_LINE('SCN ' || scn3 || ' was in effect at ' || TO_CHAR(sometime)); END; /

TIMESTAMP WITH TIME ZONE Datatype

The datatype , which extends the datatype , includes a time-zone displacement. The time-zone displacement is the difference (in hours and minutes) between local time and Coordinated Universal Time (UTC)—formerly Greenwich Mean Time. The syntax is:

where the optional parameter specifies the number of digits in the fractional part of the seconds field. You cannot use a symbolic constant or variable to specify the precision; you must use an integer literal in the range 0 .. 9. The default is 6.

The default timestamp with time zone format is set by the Oracle initialization parameter .

In Example 3-3, you declare a variable of type , then assign a literal value to it:

Example 3-3 Assigning a Literal to a TIMESTAMP WITH TIME ZONE Variable

DECLARE logoff TIMESTAMP(3) WITH TIME ZONE; BEGIN logoff := '10-OCT-2004 09:42:37.114 AM +02:00'; DBMS_OUTPUT.PUT_LINE( TO_CHAR(logoff)); END; /

In this example, the time-zone displacement is +.

You can also specify the time zone by using a symbolic name. The specification can include a long form such as , an abbreviation such as , or a combination. For example, the following literals all represent the same time. The third form is most reliable because it specifies the rules to follow at the point when switching to daylight savings time.





You can find the available names for time zones in the and columns of the data dictionary view.

Two values are considered identical if they represent the same instant in UTC, regardless of their time-zone displacements. For example, the following two values are considered identical because, in UTC, 8:00 AM Pacific Standard Time is the same as 11:00 AM Eastern Standard Time:



TIMESTAMP WITH LOCAL TIME ZONE Datatype

The datatype , which extends the datatype , includes a time-zone displacement. The time-zone displacement is the difference (in hours and minutes) between local time and Coordinated Universal Time (UTC)—formerly Greenwich Mean Time. You can also use named time zones, as with .

The syntax is

where the optional parameter specifies the number of digits in the fractional part of the seconds field. You cannot use a symbolic constant or variable to specify the precision; you must use an integer literal in the range 0 .. 9. The default is 6.

This datatype differs from in that when you insert a value into a database column, the value is normalized to the database time zone, and the time-zone displacement is not stored in the column. When you retrieve the value, Oracle returns it in your local session time zone.

In Example 3-4, you declare a variable of type :

Example 3-4 Assigning a Literal Value to a TIMESTAMP WITH LOCAL TIME ZONE

DECLARE logoff TIMESTAMP(3) WITH LOCAL TIME ZONE; BEGIN -- logoff := '10-OCT-2004 09:42:37.114 AM +02:00'; raises an error logoff := '10-OCT-2004 09:42:37.114 AM '; -- okay without displacement DBMS_OUTPUT.PUT_LINE( TO_CHAR(logoff)); END; /

INTERVAL YEAR TO MONTH Datatype

You use the datatype to store and manipulate intervals of years and months. The syntax is:

where precision specifies the number of digits in the years field. You cannot use a symbolic constant or variable to specify the precision; you must use an integer literal in the range 0 .. 4. The default is 2.

In Example 3-5, you declare a variable of type , then assign a value of 101 years and 3 months to it:

Example 3-5 Assigning Literals to an INTERVAL YEAR TO MONTH Variable

DECLARE lifetime INTERVAL YEAR(3) TO MONTH; BEGIN lifetime := INTERVAL '101-3' YEAR TO MONTH; -- interval literal lifetime := '101-3'; -- implicit conversion from character type lifetime := INTERVAL '101' YEAR; -- Can specify just the years lifetime := INTERVAL '3' MONTH; -- Can specify just the months END; /

INTERVAL DAY TO SECOND Datatype

You use the datatype to store and manipulate intervals of days, hours, minutes, and seconds. The syntax is:


)]

where and specify the number of digits in the days field and seconds field, respectively. In both cases, you cannot use a symbolic constant or variable to specify the precision; you must use an integer literal in the range 0 .. 9. The defaults are 2 and 6, respectively.

In Example 3-6, you declare a variable of type :

Example 3-6 Assigning Literals to an INTERVAL DAY TO SECOND Variable

DECLARE lag_time INTERVAL DAY(3) TO SECOND(3); BEGIN lag_time := '7 09:24:30'; IF lag_time > INTERVAL '6' DAY THEN DBMS_OUTPUT.PUT_LINE ( 'Greater than 6 days'); ELSE DBMS_OUTPUT.PUT_LINE ( 'Less than 6 days'); END IF; END; /

Datetime and Interval Arithmetic

PL/SQL lets you construct datetime and interval expressions. The following list shows the operators that you can use in such expressions:

Operand 1OperatorOperand 2Result Type
datetimeintervaldatetime
datetimeintervaldatetime
intervaldatetimedatetime
datetimedatetimeinterval
intervalintervalinterval
intervalintervalinterval
intervalnumericinterval
numericintervalinterval
intervalnumericinterval

You can also manipulate datetime values using various functions, such as . For a list of such functions, see Table 2-4, "Built-In Functions".

For further information and examples of datetime arithmetic, see Oracle Database SQL Reference and Oracle Database Application Developer's Guide - Fundamentals.

Avoiding Truncation Problems Using Date and Time Subtypes

The default precisions for some of the date and time types are less than the maximum precision. For example, the default for is , while the highest precision is . To avoid truncation when assigning variables and passing procedure parameters of these types, you can declare variables and procedure parameters of the following subtypes, which use the maximum values for precision:






0 Thoughts to “Varchar2 Limit In Pl/Sql What Is The Assignment Operator

Leave a comment

L'indirizzo email non verrĂ  pubblicato. I campi obbligatori sono contrassegnati *