--
create or replace procedure PF_ETL_DropBITempTable
as
BEGIN
declare
CURSOR cur_table is select table_name from all_tables where owner='NCOLAP' and Table_Name like 'BI_%';
row_table cur_table%ROWTYPE;
s_sql varchar2(2000);
BEGIN
OPEN cur_table;
FETCH cur_table INTO row_table;
WHILE cur_table%FOUND
LOOP
dbms_output.put_line(row_table.table_name);
s_sql := 'drop table '|| row_table.table_name;
dbms_output.put_line(s_sql);
EXECUTE IMMEDIATE s_sql;
FETCH cur_table INTO row_table;
END LOOP;
close cur_table;
END;
END;
--exec PF_ETL_DropBITempTable
--select * from DIM_TEMPLATE
