Simple Dynamic SQL Examples
1. Dynamic SQL with EXECUTE IMMEDIATE
Description: This example shows how to run dynamic SQL with EXECUTE IMMEDIATE.
DECLARE
v_sql VARCHAR2(100);
BEGIN
v_sql := 'CREATE TABLE test_table (id NUMBER)';
EXECUTE IMMEDIATE v_sql;
END;2. Dynamic SQL to Insert Data
Description: This example inserts a row using dynamic SQL.
DECLARE
v_sql VARCHAR2(100);
BEGIN
v_sql := 'INSERT INTO test_table (id) VALUES (1)';
EXECUTE IMMEDIATE v_sql;
END;3. Dynamic SQL to Update Data
Description: This example updates a row using dynamic SQL.
DECLARE
v_sql VARCHAR2(100);
BEGIN
v_sql := 'UPDATE test_table SET id = 2 WHERE id = 1';
EXECUTE IMMEDIATE v_sql;
END;4. Dynamic SQL to Delete Data
Description: This example deletes a row using dynamic SQL.
DECLARE
v_sql VARCHAR2(100);
BEGIN
v_sql := 'DELETE FROM test_table WHERE id = 2';
EXECUTE IMMEDIATE v_sql;
END;5. Dynamic SQL with Bind Variables
Description: This example uses bind variables to pass values safely.
DECLARE
v_sql VARCHAR2(100);
v_id NUMBER := 3;
BEGIN
v_sql := 'INSERT INTO test_table (id) VALUES (:1)';
EXECUTE IMMEDIATE v_sql USING v_id;
END;Dynamic SQL with SELECT Statements
6. Dynamic SQL to Select Data
Description: This example demonstrates dynamic sql to select data.
DECLARE
v_sql VARCHAR2(100);
v_id NUMBER;
BEGIN
v_sql := 'SELECT id FROM test_table WHERE id = 3';
EXECUTE IMMEDIATE v_sql INTO v_id;
DBMS_OUTPUT.PUT_LINE('ID: ' || v_id);
END;7. Dynamic SQL to Select Data into Multiple Variables
Description: This example demonstrates dynamic sql to select data into multiple variables.
DECLARE
v_sql VARCHAR2(200);
v_id NUMBER;
v_name VARCHAR2(50);
BEGIN
v_sql := 'SELECT id, name FROM test_table WHERE id = 3';
EXECUTE IMMEDIATE v_sql INTO v_id, v_name;
DBMS_OUTPUT.PUT_LINE('ID: ' || v_id || ', Name: ' || v_name);
END;Dynamic SQL with DML Statements
8. Dynamic SQL for Multiple Inserts
Description: This example demonstrates dynamic sql for multiple inserts.
DECLARE
v_sql VARCHAR2(100);
v_ids DBMS_SQL.NUMBER_TABLE := DBMS_SQL.NUMBER_TABLE(4, 5, 6);
BEGIN
FOR i IN 1..v_ids.COUNT LOOP
v_sql := 'INSERT INTO test_table (id) VALUES (:1)';
EXECUTE IMMEDIATE v_sql USING v_ids(i);
END LOOP;
END;9. Dynamic SQL for Multiple Updates
Description: This example demonstrates dynamic sql for multiple updates.
DECLARE
v_sql VARCHAR2(100);
v_ids DBMS_SQL.NUMBER_TABLE := DBMS_SQL.NUMBER_TABLE(4, 5, 6);
BEGIN
FOR i IN 1..v_ids.COUNT LOOP
v_sql := 'UPDATE test_table SET id = id + 1 WHERE id = :1';
EXECUTE IMMEDIATE v_sql USING v_ids(i);
END LOOP;
END;10. Dynamic SQL for Multiple Deletes
Description: This example demonstrates dynamic sql for multiple deletes.
DECLARE
v_sql VARCHAR2(100);
v_ids DBMS_SQL.NUMBER_TABLE := DBMS_SQL.NUMBER_TABLE(4,5,6);
BEGIN
FOR i IN 1..v_ids.COUNT LOOP
v_sql := 'DELETE FROM test_table WHERE id = :1';
EXECUTE IMMEDIATE v_sql USING v_ids(i);
END LOOP;
END;Dynamic SQL with Conditional Logic
11. Conditional Dynamic SQL Execution
Description: This example demonstrates conditional dynamic sql execution.
DECLARE
v_sql VARCHAR2(100);
v_action VARCHAR2(10) := 'INSERT';
BEGIN
IF v_action = 'INSERT' THEN
v_sql := 'INSERT INTO test_table (id) VALUES (7)';
ELSIF v_action = 'UPDATE' THEN
v_sql := 'UPDATE test_table SET id = 8 WHERE id = 7';
ELSE
v_sql := 'DELETE FROM test_table WHERE id = 8';
END IF;
EXECUTE IMMEDIATE v_sql;
END;12. Dynamic SQL Based on User Input
Description: This example demonstrates dynamic sql based on user input.
DECLARE
v_sql VARCHAR2(100);
v_table_name VARCHAR2(30) := 'test_table';
v_column_name VARCHAR2(30) := 'id';
BEGIN
v_sql := 'SELECT ' || v_column_name || ' FROM ' || v_table_name || ' WHERE ' || v_column_name || ' = 7';
EXECUTE IMMEDIATE v_sql INTO v_column_name;
DBMS_OUTPUT.PUT_LINE('Value: ' || v_column_name);
END;Dynamic SQL with Error Handling
13. Dynamic SQL with Exception Handling
Description: This example demonstrates dynamic sql with exception handling.
DECLARE
v_sql VARCHAR2(100);
BEGIN
v_sql := 'INSERT INTO test_table (id) VALUES (9)';
BEGIN
EXECUTE IMMEDIATE v_sql;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error occurred: ' || SQLERRM);
END;
END;14. Dynamic SQL with Commit and Rollback
Description: This example demonstrates dynamic sql with commit and rollback.
DECLARE
v_sql VARCHAR2(100);
BEGIN
v_sql := 'INSERT INTO test_table (id) VALUES (10)';
EXECUTE IMMEDIATE v_sql;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error occurred: ' || SQLERRM);
END;Dynamic SQL with Cursors
15. Dynamic SQL with Explicit Cursor
Description: This example demonstrates dynamic sql with explicit cursor.
DECLARE
CURSOR c IS
SELECT table_name FROM user_tables WHERE table_name LIKE 'TEST_%';
v_sql VARCHAR2(200);
v_count NUMBER;
BEGIN
FOR r IN c LOOP
v_sql := 'SELECT COUNT(*) FROM ' || r.table_name;
EXECUTE IMMEDIATE v_sql INTO v_count;
DBMS_OUTPUT.PUT_LINE('Table: ' || r.table_name || ', Count: ' || v_count);
END LOOP;
END;16. Dynamic SQL with Cursor FOR Loop
Description: This example demonstrates dynamic sql with cursor for loop.
DECLARE
CURSOR c IS
SELECT table_name FROM user_tables WHERE table_name LIKE 'TEST_%';
v_sql VARCHAR2(200);
v_count NUMBER;
BEGIN
OPEN c;
LOOP
FETCH c INTO v_sql;
EXIT WHEN c%NOTFOUND;
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || v_sql INTO v_count;
DBMS_OUTPUT.PUT_LINE('Table: ' || v_sql || ', Count: ' || v_count);
END LOOP;
CLOSE c;
END;Dynamic SQL with DBMS_SQL Package
17. Dynamic SQL Using DBMS_SQL to Create Table
Description: This example demonstrates dynamic sql using dbms_sql to create table.
DECLARE
v_cursor INTEGER;
v_dummy NUMBER;
BEGIN
v_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursor, 'CREATE TABLE test_table2 (id NUMBER)', DBMS_SQL.NATIVE);
DBMS_SQL.EXECUTE(v_cursor);
DBMS_SQL.CLOSE_CURSOR(v_cursor);
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(v_cursor) THEN
DBMS_SQL.CLOSE_CURSOR(v_cursor);
END IF;
RAISE;
END;18. Dynamic SQL Using DBMS_SQL to Select Data
Description: This example demonstrates dynamic sql using dbms_sql to select data.
DECLARE
v_cursor INTEGER;
v_dummy NUMBER;
BEGIN
v_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursor, 'SELECT id FROM test_table2', DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN(v_cursor, 1, v_dummy);
DBMS_SQL.EXECUTE(v_cursor);
LOOP
IF DBMS_SQL.FETCH_ROWS(v_cursor) = 0 THEN
EXIT;
END IF;
DBMS_SQL.COLUMN_VALUE(v_cursor, 1, v_dummy);
DBMS_OUTPUT.PUT_LINE('ID: ' || v_dummy);
END LOOP;
DBMS_SQL.CLOSE_CURSOR(v_cursor);
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(v_cursor) THEN
DBMS_SQL.CLOSE_CURSOR(v_cursor);
END IF;
RAISE;
END;Dynamic SQL with Bind Variables and Multiple Tables
19. Dynamic SQL with Bind Variables for Multiple Tables
Description: This example demonstrates dynamic sql with bind variables for multiple tables.
DECLARE
v_sql VARCHAR2(200);
v_table_name VARCHAR2(30) := 'test_table2';
v_column_name VARCHAR2(30) := 'id';
v_value NUMBER := 11;
BEGIN
v_sql := 'INSERT INTO ' || v_table_name || ' (' || v_column_name || ') VALUES (:1)';
EXECUTE IMMEDIATE v_sql USING v_value;
END;20. Dynamic SQL to Create Multiple Tables
Description: This example demonstrates dynamic sql to create multiple tables.
DECLARE
v_sql VARCHAR2(200);
v_table_name VARCHAR2(30);
v_column_name VARCHAR2(30) := 'id';
BEGIN
FOR i IN 1..3 LOOP
v_table_name := 'dynamic_table_' || i;
v_sql := 'CREATE TABLE ' || v_table_name || ' (' || v_column_name || ' NUMBER)';
EXECUTE IMMEDIATE v_sql;
END LOOP;
END;Advanced Dynamic SQL Examples
21. Dynamic SQL with Procedures
Description: This example demonstrates dynamic sql with procedures.
CREATE OR REPLACE PROCEDURE dynamic_insert (p_table_name VARCHAR2, p_column_name VARCHAR2, p_value NUMBER) IS
v_sql VARCHAR2(200);
BEGIN
v_sql := 'INSERT INTO ' || p_table_name || ' (' || p_column_name || ') VALUES (:1)';
EXECUTE IMMEDIATE v_sql USING p_value;
END;22. Dynamic SQL with Functions
Description: This example demonstrates dynamic sql with functions.
CREATE OR REPLACE FUNCTION dynamic_select (p_table_name VARCHAR2, p_column_name VARCHAR2) RETURN NUMBER IS
v_sql VARCHAR2(200);
v_value NUMBER;
BEGIN
v_sql := 'SELECT ' || p_column_name || ' FROM ' || p_table_name || ' WHERE ROWNUM = 1';
EXECUTE IMMEDIATE v_sql INTO v_value;
RETURN v_value;
END;23. Dynamic SQL with Autonomous Transaction for Logging
Description: This example demonstrates dynamic sql with autonomous transaction for logging.
CREATE OR REPLACE PROCEDURE log_error (p_error_message VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_sql VARCHAR2(200);
BEGIN
v_sql := 'INSERT INTO error_log (message, log_date) VALUES (:1, SYSDATE)';
EXECUTE IMMEDIATE v_sql USING p_error_message;
COMMIT;
END;24. Dynamic SQL with Multiple Bind Variables
Description: This example demonstrates dynamic sql with multiple bind variables.
DECLARE
v_sql VARCHAR2(200);
v_value1 NUMBER := 12;
v_value2 NUMBER := 13;
BEGIN
v_sql := 'INSERT INTO test_table (id, another_column) VALUES (:1, :2)';
EXECUTE IMMEDIATE v_sql USING v_value1, v_value2;
END;25. Dynamic SQL with Dynamic Column Names
Description: This example demonstrates dynamic sql with dynamic column names.
DECLARE
v_sql VARCHAR2(200);
v_column_name VARCHAR2(30) := 'id';
v_value NUMBER := 14;
BEGIN
v_sql := 'INSERT INTO test_table (' || v_column_name || ') VALUES (:1)';
EXECUTE IMMEDIATE v_sql USING v_value;
END;Dynamic SQL with Bulk Operations
26. Dynamic SQL for Bulk Inserts
Description: This example demonstrates dynamic sql for bulk inserts.
DECLARE
TYPE t_numbers IS TABLE OF NUMBER;
v_numbers t_numbers := t_numbers(15, 16, 17);
v_sql VARCHAR2(200);
BEGIN
FORALL i IN v_numbers.FIRST..v_numbers.LAST
v_sql := 'INSERT INTO test_table (id) VALUES (:1)';
EXECUTE IMMEDIATE v_sql USING v_numbers(i);
END;27. Dynamic SQL for Bulk Updates
Description: This example demonstrates dynamic sql for bulk updates.
DECLARE
TYPE t_numbers IS TABLE OF NUMBER;
v_numbers t_numbers := t_numbers(15, 16, 17);
v_sql VARCHAR2(200);
BEGIN
FORALL i IN v_numbers.FIRST..v_numbers.LAST
v_sql := 'UPDATE test_table SET id = id + 1 WHERE id = :1';
EXECUTE IMMEDIATE v_sql USING v_numbers(i);
END;28. Dynamic SQL for Bulk Deletes
Description: This example demonstrates dynamic sql for bulk deletes.
DECLARE
TYPE t_numbers IS TABLE OF NUMBER;
v_numbers t_numbers := t_numbers(15, 16, 17);
v_sql VARCHAR2(200);
BEGIN
FORALL i IN v_numbers.FIRST..v_numbers.LAST
v_sql := 'DELETE FROM test_table WHERE id = :1';
EXECUTE IMMEDIATE v_sql USING v_numbers(i);
END;Dynamic SQL with Collections
29. Dynamic SQL with Nested Table Type
Description: This example demonstrates dynamic sql with nested table type.
CREATE OR REPLACE TYPE t_numbers AS TABLE OF NUMBER;
DECLARE
v_numbers t_numbers := t_numbers(18, 19, 20);
v_sql VARCHAR2(200);
BEGIN
FOR i IN 1..v_numbers.COUNT LOOP
v_sql := 'INSERT INTO test_table (id) VALUES (:1)';
EXECUTE IMMEDIATE v_sql USING v_numbers(i);
END LOOP;
END;30. Dynamic SQL with VARRAY Type
Description: This example demonstrates dynamic sql with varray type.
CREATE OR REPLACE TYPE t_varray AS VARRAY(10) OF NUMBER;
DECLARE
v_numbers t_varray := t_varray(21, 22, 23);
v_sql VARCHAR2(200);
BEGIN
FOR i IN 1..v_numbers.COUNT LOOP
v_sql := 'INSERT INTO test_table (id) VALUES (:1)';
EXECUTE IMMEDIATE v_sql USING v_numbers(i);
END LOOP;
END;Dynamic SQL with Ref Cursors
31. Dynamic SQL with Ref Cursor for Select
Description: This example demonstrates dynamic sql with ref cursor for select.
DECLARE
TYPE ref_cursor IS REF CURSOR;
v_cursor ref_cursor;
v_sql VARCHAR2(200);
v_id NUMBER;
BEGIN
v_sql := 'SELECT id FROM test_table WHERE id = 24';
OPEN v_cursor FOR v_sql;
FETCH v_cursor INTO v_id;
DBMS_OUTPUT.PUT_LINE('ID: ' || v_id);
CLOSE v_cursor;
END;32. Dynamic SQL with Ref Cursor for Multiple Rows
Description: This example demonstrates dynamic sql with ref cursor for multiple rows.
DECLARE
TYPE ref_cursor IS REF CURSOR;
v_cursor ref_cursor;
v_sql VARCHAR2(200);
v_id NUMBER;
BEGIN
v_sql := 'SELECT id FROM test_table WHERE id > 20';
OPEN v_cursor FOR v_sql;
LOOP
FETCH v_cursor INTO v_id;
EXIT WHEN v_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('ID: ' || v_id);
END LOOP;
CLOSE v_cursor;
END;Dynamic SQL with Dynamic PL/SQL Blocks
33. Dynamic SQL for Executing PL/SQL Blocks
Description: This example demonstrates dynamic sql for executing pl/sql blocks.
DECLARE
v_sql VARCHAR2(200);
BEGIN
v_sql := 'BEGIN INSERT INTO test_table (id) VALUES (25); END;';
EXECUTE IMMEDIATE v_sql;
END;34. Dynamic SQL with Exception Handling in PL/SQL Block
Description: This example demonstrates dynamic sql with exception handling in pl/sql block.
DECLARE
v_sql VARCHAR2(200);
BEGIN
v_sql := 'BEGIN INSERT INTO test_table (id) VALUES (26); EXCEPTION WHEN OTHERS THEN NULL; END;';
EXECUTE IMMEDIATE v_sql;
END;Dynamic SQL for Data Definition Language (DDL)
35. Dynamic SQL for Creating Table
Description: This example demonstrates dynamic sql for creating table.
DECLARE
v_sql VARCHAR2(200);
BEGIN
v_sql := 'CREATE TABLE dynamic_table (id NUMBER)';
EXECUTE IMMEDIATE v_sql;
END;36. Dynamic SQL for Dropping Table
Description: This example demonstrates dynamic sql for dropping table.
DECLARE
v_sql VARCHAR2(200);
BEGIN
v_sql := 'DROP TABLE dynamic_table';
EXECUTE IMMEDIATE v_sql;
END;Dynamic SQL with User-Defined Types
37. Dynamic SQL with User-Defined Record Type
Description: This example demonstrates dynamic sql with user-defined record type.
DECLARE
TYPE t_record IS RECORD (id NUMBER);
v_record t_record;
v_sql VARCHAR2(200);
BEGIN
v_sql := 'SELECT id FROM test_table WHERE id = 27';
EXECUTE IMMEDIATE v_sql INTO v_record.id;
DBMS_OUTPUT.PUT_LINE('ID: ' || v_record.id);
END;38. Dynamic SQL with User-Defined Table Type
Description: This example demonstrates dynamic sql with user-defined table type.
CREATE OR REPLACE TYPE t_number_table AS TABLE OF NUMBER;
DECLARE
v_numbers t_number_table := t_number_table();
v_sql VARCHAR2(200);
BEGIN
v_sql := 'SELECT id BULK COLLECT INTO :1 FROM test_table WHERE id > 20';
EXECUTE IMMEDIATE v_sql BULK COLLECT INTO v_numbers;
FOR i IN 1..v_numbers.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('ID: ' || v_numbers(i));
END LOOP;
END;Dynamic SQL with Large Objects (LOBs)
39. Dynamic SQL for Inserting CLOB
Description: This example demonstrates dynamic sql for inserting clob.
DECLARE
v_sql VARCHAR2(200);
v_clob CLOB := 'This is a CLOB';
BEGIN
v_sql := 'INSERT INTO test_table (clob_column) VALUES (:1)';
EXECUTE IMMEDIATE v_sql USING v_clob;
END;40. Dynamic SQL for Selecting CLOB
Description: This example demonstrates dynamic sql for selecting clob.
DECLARE
v_sql VARCHAR2(200);
v_clob CLOB;
BEGIN
v_sql := 'SELECT clob_column FROM test_table WHERE id = 28';
EXECUTE IMMEDIATE v_sql INTO v_clob;
DBMS_OUTPUT.PUT
_LINE('CLOB: ' || DBMS_LOB.SUBSTR(v_clob, 100));
END;Dynamic SQL for Partitioned Tables
41. Dynamic SQL for Creating Partitioned Table
Description: This example demonstrates dynamic sql for creating partitioned table.
DECLARE
v_sql VARCHAR2(200);
BEGIN
v_sql := 'CREATE TABLE partitioned_table (id NUMBER, name VARCHAR2(50)) PARTITION BY RANGE (id) (PARTITION p1 VALUES LESS THAN (100), PARTITION p2 VALUES LESS THAN (200))';
EXECUTE IMMEDIATE v_sql;
END;42. Dynamic SQL for Inserting into Partitioned Table
Description: This example demonstrates dynamic sql for inserting into partitioned table.
DECLARE
v_sql VARCHAR2(200);
BEGIN
v_sql := 'INSERT INTO partitioned_table (id, name) VALUES (150, ''Test'')';
EXECUTE IMMEDIATE v_sql;
END;Dynamic SQL with XML
43. Dynamic SQL for Inserting XML
Description: This example demonstrates dynamic sql for inserting xml.
DECLARE
v_sql VARCHAR2(200);
v_xml XMLTYPE := XMLTYPE('<root><element>Value</element></root>');
BEGIN
v_sql := 'INSERT INTO test_table (xml_column) VALUES (:1)';
EXECUTE IMMEDIATE v_sql USING v_xml;
END;44. Dynamic SQL for Selecting XML
Description: This example demonstrates dynamic sql for selecting xml.
DECLARE
v_sql VARCHAR2(200);
v_xml XMLTYPE;
BEGIN
v_sql := 'SELECT xml_column FROM test_table WHERE id = 29';
EXECUTE IMMEDIATE v_sql INTO v_xml;
DBMS_OUTPUT.PUT_LINE('XML: ' || v_xml.getCLOBVal());
END;Dynamic SQL with PL/SQL Collections
45. Dynamic SQL with Nested Tables and Bulk Collect
Description: This example demonstrates dynamic sql with nested tables and bulk collect.
DECLARE
TYPE t_numbers IS TABLE OF NUMBER;
v_numbers t_numbers;
v_sql VARCHAR2(200);
BEGIN
v_sql := 'SELECT id BULK COLLECT INTO :1 FROM test_table WHERE id > 20';
EXECUTE IMMEDIATE v_sql BULK COLLECT INTO v_numbers;
FOR i IN 1..v_numbers.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('ID: ' || v_numbers(i));
END LOOP;
END;46. Dynamic SQL with VARRAYs
Description: This example demonstrates dynamic sql with varrays.
CREATE OR REPLACE TYPE t_varray IS VARRAY(10) OF NUMBER;
DECLARE
v_varray t_varray := t_varray(30, 31, 32);
v_sql VARCHAR2(200);
BEGIN
FOR i IN 1..v_varray.COUNT LOOP
v_sql := 'INSERT INTO test_table (id) VALUES (:1)';
EXECUTE IMMEDIATE v_sql USING v_varray(i);
END LOOP;
END;Dynamic SQL with Oracle Data Pump
47. Dynamic SQL to Export Data with Data Pump
Description: This example demonstrates dynamic sql to export data with data pump.
DECLARE
v_sql VARCHAR2(200);
BEGIN
v_sql := 'BEGIN DBMS_DATAPUMP.OPEN(operation => ''EXPORT'', job_mode => ''SCHEMA'', job_name => ''export_job''); END;';
EXECUTE IMMEDIATE v_sql;
END;48. Dynamic SQL to Import Data with Data Pump
Description: This example demonstrates dynamic sql to import data with data pump.
DECLARE
v_sql VARCHAR2(200);
BEGIN
v_sql := 'BEGIN DBMS_DATAPUMP.OPEN(operation => ''IMPORT'', job_mode => ''SCHEMA'', job_name => ''import_job''); END;';
EXECUTE IMMEDIATE v_sql;
END;Dynamic SQL with Data Guard
49. Dynamic SQL to Switch Data Guard Role
Description: This example demonstrates dynamic sql to switch data guard role.
DECLARE
v_sql VARCHAR2(200);
BEGIN
v_sql := 'ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL';
EXECUTE IMMEDIATE v_sql;
END;50. Dynamic SQL to Start Data Guard Apply
Description: This example demonstrates dynamic sql to start data guard apply.
DECLARE
v_sql VARCHAR2(200);
BEGIN
v_sql := 'ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION';
EXECUTE IMMEDIATE v_sql;
END;Dynamic SQL with DBMS_JOB
51. Dynamic SQL to Submit a Job
Description: This example demonstrates dynamic sql to submit a job.
DECLARE
v_sql VARCHAR2(200);
v_job NUMBER;
BEGIN
v_sql := 'BEGIN DBMS_JOB.SUBMIT(:1, ''my_procedure;'', SYSDATE, ''SYSDATE + 1/1440''); END;';
EXECUTE IMMEDIATE v_sql USING v_job;
END;52. Dynamic SQL to Remove a Job
Description: This example demonstrates dynamic sql to remove a job.
DECLARE
v_sql VARCHAR2(200);
v_job NUMBER := 123; -- Example job number
BEGIN
v_sql := 'BEGIN DBMS_JOB.REMOVE(:1); END;';
EXECUTE IMMEDIATE v_sql USING v_job;
END;Dynamic SQL with Parallel Execution
53. Dynamic SQL for Parallel Insert
Description: This example demonstrates dynamic sql for parallel insert.
DECLARE
v_sql VARCHAR2(200);
BEGIN
v_sql := 'INSERT /*+ PARALLEL(test_table, 4) */ INTO test_table (id) SELECT id FROM another_table';
EXECUTE IMMEDIATE v_sql;
END;54. Dynamic SQL for Parallel Update
Description: This example demonstrates dynamic sql for parallel update.
DECLARE
v_sql VARCHAR2(200);
BEGIN
v_sql := 'UPDATE /*+ PARALLEL(test_table, 4) */ test_table SET id = id + 1';
EXECUTE IMMEDIATE v_sql;
END;Dynamic SQL with Advanced Queueing (AQ)
55. Dynamic SQL to Enqueue Message
Description: This example demonstrates dynamic sql to enqueue message.
DECLARE
v_sql VARCHAR2(200);
BEGIN
v_sql := 'BEGIN DBMS_AQ.ENQUEUE(queue_name => ''my_queue'', enqueue_options => DBMS_AQ.ENQUEUE_OPTIONS_T(), message_properties => DBMS_AQ.MESSAGE_PROPERTIES_T(), payload => ''Test message'', msgid => NULL); END;';
EXECUTE IMMEDIATE v_sql;
END;56. Dynamic SQL to Dequeue Message
Description: This example demonstrates dynamic sql to dequeue message.
DECLARE
v_sql VARCHAR2(200);
BEGIN
v_sql := 'BEGIN DBMS_AQ.DEQUEUE(queue_name => ''my_queue'', dequeue_options => DBMS_AQ.DEQUEUE_OPTIONS_T(), message_properties => DBMS_AQ.MESSAGE_PROPERTIES_T(), payload => ''Test message'', msgid => NULL); END;';
EXECUTE IMMEDIATE v_sql;
END;Dynamic SQL with Oracle Text
57. Dynamic SQL to Create Text Index
Description: This example demonstrates dynamic sql to create text index.
DECLARE
v_sql VARCHAR2(200);
BEGIN
v_sql := 'CREATE INDEX text_index ON test_table (clob_column) INDEXTYPE IS CTXSYS.CONTEXT';
EXECUTE IMMEDIATE v_sql;
END;58. Dynamic SQL to Execute Text Query
Description: This example demonstrates dynamic sql to execute text query.
DECLARE
v_sql VARCHAR2(200);
v_count NUMBER;
BEGIN
v_sql := 'SELECT COUNT(*) FROM test_table WHERE CONTAINS(clob_column, ''Oracle'') > 0';
EXECUTE IMMEDIATE v_sql INTO v_count;
DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
END;Dynamic SQL with Spatial Data
59. Dynamic SQL to Create Spatial Index
Description: This example demonstrates dynamic sql to create spatial index.
DECLARE
v_sql VARCHAR2(200);
BEGIN
v_sql := 'CREATE INDEX spatial_index ON test_table (geometry) INDEXTYPE IS MDSYS.SPATIAL_INDEX';
EXECUTE IMMEDIATE v_sql;
END;60. Dynamic SQL to Execute Spatial Query
Description: This example demonstrates dynamic sql to execute spatial query.
DECLARE
v_sql VARCHAR2(200);
v_count NUMBER;
BEGIN
v_sql := 'SELECT COUNT(*) FROM test_table WHERE SDO_RELATE(geometry, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(1, 1, NULL), NULL, NULL), ''mask=ANYINTERACT'') = ''TRUE''';
EXECUTE IMMEDIATE v_sql INTO v_count;
DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
END;Dynamic SQL with Flashback
61. Dynamic SQL to Enable Flashback
Description: This example demonstrates dynamic sql to enable flashback.
DECLARE
v_sql VARCHAR2(200);
BEGIN
v_sql := 'ALTER DATABASE FLASHBACK ON';
EXECUTE IMMEDIATE v_sql;
END;62. Dynamic SQL to Disable Flashback
Description: This example demonstrates dynamic sql to disable flashback.
DECLARE
v_sql VARCHAR2(200);
BEGIN
v_sql := 'ALTER DATABASE FLASHBACK OFF';
EXECUTE IMMEDIATE v_sql;
END;Dynamic SQL with Contexts
63. Dynamic SQL to Create Context
Description: This example demonstrates dynamic sql to create context.
DECLARE
v_sql VARCHAR2(200);
BEGIN
v_sql := 'CREATE CONTEXT my_context USING my_package';
EXECUTE IMMEDIATE v_sql;
END;64. Dynamic SQL to Set Context Value
Description: This example demonstrates dynamic sql to set context value.
DECLARE
v_sql VARCHAR2(200);
BEGIN
v_sql := 'BEGIN DBMS_SESSION.SET_CONTEXT(''my_context'', ''attribute'', ''value''); END;';
EXECUTE IMMEDIATE v_sql;
END;Dynamic SQL with Auditing
65. Dynamic SQL to Enable Auditing
Description: This example demonstrates dynamic sql to enable auditing.
DECLARE
v_sql VARCHAR2(200);
BEGIN
v_sql := 'AUDIT INSERT ON test_table BY SESSION';
EXECUTE IMMEDIATE v_sql;
END;66. Dynamic SQL to Disable Auditing
Description: This example demonstrates dynamic sql to disable auditing.
DECLARE
v_sql VARCHAR2(200);
BEGIN
v_sql := 'NOAUDIT INSERT ON test_table';
EXECUTE IMMEDIATE v_sql;
END;Dynamic SQL with Oracle Streams
67. Dynamic SQL to Create Streams Capture Process
Description: This example demonstrates dynamic sql to create streams capture process.
DECLARE
v_sql VARCHAR2(200);
BEGIN
v_sql := 'BEGIN DBMS_STREAMS_ADM.ADD_TABLE_RULES(table_name => ''test_table'', streams_type => ''capture'', streams_name => ''my_capture'', include_dml => true, include_ddl => true); END;';
EXECUTE IMMEDIATE v_sql;
END;68. Dynamic SQL to Drop Streams Capture Process
Description: This example demonstrates dynamic sql to drop streams capture process.
DECLARE
v_sql VARCHAR2(200);
BEGIN
v_sql := 'BEGIN DBMS_STREAMS_ADM.REMOVE_TABLE_RULES(table_name => ''test_table'', streams_type => ''capture'', streams_name => ''my_capture''); END;';
EXECUTE IMMEDIATE v_sql;
END;Dynamic SQL with Result Cache
69. Dynamic SQL to Enable Result Cache
Description: This example demonstrates dynamic sql to enable result cache.
DECLARE
v_sql VARCHAR2(200);
BEGIN
v_sql := 'ALTER SYSTEM SET result_cache_max_size = 1048576';
EXECUTE IMMEDIATE v_sql;
END;70. Dynamic SQL to Disable Result Cache
Description: This example demonstrates dynamic sql to disable result cache.
DECLARE
v_sql VARCHAR2(200);
BEGIN
v_sql := 'ALTER SYSTEM SET result_cache_max_size = 0';
EXECUTE IMMEDIATE v_sql;
END;
No comments:
Post a Comment