Basic DDL Operations
1. Create a Table Dynamically
Description: This example creates a table at runtime using EXECUTE IMMEDIATE.
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE test_table (id NUMBER, name VARCHAR2(50))';
END;
/2. Drop a Table Dynamically
Description: This example removes a table dynamically.
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE test_table';
END;
/3. Add a Column to a Table Dynamically
Description: This example 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 drops a column dynamically.
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE test_table DROP COLUMN created_date';
END;
/5. Rename a Table Dynamically
Description: This example renames a table at runtime.
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE test_table RENAME TO new_test_table';
END;
/6. Create an Index Dynamically
Description: This example creates an index dynamically.
BEGIN
EXECUTE IMMEDIATE 'CREATE INDEX idx_name ON test_table(name)';
END;
/7. Drop an Index Dynamically
Description: This example drops an index dynamically.
BEGIN
EXECUTE IMMEDIATE 'DROP INDEX idx_name';
END;
/8. Create a Sequence Dynamically
Description: This example creates a sequence dynamically.
BEGIN
EXECUTE IMMEDIATE 'CREATE SEQUENCE test_seq START WITH 1 INCREMENT BY 1';
END;
/9. Drop a Sequence Dynamically
Description: This example drops a sequence dynamically.
BEGIN
EXECUTE IMMEDIATE 'DROP SEQUENCE test_seq';
END;
/10. Create a View Dynamically
Description: This example creates a view at runtime.
BEGIN
EXECUTE IMMEDIATE 'CREATE VIEW test_view AS SELECT * FROM test_table';
END;
/Basic DML Operations
11. Insert Data Dynamically
Description: This example inserts a row dynamically into a table.
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO test_table (id, name) VALUES (1, ''John'')';
END;
/12. Update Data Dynamically
Description: This example updates table data dynamically.
BEGIN
EXECUTE IMMEDIATE 'UPDATE test_table SET name = ''Jane'' WHERE id = 1';
END;
/13. Delete Data Dynamically
Description: This example deletes a row dynamically.
BEGIN
EXECUTE IMMEDIATE 'DELETE FROM test_table WHERE id = 1';
END;
/14. Merge Data Dynamically
Description: This example merges data dynamically using MERGE.
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
Description: This example truncates a table dynamically.
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE test_table';
END;
/16. Insert Data with Bind Variables
Description: This example inserts a row using 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
Description: This example updates a row using 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
Description: This example deletes a row using 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
Description: This example fetches a value 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
Description: This example counts rows 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
Description: This example runs a dynamic query with 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;
/22. Dynamic Query with IN Clause
Description: This example uses a dynamic query with an 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
Description: This example searches rows dynamically using a LIKE condition.
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
Description: This example orders data dynamically before fetching a value.
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
Description: This example groups rows dynamically and returns an aggregate result.
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
Description: This example filters grouped results dynamically using HAVING.
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
Description: This example joins tables dynamically and returns a value.
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
Description: This example uses a subquery inside dynamic SQL.
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
Description: This example returns a value based on a CASE expression.
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
Description: This example calculates an aggregate value dynamically.
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
Description: This example executes a nested dynamic PL/SQL 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, ''Test'')'';
END;';
END;
/32. Dynamic PL/SQL Block to Update and Commit
Description: This example updates data and commits inside a dynamic block.
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
Description: This example handles errors inside a dynamic PL/SQL block.
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
Description: This example uses a loop inside dynamic PL/SQL.
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
Description: This example uses conditional logic inside a dynamic block.
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
Description: This example nests one PL/SQL block inside another.
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
Description: This example creates an index and inserts a row in one dynamic block.
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
Description: This example creates a global temporary table and uses it immediately.
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
Description: This example combines DDL and DML inside a dynamic PL/SQL block.
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
Description: This example creates a table and drops it in the same dynamic block.
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
Description: This example returns multiple rows into a nested table using dynamic SQL.
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
Description: This example uses a VARRAY to store query results dynamically.
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
Description: This example shows dynamic SQL usage with an 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
Description: This example populates a nested table from a 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
Description: This example inserts collection items using FORALL and dynamic SQL.
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
Description: This example combines bulk collect and forall operations dynamically.
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
Description: This example inserts each collection item dynamically inside a loop.
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
Description: This example uses a dynamic query to populate an 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
Description: This example merges collection data into a table dynamically.
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
Description: This example uses a collection in a dynamic filter condition.
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
Description: This example builds a SELECT list dynamically.
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
Description: This example creates a table and inserts a row dynamically using variables.
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
Description: This example creates several tables dynamically inside a 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
Description: This example performs DDL, DML, and SELECT in sequence.
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
Description: This example runs multiple SQL operations inside one dynamic 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
Description: This example combines DDL creation and bind-variable inserts.
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
Description: This example creates an index using dynamic table and 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
Description: This example recreates a table with constraints dynamically.
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
Description: This example creates and drops a database link dynamically.
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
Description: This example creates and drops a synonym dynamically.
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
Description: This example creates and drops a partitioned table dynamically.
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
Description: This example builds the WHERE clause dynamically.
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
Description: This example opens a ref cursor for a dynamic SELECT 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;
/64. Dynamic SQL with Dynamic Cursor and Parameters
Description: This example opens a ref cursor with bind 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
Description: This example fetches a row into a 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
Description: This example fetches rows from a table using a 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
Description: This example calls 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
Description: This example calls a function dynamically and captures its result.
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
Description: This example executes several procedures inside one dynamic 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
Description: This example chooses one of two procedures dynamically.
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;
/Real-Time Use Cases
71. Dynamic SQL to Execute DDL and DML in a Single Block
Description: This example combines table creation and insert logic in one 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
Description: This example builds the column list dynamically.
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
Description: This example updates two tables in one dynamic PL/SQL block.
BEGIN
EXECUTE IMMEDIATE '
BEGIN
UPDATE table1 SET column1 = ''Value1'' WHERE condition1;
UPDATE table2 SET column2 = ''Value2'' WHERE condition2;
COMMIT;
END;';
END;
/74. Dynamic SQL with Dynamic Update Clause
Description: This example builds the SET clause dynamically.
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
Description: This example rolls back changes when an error occurs in the 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;
/76. Dynamic SQL with Cursors in Dynamic PL/SQL Block
Description: This example uses a cursor inside a dynamic 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
Description: This example includes transaction control inside a 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
Description: This example creates a temporary table, inserts a row, and reads from it.
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
Description: This example executes a PL/SQL block stored in a variable.
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
Description: This example runs a dynamic query with multiple joins and a filter.
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;
/Complex Business Scenarios
81. Dynamic SQL to Handle Dynamic Business Logic
Description: This example updates rows based on a dynamic business rule.
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
Description: This example switches between summary and detailed report logic.
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
Description: This example searches data using a runtime-built condition.
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
Description: This example combines more than one runtime condition.
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
Description: This example uses a user-selected column in a dynamic query.
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
Description: This example counts rows that satisfy a validation rule.
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
Description: This example writes a row to an audit table dynamically.
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
Description: This example uses savepoints and transaction control in a dynamic block.
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
Description: This example applies a runtime transformation in the SELECT list.
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
Description: This example runs a different query for each loop iteration.
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
Description: This example updates discount logic based on a pricing rule.
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
Description: This example switches between summary and detailed sales reports.
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
Description: This example updates a chosen column dynamically.
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
Description: This example inserts data into a target table dynamically.
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
Description: This example combines two runtime conditions in one query.
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
Description: This example deletes old records dynamically.
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
Description: This example grants privileges dynamically based on a role.
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
Description: This example updates workflow status based on an action.
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
Description: This example copies data from one table to another dynamically.
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
Description: This example synchronizes source and target tables using MERGE.
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: A few examples are intentionally educational and may need small adjustments before running in a real Oracle environment.
No comments:
Post a Comment