PL/SQL User's Guide and Reference

Contents Index Home Previous Next

PL/SQL Tables

Objects of type TABLE are called PL/SQL tables, which are modeled as (but not the same as) database tables. For example, a PL/SQL table of employee names is modeled as a database table with two columns, which store a primary key and character data, respectively. Although you cannot use SQL statements to manipulate a PL/SQL table, its primary key gives you array-like access to rows. Think of the key and rows as the index and elements of a one-dimensional array.

Like an array, a PL/SQL table is an ordered collection of elements of the same type. Each element has a unique index number that determines its position in the ordered collection. However, PL/SQL tables differ from arrays in two important ways. First, arrays have fixed lower and upper bounds, but PL/SQL tables are unbounded. So, the size of a PL/SQL table can increase dynamically. Second, arrays require consecutive index numbers, but PL/SQL tables do not. This characteristic, called sparsity, allows the use of meaningful index numbers. For example, you can use a series of employee numbers (such as 7369, 7499, 7521, 7566, ...) to index a PL/SQL table of employee names.

Why Use PL/SQL Tables?

PL/SQL tables help you move bulk data. They can store columns or rows of Oracle data, and they can be passed as parameters. So, PL/SQL tables make it easy to move collections of data into and out of database tables or between client-side applications and stored subprograms. You can even use PL/SQL tables of records to simulate local database tables.

Also, with the Oracle Call Interface (OCI) or the Oracle Precompilers, you can bind host arrays to PL/SQL tables declared as the formal parameters of a subprogram. That allows you to pass host arrays to stored functions and procedures.

Defining TABLE Types

To create PL/SQL tables, you take two steps. First, you define a TABLE type, then declare PL/SQL tables of that type. You can define TABLE types in the declarative part of any block, subprogram, or package using the syntax 

TYPE table_type_name IS TABLE OF datatype [NOT NULL]
   INDEX BY BINARY_INTEGER;

where table_type_name is a type specifier used in subsequent declarations of PL/SQL tables.

The INDEX BY clause must specify datatype BINARY_INTEGER, which has a magnitude range of -2147483647 .. 2147483647. If the element type is a record type, every field in the record must have a scalar datatype such as CHAR, DATE, or NUMBER.

To specify the element type, you can use %TYPE to provide the datatype of a variable or database column. In the following example, you define a TABLE type based on the ename column: 

DECLARE
   TYPE EnameTabTyp IS TABLE OF emp.ename%TYPE
      INDEX BY BINARY_INTEGER;

The next example shows that you can add the NOT NULL constraint to a TABLE type definition and so prevent the storing of nulls in PL/SQL tables of that type: 

DECLARE
   TYPE SalTabTyp IS TABLE OF emp.sal%TYPE NOT NULL
      INDEX BY BINARY_INTEGER;

An initialization clause is not required (or allowed).

You can also use %ROWTYPE to specify the element type. In the following example, you define a TABLE type based on the emp table:

DECLARE
   TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE
      INDEX BY BINARY_INTEGER;

In the final example, you use a RECORD type to specify the element type:

DECLARE
   TYPE TimeRecTyp IS RECORD (
      hour   SMALLINT := 0, 
      minute SMALLINT := 0,
      second SMALLINT := 0);
   TYPE TimeTabTyp IS TABLE OF TimeRecTyp
      INDEX BY BINARY_INTEGER;

Function Results

The example below shows that you can specify a TABLE type in the RETURN clause of a function specification. That allows the function to return a PL/SQL table of the same type. 

DECLARE
   TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE
      INDEX BY BINARY_INTEGER; 
   ...
   FUNCTION top_n_sals (n INTEGER) RETURN EmpTabTyp IS ...

Declaring PL/SQL Tables

Once you define a TABLE type, you can declare PL/SQL tables of that type, as the following examples show: 

DECLARE
   TYPE SalTabTyp IS TABLE OF emp.sal%TYPE
      INDEX BY BINARY_INTEGER;
   TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE
      INDEX BY BINARY_INTEGER;
   sal_tab SalTabTyp;  -- declare PL/SQL table
   emp_tab EmpTabTyp;  -- declare another PL/SQL table

