What is Dynamic SQL?
When the pattern of database access is known in advance then
static SQL is very adequate to serve us. Sometimes, in many applications we may
not know the pattern of database access in advance. For example, a report
writer must be able to decide at run time that which SQL statements will be
needed to access the database. Such a need can’t be fulfilled with static SQL
and requires an advanced form of static SQL known as dynamic SQL.
Also Read: Embedded SQL (Static SQL) in DBMS
the host variables (host variables allows us to input values for search
condition at run time), we can achieve a little bit dynamicness, for e.g.,
column name or table asked at run time not possible with embedded SQL. For
having such a feature we need dynamic SQL.
Dynamic SQL Concepts
- In dynamic SQL, the SQL statements are not hard coded in the
programming language. The text of the SQL statement is asked at the run time to
- In dynamic SQL, the SQL statements that are to be executed
are not known until runtime, so DBMS can’t get prepared for executing the
statements in advanced.
- When the program is executed, the DBMS takes the text
of SQL statements to execute the statements that are executed in such a manner
called statement string. Once DBMS receives the text, it goes through a five
steps execution as illustrated below.
Dynamic Statement Execution (Execute Immediate)
of dynamic SQL. This statement passes the text of SQL statements to DBMS and
asks the DBMS to execute the SQL statements immediately.
- The program constructs a SQL statement as a string of text
in one of its data areas (called a buffer).
- The program passes the SQL statements to the DBMS with the
EXECUTE IMMEDIATE statement.
- The DBMS executes the statement and sets the SQL CODE/SQL
STATE values to flag the finishing status same like if the statement had been
hard coded using static SQL.