-- SQL Script, executed in Toad
declare
...
... some variables ...
l_cnt1 number;
l_cntd number;
begin
... some statements
-- define two global temporary Tables and fill them with data with a "select as ...", each enclosed in "begin ... end"
begin
-- check if the table exists ...
select count(*) into l_cnt1 from user_tables ut where ut.temporary = 'Y' and ut.table_name = upper('temp_code_classif');
if l_cnt1 > 0 then
execute immediate ('truncate table temp_code_classif');
execute immediate ('drop table temp_code_classif');
end if;
l_sql := 'CREATE GLOBAL TEMPORARY TABLE temp_code_classif
ON COMMIT PRESERVE ROWS
as
(select cla.id
,cla.obj_type_id
,cla.intl_id
,cla.name
,cla.user_id
,cla.obj_classif_type_id
from code_obj_classif cla
where cla.id in (select regexp_substr((''' || classif_ids || '''),''[^,]+'', 1, level) As id
from dual
connect by regexp_substr((''' || classif_ids || '''), ''[^,]+'', 1, level) is not null
)
)';
execute immediate (l_sql);
commit;
end;
-- the second GTT ...
begin
select count(*) into l_cnt2 from user_tables ut where ut.temporary = 'Y' and ut.table_name = upper('temp_code_class');
--select * from all_tables ut where temporary = 'Y' and ut.table_name = upper('temp_code_class');
if l_cnt2 > 0 then
execute immediate ('truncate table temp_code_class');
execute immediate ('drop table temp_code_class');
end if;
l_sql := 'CREATE GLOBAL TEMPORARY TABLE temp_code_class
ON COMMIT PRESERVE ROWS
as
(select cla.id
,cla.obj_classif_id
,cla.intl_id
,cla.name
,cla.user_id
,cla.intl2_id
from code_obj_class cla
where cla.activ = ''+''
and cla.obj_classif_id in (select regexp_substr((''' || classif_ids || '''),''[^,]+'', 1, level) As id
from dual
connect by regexp_substr((''' || classif_ids || '''), ''[^,]+'', 1, level) is not null
)
)';
execute immediate (l_sql);
commit;
end;
select count(*) into l_cntd from temp_code_classif;
dbms_output.put_line('temp_code_classif select count(*) from = ' || l_cntd);
select count(*) into l_cntd from temp_code_class;
dbms_output.put_line('temp_code_classif select count(*) from = ' || l_cntd);
-- ... now the error "PL/SQL: ORA-00942: table or view does not exist" is thrown.
-- at this time, the GTT's doesn't exists. So if i define them in a separate sql-window in the same session, the script will run without error.
... some other code ...
end;
Does anyone have a proposal for solution ?
Many thanks in advance, Konrad