The identifiers sal_tab and emp_tab represent entire PL/SQL tables. Each element of sal_tab will store an employee salary. Each element of emp_tab will store a whole employee record.

A PL/SQL table is unbounded; its index can include any BINARY_ INTEGER value. So, you cannot initialize a PL/SQL table in its declaration. For example, the following declaration is illegal:

sal_tab SalTabTyp := (1500, 2750, 2000, 950, 1800);  -- illegal

PL/SQL tables follow the usual scoping and instantiation rules. In a package, PL/SQL tables are instantiated when you first reference the package and cease to exist when you end the database session. In a block or subprogram, local PL/SQL tables are instantiated when you enter the block or subprogram and cease to exist when you exit.

As Parameters

You can declare PL/SQL tables as the formal parameters of functions and procedures. That way, you can pass PL/SQL tables to stored subprograms and from one subprogram to another. In the following example, you declare PL/SQL tables as the formal parameters of two packaged procedures:

PACKAGE emp_actions IS
   TYPE EnameTabTyp IS TABLE OF emp.ename%TYPE
      INDEX BY BINARY_INTEGER;
   TYPE SalTabTyp IS TABLE OF emp.sal%TYPE
      INDEX BY BINARY_INTEGER;
   ...
   PROCEDURE hire_batch (ename_tab IN EnameTabTyp, 
                         sal_tab   IN SalTabTyp, ...);
   PROCEDURE log_names (ename_tab IN EnameTabTyp);
END emp_actions;

To define the behavior of formal parameters, you use parameter modes. The OUT and IN OUT modes let you return values to the caller of a subprogram when you exit. If you exit successfully, PL/SQL assigns values to the actual parameters. However, if you exit with an unhandled exception, PL/SQL does not assign values to the actual parameters.

Referencing PL/SQL Tables

To reference elements in a PL/SQL table, you specify an index number using the syntax 

plsql_table_name(index)

where index is an expression that yields a BINARY_INTEGER value or a value implicitly convertible to that datatype. In the following example, you reference an element in the PL/SQL table hiredate_tab:

hiredate_tab(i + j - 1) ...

As the example below shows, the index number can be negative. (For an exception, see "Using Host Arrays with PL/SQL Tables" [*].)

hiredate_tab(-5) ...

The following example shows that you can reference the elements of a PL/SQL table in subprogram calls:

raise_salary(empno_tab(i), amount);  -- call subprogram

Assignments

You can assign one PL/SQL table to another only if they have the same datatype. For example, the following assignment is legal:

DECLARE
   TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE
      INDEX BY BINARY_INTEGER;
   TYPE TempTabTyp IS TABLE OF emp%ROWTYPE
      INDEX BY BINARY_INTEGER;
   emp_tab1 EmpTabTyp;
   emp_tab2 EmpTabTyp;
BEGIN
   ...
   emp_tab2 := emp_tab1;  -- assign one PL/SQL table to another

You can assign the value of an expression to a specific element in a PL/SQL table using the following syntax: 

plsql_table_name(index) := expression;

In the next example, you assign the sum of variables salary and increase to an element in the PL/SQL table sal_tab:

sal_tab(i) := salary + increase;

Note: Until an element is assigned a value, it does not exist. If you reference a nonexistent element, PL/SQL raises the predefined exception NO_DATA_FOUND.

PL/SQL Tables of Records

With a PL/SQL table of records, you use the following syntax to reference fields in a record:

plsql_table_name(index).field_name

For example, the following IF statement references a field in the record stored by the first element of the PL/SQL table emp_tab:

DECLARE
   TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE 
      INDEX BY BINARY_INTEGER;
   emp_tab EmpTabTyp;
BEGIN
   ...
   IF emp_tab(1).job = 'CLERK' THEN ...
END;

Function Results

When calling a function that returns a PL/SQL table, you use the following syntax to reference elements in the table:

function_name(parameters)(index)

For example, the following call to the function new_sals references the third element in the PL/SQL table sal_tab:

