PLSQL Dynamic SQL

        Dynamic SQL is a programming methodology for generating and running SQL statements at run time. This allows developers to write flexible and adaptable PL/SQL code that can execute SQL commands based on variable input, changes in table structures, or other runtime conditions.

Features:

1. Runtime Execution: Constructs and executes SQL statements at runtime.
2. Flexibility: Allows for changing query structures based on input or conditions.
3. Binding Variables: Supports bind variables to prevent SQL injection and improve performance.
4. DDL Execution: Can execute DDL commands like CREATE, ALTER, and DROP.
5. Error Handling: Catches errors at runtime for better management.
6. Complex Queries: Capable of handling joins, unions, and subqueries dynamically.
7. Reduced Duplication: Enables writing more generic, reusable code.
8. Integration with Logic: Easily integrates with application logic for dynamic query creation.

Example:

CREATE OR REPLACE PROCEDURE create_user_with_permissions (
p_username IN VARCHAR2,
p_password IN VARCHAR2
) AS
sql_stmt VARCHAR2(200);
BEGIN
-- Create the new user
sql_stmt := 'CREATE USER ' || p_username || ' IDENTIFIED BY ' || p_password;
EXECUTE IMMEDIATE sql_stmt;

-- Grant permissions
sql_stmt := 'GRANT CONNECT TO ' || p_username;
EXECUTE IMMEDIATE sql_stmt;

sql_stmt := 'GRANT SELECT ON employees TO ' || p_username;
EXECUTE IMMEDIATE sql_stmt;

DBMS_OUTPUT.PUT_LINE('User ' || p_username || ' created and granted permissions.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END create_user_with_permissions;

Run:

BEGIN
create_user_with_permissions('new_user', 'password123');
END;
/