SQL - Dynamic SQL

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

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.


3 - Example

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