Mastering Dynamic SQL in PL/SQL: Native Dynamic SQL & DBMS_SQL Explained
Diving into Dynamic SQL in PL/SQL
Dynamic SQL in PL/SQL is essential when the exact structure of a SQL statement isn’t known until runtime. This is particularly useful for handling scenarios like dynamically constructing SQL queries or executing SQL/DDL statements.
Dynamic SQL Techniques in PL/SQL
There are two main approaches to handling dynamic SQL in PL/SQL:
- Native Dynamic SQL (NDS): Uses
EXECUTE IMMEDIATE
for easier execution of SQL statements. - DBMS_SQL Package: Provides finer control over SQL parsing, binding, and execution.
Native Dynamic SQL (NDS)
Native Dynamic SQL simplifies the execution of dynamic SQL statements using the EXECUTE IMMEDIATE
command.
Use Case 1: Create Tables Dynamically
CREATE OR REPLACE PROCEDURE create_table(
p_table_name VARCHAR2,
p_col_specs VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE
'CREATE TABLE ' || p_table_name || ' (' || p_col_specs || ')';
END;
/
-- Example Execution
BEGIN
create_table('EMPLOYEE_NAMES', 'id NUMBER(4) PRIMARY KEY, name VARCHAR2(40)');
END;
/
Use Case 2: Insert Rows Dynamically
CREATE PROCEDURE add_row(
p_table_name VARCHAR2,
p_id NUMBER,
p_name VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE
'INSERT INTO ' || p_table_name || ' VALUES (:1, :2)'
USING p_id, p_name;
END;
/
Use Case 3: Fetch Data Dynamically
CREATE OR REPLACE FUNCTION get_emp(p_emp_id NUMBER)
RETURN employees%ROWTYPE IS
v_stmt VARCHAR2(200);
v_emprec employees%ROWTYPE;
BEGIN
v_stmt := 'SELECT * FROM employees WHERE employee_id = :id';
EXECUTE IMMEDIATE v_stmt INTO v_emprec USING p_emp_id;
RETURN v_emprec;
END;
/
DBMS_SQL Package
The DBMS_SQL package provides granular control over SQL execution and is ideal for advanced scenarios like handling unknown numbers of variables or select-list items at runtime.
Key Methods in DBMS_SQL
OPEN_CURSOR
: Opens a cursor and returns a cursor ID.PARSE
: Parses the SQL statement.BIND_VARIABLE
: Binds variables to placeholders in the SQL statement.EXECUTE
: Executes the parsed SQL statement.FETCH_ROWS
: Retrieves rows for queries.CLOSE_CURSOR
: Closes the cursor.
Example: Insert Rows Dynamically Using DBMS_SQL
CREATE OR REPLACE PROCEDURE insert_row(
p_table_name VARCHAR2,
p_id VARCHAR2,
p_name VARCHAR2,
p_region NUMBER) IS
v_cur_id INTEGER;
v_stmt VARCHAR2(200);
v_rows_added NUMBER;
BEGIN
v_stmt := 'INSERT INTO ' || p_table_name || ' VALUES (:cid, :cname, :rid)';
v_cur_id := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cur_id, v_stmt, DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(v_cur_id, ':cid', p_id);
DBMS_SQL.BIND_VARIABLE(v_cur_id, ':cname', p_name);
DBMS_SQL.BIND_VARIABLE(v_cur_id, ':rid', p_region);
v_rows_added := DBMS_SQL.EXECUTE(v_cur_id);
DBMS_SQL.CLOSE_CURSOR(v_cur_id);
DBMS_OUTPUT.PUT_LINE(v_rows_added || ' row added');
END;
/
-- Example Execution
EXECUTE insert_row('countries', 'LB', 'Lebanon', 4);
Comparison: Native Dynamic SQL vs. DBMS_SQL
Feature | Native Dynamic SQL (NDS) | DBMS_SQL Package |
---|---|---|
Ease of Use | Easier | More complex |
Performance | Faster | Slightly slower |
Flexibility | Limited | High |
Dynamic SQL with Bindings | Yes | Yes |
Dynamic Query Result Parsing | Limited | Advanced |
Best Practices for Dynamic SQL
-
Avoid SQL Injection:
Always use bind variables (e.g.,:1, :2
) instead of concatenating user inputs directly into SQL statements. -
Optimize Performance:
Use Native Dynamic SQL for most cases, as it is faster and easier to implement than DBMS_SQL. -
Choose the Right Method:
- Use NDS for simple dynamic SQL scenarios.
- Use DBMS_SQL for advanced cases, like unknown numbers of variables or select-list items.
-
Error Handling:
Wrap dynamic SQL inBEGIN ... EXCEPTION ... END;
blocks to gracefully handle runtime errors.
Conclusion
Dynamic SQL is a powerful feature in PL/SQL that enables flexible execution of SQL statements at runtime. Depending on your use case, you can choose between the ease of Native Dynamic SQL or the flexibility of the DBMS_SQL package.
Would you like more advanced examples or help with specific dynamic SQL scenarios? 😊