set serveroutput on; /* Insert common schema into csm$dictusers. All other data dictionary schema is already inserted into csm$dictuses when csm$dictuser is created. */ insert into csm$dictusers select distinct u.user#, u.name from sys.user$ u where u.name in ('HR', 'OE', 'SH', 'PM'); /* this function runs a DML statement. It returns how many rows updated */ create or replace function csm$rundml wrapped a000000 1 abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd 8 102 f3 Ku7Of/ePHTQvbCjNgzvJsAv6Wegwg1zQLcusZ3RAkBAYy8vIL1sftvQddpSWNxxhv9vwNYBS UM75HMFscMa79rVhSYCXJ4S4EtATscFCLCz0castOwK8cDLRMJzhjDJZDvrg8HSjPKaXf1mU OLmzKFZp9A0bluiLavPlwo2vsCAuByEwGxtH11HVVHwIG9Yw6WFLcyKyzXTZfDRRwN9ZY7Xe 5X+sUnLYhoI/ggmNZLznejUF / /* This function create a sql statment to update the clob/nvarchar2 for a table The sql statement will be something like: UPDATE USERNAME.TABLENAME SET COL1 := CONVERT(COL1, TOCHAR, FROMCHAR'), COL2:=CONVERT(COL2, TOCHAR, FROMCHAR) WHERE ROWID >=MINROWID AND ROWID 'Y') then return; end if; csm$main(); return; end; / declare svalue varchar2(30); sqlstmt varchar2(1024); tochar varchar2(30); rowcnt number; begin select value into svalue from csm$parameters where name='CSALTER_FINISHED'; if (svalue <> 'TRUE') then dbms_output.put_line('Checking or Converting phase did not finish successfully'); dbms_output.put_line('No database (national) character set will be altered'); dbms_output.put_line('CSALTER finished unsuccessfully.'); return; end if; select value into svalue from csm$parameters where name='SCAN_CHAR'; if (svalue = 'YES') then dbms_output.put_line('Alter the database character set...'); select value into tochar from csm$parameters where name='TO_CHARSET_NAME'; rowcnt := csm$rundml('alter database character set internal_use '||tochar); commit; end if; select value into svalue from csm$parameters where name='SCAN_NCHAR'; if (svalue = 'YES') then dbms_output.put_line('Alter the national database character set...'); select value into tochar from csm$parameters where name='TO_NCHARSET_NAME'; rowcnt := csm$rundml('alter database national character set internal_use '||tochar); commit; end if; dbms_output.put_line('CSALTER operation completed, please restart database'); end; / /* restore csm$dictuser */ delete from csm$dictusers where username in ('PM', 'SH', 'HR', 'OE'); drop function csm$rundml; drop function csm$createsql; drop procedure csm$main;