Why does EXECUTE IMMEDIATE exit a loop?

[Gabor]

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:

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:

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:

Procedure created.

No errors.
1 ln_sql=
2 ln_sql=
3 ln_sql=

PL/SQL procedure successfully completed.

[davey23uk]

probably becuase its erroring and your exception block isnt catching it

[Gabor]

Yes, that solved it.
Thanks!
Gabor

probably becuase its erroring and your exception block isnt catching it