Dynamic SQL

As we have seen PL/SQL can use only DML statements in PL/SQL blocks. This can be rectified, however, through dynamic SQL. Rather than being parsed along with the PL/SQL block, dynamic SQL is parsed and executed at runtime.

The native dynamic SQL introduced in Oracle 8i is an integral part of the language. Oracle has the special dynamic SQL statement execute immediate that has the following syntax:

execute immediate plsql_string_variable_or_constant
 [using list_of_values];

The following examples show how to use simple execute immediate:

create or replace procedure AddUser as
begin
  execute immediate 'create user test identified by password';
end;

This is very simple example that just shows how to execute a constant non-DML statement. Of course, this is not very useful example and now we will try to make it more advanced:

create or replace procedure AddUser(p_uname in varchar2) as
  v_cruser varchar2(256);
begin
  v_cuser := 'create user ' || p_uname || ' identified by password';
  execute immediate v_cuser;
end;

In fact, the PL/SQL string variable may contain not only a SQL statement, but also a whole PL/SQL block like this example shows. Please note that the trailing semicolon is not included for DML and DDL statements, but is for anonymous blocks.

execute immediate is also used to execute statements with bind variables. In this case, the string to be executed contains placeholders, which are identified by a leading colon. The placeholders are matched by position with PL/SQL variables that are found in the using clause of execute immediate, as the following example illustrates:

create or replace procedure ShowAllAboutUser(p_uname in varchar2) as
  v_select varchar2(256);
  v_user  varchar2(32);
begin
  v_select := 'select * from ALL_TABLES where user=:username';
  execute immediate v_select using p_uname;
  select user from dual into v_user;
  execute immediate 'insert into Inquiries values (:who, :about, sysdate)' using v_user, p_uname;
end;

You can find more detailed example from Scott Urman book here

Executing queries

. We already saw how to use open ... for statement to initialize a cursor variable. It turns out that this statement can have a PL/SQL variable instead of explicitly specified select statement. The same example we discussed in the cursor variable section can be rewritten using dynamic SQL

create or replace function GPA(p_sid sid Student.SID%type) 
return number as
  /* cursor variable to find out which table to look in */
  type TCV_Student is ref cursor return Student%rowtype;
  cv_Student TCV_Student;   
  v_tablename varchar2(48);
  v_Student Student%rowtype;
  v_Select varchar2(256);
  /* cursor variable to figure out the GPA */
  type TNumber is record ( value number );
  type TCV_GPA is ref cursor;
  cv_GPA TCV_GPA;
  v_GPA TNumber;
begin
  open cv_Student for select * from Student;
  fetch cv_Student into v_Student;
  if cv_Student%found then
    /* this student is currently enrolled */
	v_tablename := 'Transcript';
  else
    /* this is probably an alumni */
	v_tablename := 'AlumniTranscript';
  end if; 
  close cv_Student;  
  v_Select := 'select avg(grade) from ' || v_tablename || ' where SID=''' || p_SID || '''';
  open cv_GPA for v_Select;
  fetch cv_GPA into v_GPA;
  close cv_GPA;
  return v_GPA.value;
end;

Please note that the cursor for the dynamic open ... for must be declared without the return type.

execute immediate can also be used for single-row queries, either with or without bind variables. In this case it works similar to select ... into statement. The only difference is that we can construct the select statement dynamically. Here is a short illustration:

...
v_Table  := 'Courses';
v_Select := 'select * from ' || v_Table || ' where CID=:cid';
execute immediate v_Select
  into v_Course
  using 'IST467';
...