In my book OCA Oracle Database SQL Expert Exam Guide (Exam 1Z0-047), I mention the use of something called “dynamic SQL” on page 391. I’ve since received some email asking for more information about it. The concept I was referring to with the phrase “dynamic SQL” is simple: write software code to create SQL statements in real time, based on the logic within the script. A typical use in many websites today is to create some sort of code using PHP or Perl to display an HTML page, ask the user questions, and then issue a custom-written SQL statement to interact with the database accordingly. An example might be to create an HTML page that displays a form:
<html> <head> <title>Sample Form</title></head> <body> <h1>Click Display list of customers</h1> <form id="form1" name="form1" method="post" action="test.php"> Choose order for displaying the results: <select name=parm_OrderBy> <option value="DESC" selected>Descending <option value="ASC">Ascending </select><br> <input type=submit value='Display Results' /> </form> </body> </html>
The form might appear this way:
This HTML could then invoke a PHP script that looks something like this:
<? php ... receive incoming parameters ... $vQuery = "SELECT FIRST_NAME, LAST_NAME FROM CUSTOMERS ORDER BY LAST_NAME" if (parm_OrderBy == "DESC") $vQuery .= " DESC"; ... run query ... ?>
The idea is that the PHP script is bulding a text string in the $vQuery variable that may or may not include DESC after the ORDER BY, based on the value selected in the HTML form.
This is obviously just a very simple example. You can use much more complex code structures to build a variety of SQL statements, including SQL statements that may or may not include joins, GROUP BY clauses, and more. It’s just a matter of building text strings based on input, nothing more.
So that’s it! Now you know what dynamic SQL is about.