Dynamic SQL in Oracle

Dynamic SQL is an advanced programming technique that adds flexibility and functionality to your applications. It is required when either table name or the query logic is not known before. Dynamically a query string can be constructed and run using either EXECUTE IMMEDIATE or by using REF CURSORS. The following example shows how to run a dynamic select query and output the result to a variable.

In practice, static SQL will meet nearly all your programming needs. Use dynamic SQL only if you need its open-ended flexibility. Its use is suggested when one of the following items is unknown at precompile time:

text of the SQL statement (commands, clauses, and so on)
the number of host variables
the datatypes of host variables
references to database objects such as columns, indexes, sequences, tables, usernames, and views

A Simple Dynamic Query

There are different ways of writing Dynamic SQL. The easiest approach is to use EXECUTE IMMEDIATE in PL/SQL block.
Using REF CURSORS is another method.


declare
ename varchar2(100);
begin
execute immediate 'select FIRST_NAME from employees where employee_id=100' INTO ename;
end;
/

Catching Errors

Using the standard built-in exceptions or user defined exceptions it is possible to capture the error during query execution.


declare
ename varchar2(100);
begin
execute immediate 'select FIRST_NAME from employees where DEPARTMENT_ID=90' INTO ename;
exception
WHEN NO_DATA_FOUND then DBMS_output.put_line('No Data Available');
WHEN TOO_MANY_ROWS then DBMS_output.put_line('More than one row returned');
end;
/

Using Bind Variables

It is possible to construct a query using bind variables. When you bind in a value, the SQL string itself does not contain the value; it contains only the placeholder name. Therefore, you can bind different values to the same SQL statement without changing the statement. Since it's the same statement, your application is more likely to be able to take advantage of the pre-parsed cursors that are cached in the System Global Area (SGA) of the database. The binding can be done as follows


declare
ename varchar2(100);
v_empid number := 100;
begin
execute immediate 'select FIRST_NAME from employees where employee_id=:empid' INTO ename USING v_empid;
exception
WHEN NO_DATA_FOUND then DBMS_output.put_line('No Data Available');
WHEN TOO_MANY_ROWS then DBMS_output.put_line('More than one row returned');
end;
/

Using Collections

If the query fetches more than one row, then the output should be redirected to a collection.


declare
TYPE varchartab is TABLE OF varchar2(100);
enametab varchartab;
begin
execute immediate 'select FIRST_NAME from employees where DEPARTMENT_ID=90' BULK COLLECT INTO enametab;
exception
WHEN NO_DATA_FOUND then DBMS_output.put_line('No Data Available');
WHEN TOO_MANY_ROWS then DBMS_output.put_line('More than one row returned');
end;
/

BULK COLLECT with BULK BIND

If the bind values are more then it can be placed in a collection and the binding can be done as follows.
Here we REF Cursoes for running Dynamic SQL.
The REF CURSOR TYPE has to declared within a package.
The collection type has to created as SQL Object.


create or replace package rec_cur_pkg as
TYPE refcurtype is REF CURSOR;
end;
/


create or replace TYPE NUMTAB as TABLE OF number;
/


declare
TYPE varchartab is TABLE OF varchar2(100);
enametab varchartab;
empidtab NUMTAB := NUMTAB();
rc1 rec_cur_pkg.refcurtype;
begin
empidtab.extend; empidtab(1) := 100;
empidtab.extend; empidtab(1) := 101;
empidtab.extend; empidtab(1) := 102;
OPEN rc1 for 'select FIRST_NAME from employees where employee_id IN(select * from TABLE(:id))' USING empidtab;
fetch rc1  BULK COLLECT INTO enametab ;
close rc1;
exception
WHEN NO_DATA_FOUND then DBMS_output.put_line('No Data Available');
WHEN TOO_MANY_ROWS then DBMS_output.put_line('More than one row returned');
end;
/

BULK UPDATE with EXCEPTION Handling

The following example demonstrates how to collect the exceptions in a collection.


declare
i1 number;
errors number;
TYPE varchartab is TABLE OF varchar2(100);
enametab varchartab;
empidtab NUMTAB := NUMTAB();
rc1 rec_cur_pkg.refcurtype;
begin
empidtab.extend; empidtab(1) := 100;
empidtab.extend; empidtab(2) := 101;
empidtab.extend; empidtab(3) := 102;
OPEN rc1 for 'select employee_id from employees where employee_id IN(select * from TABLE(:id))' USING empidtab;
fetch rc1  BULK COLLECT INTO enametab ;
close rc1;
dbms_output.put_line(enametab.count);
FORALL i1 IN 1..enametab.COUNT SAVE EXCEPTIONS
update employees set salary=salary*1.05 where employee_id=enametab(i1);

exception
WHEN OTHERS THEN
    errors := SQL%BULK_EXCEPTIONS.COUNT;
    dbms_output.put_line('Number of UPDATE statements that failed: ' || errors);
    FOR i1 IN 1 .. errors
    LOOP
      dbms_output.put_line('Error #' || i1 || ' at '|| 'iteration#' || SQL%BULK_EXCEPTIONS(i1).ERROR_INDEX);
      dbms_output.put_line('Error message is ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i1).ERROR_CODE));
    END LOOP;
end;
/