Learning Oracle PL/SQL

5.5. Building a Web-Based Search Page Using Dynamic SQL

Using dynamic SQL is not a core topic that beginners will need to know about on day one, but some applications simply cannot be written without it. So this section tries to describe this somewhat complex feature as simply as possible.

SQL is called "dynamic" when the exact statement doesn't necessarily exist until runtime, at which point the program actually builds it as a string. In our case, we're eventually going to write a program that will assemble a SELECT statement based on the criteria that the user provides in a search screen, and then execute^[6] it using PL/SQL's dynamic SQL features.

^[6] Let's hope it will run the statement rather than assassinate it.

5.5.1. Simple Dynamic SQL Using EXECUTE IMMEDIATE

Let's look at the simplest dynamic SQL syntax first. The starting point is the EXECUTE IMMEDIATE statement:

BEGIN
   EXECUTE IMMEDIATE sql_statement_string;
END;
/

where almost any SQL statement can go into sql_statement_string. For example, you could really do some damage by doing something like this:

DECLARE
   stmt VARCHAR2(50) := 'DROP TABLE books';
BEGIN
   EXECUTE IMMEDIATE stmt;  /* yes, it really will drop the table! */
END;
/

Fortunately, you can also run nice, safe SELECT statements using this approach. In fact, you can so something as innocent as this:

DECLARE
   stmt VARCHAR2(50) := 'SELECT * FROM books';
BEGIN
   EXECUTE IMMEDIATE stmt;
END;
/

That's really a silly thing to do because the query runs, but the data ...