Rem
Rem $Header: utlnchar.sql 19-jul-2004.04:01:08 kquinn Exp $
Rem
Rem utlnchar.sql
Rem
Rem Copyright (c) 2001, 2004, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      utlnchar.sql - The migration script for NCHAR data from 8i to 9i
Rem
Rem    DESCRIPTION
Rem      The migration script for NCHAR data from 8i to 9i 
Rem
Rem    NOTES
Rem      1) use SQLPLUS 
Rem      2) connect AS SYSDBA
Rem
Rem      The migration script is to migrate NCHAR data, including 
Rem      nchar, nvarchar2 and nclob, from 8i and 9i to make NCHAR
Rem      data accessible in 9i.  It should run after database is 
Rem      upgraded from 8i to 9i.
Rem      Once the script runs, what has been done can not be undone.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    kquinn      07/19/04 - 3772100: quote object names 
Rem    arrajara    05/10/01 - Replication nchar/nvarchar2 upgrade temp fix
Rem    yzhu        04/26/01 - Remove redundent values.
Rem    yzhu        04/13/01 - Add one more check for old NCHAR data.
Rem    yzhu        04/06/01 - Created
Rem

prompt
prompt Warning:
prompt The script is to migrate NCHAR data, including nchar
prompt nvarcha2 and nclob, from 8i to 9i.
prompt Once it runs, it can not undo what have been done.
prompt
prompt To run it, 1) use SQLPLUS  and 2) connect AS SYSDBA
prompt

accept confirm prompt "Press ctrl-C, then return to quit or any other key to continue: ";

set serveroutput on size 1000000;

rem-------------------------------------------------------
rem  Create table fixedcs_0000 to hold fixec char sets and
rem  its byte width
rem-------------------------------------------------------

DROP TABLE nrmig0000;
CREATE TABLE nrmig0000 
  (sname VARCHAR2(30), oname VARCHAR2(30), trigflag NUMBER);

drop table fixedcs_0000;
create table fixedcs_0000 (name varchar2(40), length number, csid number);

insert into fixedcs_0000 values('US16TSTFIXED', 2, 1001);
insert into fixedcs_0000 values('JA16EUCFIXED', 2, 1830);
insert into fixedcs_0000 values('JA16SJISFIXED', 2, 1832);
insert into fixedcs_0000 values('JA16DBCSFIXED', 2, 1833);
insert into fixedcs_0000 values('KO16KSC5601FIXED', 2, 1840);
insert into fixedcs_0000 values('KO16DBCSFIXED', 2, 1842);
insert into fixedcs_0000 values('ZHS16CGB231280FIXED', 2, 1850);
insert into fixedcs_0000 values('ZHS16GBKFIXED', 2, 1852);
insert into fixedcs_0000 values('ZHS16DBCSFIXED', 2, 1853);
insert into fixedcs_0000 values('ZHT32EUCFIXED', 2, 1860);
insert into fixedcs_0000 values('ZHT32TRISFIXED', 4, 1863);
insert into fixedcs_0000 values('ZHT16DBCSFIXED', 2, 1864);
insert into fixedcs_0000 values('ZHT16BIG5FIXED', 2, 1865);
insert into fixedcs_0000 values('Al16UTF16', 2, 2000);


rem-------------------------------------------------------
rem  Create  stored procedure new_nchar
rem-------------------------------------------------------

create or replace procedure new_nchar (
    table_obj#        in number,
    byte_width        in number
) is
  -- Cursor of colunm name, type and length for NCHAR data from col$
  cursor c is
    select name, type#, length from col$
        where obj# = table_obj# and charsetform=2 and type# in (1, 96, 112)
                     and bitand(property, 8388608) = 0;

  table_name    varchar2(30);     -- The table name for the given table_obj#
  length        number;           -- The length in char for new nchar data
  atb_stmt      varchar2(120);
  owner_name    varchar2(30);
  owner_id      number;
  ncsetnm       varchar(40);
begin
  -- Get the table_name and owner_name for the given table obj#
  select name into table_name from obj$ where obj#=table_obj#;
  select owner# into owner_id from obj$ where obj#=table_obj#;
  select username into owner_name from all_users where user_id = owner_id;

  -- Get national character set name
  select value into ncsetnm from nls_database_parameters
      where parameter = 'NLS_NCHAR_CHARACTERSET';

  -- Scan all the columns in the table for nchar, nvarchar2 and nclob
  for c_rec in c loop

    -- The common part of alter table modify command
    atb_stmt :='alter table "'||owner_name||'"."'||table_name||'" modify "';
    atb_stmt := atb_stmt || c_rec.name || '"';

    length := c_rec.length/byte_width;

    -- The nchar columns
    if c_rec.type# = 96  then

      if (length > 2000 and ncsetnm = 'UTF8') then
        dbms_output.put_line('Warning: Length of ' || c_rec.name ||
                             ' exceed 2000');
        length := 2000;
      end if;

      if (length > 1000 and ncsetnm = 'AL16UTF16') then
        dbms_output.put_line('Warning: Length of ' || c_rec.name ||
                             ' exceed 1000');
        length := 1000;
      end if;

      atb_stmt := atb_stmt || ' nchar(' || length || ')';
    end if;

    -- The nvarchar2 columns
    if c_rec.type# = 1  then

      if (length > 4000 and ncsetnm = 'UTF8') then
        dbms_output.put_line('Warning: Length of ' || c_rec.name ||
                             ' exceed 4000');
        length := 4000;
      end if;

      if (length > 2000 and ncsetnm = 'AL16UTF16') then
        dbms_output.put_line('Warning: Length of ' || c_rec.name ||
                             ' exceed 2000');
        length := 2000;
      end if;

      atb_stmt := atb_stmt || ' nvarchar2(' || length || ')';
    end if;

    -- The nclob columns
    if c_rec.type# = 112  then
      atb_stmt := atb_stmt || ' nclob';
    end if;

    -- execute the alter table modify command
    execute immediate atb_stmt;
  end loop;

end new_nchar;
/

rem-------------------------------------------------------
rem  Create  stored function get_byte_width
rem-------------------------------------------------------

create or replace function get_byte_width
return number is
  -- The cursor to get old charsetid for nchar data
  cursor c_ocsid is
    select charsetid from col$
      where charsetform = 2 and bitand(property, 8388608) = 0 and
                  obj# in (select obj# from obj$
                   where type# = 2 and owner# not in 
                   (select user_id from all_users
                    where username='SYS' or username='SYSTEM'));

  ncsid         number := 0;
  byte_width    number := 1;
begin
  -- Get byte_width of old ncharset
  open c_ocsid;
  fetch c_ocsid into  ncsid;
  close c_ocsid;

  select length into byte_width from fixedcs_0000
        where ncsid = fixedcs_0000.csid ;
  return(byte_width);

exception
  when NO_DATA_FOUND then
    return (1);

end get_byte_width;
/

Rem--------------------------------------------------------
Rem Disable internal triggers 
Rem--------------------------------------------------------

CREATE OR REPLACE PROCEDURE disable_internal_triggers0000 IS
BEGIN
  FOR cur IN (SELECT sname, oname, trigflag FROM nrmig0000) LOOP

    dbms_output.put_line('disable_internal_triggers0000: sname :' 
                          || cur.sname || '  oname :' || cur.oname
                          || '  trigflag:' || cur.trigflag);
    -- replication trigger
    IF bitand(cur.trigflag,1)=1 THEN
      sys.dbms_internal_trigger.destroy(cur.sname, cur.oname, 
                                      dbms_internal_trigger.trigger_type_rep);
    END IF;

    -- materialized view log 
    IF bitand(cur.trigflag,2)=2 THEN
      sys.dbms_internal_trigger.destroy(cur.sname, cur.oname, 
                                      dbms_internal_trigger.trigger_type_log);
    END IF;

    -- updatable materialized view
    IF bitand(cur.trigflag,4)=4 THEN
      sys.dbms_internal_trigger.destroy(cur.sname, cur.oname, 
                                      dbms_internal_trigger.trigger_type_snp);
    END IF;

    -- sync cdc trigger
    IF bitand(cur.trigflag,16)=16 THEN
      sys.dbms_internal_trigger.destroy(cur.sname, cur.oname, 
                                      dbms_internal_trigger.trigger_type_scdc);
    END IF;
   
  END LOOP;
