Basic DDL Operations
1. Create a Table Dynamically
Description: This example dynamically creates a table using EXECUTE IMMEDIATE.
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE test_table (id NUMBER, name VARCHAR2(50))';
END;
/
2. Drop a Table Dynamically
Description: This example dynamically drops an existing table.
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE test_table';
END;
/
3. Add a Column to a Table Dynamically
Description: This example dynamically adds a new column to an existing table.
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE test_table ADD (created_date DATE)';
END;
/
4. Drop a Column from a Table Dynamically
Description: This example dynamically removes a column from a table.
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE test_table DROP COLUMN created_date';
END;
/
5. Rename a Table Dynamically
Description: This example dynamically renames an existing table.
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE test_table RENAME TO new_test_table';
END;
/
Basic DML Operations
11. Insert Data Dynamically
Description: This example dynamically inserts a row into a table.
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO test_table (id, name) VALUES (1, ''John'')';
END;
/
12. Update Data Dynamically
Description: This example dynamically updates an existing row.
BEGIN
EXECUTE IMMEDIATE 'UPDATE test_table SET name = ''Jane'' WHERE id = 1';
END;
/
13. Delete Data Dynamically
Description: This example dynamically deletes a row from a table.
BEGIN
EXECUTE IMMEDIATE 'DELETE FROM test_table WHERE id = 1';
END;
/
Dynamic Queries
21. Dynamic Query with WHERE Clause
Description: This example executes a dynamic SELECT statement using a bind variable in the WHERE clause.
DECLARE
v_id NUMBER := 1;
v_name VARCHAR2(50);
BEGIN
EXECUTE IMMEDIATE
'SELECT name FROM test_table WHERE id = :1'
INTO v_name
USING v_id;
DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
END;
/
Dynamic PL/SQL Blocks
33. Dynamic PL/SQL Block with EXCEPTION Handling
Description: This example executes a dynamic PL/SQL block containing exception handling.
BEGIN
EXECUTE IMMEDIATE '
BEGIN
UPDATE test_table
SET name = ''Updated''
WHERE id = 1;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;';
END;
/
Advanced Dynamic SQL Operations
63. Dynamic SQL to Execute Dynamic Cursor
Description: This example opens a REF CURSOR using a dynamically supplied SQL statement.
DECLARE
v_cursor SYS_REFCURSOR;
v_name VARCHAR2(50);
BEGIN
OPEN v_cursor FOR 'SELECT name FROM test_table';
LOOP
FETCH v_cursor INTO v_name;
EXIT WHEN v_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
END LOOP;
CLOSE v_cursor;
END;
/
Real-Time Use Cases
81. Dynamic SQL to Handle Dynamic Business Logic
Description: This example constructs an UPDATE statement using a dynamic business condition.
DECLARE
v_logic VARCHAR2(100) := 'id = 1';
v_query VARCHAR2(1000);
BEGIN
v_query :=
'UPDATE test_table SET name = ''New Name'' WHERE ' ||
v_logic;
EXECUTE IMMEDIATE v_query;
END;
/
Complex Business Scenarios
100. Dynamic SQL to Implement Real-Time Data Synchronization
Description: This example dynamically constructs a MERGE statement to synchronize source and target tables.
DECLARE
v_source_table VARCHAR2(50) := 'source_table';
v_target_table VARCHAR2(50) := 'target_table';
v_query VARCHAR2(1000);
BEGIN
v_query :=
'MERGE INTO ' || v_target_table ||
' t USING ' || v_source_table ||
' s ON (t.id = s.id) ' ||
'WHEN MATCHED THEN UPDATE SET t.name = s.name ' ||
'WHEN NOT MATCHED THEN INSERT (id, name) ' ||
'VALUES (s.id, s.name)';
EXECUTE IMMEDIATE v_query;
END;
/
Note: Dynamic SQL should be used carefully when SQL text contains user-controlled values. Prefer bind variables where possible and validate dynamically supplied object names.