EXECUTE IMMEDIATE BULK COLLECT v14

You can use the BULK COLLECT clause with an EXECUTE IMMEDIATE statement to specify a collection to receive the returned rows:

EXECUTE IMMEDIATE '<sql_expression>;'
   BULK COLLECT INTO <collection> [,...]
   [USING {[<bind_type>] <bind_argument>} [, ...]}];

Where:

collection specifies the name of a collection.

bind_type specifies the parameter mode of the bind_argument.

  • A bind_type of IN specifies that the bind_argument contains a value that's passed to the sql_expression.
  • A bind_type of OUT specifies that the bind_argument receives a value from the sql_expression.
  • A bind_type of IN OUT specifies that the bind_argument is passed to sql_expression and then stores the value returned by sql_expression.

bind_argument specifies a parameter that contains a value that either:

  • Is passed to the sql_expression (specified with a bind_type of IN)
  • Receives a value from the sql_expression (specified with a bind_type of OUT)
  • Does both (specified with a bind_type of IN OUT)

If you specify a single collection, then collection can be a collection of a single field or a collection of a record type. If you specify more than one collection, each collection must consist of a single field.