A H M A D G O H A R

Please Wait For Loading

    You Here!
  • Home
  • DatabasesPL/SQLMastering Dynamic SQL in PL/SQL: Native Dynamic SQL & DBMS_SQL Explained
Ahmad Gohar Featured Image 1886_826

Mastering Dynamic SQL in PL/SQL: Native Dynamic SQL & DBMS_SQL Explained

November 18, 2013 Ahmad Gohar 0 Comments

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:

  1. Native Dynamic SQL (NDS): Uses EXECUTE IMMEDIATE for easier execution of SQL statements.
  2. 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

  1. OPEN_CURSOR: Opens a cursor and returns a cursor ID.
  2. PARSE: Parses the SQL statement.
  3. BIND_VARIABLE: Binds variables to placeholders in the SQL statement.
  4. EXECUTE: Executes the parsed SQL statement.
  5. FETCH_ROWS: Retrieves rows for queries.
  6. 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

  1. Avoid SQL Injection:
    Always use bind variables (e.g., :1, :2) instead of concatenating user inputs directly into SQL statements.

  2. Optimize Performance:
    Use Native Dynamic SQL for most cases, as it is faster and easier to implement than DBMS_SQL.

  3. 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.
  4. Error Handling:
    Wrap dynamic SQL in BEGIN ... 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? 😊

author avatar
Ahmad Gohar
With over 18 years of experience in software architecture, Java technologies, and leadership, I specialize in crafting scalable, future-proof solutions for global organizations. Whether it’s transforming legacy systems, building cutting-edge cloud-native applications, or mentoring teams to excel, I’m committed to delivering value-driven results.

Leave A Comment