Absolutely! Let’s take each key Dynamic SQL concept and provide a real-time example code snippet with annotated explanations. This will make it crystal clear and resume-ready.
1️⃣ EXECUTE IMMEDIATE
DECLARE
table_name VARCHAR2(30) := 'TEMP_EMP';
BEGIN
-- Dynamically drop the table at runtime
EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;
EXCEPTION
WHEN OTHERS THEN
-- Handles the case if the table does not exist
DBMS_OUTPUT.PUT_LINE('Table does not exist or cannot be dropped.');
END;
Explanation:
EXECUTE IMMEDIATE allows you to build and run SQL statements as strings at runtime.
Ideal for DDL/DML operations where object names or values are not known in advance.
Exception handling ensures the block doesn’t fail if the table is missing.
2️⃣ DBMS_SQL
DECLARE
cur_handle NUMBER;
col_value VARCHAR2(100);
BEGIN
cur_handle := DBMS_SQL.OPEN_CURSOR;
-- Parse dynamic query with a bind variable
DBMS_SQL.PARSE(cur_handle, 'SELECT first_name FROM employees WHERE department_id = :dept', DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(cur_handle, ':dept', 10);
DBMS_SQL.DEFINE_COLUMN(cur_handle, 1, col_value, 100);
DBMS_SQL.EXECUTE(cur_handle);
IF DBMS_SQL.FETCH_ROWS(cur_handle) > 0 THEN
DBMS_SQL.COLUMN_VALUE(cur_handle, 1, col_value);
DBMS_OUTPUT.PUT_LINE('Employee: ' || col_value);
END IF;
DBMS_SQL.CLOSE_CURSOR(cur_handle);
END;
Explanation:
DBMS_SQL is used for advanced dynamic queries where column structure may not be known at compile time.
Bind variables enhance security and prevent SQL injection.
Useful in dynamic reporting, ETL, and schema-independent operations.
3️⃣ Bind Variables
DECLARE
dept_id NUMBER := 20;
emp_name VARCHAR2(50);
BEGIN
EXECUTE IMMEDIATE
'SELECT first_name FROM employees WHERE department_id = :d'
INTO emp_name
USING dept_id; -- Bind variable passed securely
DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_name);
END;
Explanation:
Bind variables improve security by preventing SQL injection.
Also help with performance by allowing query plan reuse.
4️⃣ Anonymous PL/SQL Blocks
BEGIN
EXECUTE IMMEDIATE '
BEGIN
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;
INSERT INTO audit_log(action) VALUES(''Salary Updated'');
END;';
END;
Explanation:
Multiple statements can be executed dynamically in a single anonymous PL/SQL block.
Useful for batch updates or operations that require multiple steps without creating stored procedures.
5️⃣ DDL / DML Execution
DECLARE
src_table VARCHAR2(30) := 'EMPLOYEES';
backup_table VARCHAR2(30) := 'EMPLOYEES_BACKUP';
BEGIN
-- Create backup table dynamically
EXECUTE IMMEDIATE 'CREATE TABLE ' || backup_table || ' AS SELECT * FROM ' || src_table;
END;
Explanation:
Shows ability to dynamically manage schema and data.
Useful for backups, table migrations, or ETL prep.
6️⃣ Exception Handling
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE NON_EXISTENT_TABLE';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error caught: ' || SQLERRM);
END;
Explanation:
Dynamic SQL can fail at runtime; robust exception handling ensures process continuity.
Demonstrates production-level reliability.
7️⃣ Runtime Flexibility / Parameterization
DECLARE
filter_column VARCHAR2(30) := 'department_id';
filter_value NUMBER := 30;
emp_name VARCHAR2(50);
BEGIN
EXECUTE IMMEDIATE
'SELECT first_name FROM employees WHERE ' || filter_column || ' = :val'
INTO emp_name
USING filter_value;
DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_name);
END;
Explanation:
Query structure and filters can adapt at runtime.
Shows dynamic, reusable, and maintainable code for varying business requirements.
✅ How to integrate in a resume (2–3 liner paragraph):
“Implemented Dynamic SQL using EXECUTE IMMEDIATE and DBMS_SQL to perform flexible DML/DDL operations at runtime. Leveraged bind variables, anonymous PL/SQL blocks, and robust exception handling to enable secure, high-performance, and maintainable database workflows with runtime adaptability.”
Top comments (0)