You can execute multiple SQL statements sequentially with a single packaged query. Multiple database-specific SQL statements can be invoked to create tables, modify tables, drop tables, insert rows, update rows, delete rows, invoke procedures, commit transactions, or roll them back. The sequence of SQL executions ends when an error occurs or after a SQL statement returns a result set.

Sequences of SQL statements can be sent to the data source through packaged queries as long as no SQL statement except the last one returns a result. The return of a result set cursor, or an exception, signals the end of the sequence of SQL executions.

The first line of SQL in the packaged query must set the multi-part separator character or set of characters. The following sample multi-part separator sets the SQL statement delimiter as two semicolons separated by a space:

This statement can be used to define other unique character sets to comply with any special data source query language requirements, but make sure the chosen delimiter is not used within any string, which can cause an unintended break.

Using a separator like “;” causes problems if a semicolon also shows up inside a string value substituted into the query.

Given definition of the multi-part separator, SQL statements are parsed and executed sequentially until the last statement returns a result set.

Note: If you execute anything that modifies the state of the connection in a way that survives beyond the current transaction, you can cause unexpected results for another user when and if the connection is put back into the pool.