Mumbai University > Information Technology > Sem 5 > Advanced Database Management System

Marks: 5M

Year: May 2016

• Dynamic SQL is a programming methodology for generating & running SQL statements at run time. It is useful when writing general purpose & flexible programs like ad_hoc query systems, when writing programs that must run database definition language. (DDL) statements, or the number or data types of its input & output variables.

• PL/SQL provides two ways to write dynamic SQL:

  • Native dynamic SQL, a PL/SQL language (that is, native) feature for building & running dynamic SQL statements.
  • DBMS_SQL Package, an API for building, running & describing dynamic SQL statements.

• WHEN YOU NEED DYNAMIC SQL

  • In PL/SQL, you need dynamic SQL to run:
    • SQL whose text is unknown at compile time for example, a SELECT statement that includes an identifier that is unknown at compile time (such as a table name) or a WHERE clause in which the number of sub clauses is unknown at compile time.
    • SQL that is not supported as static SQL. That is, any SQL construct not included in “description of static SQL”.