select

'ALTER TABLE ' || TABLE_NAME || ' ADD ( ' || COLUMN_NAME || '_TMP VARCHAR2(' || DATA_LENGTH || ')); ' ||
'UPDATE ' || TABLE_NAME || ' SET ' || COLUMN_NAME || '_TMP = ' || COLUMN_NAME || '; ' ||
'UPDATE ' || TABLE_NAME || ' SET ' || COLUMN_NAME || ' = NULL; '||
'ALTER TABLE ' || TABLE_NAME || ' MODIFY ( ' || COLUMN_NAME || ' VARCHAR2(' || DATA_LENGTH || ')); ' ||
'UPDATE ' || TABLE_NAME || ' SET ' || COLUMN_NAME || ' = ' || COLUMN_NAME || '_TMP; ' ||
'ALTER TABLE ' || TABLE_NAME || ' DROP COLUMN ' || COLUMN_NAME || '_TMP; ' AS SQL

from all_tab_columns
where data_type like 'NVARCHAR%'
and OWNER = 'LLC'
ORDER BY TABLE_NAME;

+ Recent posts