In a collection-derived table

You cannot specify a named row type to declare a row variable that you use in a collection-derived table. does not have information about the named row type, only the database server does. For example, suppose your database has a named row type, r1, and a table, tab1, that are defined as follows:

CREATE ROW TYPE r1 (i integer);

CREATE TABLE tab1
(
   nt_col INTEGER,
   row_col r1
);

To access this column, suppose you declare two row variables, as follows:

EXEC SQL BEGIN DECLARE SECTION;
row (i integer) row1;
row (j r1) row2;
EXEC SQL END DECLARE SECTION;

With these declarations, the following statement succeeds because has the information it needs about the structure of row1:

EXEC SQL update table(:row1) set i = 31;
checksql("UPDATE Collection Derived Table 1");

The following statement fails; however, because does not have the necessary information to determine the correct storage structure of the row2 row variable.

EXEC SQL update table(:row2) set j = :row1;
checksql("UPDATE Collection Derived Table 2");

Similarly, the following statement also fails. In this case, treats r1 as a user-defined type instead of a named row type.

EXEC SQL insert into tab1 values (:row2);
checksql("INSERT row variable");

You can get around this restriction in either of the following ways:

• Use the actual data types in the row-variable declarations, as the following example shows:

  EXEC SQL BEGIN DECLARE SECTION;
  row (i integer) row1;
  row (j row(i integer)) row2;
  EXEC SQL END DECLARE SECTION;

• Declare an untyped row variable and perform a select so that obtains the data type information from the database server.

  EXEC SQL BEGIN DECLARE SECTION;
  row (i integer) row1;
  row row2_untyped;
  EXEC SQL END DECLARE SECTION;
  ;
  
  EXEC SQL select row_col into :row2_untyped from tab1;

  For this method to work, at least one row must exist in table tab1.