Coding on the Fly: Using the Execute Immediate Procedure

Dr. Paul Dorsey & Yalim Gerger

Dulcian, Inc.

Overview

There are a few new features in Oracle8 v. 8.1.5 that can greatly help developers create systems more easily and efficiently. This article will discuss one of these – the EXECUTE IMMEDIATE command and how it can be useful in building flexible systems.

What does EXECUTE IMMEDIATE do?

The EXECUTE IMMEDIATE command enables you to build an anonymous PL/SQL block on the fly and execute it in interpreted mode. This allows a developer to build a block as a textual string and command that this string be executed at runtime.

For example, if you want to execute a procedure called PROC_DO_IT, you would use EXECUTE IMMEDIATE with the following syntax:

DECLARE

code_text varchar2(2000):=

'begin

proc_do_it;

end;'

BEGIN

EXECUTE IMMEDIATE (code_text);

END;

Of course, instead of the code above, you could simply write a single line of code:

proc_do_it;

However, by using the EXECUTE IMMEDIATE procedure, you can build the desired code on the fly based on something queried from the database. At Dulcian, we build systems based on very abstract structures with most of the business rules stored as data in the database. We then generate potentially hundreds of PL/SQL procedures that are continuously executed under particular circumstances. Using EXECUTE IMMEDIATE greatly simplified our code generation procedures, creating substantially less code that was simpler and much more readable.

Technical Aspects of the EXECUTE IMMEDIATE command

Notice that in the PROC_DO_IT procedure we used a “begin” and “end,” bracketing the procedure and building an entire PL/SQL block. In this context, it is necessary to do this since you cannot pass an isolated procedure call. If you are passing a DDL or DML command such as UPDATE TABLE or CREATE AND REPLACE VIEW, you do not need to bracket the procedure with “begin” and “end.” In those cases, the command can be passed alone.

For DDL commands, the appropriate grant must be given to the schema that the procedure is in. Otherwise you will get the ORA-1031 “Insufficient privileges” error.

Advantages

There are several advantages of using EXECUTE IMMEDIATE commands.

1. EXECUTE IMMEDIATE commands do not usually significantly impact performance. However, there are times that performance can be affected. We ran a 100-line program by calling it directly. Then we used EXECUTE IMMEDIATE and passed the whole code to it. The directly called procedure executed in 0.04 seconds. The EXECUTE IMMEDIATE procedure executed in 0.396 seconds. Note that the performance hit only occurs the first time that the routines are executed. When we tried it a second time, they both executed in 0.04 seconds, which means that once the PL/SQL block) in EXECUTE IMMEDIATE is compiled, it resides in the memory just like any other PL/SQL block and calling it is just as fast as calling any other PL/SQL block in the memory. Our experience is that the compilation time required by EXECUTE IMMEDIATE is not a problem in our applications.

2. We were pleasantly surprised to find that the error messages generated when performing an EXECUTE IMMEDIATE are of the same quality as error messages produced by any other part of the program. Compiler error messages are received during execution. This was helpful in finding bugs in our code more easily.

Limitations

The EXECUTE IMMEDIATE command does have the following limitations:

1. The most significant limitation to the EXECUTE IMMEDIATE command is that it is not exactly the same as an anonymous PL/SQL block built on the fly. The difference is that variables outside of the EXECUTE IMMEDIATE program string are not visible to the EXECUTE IMMEDIATE command. The only way to pass information to an EXECUTE IMMEDIATE string is by concatenating it into the string when it is created as in the following example, which would execute the command PROC_DEPT for Department 10:

DECLARE

deptno NUMBER :=10;

code_text VARCHAR2(2000);

BEGIN

code_text :=

'begin

