Dynamic SQL

💡 Dynamic SQL Example: DML Statement in Oracle PL/SQL

Dynamic SQL allows you to build SQL statements dynamically at runtime and execute them. This is useful when the exact SQL code is not known until the program runs. Below is a simple example using EXECUTE IMMEDIATE to execute a DML (SELECT) statement dynamically.

---

📌 Example: Count Total Employees Using Dynamic SQL

SET SERVEROUTPUT ON;

DECLARE
    SQL_STMT   VARCHAR2(1000);
    EMP_TOT    NUMBER;
BEGIN
    SQL_STMT := 'SELECT COUNT(*) FROM EMP';
    EXECUTE IMMEDIATE SQL_STMT INTO EMP_TOT;
    DBMS_OUTPUT.PUT_LINE('TOTAL EMP ARE: ' || EMP_TOT);
END;
/

---

🧠 Explanation:

• SQL_STMT: A variable that stores the SQL query string.
• EXECUTE IMMEDIATE: Executes the SQL command stored in SQL_STMT.
• INTO EMP_TOT: Stores the output of the SELECT statement.
• DBMS_OUTPUT: Displays the result in the output console.

---

✅ When to Use Dynamic SQL

• Building flexible, parameterized queries.
• Running DDL commands like CREATE, DROP, ALTER.
• Working with unknown table names or column lists at runtime.

---

📎 Related Posts

• Dynamic SQL vs Static SQL in Oracle
• How to Use EXECUTE IMMEDIATE with Bind Variables
• Best Practices for Writing Dynamic SQL

``` --- ✅ **Instructions to Use in Blogger**: * Go to Blogger post editor. * Switch to the **HTML view**. * Paste the HTML code above. * Add your images or customize the sidebar links if needed. Would you like the HTML for an example using **Dynamic DDL** or **Dynamic Insert/Update** next?