Jump to content
suhaasv

Can an Execute Immediate statement be nested inside another Execute Immediate

Recommended Posts

I have a stored procedure where is an Execute Immediate will call a "Begin ... End " block which contains another Execute Immediate which will be called only if a certain condition is met.

The inner Execute Immediate command inside "Begin... End clock" is enclosed in single quotes. So it looks like

BEGIN
...SOME STUFF HERE...

FOR ..... LOOP
EXECUTE IMMEDIATE
           'BEGIN
              IF (condition) THEN
                     EXECUTE IMMEDIATE 'DML STRING'
              END IF
            END;'

END LOOP;

END;

That begs another question can an Execute Immediate statement be called from within the loop. I think the answer is yes.

Share this post


Link to post
Share on other sites

Hi, and welcome to the forum!

This is a good question, but I have never tried it.

You should be able to build a simple test procedure of bested execute immediate calls and see if you get an error!

You could create a dummy table and use the execute immediate to delete specific rows:

http://www.dba-oracle.com/t_oracle_execute_immediate.htm

Let us know the results of your test.

I have also asked Bob Ganesan, a PL/SQL guru, to pop-in and advise you.

Good luck!

Share this post


Link to post
Share on other sites

Hello Suhaasv,

Eventhough you don't have to use an EXECUTE IMMEDIATE in a nested way, your script will work just fine. It makes more sense to not include the syntax as it's already a string.

 

Thank you,

Boobal Ganesan

Author of Advanced PL/SQL Programming: The definitive reference

Share this post


Link to post
Share on other sites
Guest
This topic is now closed to further replies.

×