Declare
   ver_id number;
   index_id number;
   nid number;
   charset varchar2(30);
   nodeid nx_text_strings.node_id%TYPE;
   lang nx_text_strings.language_code%TYPE;
   str nx_text_strings.text%TYPE; 
   length number;
   i number;
   tmp varchar2(1);
   tmpin number;
   stat varchar2(3);
   str_status nx_text_strings.status%TYPE; 
   cursor string_cursor is
       select node_id, language_code, text, lengthb(text), status
          from nx_text_strings;

Begin
   open string_cursor;
   Loop
      FETCH string_cursor into nodeid, lang, str, length, str_status;
      EXIT WHEN string_cursor%NOTFOUND;

      stat := 'TDR';
      if str_status = 'TST'
      then stat := 'TST';
      End if;

      select (nodeid - root_node + 1) into nid from nx_trees
         where tree_id =
              (select tree_id from nx_nodes where node_id = nodeid); 

      /* Get index_id */
      If length < 250
      Then index_id := length * power(16,6);
      Else index_id := 250 * power(16,6);
      End If;

      If length > 6 Then i := 6;
      Else i := length;
      End If;

      While i > 0 Loop
         select substrb(rawtohex(str),2*i,1) into tmp from dual;
         select (decode(tmp, 'F', 15,
                             'E', 14,
                             'D', 13,
                             'C', 12,
                             'B', 11,
                             'A', 10,
                             to_number(tmp))) into tmpin from dual;
         index_id := index_id + tmpin*power(16, i-1);
         i := i - 1;
      End Loop;

      select nv.version_id, p.character_set into ver_id, charset
         from nx_version nv, nx_module nm, nx_project np, nx_projects p,
              nx_versions v, nx_nodes n
      where n.node_id = nodeid
      and   n.tree_id = v.tree_id
      and   v.language_code = lang
      and   v.project_name = np.name
      and   p.language_code = lang
      and   p.project_name = np.name
      and   np.project_id = nm.project_id
      and   v.module_name = nm.name
      and   nv.project_id = np.project_id
      and   nv.module_id = nm.module_id
      and   nv.version_number = v.version_number;

      If length > 250
      Then
         Insert into nx_string values (substrb(rawtohex(str),1,500), str,
            index_id, 0, NULL, NULL, NULL, stat,
            ver_id, lang, charset, nid);
         i := round(length / 250);
         While ((i > 0) and ((length mod 250) > 0)) Loop
            Insert into nx_string values (substrb(rawtohex(str),500*i+1,500),
                                          NULL, NULL, i, NULL, NULL, NULL,
                                          NULL, ver_id, lang, charset, nid);
            i := i - 1;
         End Loop;   
      Else
         Insert into nx_string values (rawtohex(str), str,
            index_id, 0, NULL, NULL, NULL, stat,
            ver_id, lang, charset, nid);
      End If;
   End Loop;
   CLOSE string_cursor;
   COMMIT;
End;
/