DECLARE
   TYPE SalTabTyp IS TABLE OF emp.sal%TYPE
      INDEX BY BINARY_INTEGER;
   salary REAL;
   FUNCTION new_sals (max_sal REAL) RETURN SalTabTyp IS
      sal_tab SalTabTyp;
   BEGIN 
      ...
      RETURN sal_tab;  -- return PL/SQL table
   END;
BEGIN
   salary := new_sals(5000)(3);  -- call function
   ...
END;

If the function result is a PL/SQL table of records, you use the following syntax to reference fields in a record:

function_name(parameters)(index).field_name

For example, the following call to the function new_depts references the field loc in the record stored by the third element of the PL/SQL table dept_tab:

DECLARE
   TYPE DeptTabTyp IS TABLE OF dept%ROWTYPE
      INDEX BY BINARY_INTEGER;
   FUNCTION new_depts (max_num INTEGER) RETURN DeptTabTyp IS 
      dept_tab DeptTabTyp;
   BEGIN
      ...
      RETURN dept_tab;
   END;
BEGIN
   ...
   IF new_depts(90)(3).loc = 'BOSTON' THEN ...
END;

Restriction Currently, you cannot use the syntax above to call a parameterless function because PL/SQL does not allow empty parameter lists. That is, the following syntax is illegal: 

function_name()(index)  -- illegal; empty parameter list

Instead, declare a local PL/SQL table to which you can assign the function result, then reference the PL/SQL table directly, as shown in the following example:

DECLARE
   TYPE JobTabTyp IS TABLE OF emp.job%TYPE
      INDEX BY BINARY_INTEGER;
   job_tab JobTabTyp;  -- declare local PL/SQL table
   job_title emp.job%TYPE;
   FUNCTION new_jobs RETURN JobTabTyp IS
      new_job_tab JobTabTyp;
   BEGIN 
      ...
      RETURN new_job_tab;  -- return PL/SQL table
   END;
BEGIN
   ...
   job_tab := new_jobs;  -- assign function result
   job_title := job_tab(1);  -- reference PL/SQL table
   ...
END;

Using PL/SQL Table Attributes

Attributes are characteristics of an object. For example, a cursor has the attributes %FOUND, %NOTFOUND, %ISOPEN, and %ROWCOUNT. Likewise, a PL/SQL table has the attributes EXISTS, COUNT, FIRST, LAST, PRIOR, NEXT, and DELETE. They make PL/SQL tables easier to use and your applications easier to maintain. To apply the attributes to a PL/SQL table, you use dot notation, as follows: 

plsql_table_name.attribute_name

The attributes EXISTS, PRIOR, NEXT, and DELETE take parameters. Each parameter must be an expression that yields a BINARY_INTEGER value or a value implicitly convertible to that datatype.

DELETE acts like a procedure, which is called as a statement. However, the other PL/SQL table attributes act like a function, which is called as part of an expression.

Using EXISTS

EXISTS(n) returns TRUE if the nth element in a PL/SQL table exists. Otherwise, EXISTS(n) returns FALSE. You can use EXISTS to avoid the exception NO_DATA_FOUND, which is raised when you reference a nonexistent element. In the following example, PL/SQL executes the assignment statement only if the element sal_tab(i) exists: 

IF sal_tab.EXISTS(i) THEN
   sal_tab(i) := sal_tab(i) + 500;
ELSE
   RAISE salary_missing;
END IF;

Using COUNT

COUNT returns the number of elements that a PL/SQL table contains. For example, if the PL/SQL table ename_tab contains 50 elements, the following IF condition is true: 

IF ename_tab.COUNT = 50 THEN
   ...
END;

COUNT is useful because the future size of a PL/SQL table is unconstrained and therefore unknown. Suppose you fetch a column of Oracle data into a PL/SQL table. How many elements does the PL/SQL table contain? COUNT gives you the answer.

You can use COUNT wherever an integer expression is allowed. In the following example, you use COUNT to specify the upper bound of a loop range:

FOR i IN 1 .. job_tab.COUNT LOOP
   ...