proc_dept('

||deptno||');';

EXECUTE IMMEDIATE (code_text);

END;

2. Getting information out of an EXECUTE IMMEDIATE command is similarly problematic. You cannot update any variable that was not declared within the context of the EXECUTE IMMEDIATE command.

To make information visible to code outside of the EXECUTE IMMEDIATE command, you have to either write the information to some place in a database table, or place the information into a package variable outside of the procedure when the command is executed. You cannot update variables in the procedure that executes the EXECUTE IMMEDIATE command.

3. You cannot use EXECUTE IMMEDIATE within Oracle Developer v. 6.0. The command is not recognized. If you want to use EXECUTE IMMEDIATE within a Forms application, you must write a server-side procedure and call that procedure from within Forms.

When Should You Use EXECUTE IMMEDIATE?

Even if you do not fully embrace abstract data structures and code generation, the EXECUTE IMMEDIATE command is still useful. For example, DBAs can use it to build a utility allowing them to delete all information from multiple tables based on filter criteria.

Developers might find EXECUTE IMMEDIATE handy for replacing large, cumbersome IF THEN ELSIF statements that have dozens of similar ELSIF clauses.

The EXECUTE IMMEDIATE command seems to be reasonably robust. We have used it to execute a procedure, which itself successfully called an EXECUTE IMMEDIATE command. However, thus far, we have not tried to build particularly large routines using EXECUTE IMMEDIATE so we cannot comment on how well it would work with large program units. It does work very well with small program units.

Examples

In one of the systems we are building, we are generating procedures associated with a state transition rule engine. Each state has a procedure that is generated in association with it. We are taking documents that exist in a particular state and, as a result of being in that state, a particular procedure should be executed. Part of what this procedure does is to move the document along to the next state in the workflow.

We wanted to observe the state that the document is in, call the associated procedure and move it along to its next state. We could do this with a large IF THEN ELSIF statement using a separate condition for each state. Unfortunately, the possible document states are very volatile. Either this procedure would require constant maintenance or a code generator would be required to generate the procedure every time the workflow changed. By using EXECUTE IMMEDIATE, we were able to solve this problem easily as the following code illustrates:

PROCEDURE p_do_it IS

/*cursor to loop through all

active documents*/

CURSOR c_doc_id IS

SELECT d.doc_id, d.doc_state_id, ds.z_doc_state_cd

FROM doc d, doc_state ds

WHERE d.activ_yn = 'Y'

AND d.doc_state_id = ds.doc_state_id;

/*cursor to retrieve the current

state of the document*/

CURSOR c_info (cin_doc_id NUMBER) IS

SELECT ds.z_doc_state_cd, ds.doc_state_id

FROM doc d, doc_state ds

WHERE d.doc_state_id = ds.doc_state_id

AND d.doc_id = cin_doc_id;

ci c_info%ROWTYPE;

v_exec_tx VARCHAR2(2000);

v_z_doc_state_cd VARCHAR2(100) := 'AUTO';

v_count_nr NUMBER;

BEGIN --------main-------------

--loop through active docs

FOR cdi IN c_doc_id LOOP

v_count_nr := 0;

---fetch doc state

OPEN c_info(cdi.doc_id);

FETCH c_info into ci;

CLOSE c_info;

/*only execute the procedure for

automatic states and only allow

a total of 10 states to be

executed for each doc*/

WHILE v_count_nr < 10 AND ci.z_doc_state_cd IN ('automatic_state','AUTO') LOOP

---build code string

v_exec_tx := 'begin p_auto_'||ci.doc_state_id||'('||cdi.doc_id||'); end;';

EXECUTE IMMEDIATE (v_exec_tx);

COMMIT;

OPEN c_info(cdi.doc_id);

FETCH c_info into ci;

CLOSE c_info;

v_count_nr := v_count_nr +1;

END LOOP; --state

COMMIT;

END LOOP; ---doc

END;

This procedure walks through all active documents, one at a time, and passes them from state to state until they reach a termination state. We added a loop terminator so that no document could pass through more than ten states to prevent infinite loops.

NOTE: This procedure assumes the use of consistent naming conventions for procedures all being called P_AUTO_[procedure ID for specific state].

For additional information about EXECUTE IMMEDIATE, you can also see the whitepaper “Oracle 8i PL/SQL: Faster, Easier, and Better” by Usha Sangam, Ashok Swaminathan, and Dennis Fogg (Oracle Magazine, Sept/Oct 1999, pp. 79-82)

Conclusion

EXECUTE IMMEDIATE can be very useful. Ever since we began using generic models and tried to store business rules as data, we have wanted the capabilities that EXECUTE IMMEDIATE supplies. From the authors’ perspective, the greatest benefit of using EXECUTE IMMEDIATE is to make the coding of applications to support generic data structures much easier. Now with Oracle8.1.5, we can realize the time and effort savings that this new procedure provides.

About the Authors

Dr. Paul Dorsey is the founder and President of Dulcian, Inc. (www.dulcian.com) Dulcian specializes in Oracle Client-Server and Web custom application development and data migration. Paul is co-author with Peter Koletzke of The Oracle Designer Handbook (now in its second edition) and with Joseph R. Hudicka of Oracle8 Design Using UML Object Modeling, both from Oracle Press, 1999. Paul is an Associate Editor of SELECT Magazine. He is the President of the New York Oracle Users’ Group. Paul and Peter shared the Pinnacle Publishing Technical Achievement Award at ECO’95 for their work on a Forms template that became the basis for this book. Paul has won best presentation awards at both ECO and IOUW conferences.

Yalim Gerger is a Developer with Dulcian, Inc. He graduated from Istanbul Technical University in 1999. Yalim specializes in data modeling and Oracle development using Designer and Developer.