Convert Font

CREATE OR REPLACE PACKAGE   CONVERTOR AS
   /*
   *  VNI-to-Unicode conversion
   *
   *  The process consists of 3 parts:
   *
   *  1) converts single-byte (ASCII) characters except O+ and U+ because they are also
   *     used as base in some VNI double-byte characters
   *  2) converts VNI double-byte characters
   *  3) converts O+ and U+
   *
   * @author     Quan Nguyen
   * @version    1.2, 23 October 05
   */
  FUNCTION  VNI_TO_UNI (p_vni_string varchar2) RETURN VARCHAR2;
  FUNCTION  OLAS_TO_UNI (p_olas_string varchar2) RETURN VARCHAR2;
  FUNCTION  OLAS1258_TO_UNI (p_olas1258_string varchar2) RETURN VARCHAR2;
  FUNCTION  UNI_TO_VNI (p_uni_string varchar2) RETURN VARCHAR2;
END CONVERTOR;

==============================================================
CREATE OR REPLACE PACKAGE BODY  CONVERTOR AS

  TYPE MAPPING_T IS TABLE OF VARCHAR2(10) INDEX BY VARCHAR2(10);
  --VNI_TO_UNI
  FUNCTION  VNI_TO_UNI (p_vni_string varchar2) RETURN VARCHAR2 AS
/*
 *  VNI-to-Unicode conversion
 *
 *  The process consists of 3 parts:
 *
 *  1) converts single-byte (ASCII) characters except O+ and U+ because they are also
 *     used as base in some VNI double-byte characters
 *  2) converts VNI double-byte characters
 *  3) converts O+ and U+
 *
 * @author     Quan Nguyen
 * @Modifier   Nguyen Tich Thanh
 * @version    1.2, 16/05/2011
 */
  vni_mapping MAPPING_T;
  --v_count PLS_INTEGER := 0;
  v_result VARCHAR2(512);
  v_pattern VARCHAR2(10);
  v_str  VARCHAR2(512);
  v_startIndex PLS_INTEGER;
  v_foundIndex PLS_INTEGER;
 