END LOOP;

Using FIRST and LAST

FIRST and LAST return the first and last (smallest and largest) index numbers in a PL/SQL table. If the PL/SQL table is empty, FIRST and LAST return nulls. If the PL/SQL table contains only one element, FIRST and LAST return the same index number, as the following example shows: 

IF sal_tab.FIRST = sal_tab.LAST THEN  -- sal_tab has one element
   ...
END IF;

The next example shows that you can use FIRST and LAST to specify the lower and upper bounds of a loop range provided each element in that range exists:

FOR i IN emp_tab.FIRST .. emp_tab.LAST LOOP
   ...
END LOOP;

In fact, you can use FIRST or LAST wherever an integer expression is allowed. In this example, you use FIRST to initialize a loop counter:

i BINARY_INTEGER := sal_tab.FIRST;
WHILE i IS NOT NULL LOOP
   ...
   IF sal_tab(i) > 5000 THEN
      RAISE over_limit;
   END IF;
END LOOP;

Using PRIOR and NEXT

PRIOR(n) returns the index number that precedes index n in a PL/SQL table. NEXT(n) returns the index number that succeeds index n. If n has no predecessor, PRIOR(n) returns a null. Likewise, if n has no successor, NEXT(n) returns a null.

PRIOR and NEXT do not wrap from one end of a PL/SQL table to the other. For example, the following statement assigns a null to n because the first element in a PL/SQL table has no predecessor:

n := sal_tab.PRIOR(sal_tab.FIRST);  -- assigns NULL to n

Note that PRIOR is the inverse of NEXT. For example, the following statement assigns index n to itself:

n := sal_tab.PRIOR(sal_tab.NEXT(n));  -- assigns n to n

You can use PRIOR or NEXT to traverse PL/SQL tables indexed by any series of integers. (Recall that index numbers need not be consecutive.) In the following example, the PL/SQL table is indexed by a series of employee numbers, which begins with 1000:

i BINARY_INTEGER := 1000;
WHILE i IS NOT NULL LOOP
   raise_salary(empno_tab(i));  -- pass element to procedure
   i := empno_tab.NEXT(i);  -- get index of next element
END LOOP;

Likewise, you can use PRIOR or NEXT to traverse PL/SQL tables from which some elements have been deleted, as the following generic example shows:

DECLARE
   ...
   i BINARY_INTEGER;
BEGIN
   ..
   i := any_tab.FIRST;  -- get index of first element
   WHILE i IS NOT NULL LOOP
      ...  -- process any_tab(i)
      i := any_tab.NEXT(i);  -- get index of next element
   END LOOP;
END;

Using DELETE

This attribute has three forms. DELETE removes all elements from a PL/SQL table. DELETE(n) removes the nth element. If n is null, DELETE(n) does nothing. DELETE(m, n) removes all elements in the range m .. n. If m is larger than n or if m or n is null, DELETE(m, n) does nothing.

DELETE lets you free the resources held by a PL/SQL table. DELETE(n) and DELETE(m, n) let you prune a PL/SQL table. Consider the following examples:

ename_tab.DELETE(3);       -- delete element 3
ename_tab.DELETE(5, 5);    -- delete element 5
ename_tab.DELETE(20, 30);  -- delete elements 20 through 30
ename_tab.DELETE(-15, 0);  -- delete elements -15 through 0
ename_tab.DELETE;          -- delete entire PL/SQL table

If an element to be deleted does not exist, DELETE simply skips it; no exception is raised.

Note: The amount of memory allocated to a PL/SQL table can increase or decrease dynamically. As you delete elements, memory is freed page by page. If you delete the entire PL/SQL table, all the memory is freed.

Restriction

Currently, you cannot use PL/SQL table attributes in a SQL statement. If you try, you get a compilation error, as the following example shows: 

DECLARE
   TYPE PartTabTyp IS TABLE OF VARCHAR2(30)
      INDEX BY BINARY_INTEGER;
   part_tab   PartTabTyp;
   part_count INTEGER;
