Dynamic SQL

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:

  <title>Sample Form</title></head>
  <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
  <input type=submit value='Display Results'  />


The form might appear this way:

Blog Sample Page

This HTML could then invoke a PHP script that looks something like this:

<? php

  ... receive incoming parameters ...

  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.

Happy developing!

SQL Scripts for 1Z0-047

OCA Oracle Database SQL Certified Expert Exam Guide (1Z0-047)I’ve received a number of requests for an electronic copy of the SQL scripts in my book, OCA Oracle Database SQL Expert Exam Guide (Exam 1Z0-047).  I’ve emailed them to readers who have asked.  And I’m probably overdue for publishing them here in this blog.  So … now you can!

For info, visit the SQL Scripts page!

Enjoy! And as always, please let me know if you have any questions!



Well well well!

Hello out there … the Skere9 world is taking shape, slowly but surely … the Skerinators are working in the background to hammer the dots and cross the eyes and get the parts connected and the parameters passing and all the cool nifty bells and whistles … uh … well … belling and whistling, I guess. Yes, that’s it. BUT – you wouldn’t necessarily know all of that because the work so far has been in the background, but WAIT … more is coming soon …

Stay tuned.