Method of different character sets

zhaozj2021-02-08  214

Step 1: The scott user in the EXP original US7ASCII database. Step 2: EXP original US7ASCII database zhaoboyi users in the database. Step 3: CREATE New Database (Character Set Simplified Chinese_China.zHS16GBK), all paths are the same as the original database.

Step 4: Put the init in the zhs16GBK. Ora Copy Init. ORA. Bak. Step 5: Put the init in US7ASCII. ORA COPY ZHS16GBK Modify the corresponding parameters. Step 6: Generate Scott users in the zhs16GBK, authorized, and scott users in Exp Zs16GBK. Step 7: Generate zhaoboyi users in ZHS16GBK, the zhaoboyi users in Exp ZHS16GBK. Step 8: Open (US7ASCII) and (ZHS16GBK) Scott users with UltraEdit, and change the US7ASCII in the top 5 lines. Step Ninth: Corresponding to the Zhaoboyi user. Step 10: In ZHS16GBK, execute the Owaload of the SYS user. SQL, for HTP. P Authorization. Step 11: Do IMP, pour the original database into the new database. Step 12: Solve large fields. First, the stored procedure fwrite_clob_niu is executed in the original database, and the table containing the large field is generated into the file, then compiled fupdate_clob_text in the new database, reverse the big field to the new database. Appendix 1.

Stored procedure: FWRITE - CLOB - NIUprocedure fwrite_clob_NIU as CURSOR c_lt IS SELECT c.text, c.info_id FROM info_ctext c where info_id <= 120 --change7.16 WHERE info_id <= 100 order by info_id asc; / * 1000- 1100 F 1100-1600 OK 1600-1630 OK 1629-1640 OK 1640-1660 F 1660-1668 OK 1669 F 1670-1750 ok * / str varchar2 (32767): = NULL; POSITION INTEGER: = 1; new_position integer: = 1 Pat varchar2 (80): = 'D: / writeclob'; - Change716 Pat varcha2 (80): = '/ export / home0 / ora8i / doc / ch'; save_file utl_file.file_type; result boolean: = false; dir_delimiter VARCHAR2 (200): = '/'; - Change 7.16 Dir_Delimiter varchar2 (200): = '/'; - NOTE: USE '/' for Windows NT P_FileName VARCHAR2 (200); v_offset integer; v_amount integer; v_buffer varcha2 (30240); begin v_amount: = 30240; if Substr (Pat, Length (PAT), 1)! = DIR_DELIMITER THEN PAT: = PAT || DIR_DELIMITER; E Nd if; begin for r in c_lt loop if r.text is not null life p_filename: = R.INFO_ID || '.dat'; new_position: = 1; position: = 1; Save_File: = UTL_FILE.FOPEN (PAT, P_FileName , 'w'); if dbms_lob.instr (R.Text, Chr (10), POSITION) = 0 THEN begin v_offset: = 1; v_buffer: = '; loop dbms_lob.read (r.Text, v_amount, v_offset, v_buffer; v_offset: = v_offset v_amount; utl_file.put (save_file, v_buffer; - htp.p (v_buffer); - htp.p ('jjjj');

End loop; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; - htp.p ('111'); - str: = dbms_lob.substr (R.Text, 1) || CHR (10); - htp.p ( Str); end if; while (new_position! = 0) loop new_position: = dbms_lob.instr (r.Text, chr (10), position); str: = dbms_lob.substr (r.text, new_position - position, position) ; - htp.p ('str ='); - htp.p (str); position: = new_position 1;

--Change 7.17 if new_position! = 0 THEN UTL_FILE.PUT_LINE (SAVE_FILE, Replace); - Else - UTL_FILE.PUT_LINE (Save_File, Str); - Change 7.17 - HTP .p (new_position); - end if; end loop; utl_file.fclose; htp.p ('

the file' || p_filename || 'generated! '); End if; end loop; Exception when NO_DATA_FOUND THEN HTP.P ('

The file '|| p_filename ||' Failed! ); end; end; fwrite_clob_niu; Appendix II: stored procedure fupdate_clob_textprocedure fupdate_clob_text as CURSOR c_lt IS SELECT c.text, c.info_id FROM info_ctext c - where info_id = 112 where ((info_id> = 1 and info_id <= 30) or (info_id> = 30 and INFO_ID <= 60) or info_id <= 90) OR (info_id> = 90 and info_id <= 120)) / * - Change 716 WHERE TYPE NOT IN ('B', 'C', ' V ',' a ',' e ') AND ((info_id> = 10000 and info_id <20000) OR (INFO_ID> = 1100 and info_id <1601) OR (info_id> = 1600 and info_id <1630) or info_id <1668) or info_id> = 1670 or (info_id> = 0 AND INFO_ID <501)) * / ORDER BY INFO_ID ASC; / * 1000-1100 F 1100-1600 OK 1600-1630 OK 1629-1640 OK 1640-1660 F 1660-1668 OK 1669 F 1670-1750 OK * / TMP Clob; Pat Varchar2 (80): = 'D: / writeclob'; - Change 7.16 Pat varchar2 (80): = '/ export / home0 / ora8i / doc / ch';

Save_file UTL_FILE.FILE_TYPE; Result Boolean: = false; dir_delimiter varchar2 (200): = '/'; - Change 7.16 Dir_Delimiter varchar2 (200): = '/'; - NOTE: USE '/' for Windows NT P_FileName Varchar2 (200); begin htp.p ('we'); if Substr (Pat, Length (PAT), 1)! = DIR_DELIMITER TEN PAT: = PAT || DIR_DELIMITER; END IF; for R IN C_LT loop begin p_filename: = to_char (r.info_id) || '.dat'; UPDATE info_ctext SET text = '' WHERE info_id = r.info_id; COMMIT; SELECT text INTO Tmp FROM info_ctext WHERE info_id = r.info_id FOR UPDATE; - DBMS_LOB.TRIM ( TMP, 0);

Result: = loading_file (PAT, P_FileName, TMP);

If Result TEXDATE INFO_CTEXT SET = Tmp WHERE INFO_ID = R.INFO_ID; Commit; Endness; End Loop; Exception When No_Data_Found Then Htp.p ('

the file' || p_filename || ' Failed! ');

End fupdate_clob_text; Appendix 3: New Database Character Set NLS - Lang Simplified Chinese_China.zHS16GBK Original Database Character Set American_America.us 7ASCII.

_____________________________________

转载请注明原文地址:https://www.9cbs.com/read-2843.html

New Post(0)