BEGIN
   part_tab(65) := 'OIL PAN';
   part_tab(97) := 'TRUNK LOCK';
   part_tab(44) := 'SHOCK ABSORBER';
   ...
   SELECT part_tab.COUNT    -- causes compilation error
      INTO part_count FROM dual;
   ...
END;

Using PL/SQL Tables

Mainly, you use PL/SQL tables to move bulk data into and out of database tables or between client-side applications and stored subprograms.

Retrieving Oracle Data

You can retrieve Oracle data into a PL/SQL table in three ways: the SELECT INTO statement lets you select a single row of data; the FETCH statement or a cursor FOR loop lets you fetch multiple rows.

Using the SELECT INTO statement, you can select a column entry into a scalar element. Or, you can select an entire row into a record element. In the following example, you select a row from the database table dept into a record stored by the first element of the PL/SQL table dept_tab:

DECLARE
   TYPE DeptTabTyp IS TABLE OF dept%ROWTYPE
      INDEX BY BINARY_INTEGER;
   dept_tab DeptTabTyp;
BEGIN
   /* Select entire row into record stored by first element. */
   SELECT * INTO dept_tab(1) FROM dept WHERE deptno = 10;
   IF dept_tab(1).dname = 'ACCOUNTING' THEN ...
   ...
END;

Using the FETCH statement, you can fetch an entire column of Oracle data into a PL/SQL table of scalars. Or, you can fetch an entire table of Oracle data into a PL/SQL table of records. In the following example, you fetch rows from a cursor into the PL/SQL table of records emp_tab: 

DECLARE
   TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE
      INDEX BY BINARY_INTEGER;
   emp_tab EmpTabTyp;
   i BINARY_INTEGER := 0;
   CURSOR c1 IS SELECT * FROM emp;
BEGIN
   OPEN c1;
   LOOP
      i := i + 1;
      /* Fetch entire row into record stored by ith element. */
      FETCH c1 INTO emp_tab(i);
      EXIT WHEN c1%NOTFOUND;
      -- process data record
   END LOOP;
   CLOSE c1;
END;

After loading PL/SQL tables of records this way, you can use them to simulate local database tables.

Instead of the FETCH statement, you can use a cursor FOR loop, which implicitly declares its loop index as a record, opens the cursor associated with a given query, repeatedly fetches rows of values into fields in the record, then closes the cursor. In the following example, you use a cursor FOR loop to fetch entire columns of Oracle data into the PL/SQL tables ename_tab and sal_tab:

DECLARE
   TYPE EnameTabTyp IS TABLE OF emp.ename%TYPE
      INDEX BY BINARY_INTEGER;
   TYPE SalTabTyp IS TABLE OF emp.sal%TYPE
       INDEX BY BINARY_INTEGER;
   ename_tab EnameTabTyp;
   sal_tab   SalTabTyp;
   n BINARY_INTEGER := 0;
BEGIN
   /* Fetch entire columns into PL/SQL tables. */
   FOR emp_rec IN (SELECT ename, sal FROM emp) LOOP
      n := n + 1;
      ename_tab(n) := emp_rec.ename;
      sal_tab(n) := emp_rec.sal;
   END LOOP;
   ...
END;

Alternatively, you can place the cursor FOR loop in a standalone procedure. For example, given the declaration 

CREATE PACKAGE emp_defs AS
   TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE
      INDEX BY BINARY_INTEGER;
   ...
END emp_defs;

you might use the following standalone procedure to fetch all rows from the database table emp into the PL/SQL table of records emp_tab:

CREATE PROCEDURE load_emp_tab (
   n IN OUT BINARY_INTEGER,
   emp_tab OUT emp_defs.EmpTabTyp) AS  -- use packaged type
BEGIN
   n := 0;
   /* Fetch entire database table into PL/SQL table of records. */
   FOR emp_rec IN (SELECT * FROM emp) LOOP
      n := n + 1;
      emp_tab(n) := emp_rec;  -- assign record to nth element
   END LOOP;
END;

You can also use a cursor FOR loop to fetch Oracle data into packaged PL/SQL tables. For instance, given the declarations

