Oracle PL/SQL Programming: A Developer's Workbook

Chapter 21. DBMS_SQL Built-in Package

Beginner

Q:
21-1.	Dynamic SQL means that the SQL string, whether it be a SELECT statement or a DDL statement like CREATE TABLE, is constructed as the program is running, rather than when it is compiled.
Q:
21-2.	Dynamic PL/SQL means that the PL/SQL block of code is constructed as the program is running, rather than when it is compiled.
Q:
21-3.	The four methods of dynamic SQL are: Method 1 Non-queries (DDL and DML—inserts, updates, deletes) that do not have any placeholders (do not bind any variables from the calling program). Method 2 Nonqueries (DDL and DML—inserts, updates, deletes) that contain a fixed (at compile time) number of placeholders. Method 3 Queries (SELECT statements) that contain a fixed (at compile time) number of columns and placeholders. Method 4 Queries and DML statements that contain a variable number of columns and/or placeholders. There is no way to know, in other words, until runtime just how many columns you are querying or placeholders you are binding to variables.
Q:
21-4.	A placeholder is an identifier (maximum of 30 characters, must start with a letter, and then may contain letters, numbers, or any of the characters #, $ and _) preceded by a colon, appearing inside a literal string, as in: 'SELECT latest_excuse FROM pentagon WHERE budget_overrun > :amount' In this string, “:amount” is a placeholder, which means you have to “bind” a value to that placeholder before you can execute the query. Notice that I have embedded line breaks inside ...