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;
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;
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.
Comments (0)