Check the unicode conversions and drop tables with function:

The important parts about this script is the pragma autonomous_transaction and also the use of execute immediate for the drop table.  This functional also has an additional parameter for determining whether to drop the table or not – allowing some prudent testing.

Remember the first parameter is the singlebyte tablename and the second is the unicode_tablename.

If the third parameter is a Y, and you have permission, the table will be dropped.

Another cool feature is that this information is returned to the query (and therefore spooled).

CREATE OR REPLACE FUNCTION test_unicode_conversion (singlebyte_tablename in VARCHAR, unicode_tablename IN VARCHAR, dropTable IN CHAR)
RETURN VARCHAR
IS
unicode_count integer;
singlebyte_count integer;
 unicode_cursor sys_refcursor; 
 singlebyte_cursor  sys_refcursor;
 pragma autonomous_transaction;
BEGIN
   dbms_output.put_line('Starting...');
  open unicode_cursor for 'select count(1) from ' || unicode_tablename  ;
  fetch unicode_cursor into unicode_count;
  open singlebyte_cursor for 'select count(1) from ' || singlebyte_tablename ;
  fetch singlebyte_cursor into singlebyte_count;
  if unicode_count <> singlebyte_count then
    dbms_output.put_line('cannot drop table, transation not complete');
    return 'Row counts do not match for' || unicode_tablename || ' and ' || singlebyte_tablename ;
  else
    dbms_output.put_line('Drop the temp table, all good');
    if droptable = 'Y' then
      execute immediate 'drop table ' || singlebyte_tablename ;
      return 'Row counts MATCH for ' || unicode_tablename || ' and DROPPED ' || singlebyte_tablename ;
    end if;
    return 'Row counts MATCH for' || unicode_tablename || ' and ' || singlebyte_tablename ;
  end if;
  close unicode_cursor;
  close singlebyte_cursor;
END ;

Called with

select jde.test_unicode_conversion('TESTDTA.F4009_NONUNI', 'TESTDTA.F4009', 'N') from dual ;