Thursday 19 February 2015

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 ;

No comments: