writing and running oracle function that uses a table name as a parameter in a cursor

I need to drop tables that have been verified as unicode converted, I’m no expert at PL/SQL, so I decided to make a function that was generic.

What I decide to do is if the NONUNI = normal row count, my assumption is that everything has worked like a peach, this is going to help me do this.

Note that the drop has not been coded, because I’m a little worried…

 

CREATE OR REPLACE FUNCTION test_unicode_conversion (singlebyte_tablename in VARCHAR, unicode_tablename IN VARCHAR)
RETURN VARCHAR
IS
unicode_count integer;
singlebyte_count integer;
 unicode_cursor sys_refcursor; 
 singlebyte_cursor  sys_refcursor;
BEGIN
  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');
    --drop table PRODDTA.F55B30 ;
    return 'Row counts MATCH for' || unicode_tablename || ' and ' || singlebyte_tablename ;
  end if;
  close unicode_cursor;
  close singlebyte_cursor;
END ;

select jde.test_unicode_conversion('TESTDTA.F0101', 'TESTDTA.F0101') from dual ;

 

And calling this ripper:

select jde.test_unicode_conversion('TESTDTA.F0101', 'TESTDTA.F0101') from dual ;

 

Which returns:

Row counts MATCH forTESTDTA.F0101 and TESTDTA.F0101

That is pretty cool.  Note that there are a number of specifics (syntax is a killer!)