END disable_internal_triggers0000;
/

Rem--------------------------------------------------------
Rem Enable internal triggers 
Rem--------------------------------------------------------

CREATE OR REPLACE PROCEDURE enable_internal_triggers0000 IS
BEGIN
  FOR cur IN (SELECT sname, oname, trigflag FROM nrmig0000) LOOP 

    dbms_output.put_line('enable_internal_triggers0000: sname :' 
                           || cur.sname || '  oname :' || cur.oname
                           || '  trigflag:' || cur.trigflag);

    -- replication trigger
    IF bitand(cur.trigflag,1)=1 THEN
      sys.dbms_internal_trigger.make(cur.sname, cur.oname, 
                                      dbms_internal_trigger.trigger_type_rep);
    END IF;

    -- materialized view log 
    IF bitand(cur.trigflag,2)=2 THEN
      sys.dbms_internal_trigger.make(cur.sname, cur.oname, 
                                      dbms_internal_trigger.trigger_type_log);
    END IF;

    -- updatable materialized view
    IF bitand(cur.trigflag,4)=4 THEN
      sys.dbms_internal_trigger.make(cur.sname, cur.oname, 
                                      dbms_internal_trigger.trigger_type_snp);
    END IF;

    -- sync cdc trigger
    IF bitand(cur.trigflag,16)=16 THEN
      sys.dbms_internal_trigger.make(cur.sname, cur.oname, 
                                      dbms_internal_trigger.trigger_type_scdc);
    END IF;
  END LOOP;
END enable_internal_triggers0000;
/
   

rem--------------------------------------------------------
rem Migrate old NCHAR data to new NCHAR data
rem--------------------------------------------------------

declare
  -- The cursor to get each user table obj#
  cursor c is
    select obj# from obj$
        where type# = 2 and owner# not in ( select user_id from all_users
                    where username='SYS' or username='SYSTEM');

  byte_width    number;
  obj#          number;
begin
  -- Get byte_width
  byte_width := get_byte_width();

  -- Get all the replicated tables with nchar/nvarchar2 column
  INSERT INTO nrmig0000 (sname, oname, trigflag)
    (SELECT trim(u.name) sname, trim(o.name) oname, t.trigflag
      FROM sys.tab$ t, sys.obj$ o, sys.user$ u
      WHERE t.obj# = o.obj#  AND
        t.trigflag > 0   AND
        u.user# = o.owner# AND
        t.obj# IN (SELECT c.obj# FROM col$ c
                     WHERE charsetform=2 and c.type# in (1, 96, 112)));

  BEGIN 
    disable_internal_triggers0000;  
  EXCEPTION WHEN OTHERS THEN
    dbms_output.put_line('disable_internal_triggers0000 error:'|| sqlcode);
  END;

  -- For each user table, loop to check NCHAR data
  for c_rec in c loop
    obj# := c_rec.obj#;
    new_nchar(obj#, byte_width);
  end loop;

  BEGIN
    enable_internal_triggers0000;  
  EXCEPTION WHEN OTHERS THEN 
    dbms_output.put_line('enable_internal_triggers0000 error:'|| sqlcode);
  END;

end;
/

drop table nrmig0000;
drop procedure  enable_internal_triggers0000;
drop procedure disable_internal_triggers0000;

drop table fixedcs_0000;
drop procedure new_nchar;
drop function get_byte_width;
set serveroutput off;