Create table translate_application_log ( MODULE_NAME VARCHAR2(50 BYTE) , LINE_NUMBER NUMBER , log_message VARCHAR2(4000)); CREATE SEQUENCE translate_application_log_seq MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1712041 CACHE 20 NOORDER NOCYCLE ; CREATE TABLE INPUT_FR ("STRING_FR" CLOB, "LINE_NUMBER" NUMBER, "MODULE_NAME" VARCHAR2(50 BYTE) ): CREATE SEQUENCE "INPUT_FR_SEQ" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1712041 CACHE 20 NOORDER NOCYCLE ; create or replace package translate_application AUTHID CURRENT_USER as procedure translate_report (p_report_name varchar2) ; procedure translate_form (p_form_name varchar2) ; procedure translate_menu (p_menu_name varchar2, p_english_to_french boolean default false) ; Function search_string(p_string in out varchar2, p_search in varchar2) return varchar2; procedure search_and_translate_string(p_string in out varchar2, p_search in varchar2); FUNCTION find_translation (p_string_fr varchar2) return varchar2; FUNCTION clob_to_blob(p_clob IN CLOB) RETURN BLOB ; PROCEDURE LOG_MESSAGE (p_message varchar2); END translate_application; / create or replace PACKAGE body translate_application AS v_current_module VARCHAR2(100); v_current_item VARCHAR2(100); v_current_objecT_type VARCHAR2(100); v_name VARCHAR2(2000); v_name_start_pos NUMBER; v_name_end_pos NUMBER; v_error_message VARCHAR2(4000); v_english_to_french boolean := false; PROCEDURE translate_report( p_report_name VARCHAR2) AS /************************************************************** PURPOSE: translate all string in a report IN/OUT PARAMETERS: * p_report_name : name of the report to be translated CHANGE HISTORY 11/03/2013 Arno Hollanders Initial creation ***************************************************************/ v_line VARCHAR2(32000) := 'XYZ'; v_previous_line VARCHAR2(32000) := 'XYZ'; v_new_line VARCHAR2(32000); v_string_fr VARCHAR2(32000); v_string_en VARCHAR2(32000); v_string_fr_start_pos NUMBER; v_string_fr_end_pos NUMBER; v_report_name_fr VARCHAR2(100); v_report_name_en VARCHAR2(100); v_clob CLOB; BEGIN DBMS_OUTPUT.ENABLE(32000000); v_current_objecT_type := 'report'; v_current_module := p_report_name; -- first remove the entries from qms_detail_transapps DELETE FROM HST50.qms_detail_transapps WHERE file_name = v_current_module; v_report_name_fr := p_report_name||'.xml'; v_report_name_en := p_report_name||'_en.xml'; -- Loop through all lines encountered in the original FOR rec_loop IN (SELECT line_number, string_fr FROM input_fr WHERE module_name = v_report_name_fr ORDER BY line_number ) LOOP v_previous_line := v_line; v_line := rec_loop.string_fr; -- Retrieve the Item name only if it is a the line containts text name= IF v_line LIKE '%, we need to perform a translation IF v_previous_line LIKE '%%' THEN v_string_fr_start_pos := instr(v_line,'[',1,2) ; v_string_fr_end_pos := instr(v_line,']',1,2); -- sometimes the ] is on a second like eg when the label contains a carriage return IF v_string_fr_end_pos = 0 THEN v_string_fr_end_pos := 1000; END IF; v_string_fr := SUBSTR(v_line,v_string_fr_start_pos+1,v_string_fr_end_pos-v_string_fr_start_pos-2); v_string_en := find_translation (v_string_fr ); v_new_line := SUBSTR(v_line,1,v_string_fr_start_pos)||v_string_en||SUBSTR(v_line,v_string_fr_end_pos-1,32000); ELSE v_new_line := v_line; END IF; -- build up the translated report v_clob := v_clob||chr(10)||v_new_line; END LOOP; -- remove the trailing carriagge return v_clob := ltrim(v_clob,chr(10)); --export the clob to the filesystem ExportBlobJava ('/mnt/issuetracker/'||v_report_name_en,clob_to_blob(v_clob)); COMMIT; EXCEPTIOn WHEN OTHERS THEN v_error_message := substr('error in procedure translate_report'||sqlerrm,1,4000); LOG_MESSAGE(v_error_message); END translate_report; PROCEDURE translate_form( p_form_name VARCHAR2) AS /************************************************************** PURPOSE: translate all strings in a form IN/OUT PARAMETERS: * p_form_name : name of the report to be translated CHANGE HISTORY 11/03/2013 Arno Hollanders Initial creation ***************************************************************/ v_line VARCHAR2(32000) := 'XYZ'; v_form_name_fr VARCHAR2(100); v_form_name_en VARCHAR2(100); v_current_block VARCHAR2(100); v_current_form_item VARCHAR2(100); v_clob CLOB; v_rv_meaning VARCHAR2(100); v_rv_low_value VARCHAR2(100); cursor cur_cg_ref_codes (l_rv_meaning varchar2, l_rv_low_value varchar2) is select 1 from cg_ref_codes@dev where rv_meaning = l_rv_meaning and rv_low_value = l_rv_low_value; v_dummy number; BEGIN DBMS_OUTPUT.ENABLE(32000000); v_current_objecT_type := 'form'; v_current_module := p_form_name; -- first remove the entries from qms_detail_transapps DELETE FROM HST50.qms_detail_transapps WHERE file_name = v_current_module; v_form_name_fr := p_form_name||'.xml'; v_form_name_en := p_form_name||'_en.xml'; -- Loop through all line encountered in the original FOR rec_loop IN (SELECT line_number, string_fr FROM input_fr WHERE module_name = v_form_name_fr ORDER BY line_number ) LOOP -- Do not try to translate programunits and triggers IF instr(rec_loop.string_fr,'ProgramUnitType') = 0 AND instr(rec_loop.string_fr,'Trigger') = 0 THEN v_line := rec_loop.string_fr; -- First determine the name of the item to be translated : only required to store a link in qms_detail_transapps v_name_start_pos := instr(v_line,'"',1,1); v_name_end_pos := instr(v_line,'"',1,2); v_name := SUBSTR(v_line,v_name_start_pos+1,v_name_end_pos-v_name_start_pos-1); v_name := SUBSTR(v_name,1,30); IF v_line LIKE '% v_line, p_search => ' Name="' ); v_rv_low_value := search_string(p_string => v_line, p_search => ' Value="' ); open cur_cg_ref_codes(v_rv_meaning, v_rv_low_value); fetch cur_cg_ref_codes into v_dummy; IF cur_cg_ref_codes%notfound THEN v_current_item := v_current_block||'.'||v_current_form_item||'.ListItemElement'; search_and_translate_string(p_string => v_line, p_search => ' Name="' ); END IF; close cur_cg_ref_codes; END IF; ELSE v_current_item := v_current_block||'.'||v_current_form_item||'.Hint'; search_and_translate_string(p_string => v_line, p_search => ' Hint="' ); v_current_item := v_current_block||'.'||v_current_form_item||'.Prompt'; search_and_translate_string(p_string => v_line, p_search => ' Prompt="' ); v_current_item := v_current_block||'.'||v_current_form_item||'.Label'; search_and_translate_string(p_string => v_line, p_search => ' Label="' ); v_current_item := v_current_block||'.'||v_current_form_item||'.Tooltip'; search_and_translate_string(p_string => v_line, p_search => ' Tooltip="' ); v_current_item := v_current_block||'.'||v_current_form_item||'.Title'; search_and_translate_string(p_string => v_line, p_search => ' Title="' ); v_current_item := v_current_block||'.'||v_current_form_item||'.FrameTitle'; search_and_translate_string(p_string => v_line, p_search => ' FrameTitle="' ); v_current_item := v_current_block||'.'||v_current_form_item||'.GraphicsText'; search_and_translate_string(p_string => v_line, p_search => ' GraphicsText="' ); v_current_item := v_current_block||'.'||v_current_form_item||'.Text'; search_and_translate_string(p_string => v_line, p_search => ' Text="' ); v_line := REPLACE(v_line,'DataType="100"',''); END IF; -- build up the translated form v_clob := v_clob||chr(10)||v_line; ELSE v_clob := v_clob||chr(10)||rec_loop.string_fr; END IF; END LOOP; -- remove the trailing carriagge return v_clob := ltrim(v_clob,chr(10)); --export the clob to the filesystem ExportBlobJava ('/mnt/issuetracker/'||v_form_name_en,clob_to_blob(v_clob)); COMMIT; EXCEPTIOn WHEN OTHERS THEN v_error_message := substr('error in procedure translate_form'||sqlerrm,1,4000); LOG_MESSAGE(v_error_message); END translate_form; PROCEDURE translate_menu( p_menu_name VARCHAR2 ,p_english_to_french boolean default false) AS /************************************************************** PURPOSE: translate all string in a manu IN/OUT PARAMETERS: * p_menu_name : name of the menu to be translated CHANGE HISTORY 11/03/2013 Arno Hollanders Initial creation ***************************************************************/ v_line VARCHAR2(32000) := 'XYZ'; v_new_line VARCHAR2(32000); v_menu_name_fr VARCHAR2(100); v_menu_name_en VARCHAR2(100); v_clob CLOB; v_loopcounter NUMBER := 0; v_current_menu VARCHAR2(100) := ''; v_current_menu_item VARCHAR2(100) := ''; BEGIN v_english_to_french := p_english_to_french; v_current_objecT_type := 'menu'; v_current_module := p_menu_name; v_current_item := v_current_menu||'.'||v_current_menu_item; -- first remove the entries from qms_detail_transapps DELETE FROM HST50.qms_detail_transapps WHERE file_name = v_current_module; v_menu_name_fr := p_menu_name||'.xml'; v_menu_name_en := p_menu_name||'_en.xml'; -- Note on temp_convert_menu_doubles -- In a menu, the name of the menu item can sometimes exists multiple times because this is based on the label defined in designer -- eg for screens which are readonly for certain roles and updateable for other roles -- when converting the xml to mmb, the double menu items are thrown away which causes missing menu items -- Table temp_convert_menu_doubles will contain all menu items, if a double in encoutered, the name is concatenated with a sequence number to make it unique -- DELETE FROM temp_convert_menu_doubles; -- Loop through all lines encountered in the original FOR rec_loop IN (SELECT line_number, string_fr FROM input_fr WHERE module_name = v_menu_name_fr ORDER BY line_number ) LOOP v_line := rec_loop.string_fr; -- Remove doubles is a new menu is encoutered, doubles are only blocking if they occur within a single (sub)menu IF v_line LIKE '%%' THEN DELETE FROM temp_convert_menu_doubles; END IF; -- determine the name of the menu item -- Limit the name to 30 positions, otherwise conversion from XML to mmb will fail v_name_start_pos := instr(v_line,'"',1,1); v_name_end_pos := instr(v_line,'"',1,2); v_name := SUBSTR(v_line,v_name_start_pos+1,v_name_end_pos-v_name_start_pos-1); v_name := SUBSTR(v_name,1,30); IF v_name IS NOT NULL AND v_line LIKE '% v_line, p_search => ' MenuItemCode="' ); --v_current_item := v_current_item||search_string(p_string => v_line, p_search => ' SubMenuName="' ); v_current_item := v_current_menu||'.'||v_current_menu_item||'.Hint'; search_and_translate_string(p_string => v_new_line, p_search => 'Hint="' ); v_current_item := v_current_menu||'.'||v_current_menu_item||'.Label'; search_and_translate_string(p_string => v_new_line, p_search => 'Label="' ); -- remove DataType="100" because this willl block conversion from XML to MMB -- Don't ask me why the original can contain DataType="100", thisis one of the mysteries of forms v_line := REPLACE(v_line,'DataType="100"',''); -- build up the translated form v_clob := v_clob||chr(10)||v_new_line; ELSE v_clob := v_clob||chr(10)||v_new_line; END IF; END LOOP; -- remove the trailing carriagge return v_clob := ltrim(v_clob,chr(10)); --export the clob to the filesystem ExportBlobJava ('/mnt/issuetracker/'||v_menu_name_en,clob_to_blob(v_clob)); COMMIT; EXCEPTIOn WHEN OTHERS THEN v_error_message := substr('error in procedure translate_menu'||sqlerrm,1,4000); LOG_MESSAGE(v_error_message); END translate_menu; Function search_string ( p_string IN OUT VARCHAR2, p_search IN VARCHAR2 ) return varchar2 IS /************************************************************** PURPOSE: search for text in a string IN/OUT PARAMETERS: * p_string : String which needs to be searched * p_search : Search for which will be searched CHANGE HISTORY 11/03/2013 Arno Hollanders Initial creation ***************************************************************/ v_string_start_pos NUMBER; v_string_end_pos NUMBER; v_string_out VARCHAR2(32000) := ''; v_length_search_string NUMBER; BEGIN v_length_search_string := LENGTH(p_search); -- Look for the starting position of the seachfield in the string v_string_start_pos := instr(p_string,p_search,1) ; IF v_string_start_pos > 0 THEN v_string_start_pos := v_string_start_pos +v_length_search_string; v_string_end_pos := instr(p_string,'"',v_string_start_pos,1); -- Retrieve the string to be translated v_string_out := SUBSTR(p_string,v_string_start_pos,v_string_end_pos-v_string_start_pos); END IF; return v_string_out; EXCEPTIOn WHEN OTHERS THEN v_error_message := substr('error in procedure search_string'||sqlerrm,1,4000); LOG_MESSAGE(v_error_message); END search_string; PROCEDURE search_and_translate_string ( p_string IN OUT VARCHAR2, p_search IN VARCHAR2 ) IS /************************************************************** PURPOSE: search for text in a string IN/OUT PARAMETERS: * p_string : String which needs to be searched * p_search : Search for which will be searched CHANGE HISTORY 11/03/2013 Arno Hollanders Initial creation ***************************************************************/ v_string_start_pos NUMBER; v_string_end_pos NUMBER; v_string_fr VARCHAR2(32000); v_string_en VARCHAR2(32000); v_length_search_string NUMBER; BEGIN v_length_search_string := LENGTH(p_search); -- Look for the starting position of the seachfield in the string v_string_start_pos := instr(p_string,p_search,1) ; IF v_string_start_pos > 0 THEN v_string_start_pos := v_string_start_pos +v_length_search_string; v_string_end_pos := instr(p_string,'"',v_string_start_pos,1); -- Retrieve the string to be translated v_string_fr := SUBSTR(p_string,v_string_start_pos,v_string_end_pos-v_string_start_pos); IF LENGTH(v_string_fr) > 0 THEN v_string_en := find_translation (v_string_fr ); ELSE v_string_en := v_string_fr; END IF; -- rebuild the original string by replacing the french with the English translation p_string := SUBSTR(p_string,1,v_string_start_pos-1) ||v_string_en ||SUBSTR(p_string,v_string_end_pos,32000); END IF; EXCEPTIOn WHEN OTHERS THEN v_error_message := substr('error in procedure search_and_translate_string'||sqlerrm,1,4000); LOG_MESSAGE(v_error_message); END search_and_translate_string; FUNCTION find_translation ( p_string_fr VARCHAR2 ) RETURN VARCHAR2 IS /************************************************************** PURPOSE: Find the English translation for a french string IN/OUT PARAMETERS: * p_string_fr : String which needs to be translated CHANGE HISTORY 11/03/2013 Arno Hollanders Initial creation ***************************************************************/ CURSOR cur_trans(b_french VARCHAR2) IS SELECT english FROM HST50.qms_transapps WHERE french = b_french; CURSOR cur_trans_upd(b_french VARCHAR2) IS SELECT english, status FROM HST50.qms_transapps WHERE french_upd = b_french; v_string_en VARCHAR2(32000) := NULL; v_string_fr VARCHAR2(32000) := NULL; v_status VARCHAR2(1) := NULL; BEGIN -- First replace some esacped characters by the original string v_string_fr := REPLACE(p_string_fr,'&#10;','\n'); v_string_fr := REPLACE(v_string_fr,'&','&'); v_string_fr := REPLACE(v_string_fr,'<','<'); v_string_fr := REPLACE(v_string_fr,'"','"'); -- Don't do anything if the string only contains blanks IF NVL(LENGTH(REPLACE(v_string_fr,' ','')),0) = 0 THEN v_string_en := v_string_fr; ELSE -- Look up the translation in qms_transapps OPEN cur_trans(v_string_fr); FETCH cur_trans INTO v_string_en; IF cur_trans%notfound THEN OPEN cur_trans_upd(v_string_fr); FETCH cur_trans_upd INTO v_string_en, v_status; IF cur_trans_upd%notfound THEN v_string_en := v_string_fr; v_status := 'N'; END IF; close cur_trans_upd; INSERT INTO HST50.qms_transapps ( french, english, status, french_upd ) VALUES ( v_string_fr, v_string_en, v_status, null ); END IF; CLOSE cur_trans; -- Insert the current item in qms_detail_transapps INSERT INTO HST50.qms_detail_transapps ( french, english, file_name, item_name, object_type, source_type )--, qdt_id ) VALUES ( v_string_fr, v_string_en, v_current_module, v_current_item, v_current_objecT_type, 'OD' );--, qdt_id - -- Finally replace original string with some esacped characters IF v_current_objecT_type <> 'report' THEN v_string_en := REPLACE(v_string_en,'&','&'); v_string_en := REPLACE(v_string_en,'<','<'); v_string_en := REPLACE(v_string_en,'\n','&#10;'); v_string_en := REPLACE(v_string_en,'"','"'); END IF; END IF; RETURN v_string_en; --select french, english, status, french_upd --from HST50.qms_transapps EXCEPTIOn WHEN OTHERS THEN v_error_message := substr('error in procedure find_translation'||sqlerrm,1,4000); LOG_MESSAGE(v_error_message); END find_translation; FUNCTION clob_to_blob ( p_clob IN CLOB ) RETURN BLOB AS /************************************************************** PURPOSE: write output of a clob to the filesystem as a blob IN/OUT PARAMETERS: * p_clob : Clob that needs to be exported CHANGE HISTORY 11/03/2013 Arno Hollanders Initial creation ***************************************************************/ v_blob BLOB; v_offset NUMBER DEFAULT 1; v_amount NUMBER DEFAULT 4096; v_offsetwrite NUMBER DEFAULT 1; v_amountwrite NUMBER; v_buffer VARCHAR2(4096 CHAR); BEGIN dbms_lob.createtemporary(v_blob, TRUE); BEGIN LOOP dbms_lob.READ(p_clob, v_amount, v_offset, v_buffer); v_amountwrite := utl_raw.length(utl_raw.cast_to_raw(v_buffer)); dbms_lob.WRITE(v_blob, v_amountwrite, v_offsetwrite, utl_raw.cast_to_raw(v_buffer)); v_offsetwrite := v_offsetwrite + v_amountwrite; v_offset := v_offset + v_amount; v_amount := 4096; END LOOP; EXCEPTION WHEN no_data_found THEN NULL; END; RETURN v_blob; EXCEPTIOn WHEN OTHERS THEN v_error_message := substr('error in procedure clob_to_blob'||sqlerrm,1,4000); LOG_MESSAGE(v_error_message); END clob_to_blob; PROCEDURE LOG_MESSAGE (p_message varchar2) AS pragma autonomous_transaction; BEGIN insert into translate_application_log ( MODULE_NAME , LINE_NUMBER , log_message) values ( v_current_module , translate_application_log_seq.nextval , p_message); commit; END LOG_MESSAGE; END translate_application; /