Why does EXECUTE IMMEDIATE exit a loop?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Why does EXECUTE IMMEDIATE exit a loop?

  1. #1
    Join Date
    Oct 2004
    Posts
    14

    Question Why does EXECUTE IMMEDIATE exit a loop?

    Hello,

    Script 1 below result in script 2 (as it should).
    If the EXECUTE IMMEDIATE statement is uncommented, it results in script 3 (but it should not).

    Why does it exit the loop?

    Thanks!

    Gabor

    Script 1:

    Code:
    create or replace procedure p as
    	cursor c1 is
    		select		column_name
    		from		dba_tab_columns
    		where		owner = 'RETAIL'
    		  and		table_name = 'ENV'
    		  and		data_type = 'VARCHAR2'
    		order by	1;
    	ls_column_name varchar2(30);
    	ls_sql varchar2(4000);
    	ln_cnt number := 100;
    begin
    	dbms_output.put_line ( '1 ln_sql=' || ls_sql );
    	open c1;
    	loop
    		dbms_output.put_line ( '2 ln_sql=' || ls_sql );
    		fetch c1 into ls_column_name;
    		exit when c1%notfound;
    
    		dbms_output.put_line ( '3 ln_sql=' || ls_sql );
    		ls_sql :=	' select count(*)'
    				|| ' from retail.env where '
    				|| ls_column_name || ' = ''DEMO''';
    		--execute immediate ls_sql into ln_cnt;
    		dbms_output.put_line ( '4 ln_sql=' || ls_sql );
    	end loop;
    	close c1;
    	dbms_output.put_line ( '5 ln_sql=' || ls_sql );
    exception
    	when others then
    		if	c1%isopen
    		then	close c1;
    		end if;
    end p;
    /
    show errors
    begin
    	p;
    end;
    /
    Script 2:

    Code:
    Procedure created.
    
    No errors.
    1 ln_sql=
    2 ln_sql=
    3 ln_sql=
    4 ln_sql= select count(*) from retail.env where EN_ACTION = 'DEMO'
    2 ln_sql= select count(*) from retail.env where EN_ACTION = 'DEMO'
    3 ln_sql= select count(*) from retail.env where EN_ACTION = 'DEMO'
    4 ln_sql= select count(*) from retail.env where EN_AELONGNAME = 'DEMO'
    2 ln_sql= select count(*) from retail.env where EN_AELONGNAME = 'DEMO'
    3 ln_sql= select count(*) from retail.env where EN_AELONGNAME = 'DEMO'
    4 ln_sql= select count(*) from retail.env where EN_BCAST = 'DEMO'
    2 ln_sql= select count(*) from retail.env where EN_BCAST = 'DEMO'
    3 ln_sql= select count(*) from retail.env where EN_BCAST = 'DEMO'
    4 ln_sql= select count(*) from retail.env where EN_BRANCH = 'DEMO'
    2 ln_sql= select count(*) from retail.env where EN_BRANCH = 'DEMO'
    3 ln_sql= select count(*) from retail.env where EN_BRANCH = 'DEMO'
    4 ln_sql= select count(*) from retail.env where EN_EMAIL_USERID = 'DEMO'
    2 ln_sql= select count(*) from retail.env where EN_EMAIL_USERID = 'DEMO'
    3 ln_sql= select count(*) from retail.env where EN_EMAIL_USERID = 'DEMO'
    4 ln_sql= select count(*) from retail.env where EN_ENV = 'DEMO'
    2 ln_sql= select count(*) from retail.env where EN_ENV = 'DEMO'
    3 ln_sql= select count(*) from retail.env where EN_ENV = 'DEMO'
    4 ln_sql= select count(*) from retail.env where EN_FAX = 'DEMO'
    2 ln_sql= select count(*) from retail.env where EN_FAX = 'DEMO'
    3 ln_sql= select count(*) from retail.env where EN_FAX = 'DEMO'
    4 ln_sql= select count(*) from retail.env where EN_FILLNTC = 'DEMO'
    2 ln_sql= select count(*) from retail.env where EN_FILLNTC = 'DEMO'
    3 ln_sql= select count(*) from retail.env where EN_FILLNTC = 'DEMO'
    4 ln_sql= select count(*) from retail.env where EN_FSTNAM = 'DEMO'
    2 ln_sql= select count(*) from retail.env where EN_FSTNAM = 'DEMO'
    3 ln_sql= select count(*) from retail.env where EN_FSTNAM = 'DEMO'
    4 ln_sql= select count(*) from retail.env where EN_JOBFUNC = 'DEMO'
    2 ln_sql= select count(*) from retail.env where EN_JOBFUNC = 'DEMO'
    3 ln_sql= select count(*) from retail.env where EN_JOBFUNC = 'DEMO'
    4 ln_sql= select count(*) from retail.env where EN_LANG = 'DEMO'
    2 ln_sql= select count(*) from retail.env where EN_LANG = 'DEMO'
    3 ln_sql= select count(*) from retail.env where EN_LANG = 'DEMO'
    4 ln_sql= select count(*) from retail.env where EN_LSTDTE = 'DEMO'
    2 ln_sql= select count(*) from retail.env where EN_LSTDTE = 'DEMO'
    3 ln_sql= select count(*) from retail.env where EN_LSTDTE = 'DEMO'
    4 ln_sql= select count(*) from retail.env where EN_LSTNAM = 'DEMO'
    2 ln_sql= select count(*) from retail.env where EN_LSTNAM = 'DEMO'
    3 ln_sql= select count(*) from retail.env where EN_LSTNAM = 'DEMO'
    4 ln_sql= select count(*) from retail.env where EN_MENU = 'DEMO'
    2 ln_sql= select count(*) from retail.env where EN_MENU = 'DEMO'
    3 ln_sql= select count(*) from retail.env where EN_MENU = 'DEMO'
    4 ln_sql= select count(*) from retail.env where EN_MIDDLE_INIT = 'DEMO'
    2 ln_sql= select count(*) from retail.env where EN_MIDDLE_INIT = 'DEMO'
    3 ln_sql= select count(*) from retail.env where EN_MIDDLE_INIT = 'DEMO'
    4 ln_sql= select count(*) from retail.env where EN_NAME = 'DEMO'
    2 ln_sql= select count(*) from retail.env where EN_NAME = 'DEMO'
    3 ln_sql= select count(*) from retail.env where EN_NAME = 'DEMO'
    4 ln_sql= select count(*) from retail.env where EN_NAME_PREFIX = 'DEMO'
    2 ln_sql= select count(*) from retail.env where EN_NAME_PREFIX = 'DEMO'
    3 ln_sql= select count(*) from retail.env where EN_NAME_PREFIX = 'DEMO'
    4 ln_sql= select count(*) from retail.env where EN_NAME_QUALIFIER = 'DEMO'
    2 ln_sql= select count(*) from retail.env where EN_NAME_QUALIFIER = 'DEMO'
    3 ln_sql= select count(*) from retail.env where EN_NAME_QUALIFIER = 'DEMO'
    4 ln_sql= select count(*) from retail.env where EN_PHONE = 'DEMO'
    2 ln_sql= select count(*) from retail.env where EN_PHONE = 'DEMO'
    3 ln_sql= select count(*) from retail.env where EN_PHONE = 'DEMO'
    4 ln_sql= select count(*) from retail.env where EN_PIN = 'DEMO'
    2 ln_sql= select count(*) from retail.env where EN_PIN = 'DEMO'
    3 ln_sql= select count(*) from retail.env where EN_PIN = 'DEMO'
    4 ln_sql= select count(*) from retail.env where EN_SCMPIN = 'DEMO'
    2 ln_sql= select count(*) from retail.env where EN_SCMPIN = 'DEMO'
    3 ln_sql= select count(*) from retail.env where EN_SCMPIN = 'DEMO'
    4 ln_sql= select count(*) from retail.env where EN_SUPER = 'DEMO'
    2 ln_sql= select count(*) from retail.env where EN_SUPER = 'DEMO'
    3 ln_sql= select count(*) from retail.env where EN_SUPER = 'DEMO'
    4 ln_sql= select count(*) from retail.env where EN_WEBSITE = 'DEMO'
    2 ln_sql= select count(*) from retail.env where EN_WEBSITE = 'DEMO'
    5 ln_sql= select count(*) from retail.env where EN_WEBSITE = 'DEMO'
    
    PL/SQL procedure successfully completed.
    Script 3:
    Code:
    Procedure created.
    
    No errors.
    1 ln_sql=
    2 ln_sql=
    3 ln_sql=
    
    PL/SQL procedure successfully completed.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    probably becuase its erroring and your exception block isnt catching it

  3. #3
    Join Date
    Oct 2004
    Posts
    14
    Yes, that solved it.
    Thanks!
    Gabor
    Quote Originally Posted by davey23uk View Post
    probably becuase its erroring and your exception block isnt catching it

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width