CREATE PACKAGE emp_defs AS
   TYPE EmpnoTabTyp IS TABLE OF emp.empno%TYPE
      INDEX BY BINARY_INTEGER;
   empno_tab EmpnoTabTyp;
   ...
END emp_defs;

you might use the following block to fetch the database column empno into the public PL/SQL table empno_tab:

DECLARE
   ...
   i BINARY_INTEGER := 0;
BEGIN
   /* Fetch entire column into public PL/SQL table. */
   FOR emp_rec IN (SELECT empno FROM emp ORDER BY empno) LOOP
      i := i + 1;
      emp_defs.empno_tab(i) := emp_rec.empno;
   END LOOP;
   ...
END;

Inserting Oracle Data

You must use a loop to insert values from a PL/SQL table into a database column. For example, given the declarations 

CREATE PACKAGE emp_defs AS
   TYPE EmpnoTabTyp IS TABLE OF emp.empno%TYPE
      INDEX BY BINARY_INTEGER;
   TYPE EnameTabTyp IS TABLE OF emp.ename%TYPE
      INDEX BY BINARY_INTEGER;
   empno_tab EmpnoTabTyp;
   ename_tab EnameTabTyp;
   ...
END emp_defs;

you might use the following standalone procedure to insert values from the PL/SQL tables empno_tab and ename_tab into the database table emp:

CREATE PROCEDURE insert_emp_ids (
   rows      IN BINARY_INTEGER,
   empno_tab IN EmpnoTabTyp,
   ename_tab IN EnameTabTyp) AS
BEGIN
   FOR i IN 1..rows LOOP
      INSERT INTO emp (empno, ename)
         VALUES (empno_tab(i), ename_tab(i));
   END LOOP;
END;

Restriction You cannot reference record variables in the VALUES clause. So, you cannot insert entire records from a PL/SQL table of records into rows in a database table. For example, the following INSERT statement is illegal:

DECLARE
   TYPE DeptTabTyp IS TABLE OF dept%ROWTYPE
      INDEX BY BINARY_INTEGER;
   dept_tab DeptTabTyp;
   ...
BEGIN
   ...
   FOR i IN dept_tab.FIRST .. dept_tab.LAST LOOP
      INSERT INTO dept VALUES (dept_tab(i));  -- illegal
   END LOOP;
END;

Instead, you must specify one or more fields in the record, as the following example shows:

FOR i IN dept_tab.FIRST .. dept_tab.LAST LOOP
   INSERT INTO dept (deptno, dname)
      VALUES (dept_tab(i).deptno, dept_tab(i).dname);
END LOOP;

Using Host Arrays with PL/SQL Tables

With the Oracle Call Interface or the Oracle Precompilers, you can bind host arrays of scalars (but not host arrays of structures) to PL/SQL tables declared as the formal parameters of a subprogram. That allows you to pass host arrays to stored functions and procedures.

You can use a BINARY_INTEGER variable or compatible host variable to index the host arrays. Given the array subscript range m .. n, the corresponding PL/SQL table index range is always 1 .. n - m + 1. For example, if the array subscript range is 5 .. 10, the corresponding PL/SQL table index range is 1 .. (10 - 5 + 1) or 1 .. 6.

To assign all the values in a host array to elements in a PL/SQL table, you can use a subprogram call. In the Pro*C example below, you pass the host array salary to a PL/SQL block. From the block, you call a local function that declares the PL/SQL table sal_tab as one of its formal parameters. The function call assigns all values in the actual parameter salary to elements in the formal parameter sal_tab.

#include <stdio.h>
main()
{
   EXEC SQL BEGIN DECLARE SECTION;
      ...
      /* Declare host array. */
      float salary [100];
   EXEC SQL END DECLARE SECTION;

   /* Populate host array. */
   ...
   EXEC SQL EXECUTE
   DECLARE
      TYPE SalTabTyp IS TABLE OF emp.sal%TYPE
         INDEX BY BINARY_INTEGER;
      mid_salary  REAL;
      n  BINARY_INTEGER := 100;
      FUNCTION median (sal_tab SalTabTyp, n INTEGER)
         RETURN REAL IS
      BEGIN
          -- compute median salary
      END;
   BEGIN
      mid_salary := median(:salary, n);  -- pass array
      ...
   END;
   END-EXEC;
   ...
}

