In Oracle PL/SQL, how can you execute DDL statements such as CREATE, DROP, or TRUNCATE from within a PL/SQL block?

Introduction / Context:
PL/SQL blocks commonly manipulate data using DML statements, but sometimes they must also create or modify schema objects, such as temporary tables or indexes. These are DDL statements, and Oracle provides a mechanism to run them from PL/SQL through dynamic SQL. This question tests whether you know how to execute DDL from within PL/SQL in a safe and supported way.

Given Data / Assumptions:

• The database is Oracle and supports native dynamic SQL in PL/SQL.
• We need to execute statements such as CREATE TABLE, DROP TABLE, TRUNCATE TABLE, or ALTER INDEX from PL/SQL code.
• Security privileges for DDL have been granted to the executing schema.
• Transactions and commit behaviour of DDL are understood.

Concept / Approach:
In Oracle PL/SQL, DDL statements cannot be issued directly as static SQL inside a block. Instead, they must be executed through dynamic SQL using the EXECUTE IMMEDIATE statement or the older DBMS_SQL package. EXECUTE IMMEDIATE takes a string that contains a DDL or DML statement and sends it to the SQL engine for execution. This allows PL/SQL programs to create or modify schema objects at runtime, though it should be done with care because DDL implicitly commits transactions in Oracle.

Step-by-Step Solution:
Step 1: Concatenate or construct the DDL statement as a VARCHAR2 string, for example 'CREATE TABLE temp_data (id NUMBER)'.Step 2: Use the EXECUTE IMMEDIATE statement in PL/SQL, such as EXECUTE IMMEDIATE 'CREATE TABLE temp_data (id NUMBER)'; to send the DDL to the database engine.Step 3: For dynamic object names or conditional DDL, build the statement using variables and string concatenation before calling EXECUTE IMMEDIATE.Step 4: Be aware that Oracle treats DDL as an implicit commit, so any pending transaction is committed before and after the DDL statement, which affects transactional design.Step 5: Optionally, use DBMS_SQL when you need more flexible parsing and binding, but EXECUTE IMMEDIATE is simpler and preferred for most cases.

Verification / Alternative check:
To verify, write a small PL/SQL block that uses EXECUTE IMMEDIATE to create a simple table and then query USER_TABLES to confirm that the new table appears. Dropping the table with another EXECUTE IMMEDIATE of a DROP statement should remove it. Observing transaction logs and behaviour shows that DDL causes commits around these operations, reinforcing the need for careful use of dynamic DDL.

Why Other Options Are Wrong:
Option B incorrectly states that DDL cannot be executed from PL/SQL, which is contradicted by widespread use of dynamic SQL in real systems. Option C suggests that DDL runs automatically when PL/SQL starts, which is not true. Option D claims you must use shell scripts, ignoring the built in dynamic SQL features.

Common Pitfalls:
A frequent mistake is forgetting that DDL commits transactions, which can break assumptions about rollback. Another pitfall is allowing user input into dynamic DDL strings without validation, which can lead to security issues. Good practice is to minimize dynamic DDL, validate object names, and clearly document when and why schema changes occur from PL/SQL.

Final Answer:
You can execute DDL statements from PL/SQL by building them as strings and passing them to the EXECUTE IMMEDIATE statement of native dynamic SQL, which allows CREATE, DROP, TRUNCATE, and other DDL to run inside a PL/SQL block.