Previous | Table of Contents | Next

Page 208

ORA-06500: PL/SQL: storage error
ORA-06512: at line 7
ORA-04030: out of process memory when trying to allocate 32792
bytes (callheap,DPAGE)
ORA-09240: smpalo: error allocating PGA memory
OSD-04100: malloc() failure, unable to allocate memory

Even though a NULL value was being inserted into the table (a zero-byte value), Oracle wanted a 32KB buffer, plus some overhead when the operating system just shrugged its shoulders. Notice that the memory remains allocated until you close the SQL*Plus session. Then very slowly, Oracle releases the memory back to the operating system.

When declaring many long string variables, it would be wise to consider if PL/SQL always allocates a buffer of the size declared for VARCHAR2 variables.

You can also specify that a stored value must not be NULL by declaring the table like this:

DECLARE
  TYPE STR_TYPE IS TABLE OF VARCHAR2(8) NOT NULL
       INDEX BY BINARY INTEGER;
  arr STR_TYPE;
BEGIN
  arr(0) := NULL;  -- raises an exception!

You can also used a based reference, such as to a database column.

DECLARE
  TYPE QTY_TYPE IS TABLE OF items.quantity%TYPE NOT NULL
       INDEX BY BINARY INTEGER;
  qty QTY_TYPE;
BEGIN
  qty(0) := 0;  -- quantity of zero

Some of the benefits of using PL/SQL tables include the following:

Additionally, you can bind these PL/SQL tables to arrays in OCI and precompiler programs, allowing very fast data transfer. However, think about the increase in network traffic when moving large amounts of data between PL/SQL tables and these programs.

NOTE
You can only bind a one-dimensional host array to a PL/SQL table based on a scalar data type (record-based host arrays are not supported).

You can assign one table variable to another only if they have the same base types. The assignment copies the entire contents, if any, of the table to the other table variable. This technique is also used to clear a table, as once an element is assigned a value, there is no way to delete it.

Page 209

DECLARE

  TYPE ORDITEM_TYPE IS TABLE OF orders.item_no%TYPE
       INDEX BY BINARY INTEGER;
  clr_orditem ORDITEM_TYPE;  -- use ONLY to clear array
  new_orditem ORDITEM_TYPE;  -- new list of order items
  old_orditem ORDITEM_TYPE;  -- old list of order items
BEGIN
  …  -- fill up new order items array
  old_orditem := new_orditem;  -- copy for safekeeping
  …  -- modify new order items array and save
  new_orditem := clr_orditem;  -- erase contents for next operation

If you try to read an element that has not yet been assigned a value, PL/SQL raises the ROW_NOT_FOUND exception. Listing 10.11 illustrates this.

Listing 10.11 badref.sql—Referencing an Array Element Before It Has a Value Raises an Exception

DECLARE
  SCHAR VARCHAR2(1);  -- we'll use this for typing only
  TYPE SCHAR_TYPE IS TABLE OF SCHAR%TYPE INDEX BY BINARY_INTEGER;
  schar_arr SCHAR_TYPE;
  local_schar SCHAR%TYPE;
BEGIN
  local_schar := schar_arr(0);  -- no value stored!
END;
/

ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 7

In PL/SQL 2.3 (Oracle7.3) and later versions, tables can also be based on database tables and RECORD types you declare. Each record member must be a scalar type. This enables you to load and subsequently manipulate a set of database rows.

Referencing Arrays of Row TypesYou use dot notation to reference a particular member of a record with the array element.

DECLARE
  TYPE ORDERS_TYPE IS TABLE OF orders%ROWTYPE
       INDEX BY BINARY_INTEGER;
  qty orders.quantity%TYPE;  -- local copy of quantity
  orders_tab_rec ORDERS_TYPE;  -- array of table rows
BEGIN
  …  -- fill the array with database table rows
  qty := orders_tab_rec(i).quantity;

Page 210

PL/SQL Table AttributesTables in PL/SQL 2.3 have attributes to help you manipulate them more easily. COUNT, FIRST, and LAST operate on the table itself. EXISTS, PRIOR, NEXT, and DELETE operate on a specific table element. Table 10.4 lists some of these attributes.

