#4(Main Page) - Warzone. Static & Dynamic SQL.

---

    MAKING A DECISION... Dynamic vs Static

---

• Dynamic SQL is powerful & super flexible; but is fraught with risk. Use it only when absolutely necessary.
• Of course, we need to write SQL statements to both fetch from and change the contents of tables. And when we want that SQL to run as quickly, be as secure as possible, easy to maintain as possible, we will write PL/SQL APIs in the form of packages around your SQL statements. Now we need to make a decision regarding how to write that SQL inside PL/SQL...and  have two basic choices :-

• Dynamic SQL
• Static SQL

---

•  SQL statements are Dynamic when the statement is parsed at runtime (Sample code on page #2).

---

•  SQL statements are Static OR Embedded SQL when the statements are parsed at the instant the PL/SQL program unit is compiled (Sample code discussed on page #3).

---

A  very simple rule for deciding whether to write static or dynamic SQL:

Construct and execute SQL at runtime only when you have to(The places one can use static SQL discussed on page #2).

Three reasons to follow this principle:

• Maintainability The code written to support dynamic SQL is more & harder to maintain and sometimes to understand too.
• Performance Although the overhead of executing dynamic SQL has been 'way down over the years', use of static SQL still has an edge due to faster execution. It’s easier to optimize static SQL statements, as one can analyze explain plans before the execution in production and modify the SQL accordingly.
• And, Security Dynamic SQL makes way for to SQL injection, which may lead to data corruption and leak sensitive data. It's impossible to put in some malicious code into static SQL statements. If users are asked in to put row orders for a table(for instance), it may sometimes lead to clashing data or inconsistencies, which is almost undesirable, for the smallest as well as the largest of database users.

---

After reading this, you might think that dynamic SQL's flexibilities are a gimmick; trash it & just force - forbid the use of Dynamic SQL in every possible databases.

---

But, you must have gone through the fact that, use of Dynamic SQL is inevitable at instances OR in specific applications...you can for sure not hard-code values in an application which has dynamic requirements.

---

It is quite common that an urge to use dynamic SQL is due to a mistake earlier in the design where they would be violation of some or the other violation of  basic rules for relational databases.

At the end, what we suggest is- try to implement Static OR Embedded SQL at all possible locations. Even the professionals agree on this and you may find many blogs supporting the fact (as per the experiences while writing this blog). But, when you are at a point when the use of Dynamic SQL is inevitable, design your system (databases & queries) with utmost care. Do pay special attention while designing table structure, applying constraints, and what data can be really allowed to be dynamic; rest can be just hard-coded OR be static-ified(using sequences for numbering, etc.).

---

---

                                        QUICK  SUMMARY

                                         (Static SQL vs Dynamic SQL)

  

Static (Embedded) SQL	Dynamic (Interactive) SQL
In Static SQL, 'how database will be accessed' is predetermined SQL statement.	In Dynamic SQL, 'how database will be accessed' is determined at run time.
More quick and efficient.	Comparatively less and efficient.
SQL statements are compiled at compile time.	SQL statements are compiled at run time.
Parsing, Validation, Optimization and Generation of application plan are done at compile time.	Parsing, Validation, Optimization and Generation of application plan are done at run time.
It is generally used for situations where data is distributed uniformly.	It is generally used for situations where data is distributed non uniformly.
Less flexible.	It is more flexible.

---

---

  Click on the topic name below to navigate to other pages 

---

PREVIOUS :-

#3 - What is Static or Embedded SQL