Basic DDL Operations
1. Create a Table Dynamically
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE test_table (id NUMBER, name VARCHAR2(50))';
END;
2. Drop a Table Dynamically
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE test_table';
END;
3. Add a Column to a Table Dynamically
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE test_table ADD (created_date DATE)';
END;
4. Drop a Column from a Table Dynamically
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE test_table DROP COLUMN created_date';
END;
5. Rename a Table Dynamically
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE test_table RENAME TO new_test_table';
END;
6. Create an Index Dynamically
BEGIN
EXECUTE IMMEDIATE 'CREATE INDEX idx_name ON test_table(name)';
END;
7. Drop an Index Dynamically
BEGIN
EXECUTE IMMEDIATE 'DROP INDEX idx_name';
END;
8. Create a Sequence Dynamically
BEGIN
EXECUTE IMMEDIATE 'CREATE SEQUENCE test_seq START WITH 1 INCREMENT BY 1';
END;
9. Drop a Sequence Dynamically
BEGIN
EXECUTE IMMEDIATE 'DROP SEQUENCE test_seq';
END;
10. Create a View Dynamically
BEGIN
EXECUTE IMMEDIATE 'CREATE VIEW test_view AS SELECT * FROM test_table';
END;
Basic DML Operations
11. Insert Data Dynamically
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO test_table (id, name) VALUES (1, ''John'')';
END;
12. Update Data Dynamically
BEGIN
EXECUTE IMMEDIATE 'UPDATE test_table SET name = ''Jane'' WHERE id = 1';
END;
13. Delete Data Dynamically
BEGIN
EXECUTE IMMEDIATE 'DELETE FROM test_table WHERE id = 1';
END;
14. Merge Data Dynamically
BEGIN
EXECUTE IMMEDIATE '
MERGE INTO test_table t
USING (SELECT 1 AS id, ''Jane'' AS name FROM dual) 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)';
END;
15. Truncate a Table Dynamically
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE test_table';
END;
16. Insert Data with Bind Variables
DECLARE
v_id NUMBER := 2;
v_name VARCHAR2(50) := 'Doe';
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO test_table (id, name) VALUES (:1, :2)' USING v_id, v_name;
END;
17. Update Data with Bind Variables
DECLARE
v_id NUMBER := 2;
v_name VARCHAR2(50) := 'Smith';
BEGIN
EXECUTE IMMEDIATE 'UPDATE test_table SET name = :1 WHERE id = :2' USING v_name, v_id;
END;
18. Delete Data with Bind Variables
DECLARE
v_id NUMBER := 2;
BEGIN
EXECUTE IMMEDIATE 'DELETE FROM test_table WHERE id = :1' USING v_id;
END;
19. Select Data Dynamically into a Variable
DECLARE
v_name VARCHAR2(50);
BEGIN
EXECUTE IMMEDIATE 'SELECT name FROM test_table WHERE id = 1' INTO v_name;
DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
END;
20. Count Records Dynamically
DECLARE
v_count NUMBER;
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM test_table' INTO v_count;
DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
END;
Dynamic Queries
21. Dynamic Query with 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;
22. Dynamic Query with IN Clause
DECLARE
v_name VARCHAR2(50);
BEGIN
EXECUTE IMMEDIATE 'SELECT name FROM test_table WHERE id IN (1, 2)' INTO v_name;
DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
END;
23. Dynamic Query with LIKE Clause
DECLARE
v_name VARCHAR2(50);
BEGIN
EXECUTE IMMEDIATE 'SELECT name FROM test_table WHERE name LIKE ''%John%''' INTO v_name;
DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
END;
24. Dynamic Query with ORDER BY Clause
DECLARE
v_name VARCHAR2(50);
BEGIN
EXECUTE IMMEDIATE 'SELECT name FROM test_table ORDER BY id DESC' INTO v_name;
DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
END;
25. Dynamic Query with GROUP BY Clause
DECLARE
v_count NUMBER;
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(*), name FROM test_table GROUP BY name' INTO v_count;
DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
END;
26. Dynamic Query with HAVING Clause
DECLARE
v_count NUMBER;
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(*), name FROM test_table GROUP BY name HAVING COUNT(*) > 1' INTO v_count;
DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
END;
27. Dynamic Query with JOIN Clause
DECLARE
v_name VARCHAR2(50);
BEGIN
EXECUTE IMMEDIATE 'SELECT t1.name FROM test_table t1 JOIN another_table t2 ON t1.id = t2.id' INTO v_name;
DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
END;
28. Dynamic Query with SUBQUERY
DECLARE
v_name VARCHAR2(50);
BEGIN
EXECUTE IMMEDIATE 'SELECT name FROM test_table WHERE id = (SELECT MAX(id) FROM test_table)' INTO v_name;
DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
END;
29. Dynamic Query with CASE Statement
DECLARE
v_result VARCHAR2(50);
BEGIN
EXECUTE IMMEDIATE 'SELECT CASE WHEN id = 1 THEN ''One'' ELSE ''Other'' END FROM test_table WHERE id = 1' INTO v_result;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
END;
30. Dynamic Query with Aggregation
DECLARE
v_avg NUMBER;
BEGIN
EXECUTE IMMEDIATE 'SELECT AVG(id) FROM test_table' INTO v_avg;
DBMS_OUTPUT.PUT_LINE('Average ID: ' || v_avg);
END;
Dynamic PL/SQL Blocks
31. Dynamic PL/SQL Block to Create and Populate a Table
BEGIN
EXECUTE IMMEDIATE '
BEGIN
EXECUTE IMMEDIATE ''CREATE TABLE temp_table (id NUMBER, name VARCHAR2(50))'';
EXECUTE IMMEDIATE ''INSERT INTO temp_table (id, name) VALUES (1, ''Test'')'';
END;';
END;
32. Dynamic PL/SQL Block to Update and Commit
BEGIN
EXECUTE IMMEDIATE '
BEGIN
UPDATE test_table SET name = ''Updated Name'' WHERE id = 1;
COMMIT;
END;';
END;
33. Dynamic PL/SQL Block with EXCEPTION Handling
BEGIN
EXECUTE IMMEDIATE '
BEGIN
UPDATE test_table SET name = ''Updated'' WHERE id = 1;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;';
END;
34. Dynamic PL/SQL Block with Looping
BEGIN
EXECUTE IMMEDIATE '
DECLARE
v_id NUMBER := 1;
BEGIN
FOR i IN 1..5 LOOP
INSERT INTO test_table (id, name) VALUES (v_id, ''Name'' || i);
v_id := v_id + 1;
END LOOP;
COMMIT;
END;';
END;
35. Dynamic PL/SQL Block with Conditional Logic
BEGIN
EXECUTE IMMEDIATE '
DECLARE
v_name VARCHAR2(50);
BEGIN
SELECT name INTO v_name FROM test_table WHERE id = 1;
IF v_name = ''Test'' THEN
UPDATE test_table SET name = ''Updated Test'' WHERE id = 1;
ELSE
INSERT INTO test_table (id, name) VALUES (2, ''New Entry'');
END IF;
COMMIT;
END;';
END;
36. Dynamic PL/SQL Block with Nested Blocks
BEGIN
EXECUTE IMMEDIATE '
BEGIN
DECLARE
v_name VARCHAR2(50);
BEGIN
SELECT name INTO v_name FROM test_table WHERE id = 1;
IF v_name IS NULL THEN
INSERT INTO test_table (id, name) VALUES (1, ''Inserted Name'');
END IF;
END;
COMMIT;
END;';
END;
37. Dynamic PL/SQL Block to Create Index and Insert Data
BEGIN
EXECUTE IMMEDIATE '
BEGIN
EXECUTE IMMEDIATE ''CREATE INDEX idx_temp ON temp_table(name)'';
INSERT INTO temp_table (id, name) VALUES (1, ''Indexed Name'');
COMMIT;
END;';
END;
38. Dynamic PL/SQL Block to Create and Use a Temporary Table
BEGIN
EXECUTE IMMEDIATE '
DECLARE
v_count NUMBER;
BEGIN
EXECUTE IMMEDIATE ''CREATE GLOBAL TEMPORARY TABLE temp_table (id NUMBER, name VARCHAR2(50)) ON COMMIT DELETE ROWS'';
EXECUTE IMMEDIATE ''INSERT INTO temp_table (id, name) VALUES (1, ''Temp'')'';
SELECT COUNT(*) INTO v_count FROM temp_table;
DBMS_OUTPUT.PUT_LINE(''Count: '' || v_count);
COMMIT;
END;';
END;
39. Dynamic PL/SQL Block with Dynamic DDL Statements
BEGIN
EXECUTE IMMEDIATE '
BEGIN
EXECUTE IMMEDIATE ''CREATE TABLE dynamic_ddl_table (id NUMBER, name VARCHAR2(50))'';
EXECUTE IMMEDIATE ''INSERT INTO dynamic_ddl_table (id, name) VALUES (1, ''DDL Test'')'';
COMMIT;
END;';
END;
40. Dynamic PL/SQL Block to Create and Drop Table
BEGIN
EXECUTE IMMEDIATE '
BEGIN
EXECUTE IMMEDIATE ''CREATE TABLE temp_table (id NUMBER, name VARCHAR2(50))'';
EXECUTE IMMEDIATE ''DROP TABLE temp_table'';
COMMIT;
END;';
END;
Working with Collections
41. Dynamic SQL with Nested Table
DECLARE
TYPE NameTable IS TABLE OF VARCHAR2(50);
v_names NameTable;
BEGIN
EXECUTE IMMEDIATE 'SELECT name BULK COLLECT INTO :1 FROM test_table' USING OUT v_names;
FOR i IN 1..v_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v_names(i));
END LOOP;
END;
42. Dynamic SQL with VARRAY
DECLARE
TYPE NameArray IS VARRAY(10) OF VARCHAR2(50);
v_names NameArray := NameArray();
BEGIN
EXECUTE IMMEDIATE 'SELECT name FROM test_table WHERE ROWNUM <= 10' BULK COLLECT INTO v_names;
FOR i IN 1..v_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v_names(i));
END LOOP;
END;
43. Dynamic SQL with Associative Array
DECLARE
TYPE NameAssocArray IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
v_names NameAssocArray;
BEGIN
EXECUTE IMMEDIATE 'SELECT ROWNUM, name FROM test_table' BULK COLLECT INTO v_names;
FOR i IN 1..v_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v_names(i));
END LOOP;
END;
44. Dynamic SQL to Populate Nested Table from Dynamic Query
DECLARE
TYPE NameTable IS TABLE OF VARCHAR2(50);
v_names NameTable;
BEGIN
EXECUTE IMMEDIATE 'SELECT name BULK COLLECT INTO :1 FROM test_table WHERE id > 1' USING OUT v_names;
FOR i IN 1..v_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v_names(i));
END LOOP;
END;
45. Dynamic SQL with FORALL to Insert Data into a Table
DECLARE
TYPE NameTable IS TABLE OF VARCHAR2(50);
v_names NameTable := NameTable('Name1', 'Name2', 'Name3');
BEGIN
FORALL i IN 1..v_names.COUNT
EXECUTE IMMEDIATE 'INSERT INTO test_table (id, name) VALUES (:1, :2)' USING i, v_names(i);
COMMIT;
END;
46. Dynamic SQL to Perform BULK COLLECT and FORALL in a Single Block
DECLARE
TYPE NameTable IS TABLE OF VARCHAR2(50);
v_names NameTable;
BEGIN
EXECUTE IMMEDIATE 'SELECT name BULK COLLECT INTO :1 FROM test_table WHERE id > 1' USING OUT v_names;
FORALL i IN 1..v_names.COUNT
EXECUTE IMMEDIATE 'INSERT INTO another_table (id, name) VALUES (:1, :2)' USING i, v_names(i);
COMMIT;
END;
47. Dynamic SQL to Handle Collections in PL/SQL Block
DECLARE
TYPE NameTable IS TABLE OF VARCHAR2(50);
v_names NameTable := NameTable('John', 'Doe');
v_id NUMBER := 1;
BEGIN
FOR i IN 1..v_names.COUNT LOOP
EXECUTE IMMEDIATE 'INSERT INTO test_table (id, name) VALUES (:1, :2)' USING v_id, v_names(i);
v_id := v_id + 1;
END LOOP;
COMMIT;
END;
48. Dynamic SQL to Populate Associative Array
DECLARE
TYPE NameAssocArray IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
v_names NameAssocArray;
BEGIN
EXECUTE IMMEDIATE 'SELECT ROWNUM, name FROM test_table' BULK COLLECT INTO v_names;
FOR i IN 1..v_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v_names(i));
END LOOP;
END;
49. Dynamic SQL to Merge Data from Collection into a Table
DECLARE
TYPE NameTable IS TABLE OF VARCHAR2(50);
v_names NameTable := NameTable('John', 'Jane', 'Doe');
BEGIN
FOR i IN 1..v_names.COUNT LOOP
EXECUTE IMMEDIATE '
MERGE INTO test_table t
USING (SELECT :1 AS name FROM dual) s
ON (t.name = s.name)
WHEN MATCHED THEN UPDATE SET t.name = s.name
WHEN NOT MATCHED THEN INSERT (name) VALUES (s.name)' USING v_names(i);
END LOOP;
COMMIT;
END;
50. Dynamic SQL with COLLECTION in IN Clause
DECLARE
TYPE NameTable IS TABLE OF VARCHAR2(50);
v_names NameTable := NameTable('John', 'Jane', 'Doe');
v_count NUMBER;
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM test_table WHERE name IN (:1)' INTO v_count USING v_names;
DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
END;
Advanced Dynamic SQL Operations
51. Dynamic SQL with Dynamic Column List
DECLARE
v_column_list VARCHAR2(100) := 'id, name';
v_query VARCHAR2(1000);
BEGIN
v_query := 'SELECT ' || v_column_list || ' FROM test_table';
EXECUTE IMMEDIATE v_query;
END;
52. Dynamic SQL to Create and Populate a Table with User Input
DECLARE
v_table_name VARCHAR2(50) := 'user_table';
v_column_def VARCHAR2(100) := 'id NUMBER, name VARCHAR2(50)';
v_query VARCHAR2(1000);
BEGIN
v_query := 'CREATE TABLE ' || v_table_name || ' (' || v_column_def || ')';
EXECUTE IMMEDIATE v_query;
EXECUTE IMMEDIATE 'INSERT INTO ' || v_table_name || ' (id, name) VALUES (1, ''User'')';
END;
53. Dynamic SQL to Execute DDL Statements in Loop
DECLARE
v_query VARCHAR2(1000);
BEGIN
FOR i IN 1..5 LOOP
v_query := 'CREATE TABLE temp_table_' || i || ' (id NUMBER, name VARCHAR2(50))';
EXECUTE IMMEDIATE v_query;
END LOOP;
END;
54. Dynamic SQL to Create, Insert, and Select from a Table
DECLARE
v_query VARCHAR2(1000);
v_name VARCHAR2(50);
BEGIN
v_query := 'CREATE TABLE dynamic_table (id NUMBER, name VARCHAR2(50))';
EXECUTE IMMEDIATE v_query;
EXECUTE IMMEDIATE 'INSERT INTO dynamic_table (id, name) VALUES (1, ''Dynamic'')';
EXECUTE IMMEDIATE 'SELECT name FROM dynamic_table WHERE id = 1' INTO v_name;
DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
END;
55. Dynamic SQL to Perform Multiple Operations in a Single Block
BEGIN
EXECUTE IMMEDIATE '
BEGIN
EXECUTE IMMEDIATE ''CREATE TABLE combined_table (id NUMBER, name VARCHAR2(50))'';
EXECUTE IMMEDIATE ''INSERT INTO combined_table (id, name) VALUES (1, ''Combined'')'';
EXECUTE IMMEDIATE ''CREATE INDEX idx_combined ON combined_table(name)'';
EXECUTE IMMEDIATE ''DROP TABLE combined_table'';
COMMIT;
END;';
END;
56. Dynamic SQL with Bind Variables and DDL Operations
DECLARE
v_table_name VARCHAR2(50) := 'bind_table';
v_column_def VARCHAR2(100) := 'id NUMBER, name VARCHAR2(50)';
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE ' || v_table_name || ' (' || v_column_def || ')';
EXECUTE IMMEDIATE 'INSERT INTO ' || v_table_name || ' (id, name) VALUES (:1, :2)' USING 1, 'Bind Test';
END;
57. Dynamic SQL to Create Indexes Based on Column Names
DECLARE
v_index_name VARCHAR2(50) := 'idx_dynamic';
v_table_name VARCHAR2(50) := 'dynamic_table';
v_column_name VARCHAR2(50) := 'name';
BEGIN
EXECUTE IMMEDIATE 'CREATE INDEX ' || v_index_name || ' ON ' || v_table_name || ' (' || v_column_name || ')';
END;
58. Dynamic SQL to Drop and Recreate a Table with Constraints
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE constrained_table';
EXECUTE IMMEDIATE '
CREATE TABLE constrained_table (
id NUMBER PRIMARY KEY,
name VARCHAR2(50) NOT NULL,
age NUMBER CHECK (age >= 0)
)';
END;
59. Dynamic SQL to Manage Database Links
DECLARE
v_dblink_name VARCHAR2(50) := 'remote_link';
v_username VARCHAR2(50) := 'user';
v_password VARCHAR2(50) := 'password';
v_host VARCHAR2(50) := 'remote_host';
BEGIN
EXECUTE IMMEDIATE 'CREATE DATABASE LINK ' || v_dblink_name || ' CONNECT TO ' || v_username || ' IDENTIFIED BY ''' || v_password || ''' USING ''' || v_host || '''';
EXECUTE IMMEDIATE 'DROP DATABASE LINK ' || v_dblink_name;
END;
60. Dynamic SQL to Create and Use Synonyms
DECLARE
v_synonym_name VARCHAR2(50) := 'syn_dynamic';
v_table_name VARCHAR2(50) := 'dynamic_table';
BEGIN
EXECUTE IMMEDIATE 'CREATE SYNONYM ' || v_synonym_name || ' FOR ' || v_table_name;
EXECUTE IMMEDIATE 'SELECT name FROM ' || v_synonym_name INTO v_table_name;
DBMS_OUTPUT.PUT_LINE('Name: ' || v_table_name);
EXECUTE IMMEDIATE 'DROP SYNONYM ' || v_synonym_name;
END;
61. Dynamic SQL to Manage Partitioned Tables
BEGIN
EXECUTE IMMEDIATE '
CREATE TABLE partitioned_table (
id NUMBER,
name VARCHAR2(50),
created_date DATE
)
PARTITION BY RANGE (created_date)
(
PARTITION p1 VALUES LESS THAN (TO_DATE(''01-JAN-2020'', ''DD-MON-YYYY'')),
PARTITION p2 VALUES LESS THAN (TO_DATE(''01-JAN-2021'', ''DD-MON-YYYY''))
)';
EXECUTE IMMEDIATE 'DROP TABLE partitioned_table';
END;
62. Dynamic SQL with Dynamic WHERE Clause Construction
DECLARE
v_condition VARCHAR2(100) := 'id > 1';
v_query VARCHAR2(1000);
BEGIN
v_query := 'SELECT name FROM test_table WHERE ' || v_condition;
EXECUTE IMMEDIATE v_query;
END;
63. Dynamic SQL to Execute Dynamic Cursor
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;
64. Dynamic SQL with Dynamic Cursor and Parameters
DECLARE
v_cursor SYS_REFCURSOR;
v_name VARCHAR2(50);
v_id NUMBER := 1;
BEGIN
OPEN v_cursor FOR 'SELECT name FROM test_table WHERE id = :1' USING v_id;
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;
65. Dynamic SQL to Fetch Data into PL/SQL Record
DECLARE
TYPE test_record IS RECORD (id NUMBER, name VARCHAR2(50));
v_record test_record;
BEGIN
EXECUTE IMMEDIATE 'SELECT id, name FROM test_table WHERE id = 1' INTO v_record;
DBMS_OUTPUT.PUT_LINE('ID: ' || v_record.id || ', Name: ' || v_record.name);
END;
66. Dynamic SQL to Fetch Data into PL/SQL Cursor
DECLARE
CURSOR c IS SELECT id, name FROM test_table;
v_id NUMBER;
v_name VARCHAR2(50);
BEGIN
OPEN c;
LOOP
FETCH c INTO v_id, v_name;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('ID: ' || v_id || ', Name: ' || v_name);
END LOOP;
CLOSE c;
END;
67. Dynamic SQL to Execute a Procedure Dynamically
BEGIN
EXECUTE IMMEDIATE 'BEGIN my_procedure(:1); END;' USING 1;
END;
68. Dynamic SQL to Execute a Function and Get Return Value
DECLARE
v_result NUMBER;
BEGIN
EXECUTE IMMEDIATE 'BEGIN :1 := my_function(:2); END;' USING OUT v_result, 1;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
END;
69. Dynamic SQL to Execute Multiple Procedures in a Single Block
BEGIN
EXECUTE IMMEDIATE '
BEGIN
my_procedure1(:1);
my_procedure2(:2);
my_procedure3(:3);
END;' USING 1, 2, 3;
END;
70. Dynamic SQL with Conditional Execution of Procedures
DECLARE
v_condition VARCHAR2(10) := 'YES';
BEGIN
IF v_condition = 'YES' THEN
EXECUTE IMMEDIATE 'BEGIN my_procedure(:1); END;' USING 1;
ELSE
EXECUTE IMMEDIATE 'BEGIN my_other_procedure(:1); END;' USING 1;
END IF;
END;
71. Dynamic SQL to Execute DDL and DML in a Single Block
BEGIN
EXECUTE IMMEDIATE '
BEGIN
EXECUTE IMMEDIATE ''CREATE TABLE temp_table (id NUMBER, name VARCHAR2(50))'';
EXECUTE IMMEDIATE ''INSERT INTO temp_table (id, name) VALUES (1, ''Temp'')'';
COMMIT;
END;';
END;
72. Dynamic SQL to Handle Dynamic Number of Columns in a Query
DECLARE
v_column_list VARCHAR2(100) := 'id, name';
v_query VARCHAR2(1000);
BEGIN
v_query := 'SELECT ' || v_column_list || ' FROM test_table';
EXECUTE IMMEDIATE v_query;
END;
73. Dynamic SQL to Update Multiple Tables in a Single Transaction
BEGIN
EXECUTE IMMEDIATE '
BEGIN
UPDATE table1 SET column1 = ''Value
1'' WHERE condition1;
UPDATE table2 SET column2 = ''Value2'' WHERE condition2;
COMMIT;
END;';
END;
74. Dynamic SQL with Dynamic Update Clause
DECLARE
v_update_clause VARCHAR2(100) := 'name = ''Updated Name''';
v_query VARCHAR2(1000);
BEGIN
v_query := 'UPDATE test_table SET ' || v_update_clause || ' WHERE id = 1';
EXECUTE IMMEDIATE v_query;
END;
75. Dynamic SQL to Perform Rollback if Error Occurs
BEGIN
EXECUTE IMMEDIATE '
BEGIN
UPDATE test_table SET name = ''New Name'' WHERE id = 1;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;';
END;
76. Dynamic SQL with Cursors in Dynamic PL/SQL Block
BEGIN
EXECUTE IMMEDIATE '
DECLARE
CURSOR c IS SELECT id, name FROM test_table;
v_id NUMBER;
v_name VARCHAR2(50);
BEGIN
OPEN c;
LOOP
FETCH c INTO v_id, v_name;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(''ID: '' || v_id || '', Name: '' || v_name);
END LOOP;
CLOSE c;
END;';
END;
77. Dynamic SQL with COMMIT and ROLLBACK in Dynamic Block
BEGIN
EXECUTE IMMEDIATE '
BEGIN
UPDATE test_table SET name = ''New Name'' WHERE id = 1;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;';
END;
78. Dynamic SQL to Create, Insert, and Select from Temporary Table
BEGIN
EXECUTE IMMEDIATE '
BEGIN
EXECUTE IMMEDIATE ''CREATE GLOBAL TEMPORARY TABLE temp_table (id NUMBER, name VARCHAR2(50)) ON COMMIT DELETE ROWS'';
EXECUTE IMMEDIATE ''INSERT INTO temp_table (id, name) VALUES (1, ''Temp'')'';
EXECUTE IMMEDIATE ''SELECT name FROM temp_table WHERE id = 1'';
COMMIT;
END;';
END;
79. Dynamic SQL to Execute PL/SQL Blocks Dynamically
DECLARE
v_block VARCHAR2(1000) := 'BEGIN DBMS_OUTPUT.PUT_LINE(''Dynamic Block Execution''); END;';
BEGIN
EXECUTE IMMEDIATE v_block;
END;
80. Dynamic SQL to Handle Complex Query with Multiple Joins
DECLARE
v_query VARCHAR2(1000);
v_name VARCHAR2(50);
BEGIN
v_query := 'SELECT t1.name FROM test_table t1 JOIN another_table t2 ON t1.id = t2.id WHERE t1.id = 1';
EXECUTE IMMEDIATE v_query INTO v_name;
DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
END;
Real-Time Use Cases
81. Dynamic SQL to Handle Dynamic Business Logic
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;
82. Dynamic SQL to Generate Reports Dynamically
DECLARE
v_report_type VARCHAR2(50) := 'SUMMARY';
v_query VARCHAR2(1000);
BEGIN
IF v_report_type = 'SUMMARY' THEN
v_query := 'SELECT COUNT(*), name FROM test_table GROUP BY name';
ELSE
v_query := 'SELECT * FROM test_table';
END IF;
EXECUTE IMMEDIATE v_query;
END;
83. Dynamic SQL to Implement Dynamic Search Functionality
DECLARE
v_search_criteria VARCHAR2(100) := 'name = ''John''';
v_query VARCHAR2(1000);
v_name VARCHAR2(50);
BEGIN
v_query := 'SELECT name FROM test_table WHERE ' || v_search_criteria;
EXECUTE IMMEDIATE v_query INTO v_name;
DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
END;
84. Dynamic SQL to Handle Multiple Dynamic Conditions
DECLARE
v_condition1 VARCHAR2(50) := 'id = 1';
v_condition2 VARCHAR2(50) := 'name = ''John''';
v_query VARCHAR2(1000);
BEGIN
v_query := 'SELECT name FROM test_table WHERE ' || v_condition1 || ' AND ' || v_condition2;
EXECUTE IMMEDIATE v_query;
END;
85. Dynamic SQL to Handle User-Defined Parameters
DECLARE
v_parameter VARCHAR2(50) := 'id';
v_query VARCHAR2(1000);
v_value NUMBER := 1;
BEGIN
v_query := 'SELECT ' || v_parameter || ' FROM test_table WHERE id = :1';
EXECUTE IMMEDIATE v_query USING v_value;
END;
86. Dynamic SQL to Handle Data Validation Rules
DECLARE
v_rule VARCHAR2(100) := 'id > 0';
v_query VARCHAR2(1000);
BEGIN
v_query := 'SELECT COUNT(*) FROM test_table WHERE ' || v_rule;
EXECUTE IMMEDIATE v_query;
END;
87. Dynamic SQL to Implement Audit Logging
DECLARE
v_query VARCHAR2(1000);
BEGIN
v_query := 'INSERT INTO audit_log (operation, user, timestamp) VALUES (''UPDATE'', ''user1'', SYSDATE)';
EXECUTE IMMEDIATE v_query;
END;
88. Dynamic SQL to Manage Complex Transactions
BEGIN
EXECUTE IMMEDIATE '
BEGIN
SAVEPOINT before_update;
UPDATE test_table SET name = ''New Name'' WHERE id = 1;
IF SQL%ROWCOUNT = 0 THEN
ROLLBACK TO before_update;
ELSE
COMMIT;
END IF;
END;';
END;
89. Dynamic SQL to Handle Dynamic Data Transformations
DECLARE
v_transformation VARCHAR2(100) := 'UPPER(name)';
v_query VARCHAR2(1000);
BEGIN
v_query := 'SELECT ' || v_transformation || ' FROM test_table';
EXECUTE IMMEDIATE v_query;
END;
90. Dynamic SQL to Execute Multiple Dynamic Queries in a Loop
DECLARE
v_query VARCHAR2(1000);
BEGIN
FOR i IN 1..5 LOOP
v_query := 'SELECT name FROM test_table WHERE id = ' || i;
EXECUTE IMMEDIATE v_query;
END LOOP;
END;
Complex Business Scenarios
91. Dynamic SQL to Implement Dynamic Pricing Rules
DECLARE
v_rule VARCHAR2(100) := 'price > 100';
v_query VARCHAR2(1000);
BEGIN
v_query := 'UPDATE product_table SET discount = 10 WHERE ' || v_rule;
EXECUTE IMMEDIATE v_query;
END;
92. Dynamic SQL to Handle Dynamic Reporting Based on User Inputs
DECLARE
v_report_type VARCHAR2(50) := 'DETAILED';
v_query VARCHAR2(1000);
BEGIN
IF v_report_type = 'SUMMARY' THEN
v_query := 'SELECT COUNT(*), category FROM sales_table GROUP BY category';
ELSE
v_query := 'SELECT * FROM sales_table';
END IF;
EXECUTE IMMEDIATE v_query;
END;
93. Dynamic SQL to Handle Dynamic Column Updates Based on Business Rules
DECLARE
v_column VARCHAR2(50) := 'status';
v_value VARCHAR2(50) := 'APPROVED';
v_query VARCHAR2(1000);
BEGIN
v_query := 'UPDATE approval_table SET ' || v_column || ' = ''' || v_value || ''' WHERE id = 1';
EXECUTE IMMEDIATE v_query;
END;
94. Dynamic SQL to Implement Dynamic Data Loading from External Sources
DECLARE
v_table_name VARCHAR2(50) := 'external_data_table';
v_query VARCHAR2(1000);
BEGIN
v_query := 'INSERT INTO ' || v_table_name || ' (id, name) VALUES (1, ''External Data'')';
EXECUTE IMMEDIATE v_query;
END;
95. Dynamic SQL to Handle Multiple Dynamic Conditions for Complex Queries
DECLARE
v_condition1 VARCHAR2(50) := 'status = ''ACTIVE''';
v_condition2 VARCHAR2(50) := 'region = ''NORTH''';
v_query VARCHAR2(1000
);
BEGIN
v_query := 'SELECT * FROM employee_table WHERE ' || v_condition1 || ' AND ' || v_condition2;
EXECUTE IMMEDIATE v_query;
END;
96. Dynamic SQL to Handle Data Cleanup and Maintenance Tasks
DECLARE
v_condition VARCHAR2(50) := 'created_date < SYSDATE - 365';
v_query VARCHAR2(1000);
BEGIN
v_query := 'DELETE FROM data_table WHERE ' || v_condition;
EXECUTE IMMEDIATE v_query;
END;
97. Dynamic SQL to Implement Dynamic Access Control Mechanisms
DECLARE
v_role VARCHAR2(50) := 'ADMIN';
v_query VARCHAR2(1000);
BEGIN
v_query := 'GRANT SELECT, INSERT, UPDATE ON sensitive_table TO ' || v_role;
EXECUTE IMMEDIATE v_query;
END;
98. Dynamic SQL to Manage Dynamic Workflows Based on User Actions
DECLARE
v_action VARCHAR2(50) := 'APPROVE';
v_query VARCHAR2(1000);
BEGIN
IF v_action = 'APPROVE' THEN
v_query := 'UPDATE workflow_table SET status = ''APPROVED'' WHERE id = 1';
ELSE
v_query := 'UPDATE workflow_table SET status = ''REJECTED'' WHERE id = 1';
END IF;
EXECUTE IMMEDIATE v_query;
END;
99. Dynamic SQL to Handle Complex Data Migrations
DECLARE
v_source_table VARCHAR2(50) := 'old_table';
v_target_table VARCHAR2(50) := 'new_table';
v_query VARCHAR2(1000);
BEGIN
v_query := 'INSERT INTO ' || v_target_table || ' SELECT * FROM ' || v_source_table;
EXECUTE IMMEDIATE v_query;
END;
100. Dynamic SQL to Implement Real-Time Data Synchronization
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;
No comments:
Post a Comment