Distinguish between columns and row variables

When you use the collection-derived table clause with a SELECT or UPDATE statement, processes the statement. It does not send it to the database server. Therefore, some of the syntax checking that the database server performs is not done on SQL statements that include the collection-derived table clause.

In particular, the preprocessor cannot distinguish between column names and host variables. Therefore, when you use the collection-derived table clause with an UPDATE statement to modify a row host variable, the preprocessor does not check that you correctly specify host variables. You must ensure that you use valid host-variable syntax.

If you omit the host-variable symbol (colon (:) or dollar sign ($)), the preprocessor assumes that the name is a column name. For example, the following UPDATE statement omits the colon for the clob_ins host variable in the SET clause:

EXEC SQL update table(:named_row1)
   set (int_fld, clob_fld, dollar_fld) = 
      (10000000, clob_ins, 110.02);