PL/SQL TUTORIALS - DYNAMIC SQL

PL/SQL Tutorials - DYNAMIC SQL

Within PL/SQL, you can execute any kind of SQL statement (even data definition and data control statements) without resorting to cumbersome programmatic approaches. Dynamic SQL blends seamlessly into your programs, making them more efficient, readable, and concise

You need dynamic SQL in the following situations:

You want to execute a SQL data definition statement (such as CREATE), a data control statement (such as GRANT), or a session control statement (such as ALTER SESSION). In PL/SQL, such statements cannot be executed statically. You want more flexibility.

For example, you might want to defer your choice of schema objects until run time. Or, you might want your program to build different search conditions for the WHERE clause of a SELECT statement. A more complex program might choose from various SQL operations, clauses, etc.

You use package DBMS_SQL to execute SQL statements dynamically, but you want better performance, something easier to use, or functionality that DBMS_SQL lacks such as support for objects and collections.

EXECUTE IMMEDIATE

This statement, when used with in a PLSQL block does execute the statement at the server.

Syntax:

EXECUTE IMMEDIATE dynamic_string
[INTO {define_variable [, define_variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument
[, [IN | OUT | IN OUT] bind_argument]...]
[{RETURNING | RETURN} INTO bind_argument [, bind_argument]...];

Examples of Dynamic SQL

The following PL/SQL block contains several examples of dynamic SQL:

DECLARE
sql_stmt VARCHAR2 (200);
plsql_block VARCHAR2 (500);
emp_id NUMBER (4):= 7566;
salary NUMBER (7, 2);
dept_id NUMBER (2):= 50;
dept_name VARCHAR2 (14):= 'PERSONNEL';
location VARCHAR2 (13):= 'DALLAS';
emp_recemp%ROWTYPE;
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';
sql_stmt:= 'INSERT INTO dept VALUES (:1, :2, :3)';
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;
sql_stmt:= 'SELECT * FROM emp WHERE empno =: id’;
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
plsql_block:= 'BEGIN emp_pkg.raise_salary (:id, :amt); END;';
EXECUTE IMMEDIATE plsql_block USING 7788, 500;
sql_stmt:= 'UPDATE emp SET sal = 2000 WHERE empno =:1
RETURNING sal INTO: 2’;
EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;
EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno =: num’
USING dept_id;
EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
END;

Using Dynamic SQL for DML activities is not a good practice. Thomas Kyte has explained with great care all these aspects in his book "Effective Oracle by Design"

An excerpt from that book "Effective Oracle by Design" on this very relevant topic:

Use Static SQL

If you can achieve your goals using static SQL-even if you need to write a little more code-do it that way. Generic code using dynamic SQL is wonderful, but it will be less scalable, harder to debug and maintain, and slower running than the same code using static SQL.

Advantages of Static SQL

The advantages of using static SQL over dynamic SQL in PL/SQL are many and profound. Consider some of these reasons to use static SQL:

o Static SQL is checked at compile time. This way, you know that the procedure is valid and can execute. You might still have a bug in your code, but at least
you know the SQL isn't the problem.

o PL/SQL will validate datatypes, sizes, and so on. This means that you don't need to define records to fetch into or define tons of variables. You do less coding work, and the compiler does more work.

o The dependencies are set up and maintained in the data dictionary. So, no one can claim "I didn't know you were using that."

o If the base objects change over time, your code fixes itself automatically. If you add or remove columns or grants, you don't need to worry about your code. On the other hand, when you make these changes and are using dynamic SQL, you will need to inspect your code for correctness.

o Static SQL makes parse once, execute many a reality. Dynamic SQL makes it easier to lose out on this benefit. This is especially true with the newer native dynamic SQL, where each and every execution of a statement is preceded by a parse call (this changes slightly in 10g).

o Static SQL is faster. Doing something with dynamic SQL in PL/SQL is slower than doing the same thing with static SQL.

The most important features of static SQL that we lose with dynamic SQL are dependency-tracking and performance-related issues surrounding parsing and overall execution speed.

Look for Opportunities to Replace Dynamic SQL

People frequently use dynamic SQL where static SQL could be used instead. For example, suppose we want to write a small lookup routine that will return one of three columns. We might code that routine like this:

ops$tkyte@ORA920> create or replace function get_value_dyn
( p_empno in number, p_cname in varchar2 ) return varchar2
2 as
3 l_value varchar2(4000);
4 begin
5 execute immediate
6 'select ' || p_cname || ' from emp where empno = :x'
7 into l_value
8 using p_empno;
9
10 return l_value;
11 end;
12 /
Function created.

Instead, we could code this routine using static SQL, like this:

ops$tkyte@ORA920> create or replace function get_value_static
( p_empno in number, p_cname in varchar2 ) return varchar2
2 as
3 l_value varchar2(4000);
4 begin
5 select decode( upper(p_cname),
6 'ENAME', ename,
7 'EMPNO', empno,
8 'HIREDATE', to_char(hiredate,'yyyymmddhh24miss'))
9 into l_value
10 from emp
11 where empno = p_empno;
12
13 return l_value;
14 end;
15 /
Function created.

This version is not as generic as the dynamic SQL routine, so if we wanted to support additional columns over time, we would need to modify code. But a simple Runstats test might help convince you that it is well worth it. Here are the results when calling the dynamic function:

ops$tkyte@ORA920> exec runstats_pkg.rs_start;

PL/SQL procedure successfully completed.

ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
2 l_dummyvarchar2(30);
3 begin
4 for i in 1 .. 500
5 loop
6 for x in ( select empno from emp )
7 loop
8 l_dummy := get_value_dyn(x.empno, 'ENAME' );
9 l_dummy := get_value_dyn(x.empno, 'EMPNO' );
10 l_dummy := get_value_dyn(x.empno, 'HIREDATE' );
11 end loop;
12 end loop;
13 end;
14 /

PL/SQL procedure successfully completed.

And here is what happens when we do it statically:

ops$tkyte@ORA920> exec runstats_pkg.rs_middle
PL/SQL procedure successfully completed.

ops$tkyte@ORA920> declare
2 l_dummyvarchar2(30);
3 begin
4 for i in 1 .. 500
5 loop
6 for x in ( select empno from emp )
7 loop
8 l_dummy := get_value_static(x.empno, 'ENAME' );
9 l_dummy := get_value_static(x.empno, 'EMPNO' );
10 l_dummy := get_value_static(x.empno, 'HIREDATE' );
11 end loop;
12 end loop;
13 end;
14 /
PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec runstats_pkg.rs_stop(1000);
Run1 ran in 1102 hsecs
Run2 ran in 703 hsecs
run 1 ran in 156.76% of the time

The static version is obviously faster by the wall clock. However, the more
compelling reasons to use that version follow:

Name

Run1

Run2

Diff

STAT...session cursor cache hi

21,0003

4

-21,999

STAT...opened cursors cumulative

21,005

5

-21,000

STAT...parse count (total)

21,005

5

-21,000

LATCH.shared pool

63,565

21,566

-41,999

LATCH.library cache pin

127,062

43,064

-83,998

LATCH.library cache

127,087

43,088

-83,999

Those are some really big differences. Look at the latching statistics here:

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
463,431 253,318 -210,113 182.94%

PL/SQL procedure successfully completed.

This shows almost double the latching activity using dynamic SQL. Also notice the statistic for session cursor cache hits, which shows that we made heavy use of that feature. If session-cached cursors had been disabled on this system, the latching for the dynamic SQL would have been three times that of static SQL.

Static SQL Wrap-up

Yes, there are times when nothing else but dynamic SQL will do the job. But you should look for opportunities to remove dynamic SQL, especially if the dynamic SQL is there simply to save a couple lines of code. Consider this example:

If (condition1) then
Update using predicate1
Elsif (condition2) then
Update using predicate2
?
Elsif (conditionN) then
update using predicateN
End if;

An alternative is to use this code:

L_stmt := 'update ?' || some_predicate;
Execute immediate l_stmt;

It would be worth having 40 to 60 lines of code (the first version) instead of 2 lines of code, if this statement is executed repeatedly in a session. The bottom line is to use common sense and be aware of the trade-offs.

Dynamic SQL is something you want to use when static SQL is no longer practical-when you would be writing hundreds of lines of code, not just 40 or so. Or, you will need to use dynamic SQL when no amount of static SQL will do. When coding dynamic SQL, do not forget about the existing DBMS_SQL package in addition to native dynamic SQL. There are opportunities for scaling up with DBMS_SQL that are not available with native dynamic SQL. For example, with native dynamic SQL, every EXECUTE has a corresponding PARSE call to go along with it (as you can see from the Runstats example in the previous section). The additional parsing is the major cause of excessive latching. This can be removed by using the parse once, execute many rule.

DBMS_SQL, since it is a procedural API, allows Oracle to parse a statement once (such as an INSERT statement) and then execute it hundreds or thousands of times. So, if you know that your dynamic SQL statement will be executed many times but with different inputs, you would use DBMS_SQL. For example, with a dynamic-loader routine that accepts as inputs the table name and filename to load, the SQL INSERT will be constant; just the inputs will vary. If, on the other hand, you know your routine will rarely (if ever) execute the same dynamic SQL statement call after call, you would use native dynamic SQL.

Further follow this link and read thoroughly.

http://asktom.oracle.com/pls/ask/f?p=4950:8:4601780557872522698::NO::F4950_P8_DISPLAYID,F4950_P8_B:227413938857,Y