set serveroutput on;
DECLARE
v_table_name varchar(100):='abc';
sql_stmt varchar(255):='default';
cnt NUMBER:=0;
cursor tbl is select table_name from TBP_tables_tmp;
BEGIN
update TBP_tables_tmp set cnt=0;
FOR reg IN tbl LOOP
--dbms_output.put_line ( reg.table_name );
v_table_name :=reg.table_name;
sql_stmt := 'update TBP_tables_tmp '
|| ' set cnt=( '
|| 'select count(transaction_status) from '
||v_table_name
||' where transaction_status=''PUB'''
|| ') where table_name='''
|| v_table_name || '''';
DBMS_OUTPUT.put_line (sql_stmt);
EXECUTE IMMEDIATE sql_stmt ;
end loop;
select table_name into v_table_name from TBP_tables_tmp where cnt>0;
sql_stmt :='update ' ||v_table_name || ' set TRANSACTION_STATUS=''NEW'' where TRANSACTION_STATUS=''PUB''';
DBMS_OUTPUT.put_line (sql_stmt);
EXECUTE IMMEDIATE sql_stmt ;
END;
/
------------------
create table TBP_tables_tmp (table_name varchar2(50), cnt number);