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.