Conversely, you can use a subprogram call to assign all values in a PL/SQL table to corresponding elements in a host array. In the Pro*C example below, you call a standalone procedure (not shown), which declares three PL/SQL tables as OUT formal parameters. The corresponding actual parameters are host arrays. When the procedure finishes fetching a batch of employee data into the PL/SQL tables, it assigns all values in the PL/SQL tables to elements in the host arrays. 

#include <stdio.h>
...
EXEC SQL BEGIN DECLARE SECTION;
   ...
   int   array_size;
   int   number_returned;
   int   finished;
   /* Declare host arrays. */
   char  emp_name[10][11];
   char  job_title[10][10];
   float salary[10];
EXEC SQL END DECLARE SECTION;
...
main()
{
   ...
   array_size = 10;     /* determines batch size */
   number_returned = 0; /* needed for last batch */
   finished = 0;

   /* Array fetch loop. */
   for (;;)
   {
      EXEC SQL EXECUTE
      BEGIN 
         /* Call stored procedure to fetch a batch of data. */
         get_emps(:emp_name, :job_title, :salary,
            :array_size, :number_returned, :finished);
      END;
      END-EXEC;

      print_rows(number_returned);

      if (finished) break;
   }
   ...
}

Table 4 - 1 shows the legal datatype conversions between row values in a PL/SQL table and elements in a host array. For example, a host array of type VARCHAR2 is compatible with a PL/SQL table of type LONG, LONG RAW, RAW, or VARCHAR2.

PL/SQL Table
Host Array CHAR DATE LONG LONG RAW NUMBER RAW ROWID VARCHAR2
CHARF _/
CHARZ _/
DATE _/
DECIMAL _/
DISPLAY _/
FLOAT _/
INTEGER _/
LONG _/ _/
LONG VARCHAR _/ _/ _/ _/
LONG VARRAW _/ _/
NUMBER _/
RAW _/ _/
ROWID _/
STRING _/ _/ _/ _/
UNSIGNED _/
VARCHAR _/ _/ _/ _/
VARCHAR2 _/ _/ _/ _/
VARNUM _/
VARRAW _/ _/
Table 4 - 1. Legal Datatype Conversions

ARRAYLEN Statement

Suppose you pass a host array to a PL/SQL block for processing. By default, when binding the host array, the Oracle Precompilers use its declared dimension. However, you might not want to process the entire array, in which case you can use the ARRAYLEN statement to specify a smaller dimension. ARRAYLEN associates the host array with a host variable, which stores the smaller dimension.

Let us repeat the first example above using ARRAYLEN to override the default dimension of the host array salary: 

#include <stdio.h>
main()
{
   EXEC SQL BEGIN DECLARE SECTION;
      ...
      /* Declare host array. */
      float salary [100];
      int   my_dim;
      EXEC SQL ARRAYLEN salary (my_dim);
   EXEC SQL END DECLARE SECTION;
   /* Populate host array. */
   ...
   /* Set smaller host array dimension. */
   my_dim = 25;
   EXEC SQL EXECUTE
   DECLARE
      TYPE SalTabTyp IS TABLE OF emp.sal%TYPE
         INDEX BY BINARY_INTEGER;
      mid_salary  REAL;
      FUNCTION median (sal_tab SalTabTyp, n INTEGER)
         RETURN REAL IS
      BEGIN
         ... -- compute median salary
      END;
   BEGIN
      mid_salary := median(:salary, :my_dim);  -- pass array
      ...
   END;
   END-EXEC;
   ...
}

Only 25 array elements are passed to the PL/SQL block because ARRAYLEN downsizes the host array from 100 to 25 elements. As a result, when the PL/SQL block is sent to Oracle for execution, a much smaller host array is sent along. This saves time and reduces network traffic.

Contents Index Home Previous Next