Oracle Collections 100 examples

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.