Skip to Main Content
Learning Oracle PL/SQL
book

Learning Oracle PL/SQL

by Bill Pribyl, Steven Feuerstein
November 2001
Beginner content levelBeginner
424 pages
11h 11m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Learning Oracle PL/SQL

Learning Oracle PL/SQL

Darryl Hurley
Oracle PL/SQL Programming, Third Edition

Oracle PL/SQL Programming, Third Edition

Steven Feuerstein, Bill Pribyl

Publisher Resources

ISBN: 0596001800Supplemental ContentCatalog PageErrata