14.6. Dynamic SQL

Dynamic SQL enables you to write programs that reference SQL statements whose full text is not known until runtime. So in other words Dynamic SQL enables us to execute statements that change from execution to execution.

Dynamic SQL programs can handle changes in data definitions, without the need to recompile. This makes dynamic SQL much more flexible than static SQL. Dynamic SQL lets you write reusable code because the SQL can be easily adapted for different environments.

Dynamic SQL also lets you execute data definition language (DDL) statements and other SQL statements that are not supported in purely static SQL programs.

However, you should only make use of Dynamic SQL if your situation requires you to do so as it's performance is slower than static SQL.

14.6.1. The need for Dynamic SQL

You need dynamic SQL in the following situations:

You want to execute a SQL data definition statement (such as CREATE), a data control statement (such as GRANT), or a session control statement (such as ALTER SESSION).
You want more flexibility. For example, you might want to defer your choice of schema objects until run time. Or, you might want your program to build different search conditions for the WHERE clause of a SELECT statement. A more complex program might choose from various SQL operations, clauses, etc.

14.6.2. Dynamic Sql Syntax

Dynamic SQL can be used via the EXECUTE IMMEDIATE statement from SPL procedures, user defined functions and anonymous SPL blocks.

Following is the syntax for EXECUTE IMMEDIATE:

EXECUTE IMMEDIATE dynamic_string;

dynamic string is a string expression that represents a SQL statement or SPL block.

14.6.3. Examples

The following example shows the a simple Dynamic SQL statement:

DECLARE
  sql_stmt VARCHAR(200);
BEGIN
   EXECUTE IMMEDIATE 'CREATE TABLE bonus(id NUMBER, amt NUMBER)';
   sql_stmt:= 'INSERT INTO dept(deptno,loc,dname) VALUES(50,''CHICAGO'',''HR'');';
   EXECUTE IMMEDIATE sql_stmt;
END;

Following is an advanced example of USING clause with execute immediate.

create or replace function imed_using return integer
IS
DECLARE
	v_empno  emp.empno%TYPE;
	V_DNAME  dept.dname%TYPE;
	v_query_string  VARCHAR2(200);
BEGIN
	v_query_string := 'select emp.empno, dept.dname  from emp join dept using (deptno) where dname = :pname and emp.sal = :psal';
	execute immediate v_query_string INTO v_empno,v_dname USING '''SALES''',1500;
	DBMS_OUTPUT.PUT_LINE('EMPNO    DEPT');
    	DBMS_OUTPUT.PUT_LINE('-----    -------');
	DBMS_OUTPUT.PUT_LINE(v_empno||'     '|| v_dname);
	return 0;
END;

select imed_using();

The result of the above SQL script will be the following:

INFO:  EMPNO    DEPT
INFO:  -----    -------
INFO:  7844     SALES
CREATE FUNCTION
 imed_using 
------------
          0
(1 row)

When the SQL statement is executed, input host variables in the USING clause replace corresponding placeholders in the prepared dynamic SQL statement. Every placeholder in the prepared dynamic SQL statement must correspond to a different host variable in the USING clause. So, if the same placeholder appears two or more times in the prepared statement, each appearance must correspond to a host variable in the USING clause.