SQL - Dynamic SQL

> (Data|State) Management and Processing > (Data Type|Data Structure) > (Relation|Table) - Tabular data > Structured Query Language (SQL)

Table of Contents

1 - About

Some programs (a general-purpose report writer for example) must build and process a variety of SQL statements, where the exact text of the statement is unknown until run time. Such statements probably change from execution to execution. They are called dynamic SQL statements.

Advertising

3 - Example

SELECT 
  statement||DECODE(ROWNUM, MAX(ROWNUM) over (PARTITION BY 1),' order by aantal, table_name;',' union all')
FROM
(
  SELECT 'select '''||table_name||''' table_name, count(1) numberOfRows from '||table_name AS statement
  FROM user_tables
  ORDER BY table_name
);