BEGIN
  -- Initialization for vni_mapping
  vni_mapping('O\00C2') := '\00C6';
  vni_mapping('o\00E2') := '\00E6';
  vni_mapping('y\00F5') := '\1EF9';
  vni_mapping('Y\00D5') := '\1EF8';
  vni_mapping('y\00FB') := '\1EF7';
  vni_mapping('Y\00DB') := '\1EF6';
  vni_mapping('y\00F8') := '\1EF3';
  vni_mapping('Y\00D8') := '\1EF2';
  vni_mapping('\00F6\00EF') := '\1EF1';
  vni_mapping('\00D6\00CF') := '\1EF0';
  vni_mapping('\00F6\00F5') := '\1EEF';
  vni_mapping('\00D6\00D5') := '\1EEE';
  vni_mapping('\00F6\00FB') := '\1EED';
  vni_mapping('\00D6\00DB') := '\1EEC';
  vni_mapping('\00F6\00F8') := '\1EEB';
  vni_mapping('\00D6\00D8') := '\1EEA';
  vni_mapping('\00F6\00F9') := '\1EE9';
  vni_mapping('\00D6\00D9') := '\1EE8';
  vni_mapping('u\00FB') := '\1EE7';
  vni_mapping('U\00DB') := '\1EE6';
  vni_mapping('u\00EF') := '\1EE5';
  vni_mapping('U\00CF') := '\1EE4';
  vni_mapping('\00F4\00EF') := '\1EE3';
  vni_mapping('\00D4\00CF') := '\1EE2';
  vni_mapping('\00F4\00F5') := '\1EE1';
  vni_mapping('\00D4\00D5') := '\1EE0';
  vni_mapping('\00F4\00FB') := '\1EDF';
  vni_mapping('\00D4\00DB') := '\1EDE';
  vni_mapping('\00F4\00F8') := '\1EDD';
  vni_mapping('\00D4\00D8') := '\1EDC';
  vni_mapping('\00F4\00F9') := '\1EDB';
  vni_mapping('\00D4\00D9') := '\1EDA';
  vni_mapping('o\00E4') := '\1ED9';
  vni_mapping('O\00C4') := '\1ED8';
  vni_mapping('o\00E3') := '\1ED7';
  vni_mapping('O\00C3') := '\1ED6';
  vni_mapping('o\00E5') := '\1ED5';
  vni_mapping('O\00C5') := '\1ED4';
  vni_mapping('o\00E0') := '\1ED3';
  vni_mapping('O\00C0') := '\1ED2';
  vni_mapping('o\00E1') := '\1ED1';
  vni_mapping('O\00C1') := '\1ED0';
  vni_mapping('o\00FB') := '\1ECF';
  vni_mapping('O\00DB') := '\1ECE';
  vni_mapping('o\00EF') := '\1ECD';
  vni_mapping('O\00CF') := '\1ECC';
  vni_mapping('e\00E4') := '\1EC7';
  vni_mapping('E\00C4') := '\1EC6';
  vni_mapping('e\00E3') := '\1EC5';
  vni_mapping('E\00C3') := '\1EC4';
  vni_mapping('e\00E5') := '\1EC3';
  vni_mapping('E\00C5') := '\1EC2';
  vni_mapping('e\00E0') := '\1EC1';
  vni_mapping('E\00C0') := '\1EC0';
  vni_mapping('e\00E1') := '\1EBF';
  vni_mapping('E\00C1') := '\1EBE';
  vni_mapping('e\00F5') := '\1EBD';
  vni_mapping('E\00D5') := '\1EBC';
  vni_mapping('e\00FB') := '\1EBB';
  vni_mapping('E\00DB') := '\1EBA';
  vni_mapping('e\00EF') := '\1EB9';
  vni_mapping('E\00CF') := '\1EB8';
  vni_mapping('a\00EB') := '\1EB7';
  vni_mapping('A\00CB') := '\1EB6';
  vni_mapping('a\00FC') := '\1EB5';
  vni_mapping('A\00DC') := '\1EB4';
  vni_mapping('a\00FA') := '\1EB3';
  vni_mapping('A\00DA') := '\1EB2';
  vni_mapping('a\00E8') := '\1EB1';
  vni_mapping('A\00C8') := '\1EB0';
  vni_mapping('a\00E9') := '\1EAF';
  vni_mapping('A\00C9') := '\1EAE';
  vni_mapping('a\00E4') := '\1EAD';
  vni_mapping('A\00C4') := '\1EAC';
  vni_mapping('a\00E3') := '\1EAB';
  vni_mapping('A\00C3') := '\1EAA';
  vni_mapping('a\00E5') := '\1EA9';
  vni_mapping('A\00C5') := '\1EA8';
  vni_mapping('a\00E0') := '\1EA7';
  vni_mapping('A\00C0') := '\1EA6';
  vni_mapping('a\00E1') := '\1EA5';
  vni_mapping('A\00C1') := '\1EA4';
  vni_mapping('a\00FB') := '\1EA3';
  vni_mapping('A\00DB') := '\1EA2';
  vni_mapping('a\00EF') := '\1EA1';
  vni_mapping('A\00CF') := '\1EA0';
  vni_mapping('u\00F5') := '\0169';
  vni_mapping('U\00D5') := '\0168';
  vni_mapping('a\00EA') := '\0103';
  vni_mapping('A\00CA') := '\0102';
  vni_mapping('y\00F9') := '\00FD';
  vni_mapping('u\00F9') := '\00FA';
  vni_mapping('u\00F8') := '\00F9';
  vni_mapping('o\00F5') := '\00F5';
  vni_mapping('o\00F9') := '\00F3';
  vni_mapping('o\00F8') := '\00F2';
  vni_mapping('e\00E2') := '\00EA';
  vni_mapping('e\00F9') := '\00E9';
  vni_mapping('e\00F8') := '\00E8';
  vni_mapping('a\00F5') := '\00E3';
  vni_mapping('a\00E2') := '\00E2';
  vni_mapping('a\00F9') := '\00E1';
  vni_mapping('a\00F8') := '\00E0';
  vni_mapping('Y\00D9') := '\00DD';
  vni_mapping('U\00D9') := '\00DA';
  vni_mapping('U\00D8') := '\00D9';
  vni_mapping('O\00D5') := '\00D5';
  vni_mapping('O\00D9') := '\00D3';
  vni_mapping('O\00D8') := '\00D2';
  vni_mapping('E\00C2') := '\00CA';
  vni_mapping('E\00D9') := '\00C9';
  vni_mapping('E\00D8') := '\00C8';
  vni_mapping('A\00D5') := '\00C3';
  vni_mapping('A\00C2') := '\00C2';
  vni_mapping('A\00D9') := '\00C1';
  vni_mapping('A\00D8') := '\00C0';

  -- Processing
  -- Part 1
  v_result := asciistr(p_vni_string);
  v_result := replace(v_result, '\00D1', '\0110');    -- DD
  v_result := replace(v_result, '\00F1', '\0111');    -- dd
  v_result := replace(v_result, '\00D3', '\0128');    -- I~
  v_result := replace(v_result, '\00F3', '\0129');    -- i~
  v_result := replace(v_result, '\00D2', '\1ECA');    -- I.
  v_result := replace(v_result, '\00F2', '\1ECB');    -- i.
  v_result := replace(v_result, '\00C6', '\1EC8');    -- I?
  v_result := replace(v_result, '\00E6', '\1EC9');    -- i?
  v_result := replace(v_result, '\00CE', '\1EF4');    -- Y.
  v_result := replace(v_result, '\00EE', '\1EF5');    -- y.

  -- Part 2
  -- Transform "O\u00C2" -> "\u00C6" to later transform back to "\u00D4" in Part 3
  --v_count := REGEXP_COUNT(v_result, '\\', 1, 'c');
  v_startIndex := 1;
  v_foundIndex := 0;
  v_pattern := '';
  v_str := v_result;
  v_result := '';
  v_foundIndex := INSTR(v_str, '\', v_startIndex);
  while ( v_foundIndex > 0)            
  loop
      v_pattern := substr(v_str, v_foundIndex, 5);
      case
        when v_pattern = '\00D4' then
          v_pattern := substr(v_str, v_foundIndex, 10);
        when v_pattern = '\00D6' then
          v_pattern := substr(v_str, v_foundIndex, 10);
        when v_pattern = '\00F4' then
          v_pattern := substr(v_str, v_foundIndex, 10);
        when v_pattern = '\00F6' then
          v_pattern := substr(v_str, v_foundIndex, 10);
        else
          v_pattern := substr(v_str, v_foundIndex - 1, 6);
      end case;
     
    if vni_mapping.EXISTS(v_pattern) then    
      if length(v_pattern) = 10 then
        v_result := v_result || substr(v_str, v_startIndex, v_foundIndex - v_startIndex);
        v_result := v_result || vni_mapping(v_pattern);
        v_startIndex := v_foundIndex + 10;    
      else
        v_result := v_result || substr(v_str, v_startIndex, v_foundIndex - 1 - v_startIndex);
        v_result := v_result || vni_mapping(v_pattern);
        v_startIndex := v_foundIndex - 1 + 6;    
      end if;
    else
      v_pattern := substr(v_str, v_foundIndex, 5);
      v_result := v_result || substr(v_str, v_startIndex, v_foundIndex - v_startIndex);
      v_result := v_result || v_pattern;
      v_startIndex := v_foundIndex + 5;              
    end if;
     v_foundIndex := INSTR(v_str, '\', v_startIndex);
  end loop;
  v_str := v_result || substr(v_str,v_startIndex);
  v_result := v_str;
 
  -- Part 3
  v_result := replace(v_result, '\00D4', '\01A0');    -- O+
  v_result := replace(v_result, '\00F4', '\01A1');    -- o+
  v_result := replace(v_result, '\00D6', '\01AF');    -- U+
  v_result := replace(v_result, '\00F6', '\01B0');    -- u+
  v_result := replace(v_result, '\00C6', '\00D4');    -- O^
  v_result := replace(v_result, '\00E6', '\00F4');    -- o^
  RETURN unistr(v_result);
END VNI_TO_UNI;


-- OLAS1252_TO_UNI
FUNCTION  OLAS_TO_UNI (p_olas_string varchar2) RETURN VARCHAR2 AS
/*
 *  OLAS codepage 1252 to Unicode conversion
 *
 *  The process consists of 3 parts:
 *
 *  1) process which patterns have 6 chars
 *  2) and then, process which patterns have 10 chars
 *  3) process which patterns have 5 chars
 *
 * @author     Nguyen Tich Thanh
 * @version    1.0, 16/05/2011
 */
  olas_mapping MAPPING_T;
  v_result VARCHAR2(512);
  v_pattern VARCHAR2(10);
  v_str  VARCHAR2(512);
  v_startIndex PLS_INTEGER;
  v_foundIndex PLS_INTEGER;
BEGIN
  -- Initialization for olas_mapping
  olas_mapping('A\00DD') := '\1EA0'; -- Ạ
  olas_mapping('A\00DE') := '\00C1'; -- Á
  olas_mapping('A\00EC') := '\00C3'; -- Ã
  olas_mapping('A\00FD') := '\1EA2'; -- Ả
  olas_mapping('A\00FE') := '\00C0'; -- À
  olas_mapping('E\00DD') := '\1EB8'; -- Ẹ
  olas_mapping('E\00DE') := '\00C9'; -- É
  olas_mapping('E\00EC') := '\1EBC'; -- Ẽ
  olas_mapping('E\00FD') := '\1EBA'; -- Ẻ
  olas_mapping('E\00FE') := '\00C8'; -- È
  olas_mapping('I') := 'I'; -- I
  olas_mapping('I\00DD') := '\1ECA'; -- Ị
  olas_mapping('I\00DE') := '\00CD'; -- Í
  olas_mapping('I\00EC') := '\0128'; -- Ĩ
  olas_mapping('I\00FD') := '\1EC8'; -- Ỉ
  olas_mapping('I\00FE') := '\00CC'; -- Ì
  olas_mapping('O\00DD') := '\1ECC'; -- Ọ
  olas_mapping('O\00DE') := '\00D3'; -- Ó
  olas_mapping('O\00EC') := '\00D5'; -- Õ
  olas_mapping('O\00FD') := '\1ECE'; -- Ỏ
  olas_mapping('O\00FE') := '\00D2'; -- Ò
  olas_mapping('U\00DD') := '\1EE4'; -- Ụ
  olas_mapping('U\00DE') := '\00DA'; -- Ú
  olas_mapping('U\00EC') := '\0168'; -- Ũ
  olas_mapping('U\00FD') := '\1EE6'; -- Ủ
  olas_mapping('U\00FE') := '\00D9'; -- Ù
  olas_mapping('Y\00DD') := '\1EF4'; -- Ỵ
  olas_mapping('Y\00DE') := '\00DD'; -- Ý
  olas_mapping('Y\00EC') := '\1EF8'; -- Ỹ
  olas_mapping('Y\00FD') := '\1EF6'; -- Ỷ
  olas_mapping('Y\00FE') := '\1EF2'; -- Ỳ
  olas_mapping('\00C2\00DD') := '\1EAC'; -- Ậ
  olas_mapping('\00C2\00DE') := '\1EA4'; -- Ấ
  olas_mapping('\00C2\00EC') := '\1EAA'; -- Ẫ
  olas_mapping('\00C2\00FD') := '\1EA8'; -- Ẩ
  olas_mapping('\00C2\00FE') := '\1EA6'; -- Ầ
  olas_mapping('\00C3\00DE') := '\1EAE'; -- Ắ
  olas_mapping('\00C3\00EC') := '\1EB4'; -- Ẵ
  olas_mapping('\00C3\00FD') := '\1EB2'; -- Ẳ
  olas_mapping('\00C3\00FE') := '\1EB0'; -- Ằ
  olas_mapping('\00CA\00DD') := '\1EC6'; -- Ệ
  olas_mapping('\00CA\00DE') := '\1EBE'; -- Ế
  olas_mapping('\00CA\00EC') := '\1EC4'; -- Ễ
  olas_mapping('\00CA\00FD') := '\1EC2'; -- Ể
  olas_mapping('\00CA\00FE') := '\1EC0'; -- Ề
  olas_mapping('\00D2\00DD') := '\1EF0'; -- Ự
  olas_mapping('\00D2\00DE') := '\1EE8'; -- Ứ
  olas_mapping('\00D2\00EC') := '\1EEE'; -- Ữ
  olas_mapping('\00D2\00FD') := '\1EEC'; -- Ử
  olas_mapping('\00D2\00FE') := '\1EEA'; -- Ừ
  olas_mapping('\00D4\00DD') := '\1ED8'; -- Ộ
  olas_mapping('\00D4\00DE') := '\1ED0'; -- Ố
  olas_mapping('\00D4\00EC') := '\1ED6'; -- Ỗ
  olas_mapping('\00D4\00FD') := '\1ED4'; -- Ổ
  olas_mapping('\00D4\00FE') := '\1ED2'; -- Ồ
  olas_mapping('\00D5\00DD') := '\1EE2'; -- Ợ
  olas_mapping('\00D5\00DE') := '\1EDA'; -- Ớ
  olas_mapping('\00D5\00EC') := '\1EE0'; -- Ỡ
  olas_mapping('\00D5\00FD') := '\1EDE'; -- Ở
  olas_mapping('\00D5\00FE') := '\1EDC'; -- Ờ
  olas_mapping('\00E2\00DD') := '\1EAD'; -- ậ
  olas_mapping('\00E2\00DE') := '\1EA5'; -- ấ
  olas_mapping('\00E2\00EC') := '\1EAB'; -- ẫ
  olas_mapping('\00E2\00FD') := '\1EA9'; -- ẩ
  olas_mapping('\00E2\00FE') := '\1EA7'; -- ầ
  olas_mapping('\00E3\00DD') := '\1EB7'; -- ặ
  olas_mapping('\00E3\00DE') := '\1EAF'; -- ắ
  olas_mapping('\00E3\00EC') := '\1EB5'; -- ẵ
  olas_mapping('\00E3\00FD') := '\1EB3'; -- ẳ
  olas_mapping('\00E3\00FE') := '\1EB1'; -- ằ
  olas_mapping('\00EA\00DD') := '\1EC7'; -- ệ
  olas_mapping('\00EA\00DE') := '\1EBF'; -- ế
  olas_mapping('\00EA\00EC') := '\1EC5'; -- ễ
  olas_mapping('\00EA\00FD') := '\1EC3'; -- ể
  olas_mapping('\00EA\00FE') := '\1EC1'; -- ề
  olas_mapping('\00F2\00DD') := '\1EF1'; -- ự
  olas_mapping('\00F2\00DE') := '\1EE9'; -- ứ
  olas_mapping('\00F2\00EC') := '\1EEF'; -- ữ
  olas_mapping('\00F2\00FD') := '\1EED'; -- ử
  olas_mapping('\00F2\00FE') := '\1EEB'; -- ừ
  olas_mapping('\00F4\00DD') := '\1ED9'; -- ộ
  olas_mapping('\00F4\00DE') := '\1ED1'; -- ố
  olas_mapping('\00F4\00EC') := '\1ED7'; -- ỗ
  olas_mapping('\00F4\00FD') := '\1ED5'; -- ổ
  olas_mapping('\00F4\00FE') := '\1ED3'; -- ồ
  olas_mapping('\00F5\00DD') := '\1EE3'; -- ợ
  olas_mapping('\00F5\00DE') := '\1EDB'; -- ớ
  olas_mapping('\00F5\00EC') := '\1EE1'; -- ỡ
  olas_mapping('\00F5\00FD') := '\1EDF'; -- ở
  olas_mapping('\00F5\00FE') := '\1EDD'; -- ờ
  olas_mapping('a\00DD') := '\1EA1'; -- ạ
  olas_mapping('a\00DE') := '\00E1'; -- á
  olas_mapping('a\00EC') := '\00E3'; -- ã
  olas_mapping('a\00FD') := '\1EA3'; -- ả
  olas_mapping('a\00FE') := '\00E0'; -- à
  olas_mapping('e\00DD') := '\1EB9'; -- ẹ
  olas_mapping('e\00DE') := '\00E9'; -- é
  olas_mapping('e\00EC') := '\1EBD'; -- ẽ
  olas_mapping('e\00FD') := '\1EBB'; -- ẻ
  olas_mapping('e\00FE') := '\00E8'; -- è
  olas_mapping('i\00DD') := '\1ECB'; -- ị
  olas_mapping('i\00DE') := '\00ED'; -- í
  olas_mapping('i\00EC') := '\0129'; -- ĩ
  olas_mapping('i\00FD') := '\1EC9'; -- ỉ
  olas_mapping('i\00FE') := '\00EC'; -- ì
  olas_mapping('o\00DD') := '\1ECD'; -- ọ
  olas_mapping('o\00DE') := '\00F3'; -- ó
  olas_mapping('o\00EC') := '\00F5'; -- õ
  olas_mapping('o\00FD') := '\1ECF'; -- ỏ
  olas_mapping('o\00FE') := '\00F2'; -- ò
  olas_mapping('u\00DD') := '\1EE5'; -- ụ
  olas_mapping('u\00DE') := '\00FA'; -- ú
  olas_mapping('u\00EC') := '\0169'; -- ũ
  olas_mapping('u\00FD') := '\1EE7'; -- ủ
  olas_mapping('u\00FE') := '\00F9'; -- ù
  olas_mapping('y\00DD') := '\1EF5'; -- ỵ
  olas_mapping('y\00DE') := '\00FD'; -- ý
  olas_mapping('y\00EC') := '\1EF9'; -- ỹ
  olas_mapping('y\00FD') := '\1EF7'; -- ỷ
  olas_mapping('y\00FE') := '\1EF3'; -- ỳ

  -- Processing
  v_result := asciistr(p_olas_string);
  v_startIndex := 1;
  v_foundIndex := 0;
  v_pattern := '';
  v_str := v_result;
  v_result := '';
  v_foundIndex := INSTR(v_str, '\', v_startIndex);
  while ( v_foundIndex > 0)            
  loop
      -- xy ly pattern = 6 chars
      v_pattern := substr(v_str, v_foundIndex - 1, 6);
      if not olas_mapping.EXISTS(v_pattern) then
          -- xy ly pattern = 10 chars
          v_pattern := substr(v_str, v_foundIndex, 10);
      end if;    
    if olas_mapping.EXISTS(v_pattern) then    
      if length(v_pattern) = 10 then
        -- xy ly pattern = 10 chars
        v_result := v_result || substr(v_str, v_startIndex, v_foundIndex - v_startIndex);
        v_result := v_result || olas_mapping(v_pattern);
        v_startIndex := v_foundIndex + 10;    
      else
        -- xy ly pattern = 6 chars
        v_result := v_result || substr(v_str, v_startIndex, v_foundIndex - 1 - v_startIndex);
        v_result := v_result || olas_mapping(v_pattern);
        v_startIndex := v_foundIndex - 1 + 6;    
      end if;
    else
      -- xy ly pattern = 5 chars
      v_pattern := substr(v_str, v_foundIndex, 5);
      v_result := v_result || substr(v_str, v_startIndex, v_foundIndex - v_startIndex);
      v_result := v_result || v_pattern;
      case
        when v_pattern = '\00E3' then
          v_result := replace(v_result, v_pattern, '\0103'); --ã
        when v_pattern = '\00C3' then
          v_result := replace(v_result, v_pattern, '\0102'); --Ã
        when v_pattern = '\00E2' then
          v_result := replace(v_result, v_pattern, '\00E2'); --â
        when v_pattern = '\00C2' then
          v_result := replace(v_result, v_pattern, '\00C2'); --Â
        when v_pattern = '\00EA' then
          v_result := replace(v_result, v_pattern, '\00EA'); --ê
        when v_pattern = '\00CA' then
          v_result := replace(v_result, v_pattern, '\00CA'); --Ê
        when v_pattern = '\00F4' then
          v_result := replace(v_result, v_pattern, '\00F4'); --ô
        when v_pattern = '\00D4' then
          v_result := replace(v_result, v_pattern, '\00D4'); --Ô
        when v_pattern = '\00F2' then
          v_result := replace(v_result, v_pattern, '\01B0'); --ò
        when v_pattern = '\00D2' then
          v_result := replace(v_result, v_pattern, '\01AF'); --Ò
        when v_pattern = '\00F0' then
          v_result := replace(v_result, v_pattern, '\0111'); --ð
        when v_pattern = '\00D0' then
          v_result := replace(v_result, v_pattern, '\0110'); --Ð
        when v_pattern = '\00D5' then
          v_result := replace(v_result, v_pattern, '\01A0'); --Õ
        when v_pattern = '\00F5' then
          v_result := replace(v_result, v_pattern, '\01A1'); --õ
        else
          v_result := v_result;
      end case;
      v_startIndex := v_foundIndex + 5;              
    end if;
     v_foundIndex := INSTR(v_str, '\', v_startIndex);
  end loop;
  v_str := v_result || substr(v_str,v_startIndex);
  v_result := v_str;
  RETURN unistr(v_result);
END OLAS_TO_UNI;

-- OLAS 1258 TO UNI
FUNCTION  OLAS1258_TO_UNI (p_olas1258_string varchar2) RETURN VARCHAR2 AS
/*
 *  OLAS codepage 1258 to Unicode conversion
 *
 *  The process consists of 3 parts:
 *
 *  1) process which patterns have 6 chars
 *  2) and then, process which patterns have 10 chars
 *  3) process which patterns have 5 chars
 *
 * @author     Nguyen Tich Thanh
 * @version    1.0, 16/05/2011
 */
  olas1258_mapping MAPPING_T;
  v_result VARCHAR2(512);
  v_pattern VARCHAR2(10);
  v_str  VARCHAR2(512);
  v_startIndex PLS_INTEGER;
  v_foundIndex PLS_INTEGER;
BEGIN
  -- Initialization for olas1258_mapping
  olas1258_mapping('a\00EC') := '\00E1'; --aì
  olas1258_mapping('a\00CC') := '\00E0'; --aÌ
  olas1258_mapping('a\00D2') := '\1EA3'; --aÒ
  olas1258_mapping('a\00DE') := '\00E3'; --aÞ
  olas1258_mapping('a\00F2') := '\1EA1'; --aò
  olas1258_mapping('A\00EC') := '\00C1'; --Aì
  olas1258_mapping('A\00CC') := '\00C0'; --AÌ
  olas1258_mapping('A\00D2') := '\1EA2'; --AÒ
  olas1258_mapping('A\00DE') := '\00C3'; --AÞ
  olas1258_mapping('A\00F2') := '\1EA0'; --Aò
  olas1258_mapping('\00E3\00EC') := '\1EAF'; --ãì
  olas1258_mapping('\00E3\00CC') := '\1EB1'; --ãÌ
  olas1258_mapping('\00E3\00D2') := '\1EB3'; --ãÒ
  olas1258_mapping('\00E3\00DE') := '\1EB5'; --ãÞ
  olas1258_mapping('\00E3\00F2') := '\1EB7'; --ãò
  olas1258_mapping('\00C3\00EC') := '\1EAE'; --Ãì
  olas1258_mapping('\00C3\00CC') := '\1EB0'; --ÃÌ
  olas1258_mapping('\00C3\00D2') := '\1EB2'; --ÃÒ
  olas1258_mapping('\00C3\00DE') := '\1EB4'; --ÃÞ
  olas1258_mapping('\00C3\00F2') := '\1EA0'; --Ãò
  olas1258_mapping('\00E2\00EC') := '\1EA5'; --âì
  olas1258_mapping('\00E2\00CC') := '\1EA7'; --âÌ
  olas1258_mapping('\00E2\00D2') := '\1EA9'; --âÒ
  olas1258_mapping('\00E2\00DE') := '\1EAB'; --âÞ
  olas1258_mapping('\00E2\00F2') := '\1EAD'; --âò
  olas1258_mapping('\00C2\00EC') := '\1EA4'; --Âì
  olas1258_mapping('\00C2\00CC') := '\1EA6'; --ÂÌ
  olas1258_mapping('\00C2\00D2') := '\1EA8'; --ÂÒ
  olas1258_mapping('\00C2\00DE') := '\1EAA'; --ÂÞ
  olas1258_mapping('\00C2\00F2') := '\1EAC'; --Âò
  olas1258_mapping('e\00EC') := '\00E9'; --eì
  olas1258_mapping('e\00CC') := '\00E8'; --eÌ
  olas1258_mapping('e\00D2') := '\1EBB'; --eÒ
  olas1258_mapping('e\00DE') := '\1EBD'; --eÞ
  olas1258_mapping('e\00F2') := '\1EB9'; --eò
  olas1258_mapping('E\00EC') := '\00C9'; --Eì
  olas1258_mapping('E\00CC') := '\00C8'; --EÌ
  olas1258_mapping('E\00D2') := '\1EBA'; --EÒ
  olas1258_mapping('E\00DE') := '\1EBC'; --EÞ
  olas1258_mapping('E\00F2') := '\1EB8'; --Eò
  olas1258_mapping('\00EA\00EC') := '\1EBF'; --êì
  olas1258_mapping('\00EA\00CC') := '\1EC1'; --êÌ
  olas1258_mapping('\00EA\00D2') := '\1EC3'; --êÒ
  olas1258_mapping('\00EA\00DE') := '\1EC5'; --êÞ
  olas1258_mapping('\00EA\00F2') := '\1EC7'; --êò
  olas1258_mapping('\00CA\00EC') := '\1EBE'; --Êì
  olas1258_mapping('\00CA\00CC') := '\1EC0'; --ÊÌ
  olas1258_mapping('\00CA\00D2') := '\1EC2'; --ÊÒ
  olas1258_mapping('\00CA\00DE') := '\1EC4'; --ÊÞ
  olas1258_mapping('\00CA\00F2') := '\1EC6'; --Êò
  olas1258_mapping('o\00EC') := '\00F3'; --oì
  olas1258_mapping('o\00CC') := '\00F2'; --oÌ
  olas1258_mapping('o\00D2') := '\1ECF'; --oÒ
  olas1258_mapping('o\00DE') := '\00F5'; --oÞ
  olas1258_mapping('o\00F2') := '\1ECD'; --oò
  olas1258_mapping('O\00EC') := '\00D3'; --Oì
  olas1258_mapping('O\00CC') := '\00D2'; --OÌ
  olas1258_mapping('O\00D2') := '\1ECE'; --OÒ
  olas1258_mapping('O\00DE') := '\00D5'; --OÞ
  olas1258_mapping('O\00F2') := '\1ECC'; --Oò
  olas1258_mapping('\00F4\00EC') := '\1ED1'; --ôì
  olas1258_mapping('\00F4\00CC') := '\1ED3'; --ôÌ
  olas1258_mapping('\00F4\00D2') := '\1ED5'; --ôÒ
  olas1258_mapping('\00F4\00DE') := '\1ED7'; --ôÞ
  olas1258_mapping('\00F4\00F2') := '\1ED9'; --ôò
  olas1258_mapping('\00D4\00EC') := '\1ED0'; --Ôì
  olas1258_mapping('\00D4\00CC') := '\1ED2'; --ÔÌ
  olas1258_mapping('\00D4\00D2') := '\1ED4'; --ÔÒ
  olas1258_mapping('\00D4\00DE') := '\1ED6'; --ÔÞ
  olas1258_mapping('\00D4\00F2') := '\1ED8'; --Ôò
  olas1258_mapping('y\00EC') := '\00FD'; --yì
  olas1258_mapping('y\00CC') := '\1EF3'; --yÌ
  olas1258_mapping('y\00D2') := '\1EF7'; --yÒ
  olas1258_mapping('y\00DE') := '\1EF9'; --yÞ
  olas1258_mapping('y\00F2') := '\1EF5'; --yò
  olas1258_mapping('Y\00EC') := '\00DD'; --Yì
  olas1258_mapping('Y\00CC') := '\1EF2'; --YÌ
  olas1258_mapping('Y\00D2') := '\1EF6'; --YÒ
  olas1258_mapping('Y\00DE') := '\1EF8'; --YÞ
  olas1258_mapping('Y\00F2') := '\1EF4'; --Yò
  olas1258_mapping('u\00EC') := '\00FA'; --uì
  olas1258_mapping('u\00CC') := '\00F9'; --uÌ
  olas1258_mapping('u\00D2') := '\1EE7'; --uÒ
  olas1258_mapping('u\00DE') := '\0169'; --uÞ
  olas1258_mapping('u\00F2') := '\1EE5'; --uò
  olas1258_mapping('U\00EC') := '\00DA'; --Uì
  olas1258_mapping('U\00CC') := '\00D9'; --UÌ
  olas1258_mapping('U\00D2') := '\1EE6'; --UÒ
  olas1258_mapping('U\00DE') := '\0168'; --UÞ
  olas1258_mapping('U\00F2') := '\1EE4'; --Uò
  olas1258_mapping('\00FD\00EC') := '\1EE9'; --ýì
  olas1258_mapping('\00FD\00CC') := '\1EEB'; --ýÌ
  olas1258_mapping('\00FD\00D2') := '\1EED'; --ýÒ
  olas1258_mapping('\00FD\00F2') := '\1EF1'; --ýò
  olas1258_mapping('\00DD\00EC') := '\1EE8'; --Ýì
  olas1258_mapping('\00DD\00CC') := '\1EEA'; --ÝÌ
  olas1258_mapping('\00DD\00D2') := '\1EEC'; --ÝÒ
  olas1258_mapping('\00DD\00DE') := '\1EEE'; --ÝÞ
  olas1258_mapping('\00DD\00F2') := '\1EF0'; --Ýò
  olas1258_mapping('i\00EC') := '\00ED'; --iì
  olas1258_mapping('i\00CC') := '\00EC'; --iÌ
  olas1258_mapping('i\00D2') := '\1EC9'; --iÒ
  olas1258_mapping('i\00DE') := '\0129'; --iÞ
  olas1258_mapping('i\00F2') := '\1ECB'; --iò
  olas1258_mapping('I\00EC') := '\00CD'; --Iì
  olas1258_mapping('I\00CC') := '\00CC'; --IÌ
  olas1258_mapping('I\00D2') := '\1EC8'; --IÒ
  olas1258_mapping('I\00DE') := '\0128'; --IÞ
  olas1258_mapping('\00D5\00EC') := '\1EDA'; --Õì
  olas1258_mapping('\00D5\00CC') := '\1EDC'; --ÕÌ
  olas1258_mapping('\00D5\00D2') := '\1EDE'; --ÕÒ
  olas1258_mapping('\00D5\00DE') := '\1EE0'; --ÕÞ
  olas1258_mapping('\00D5\00F2') := '\1EE2'; --Õò
  olas1258_mapping('\00F5\00EC') := '\1EDB'; --õì
  olas1258_mapping('\00F5\00CC') := '\1EDD'; --õÌ
  olas1258_mapping('\00F5\00D2') := '\1EDF'; --õÒ
  olas1258_mapping('\00F5\00DE') := '\1EE1'; --õÞ
  olas1258_mapping('\00F5\00F2') := '\1EE3'; --õò
  olas1258_mapping('\00EA\00DE') := '\1EC5'; --êÞ
  olas1258_mapping('\00FD\00DE') := '\1EEF'; --ýÞ
  olas1258_mapping('I\00F2') := '\1ECA'; --Iò
  olas1258_mapping('I') := 'I'; --I

  -- Processing
  -- Part 1
  v_result := asciistr(p_olas1258_string);
  v_startIndex := 1;
  v_foundIndex := 0;
  v_pattern := '';
  v_str := v_result;
  v_result := '';
  v_foundIndex := INSTR(v_str, '\', v_startIndex);
  -- xy ly
  while ( v_foundIndex > 0)            
  loop
      -- assign v_pattern = 6 chars
      v_pattern := substr(v_str, v_foundIndex - 1, 6);
      if not olas1258_mapping.EXISTS(v_pattern) then
          -- assign v_pattern = 10 chars
          v_pattern := substr(v_str, v_foundIndex, 10);
      end if;    
    if olas1258_mapping.EXISTS(v_pattern) then    
      if length(v_pattern) = 10 then
        --xu ly 10 chars
        v_result := v_result || substr(v_str, v_startIndex, v_foundIndex - v_startIndex);
        v_result := v_result || olas1258_mapping(v_pattern);
        v_startIndex := v_foundIndex + 10;    
      else
        --xu ly 6 chars
        v_result := v_result || substr(v_str, v_startIndex, v_foundIndex - 1 - v_startIndex);
        v_result := v_result || olas1258_mapping(v_pattern);
        v_startIndex := v_foundIndex - 1 + 6;    
      end if;
    else
      --xu ly 5 chars
      v_pattern := substr(v_str, v_foundIndex, 5);
      v_result := v_result || substr(v_str, v_startIndex, v_foundIndex - v_startIndex);
      v_result := v_result || v_pattern;
      case
         when v_pattern = '\00E3' then
            v_result := replace(v_result, v_pattern, '\0103'); --ã
         when v_pattern = '\00C3' then
            v_result := replace(v_result, v_pattern, '\0102'); --Ã
         when v_pattern = '\00E2' then
            v_result := replace(v_result, v_pattern, '\00E2'); --â
         when v_pattern = '\00C2' then
            v_result := replace(v_result, v_pattern, '\00C2'); --Â
         when v_pattern = '\00EA' then
            v_result := replace(v_result, v_pattern, '\00EA'); --ê
         when v_pattern = '\00CA' then
            v_result := replace(v_result, v_pattern, '\00CA'); --Ê
         when v_pattern = '\00F4' then
            v_result := replace(v_result, v_pattern, '\00F4'); --ô
         when v_pattern = '\00D4' then
            v_result := replace(v_result, v_pattern, '\00D4'); --Ô
         when v_pattern = '\00FD' then
            v_result := replace(v_result, v_pattern, '\01B0'); --ý
         when v_pattern = '\00DD' then
            v_result := replace(v_result, v_pattern, '\01AF'); --Ý
         when v_pattern = '\00F2' then
            v_result := replace(v_result, v_pattern, '\1ECA'); --ò
         when v_pattern = '\00F0' then
            v_result := replace(v_result, v_pattern, '\0111'); --ð
         when v_pattern = '\00D0' then
            v_result := replace(v_result, v_pattern, '\0110'); --Ð
         when v_pattern = '\00D5' then
            v_result := replace(v_result, v_pattern, '\01A0'); --Õ
         when v_pattern = '\00F5' then
            v_result := replace(v_result, v_pattern, '\01A1'); --õ
         else
            v_result := v_result;
      end case;
      v_startIndex := v_foundIndex + 5;              
    end if;
     v_foundIndex := INSTR(v_str, '\', v_startIndex);
  end loop;
  v_str := v_result || substr(v_str,v_startIndex);
  v_result := v_str;
  RETURN unistr(v_result);
END OLAS1258_TO_UNI;

-- Unicode to Vni
FUNCTION  UNI_TO_VNI (p_uni_string varchar2) RETURN VARCHAR2 AS
/*
 *  Unicode to Vni conversion
 *
 * @author     Nguyen Tich Thanh
 * @version    1.0, 16/05/2011
 */
  uni_mapping MAPPING_T;
  v_result VARCHAR2(512);
  v_pattern VARCHAR2(10);
  v_str  VARCHAR2(512);
  v_startIndex PLS_INTEGER;
  v_foundIndex PLS_INTEGER;
BEGIN
  -- Initialization for uni_mapping
  uni_mapping('\00E1') := 'a\00F9'; --á
  uni_mapping('\00E0') := 'a\00F8'; --à
  uni_mapping('\1EA3') := 'a\00FB'; --ả
  uni_mapping('\00E3') := 'a\00F5'; --ã
  uni_mapping('\1EA1') := 'a\00EF'; --ạ
  uni_mapping('\00C1') := 'A\00D9'; --Á
  uni_mapping('\00C0') := 'A\00D8'; --À
  uni_mapping('\1EA2') := 'A\00DB'; --Ả
  uni_mapping('\00C3') := 'A\00D5'; --Ã
  uni_mapping('\1EA0') := 'A\00CF'; --Ạ
  uni_mapping('\1EAF') := 'a\00E9'; --ắ
  uni_mapping('\1EB1') := 'a\00E8'; --ằ
  uni_mapping('\1EB3') := 'a\00FA'; --ẳ
  uni_mapping('\1EB5') := 'a\00FC'; --ẵ
  uni_mapping('\1EB7') := 'a\00EB'; --ặ
  uni_mapping('\1EAE') := 'A\00C9'; --Ắ
  uni_mapping('\1EB0') := 'A\00C8'; --Ằ
  uni_mapping('\1EB2') := 'A\00DA'; --Ẳ
  uni_mapping('\1EB4') := 'A\00DC'; --Ẵ
  uni_mapping('\1EA0') := 'A\00CF'; --Ạ
  uni_mapping('\1EA5') := 'a\00E1'; --ấ
  uni_mapping('\1EA7') := 'a\00E0'; --ầ
  uni_mapping('\1EA9') := 'a\00E5'; --ẩ
  uni_mapping('\1EAB') := 'a\00E3'; --ẫ
  uni_mapping('\1EAD') := 'a\00E4'; --ậ
  uni_mapping('\1EA4') := 'A\00C1'; --Ấ
  uni_mapping('\1EA6') := 'A\00C0'; --Ầ
  uni_mapping('\1EA8') := 'A\00C5'; --Ẩ
  uni_mapping('\1EAA') := 'A\00C3'; --Ẫ
  uni_mapping('\1EAC') := 'A\00C4'; --Ậ
  uni_mapping('\00E9') := 'e\00F9'; --é
  uni_mapping('\00E8') := 'e\00F8'; --è
  uni_mapping('\1EBB') := 'e\00FB'; --ẻ
  uni_mapping('\1EBD') := 'e\00F5'; --ẽ
  uni_mapping('\1EB9') := 'e\00EF'; --ẹ
  uni_mapping('\00C9') := 'E\00D9'; --É
  uni_mapping('\00C8') := 'E\00D8'; --È
  uni_mapping('\1EBA') := 'E\00DB'; --Ẻ
  uni_mapping('\1EBC') := 'E\00D5'; --Ẽ
  uni_mapping('\1EB8') := 'E\00CF'; --Ẹ
  uni_mapping('\1EBF') := 'e\00E1'; --ế
  uni_mapping('\1EC1') := 'e\00E0'; --ề
  uni_mapping('\1EC3') := 'e\00E5'; --ể
  uni_mapping('\1EC5') := 'e\00E3'; --ễ
  uni_mapping('\1EC7') := 'e\00E4'; --ệ
  uni_mapping('\1EBE') := 'E\00C1'; --Ế
  uni_mapping('\1EC0') := 'E\00C0'; --Ề
  uni_mapping('\1EC2') := 'E\00C5'; --Ể
  uni_mapping('\1EC4') := 'E\00C3'; --Ễ
  uni_mapping('\1EC6') := 'E\00C4'; --Ệ
  uni_mapping('\00F3') := 'o\00F9'; --ó
  uni_mapping('\00F2') := 'o\00F8'; --ò
  uni_mapping('\1ECF') := 'o\00FB'; --ỏ
  uni_mapping('\00F5') := 'o\00F5'; --õ
  uni_mapping('\1ECD') := 'o\00EF'; --ọ
  uni_mapping('\00D3') := 'O\00D9'; --Ó
  uni_mapping('\00D2') := 'O\00D8'; --Ò
  uni_mapping('\1ECE') := 'O\00DB'; --Ỏ
  uni_mapping('\00D5') := 'O\00D5'; --Õ
  uni_mapping('\1ECC') := 'O\00CF'; --Ọ
  uni_mapping('\1ED1') := 'o\00E1'; --ố
  uni_mapping('\1ED3') := 'o\00E0'; --ồ
  uni_mapping('\1ED5') := 'o\00E5'; --ổ
  uni_mapping('\1ED7') := 'o\00E3'; --ỗ
  uni_mapping('\1ED9') := 'o\00E4'; --ộ
  uni_mapping('\1ED0') := 'O\00C1'; --Ố
  uni_mapping('\1ED2') := 'O\00C0'; --Ồ
  uni_mapping('\1ED4') := 'O\00C5'; --Ổ
  uni_mapping('\1ED6') := 'O\00C3'; --Ỗ
  uni_mapping('\1ED8') := 'O\00C4'; --Ộ
  uni_mapping('\00FD') := 'y\00F9'; --ý
  uni_mapping('\1EF3') := 'y\00F8'; --ỳ
  uni_mapping('\1EF7') := 'y\00FB'; --ỷ
  uni_mapping('\1EF9') := 'y\00F5'; --ỹ
  uni_mapping('\1EF5') := '\00EE'; --ỵ
  uni_mapping('\00DD') := 'Y\00D9'; --Ý
  uni_mapping('\1EF2') := 'Y\00D8'; --Ỳ
  uni_mapping('\1EF6') := 'Y\00DB'; --Ỷ
  uni_mapping('\1EF8') := 'Y\00D5'; --Ỹ
  uni_mapping('\1EF4') := '\00CE'; --Ỵ
  uni_mapping('\00FA') := 'u\00F9'; --ú
  uni_mapping('\00F9') := 'u\00F8'; --ù
  uni_mapping('\1EE7') := 'u\00FB'; --ủ
  uni_mapping('\0169') := 'u\00F5'; --ũ
  uni_mapping('\1EE5') := 'u\00EF'; --ụ
  uni_mapping('\00DA') := 'U\00D9'; --Ú
  uni_mapping('\00D9') := 'U\00D8'; --Ù
  uni_mapping('\1EE6') := 'U\00DB'; --Ủ
  uni_mapping('\0168') := 'U\00D5'; --Ũ
  uni_mapping('\1EE4') := 'U\00CF'; --Ụ
  uni_mapping('\1EE9') := '\00F6\00F9'; --ứ
  uni_mapping('\1EEB') := '\00F6\00F8'; --ừ
  uni_mapping('\1EED') := '\00F6\00FB'; --ử
  uni_mapping('\1EF1') := '\00F6\00EF'; --ự
  uni_mapping('\1EE8') := '\00D6\00D9'; --Ứ
  uni_mapping('\1EEA') := '\00D6\00D8'; --Ừ
  uni_mapping('\1EEC') := '\00D6\00DB'; --Ử
  uni_mapping('\1EEE') := '\00D6\00D5'; --Ữ
  uni_mapping('\1EF0') := '\00D6\00CF'; --Ự
  uni_mapping('\00ED') := '\00ED'; --í
  uni_mapping('\00EC') := '\00EC'; --ì
  uni_mapping('\1EC9') := '\00E6'; --ỉ
  uni_mapping('\0129') := '\00F3'; --ĩ
  uni_mapping('\1ECB') := '\00F2'; --ị
  uni_mapping('\00CD') := '\00CD'; --Í
  uni_mapping('\00CC') := '\00CC'; --Ì
  uni_mapping('\1EC8') := '\00C6'; --Ỉ
  uni_mapping('\0128') := '\00D3'; --Ĩ
  uni_mapping('\1EDA') := '\00D4\00D9'; --Ớ
  uni_mapping('\1EDC') := '\00D4\00D8'; --Ờ
  uni_mapping('\1EDE') := '\00D4\00DB'; --Ở
  uni_mapping('\1EE0') := '\00D4\00D5'; --Ỡ
  uni_mapping('\1EE2') := '\00D4\00CF'; --Ợ
  uni_mapping('\1EDB') := '\00F4\00F9'; --ớ
  uni_mapping('\1EDD') := '\00F4\00F8'; --ờ
  uni_mapping('\1EDF') := '\00F4\00FB'; --ở
  uni_mapping('\1EE1') := '\00F4\00F5'; --ỡ
  uni_mapping('\1EE3') := '\00F4\00EF'; --ợ
  uni_mapping('\1EC5') := 'e\00E3'; --ễ
  uni_mapping('\1EEF') := '\00F6\00F5'; --ữ
  uni_mapping('\1ECA') := '\00D2'; --Ị
  uni_mapping('\0103') := 'a\00EA'; --ă
  uni_mapping('\0102') := 'A\00CA'; --Ă
  uni_mapping('\00E2') := 'a\00E2'; --â
  uni_mapping('\00C2') := 'A\00C2'; --Â
  uni_mapping('\00EA') := 'e\00E2'; --ê
  uni_mapping('\00CA') := 'E\00C2'; --Ê
  uni_mapping('\00F4') := 'o\00E2'; --ô
  uni_mapping('\00D4') := 'O\00C2'; --Ô
  uni_mapping('\01B0') := '\00F6'; --ư
  uni_mapping('\01AF') := '\00D6'; --Ư
  uni_mapping('\1ECA') := '\00D2'; --Ị
  uni_mapping('\0111') := '\00F1'; --đ
  uni_mapping('\0110') := '\00D1'; --Đ
  uni_mapping('\01A0') := '\00D4'; --Ơ
  uni_mapping('\01A1') := '\00F4'; --ơ

  -- Processing
  -- Part 1
  v_result := asciistr(p_uni_string);
  v_startIndex := 1;
  v_foundIndex := 0;
  v_pattern := '';
  v_str := v_result;
  v_result := '';
  v_foundIndex := INSTR(v_str, '\', v_startIndex);
  -- xy ly
  while ( v_foundIndex > 0)            
  loop
      -- assign v_pattern = 5 chars
      v_pattern := substr(v_str, v_foundIndex , 5);    
    if uni_mapping.EXISTS(v_pattern) then    
        --xu ly 5 chars
        v_result := v_result || substr(v_str, v_startIndex, v_foundIndex  - v_startIndex);
        v_result := v_result || uni_mapping(v_pattern);
        v_startIndex := v_foundIndex + 5;              
    else
      --xu ly 5 chars
        v_result := v_result || substr(v_str, v_startIndex, v_foundIndex  - v_startIndex);
        v_result := v_result || v_pattern;
        v_startIndex := v_foundIndex + 5;              
    end if;
     v_foundIndex := INSTR(v_str, '\', v_startIndex);
  end loop;
  v_str := v_result || substr(v_str,v_startIndex);
  v_result := v_str;
  RETURN unistr(v_result);
END UNI_TO_VNI;

END CONVERTOR;

Reset Sequence

CREATE OR REPLACE PROCEDURE reset_sequence ( seq_name IN VARCHAR2,
startvalue IN PLS_INTEGER) AS
cval   INTEGER;
inc_by VARCHAR2(25);
BEGIN
  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' ||seq_name||' MINVALUE 0';
  EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.NEXTVAL FROM DUAL'  INTO cval;
  cval := cval - startvalue + 1;
  IF cval < 0 THEN
    inc_by := ' INCREMENT BY ';
    cval:= ABS(cval);
  ELSE
    inc_by := ' INCREMENT BY -';
  END IF;
  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name || inc_by ||  cval;
  EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.NEXTVAL FROM DUAL'  INTO cval;
  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name ||  ' INCREMENT BY 1';
END reset_sequence;

Reference: http://psoug.org/reference/sequences.html