Dynamic SQL in PL/SQL: EXECUTE IMMEDIATE Quiz Quiz

Explore key concepts and best practices of using dynamic SQL with EXECUTE IMMEDIATE in PL/SQL. Assess your understanding of syntax, usage scenarios, variable binding, and security considerations for dynamic SQL programming.

1. Basic Syntax

   Which keyword is used in PL/SQL to execute a dynamically constructed SQL statement or block of code?

   1. RUN IMMEDIATE
   2. EXECUTE IMMEDIATE
   3. PERFORM IMMEDIATE
   4. EXECUTE NOW

   Explanation: EXECUTE IMMEDIATE is the standard PL/SQL command for running dynamic SQL statements or anonymous blocks from PL/SQL code. RUN IMMEDIATE and PERFORM IMMEDIATE are not valid PL/SQL statements, and EXECUTE NOW does not exist in PL/SQL. Only EXECUTE IMMEDIATE is accepted by the compiler.

2. Binding Variables

   When using EXECUTE IMMEDIATE to run an INSERT statement with input values, which clause binds host variables safely into the statement?

   1. USING
   2. ASSIGN
   3. WITH
   4. BINDING

   Explanation: USING is the correct clause for binding variables to placeholders in the dynamic SQL when using EXECUTE IMMEDIATE. BINDING and ASSIGN are not valid clauses in this context and do not work in PL/SQL. WITH is used in other contexts but not for binding host variables.

3. Retrieving Values

   How can you return a value from a dynamic SQL statement using EXECUTE IMMEDIATE in PL/SQL, such as retrieving a column value into a variable?

   1. OUTPUT USING
   2. RETURNING TO
   3. INTO clause
   4. SELECT INTO

   Explanation: The INTO clause is used with EXECUTE IMMEDIATE in PL/SQL to fetch results directly into variables, like retrieving a column value. SELECT INTO is used in static SQL but not with EXECUTE IMMEDIATE. OUTPUT USING and RETURNING TO are incorrect as they are not valid options for value retrieval in this context.

4. SQL Statement Type

   Which type of SQL statement cannot be executed directly using EXECUTE IMMEDIATE in PL/SQL?

   1. UPDATE
   2. SELECT INTO
   3. DELETE
   4. COMMIT

   Explanation: COMMIT is a transaction control statement, not a DML or DDL statement, so it cannot be run with EXECUTE IMMEDIATE. SELECT INTO, UPDATE, and DELETE can all be run with EXECUTE IMMEDIATE when structured properly. Attempting to use EXECUTE IMMEDIATE for COMMIT will result in an error.

5. SQL Injection Risk

   What is a key security concern when using EXECUTE IMMEDIATE to construct SQL statements with user input in PL/SQL?

   1. Statement caching
   2. Deadlocks
   3. Data throttling
   4. SQL injection

   Explanation: SQL injection arises when user input is interpolated directly into SQL strings constructed for EXECUTE IMMEDIATE, allowing malicious code execution. Data throttling, deadlocks, and statement caching are distinct database topics and not the primary security risk in this context.

6. Binding Limitations

   Which statement best describes how you should bind identifiers like table or column names in EXECUTE IMMEDIATE statements?

   1. All identifiers must be declared as constants.
   2. Identifiers can always be bound with the USING keyword.
   3. Only table names can be bound, not column names.
   4. You cannot bind identifiers; they must be concatenated into the string.

   Explanation: Identifiers, such as table or column names, cannot be bound using the USING clause and must be injected directly into the string when building dynamic SQL. The USING keyword works only for values, not identifiers. Stating identifiers as constants does not affect how they are combined dynamically, and not all identifiers have special binding features.

7. Bulk Processing

   Which clause allows iterative assignment of values to dynamic SQL statements within a FORALL statement in PL/SQL?

   1. BULK
   2. BATCH
   3. LOOP INTO
   4. USING

   Explanation: USING is also used with FORALL in combination with EXECUTE IMMEDIATE for passing collections of values to each iteration of the SQL statement. BULK, LOOP INTO, and BATCH are not correct clauses in this context and are not recognized PL/SQL keywords for this use.

8. Using Placeholders

   How are placeholders for bound values represented in the dynamic SQL string passed to EXECUTE IMMEDIATE in PL/SQL?

   1. #1, #2, ...
   2. ?
   3. :1, :2, ...
   4. u00261, u00262, ...

   Explanation: In dynamic SQL with EXECUTE IMMEDIATE, placeholders for bound values are indicated by colon-prefixed numbers like :1, :2, etc. The question mark and ampersand formats are used in other languages or tools, while hash-prefixed forms are not valid in PL/SQL.

9. DDL in PL/SQL

   Which of the following statements can be executed dynamically with EXECUTE IMMEDIATE to create database objects in PL/SQL?

   1. CREATE TABLE
   2. SELECT * FROM
   3. PROMPT
   4. DESCRIBE

   Explanation: CREATE TABLE is a Data Definition Language (DDL) statement that can be executed dynamically using EXECUTE IMMEDIATE in PL/SQL. SELECT * FROM is a query statement and can also be executed, but DESCRIBE and PROMPT are not valid SQL or PL/SQL commands and cannot be used with EXECUTE IMMEDIATE.

10. Observing Results

    Which method can you use to check the number of rows affected by a DML statement executed via EXECUTE IMMEDIATE?

    1. COUNT(*)
    2. SQL%ROWCOUNT
    3. ROWNUM
    4. SELECT COUNT FROM DUAL

    Explanation: SQL%ROWCOUNT is a PL/SQL cursor attribute that reflects the count of rows affected by the last DML statement, including those run with EXECUTE IMMEDIATE. ROWNUM is a pseudocolumn for result ordering, COUNT(*) is an aggregate function used in queries, and SELECT COUNT FROM DUAL is not valid SQL syntax.