<

Dynamic SQL in Oracle

Published on
9,526 Points
3,526 Views
Last Modified:
Approved
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;

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.

Example 1
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;

Open in new window


Example 2
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.
0
Comment
Author:Swadhin Ray
0 Comments

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Join & Write a Comment

This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month