Writing Dynamic SQL

When you write dynamic SQL, you will often follow one or more of the approaches outlined below.

Task

Approach

Run a non-SELECT dynamic query such as an UPDATE

Use EXECUTE IMMEDIATE.

Run a dynamic SELECT query and output the results to the console or a client application.

Use EXECUTE IMMEDIATE with ECHO SELECT

Run a dynamic SELECT query that returns multiple rows. Iterate through each row and perform an action on the row.

Use COLLECT with a query type variable.

Run a dynamic SELECT query that returns only one row and one column.

Use SCALAR with a QUERY type variable.

Last modified: April 28, 2021

Was this article helpful?