Dynamic SQL

Dynamic SQL is a SQL statement that is constructed and executed at program execution time. In contrast to this, static SQL statements are hard-coded in the program and executed "as-is" at run-time. Dynamic SQL provides more flexibility, nevertheless, static SQL is faster and more secure than dynamic SQL.

PL/SQL examples

Starting from Oracle 8i one can use the "EXECUTE IMMEDIATE" statement to execute dynamic SQL and PL/SQL statements (statements created at run-time). Look at these examples. Note that statements are NOT terminated by semicolons:

EXECUTE IMMEDIATE 'CREATE TABLE x (a NUMBER)';

-- Using bind variables...
sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;

-- Returning a cursor...
sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;

One can also use the older DBMS_SQL package (PL/SQL v2.1 and above) to execute dynamic statements. Look at these examples:

CREATE OR REPLACE PROCEDURE DYNSQL AS
  cur integer;
  rc  integer;
BEGIN
  cur := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
  rc := DBMS_SQL.EXECUTE(cur);
  DBMS_SQL.CLOSE_CURSOR(cur);
END;
/

More complex DBMS_SQL example using bind variables:

CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS
  v_cursor integer;
  v_dname  char(20);
  v_rows   integer;
BEGIN
  v_cursor := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cursor, 'select dname from dept where deptno > :x', DBMS_SQL.V7);
  DBMS_SQL.BIND_VARIABLE(v_cursor, ':x', no);
  DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20);
  v_rows := DBMS_SQL.EXECUTE(v_cursor);
  loop
    if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then
       exit;
    end if;
    DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname);
    DBMS_OUTPUT.PUT_LINE('Deptartment name: '||v_dname);
  end loop;
  DBMS_SQL.CLOSE_CURSOR(v_cursor);
EXCEPTION
  when others then
       DBMS_SQL.CLOSE_CURSOR(v_cursor);
       raise_application_error(-20000, 'Unknown Exception Raised: '||sqlcode||' '||sqlerrm);
END;
/

EXECUTE IMMEDIATE is often used in the maintenance code that automatically alters tables, indexes, etc. because in Embedded SQL you cannot specify the object name as a variable (Oracle needs to pre-compile your query and it cannot do this with unspecified object name). When you create a dynamic SQL, your query is compiled at runtime. EXECUTE IMMEDIATE is often faster than DBMS_SQL and it should be your first choice when static SQL is not enough.

If your query grows a lot or gets very dynamic (ex. has variable number of parameters) then you might reconsider slower option: built-in package DBMS_SQL. Note that it is different from the PL/SQL coding pattern and is might make your code more complicated.

DBMS_SQL has 3 forms in Oracle 10g:

• Simple form accepting VARCHAR2(32767)
• Extended form accepting VARCHAR2S (TABLE OF VARCHAR2(256))
• Extended form accepting VARCHAR2A (TABLE OF VARCHAR2(32767))

DBMS_SQL has 3 forms in Oracle 11g:

• Simple form accepting VARCHAR2(32767)
• Simple form accepting CLOB
• Extended form accepting VARCHAR2A (TABLE OF VARCHAR2(32767))

Also see

• Embedded SQL
• Recursive SQL

Glossary of Terms
A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	#