Dynamic SQL in Oracle

Dynamic SQL is also called as generic or collective SQL. It is similar to any other dynamic code which is generated in run-time; it’s based on the run-time values and variables not like explicit strings.

This is really helpful when we write any code and want to change the selected column data and want to change the where clause based on our business requirement , whereas by using Dynamic SQL we need not need to change the whole query or write the query multiple times but we can handle all these changes in one.

We can use one any DML and DDL commands, means which change the data or change the metadata.

It can be used inside or outside procedures.

The most efficient method of using a Dynamic SQL is by using “EXECUTE IMMEDIATE” command. This is required to be used for DDL commands inside the procedures as we cannot use DDL command directly inside the procedure and also required the privileges for executing DDL commands.

DBMS_SQL package: This package is used to execute dynamic SQL but “EXECUTE IMMEDIATE” (now is called native dynamic SQL) is much faster and simpler way to use dynamic SQL.

Example 1:

SQL> DECLARE
  CURSOR C1
  IS
    SELECT object_name
    FROM all_objects
    WHERE owner     ='SCOTT'
    AND object_type ='TABLE'
    ORDER BY object_name;
  rows INTEGER ;
BEGIN
  FOR irec IN c1
  LOOP
    EXECUTE immediate 'SELECT COUNT(*) FROM '||irec.object_name INTO rows;
    dbms_output.put_line(irec.object_name||' = '||TO_CHAR(rows));
  END LOOP;
END;

Select all Open in new window

From the above PLSQL block I am getting all the record counts from all the tables present in SCOTT schema.

The cursor will get all the tables from SCOTT schema and then I am using a native dynamic SQL using execute immediate command to count all the records present from the table which are getting populated from the result of my cursor. And finally I am printing the output by using DBMS_OUTPUT package.


Now in another example let us see how we can use a variable in dynamic SQL.
I am going to use the same as PLSQL block as show above but a slight modification to it by adding a variable called “v_sql” which will be a very large varchar2 datatype variable.

Example 2:

DECLARE
  CURSOR C1
  IS
    SELECT object_name
    FROM all_objects
    WHERE owner     ='SCOTT'
    AND object_type ='TABLE'
    ORDER BY object_name;
  rows integer ;
  v_sql varchar2(4000);
BEGIN
  FOR irec IN c1
  loop
     v_sql:= 'SELECT COUNT(*) FROM '||irec.object_name ;
     EXECUTE immediate v_sql into rows; 
    dbms_output.put_line(irec.object_name||' = '||TO_CHAR(rows));
  END LOOP;
END;

Select all Open in new window

Now from the above code you can see that I have just used another variable v_sql which executed by using execute immediate command and then gets table and its total counts using DBMS_OUTPUT package.

Visit Oracle documentation web link for more information on Dynamic SQL : http://www.oracle.com/technetwork/indexes/documentation/index.html for example : http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm Hope you too will find it a valuable tool when used the right way.