sqlplus with if conditional statement and server output… simple…

 

This is pretty simple, getting the script / server to return some output to the output of SQLPlus.  I saw a lot of articles on this, but non complete, i.e. – did not have the SET SERVEROUTPUT line, therefore, nothing was being printed.

I’m doing unicode conversions manually and need to drop the temp files once I’ve confirmed that all of the data rows have been converted.  Therefore I get a couple of counts (from the nonuni and singlebyte) versions of the tables.  If the counts are the same, then I drop the “_NONUNI” – simple!

This script is just for reference and does not have the drop command.

SET SERVEROUTPUT ON FORMAT WORD_WRAPPED
declare
unicode_count integer;
singlebyte_count integer;
cursor unicode_cursor is select count(1) from TESTDTA.F0101;
cursor singlebyte_cursor is select count(1) from TESTDTA.F0101_NONUNI;

begin

  open unicode_cursor;
  fetch unicode_cursor into unicode_count;
  open singlebyte_cursor;
  fetch singlebyte_cursor into singlebyte_count;
  if unicode_count <> singlebyte_count then
    dbms_output.put_line('cannot drop table, transation not complete');
  else
    dbms_output.put_line('Drop the temp table, all good');
  end if;
  close unicode_cursor;
  close singlebyte_cursor;

end;
/
quit ;
/