Table 10.4 Using PL/SQL Table Attributes


Attribute Description Return Value Usage
EXISTS Tests a table element for a value TRUE/FALSE tablename.EXISTS (offset)
COUNT Returns number of entries in table BINARY_INTEGER tablename.COUNT
FIRST Returns the offset of the first table entry BINARY_INTEGER tablename.FIRST
LAST Returns offset of the last table entry BINARY_INTEGER tablename.LAST
PRIOR Returns offset of the previous table entry BINARY_INTEGER tablename. PRIOR(offset)
NEXT Returns offset of the next table entry BINARY_INTEGER tablename.NEXT (offset)
DELETE Removes the specified element, range or all none tablename. DELETE([off_m, [off_n]])

When you're already at the FIRST or LAST element, PRIOR and NEXT return NULL, respectively.

DELETE can remove just the specified element, a range of elements, or all elements, using any of these forms:

tablename.DELETE(m); Deletes just one element entry
tablename.DELETE(m, n); Deletes all elements in the range [m..n] inclusive
tablename.DELETE; Deletes all elements in the table

If an offset within a specified range doesn't contain an entry, it's simply skipped (no exception is raised). The range has to be ascending; if m > n, DELETE does nothing.

Using SubtypesUser-defined subtypes were introduced in Oracle 7.2 with PL/SQL 2.2. Subtypes enable you to provide another, more meaningful name for a type, optionally

Page 211

constrained. They do not define storage, merely another name for a more general data type. For example, the predefined subtypes for NUMBER and CHAR use this facility. You cannot directly constrain a subtype with a fixed length; instead you must use a two-step method involving %TYPE and %ROWTYPE. Here are some examples (right and wrong):

SUBTYPE SSN_TYPE IS VARCHAR2;             -- legal (method #1)
emp_ssn SSN_TYPE(9);                      -- legal
SUBTYPE PHONE_TYPE IS VARCHAR2(10);       -- illegal!
big_string VARCHAR2(32767);               -- will be used below
TYPE MAX_STRING_TYPE IS big_string%TYPE;  -- legal (method #2)
dynam_str MAX_STRING_TYPE;                -- legal

Note method #2. This is one way of defining subtypes that are constrained by length. Another method is to base the variable on a constrained database column type. Using this method can help you bounds-check variables based on the subtype. When used judiciously, this method leads to better self-documenting code.

Subtypes based on a database column do not inherit the NOT NULL constraints of the column. The subtype does inherit the NOT NULL constraint of a PL/SQL variable declaration, but not the default assignment.

small_str VARCHAR2(20) NOT NULL := `blick';  -- #1
subtype SMALL_TYPE is small_str%TYPE;  -- fails NOT NULL constraint!
tiny_str VARCHAR2(1) := `T';                 -- #2
subtype TINY_TYPE is tiny_str%TYPE;
tiny TINY_TYPE;  -- does not inherit assignment!

Variables based on different unconstrained subtypes are compatible for assignment purposes only if those subtypes are based, in turn, on the same type. For example, DECIMAL and NUMERIC, being subtypes of NUMBER, are interchangeable. If the parent types are different, some sort of conversion must take place.

Changing One Type to AnotherAs with strongly typed languages, sometimes you need to convert one data type to another. This generally occurs when you're assigning a value to a variable or computing an expression. There are two ways to do this: implicitly and explicitly.

Implicit Type ConversionImplicit type conversion is performed by the PL/SQL compiler, with no effort on your part. For example, given the following:

DECLARE
  flt_x FLOAT(3,2) := 2.25;
  int_y INTEGER(6) := 100;
  flt_z DOUBLE(10,2);
BEGIN
  flt_z := flt_x + int_y;  -- implicit numeric conversion
END;
/

the integer value is implicitly converted up to a floating-point value in order to properly perform the arithmetic. This is typical among compilers. A value is converted to the most generalized type in order to perform the calculation. In PL/SQL, the VARCHAR2 data type is the most generalized type of all.

Previous | Table of Contents | Next