Dynamic SQL in Oracle

Reference:  http://www.experts-exchange.com/Database/Oracle/A_11327-Dynamic-SQL-in-Oracle.html

 

Dynamic SQL is also called as generic or collective SQL. It is similar to any other dynamic code which is generated in runtime; it’s based on the runtime 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;

 

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.

 

image

 

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 variable.

 

Example 2:

SQL> 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;

 

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.

image

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s