REM Program: modrenam.sql REM Author : David Wendelken REM Tactics, Inc. (404) 248-1226 REM Part of the Tactics' Designer/2000 Toolbox REM Genesis: 10-Sep-1996 REM REM Purpose: To rename modules back to their source function's name. This is REM only done for modules with one source function. The intent is to REM 'correct' the names created by the Application Design Wizard. REM REM Note : I always back up before running a utility like this. REM REM Warning: This program will not create duplicate module names, it will REM instead provide a listing of 'errors', i.e., module names it REM could not rename. REM Warning: Do not run this if others are changing function or module names REM in the same application. PROMPT Enter the Application System Name DEFINE p_application_system_name='&1' PROMPT Enter the Application System Version DEFINE p_application_system_version='&2' PROMPT Enter Module Short Name or % for ALL DEFINE p_module_short_name='&3' Prompt (U)pdate names or just (S)how and tell DEFINE p_update_or_show='&4' PROMPT Enter the output filename (without the .lis extension) DEFINE p_filename='&5' REM This is a wide report, so set the line width accordingly. set linesize 132 set pagesize 66 REM Let's not clutter up the screen and page with program code. REM set verify off REM set echo off REM set feedback off REM Force diagnostic output on so that the user can see what progress REM is being made and what the program did. REM Increase the size of this buffer if your application is very large REM and you get a buffer-overflow error. SET SERVEROUTPUT ON SIZE 400000 SPOOL &p_filename..lis DECLARE -- rename_modules last_module_id ci_modules.id%TYPE; new_short_name ci_modules.short_name%TYPE; cannot_init_appsys EXCEPTION; -- Application not available or no -- access. tmp_short_name ci_functions.function_label%TYPE; -- To hold the new module short -- name so that we can safely check -- its length. duplicate_msg VARCHAR2(15); -- An array of module short_names to check for duplicates on new names. max_module_array_idx NUMBER; TYPE module_short_name_tabtype IS TABLE OF ci_modules.short_name%TYPE NOT NULL INDEX BY BINARY_INTEGER; -- An array of module_ids to allow proper identification of the name array above. TYPE module_id_tabtype IS TABLE OF ci_modules.id%TYPE NOT NULL INDEX BY BINARY_INTEGER; module_short_name_array module_short_name_tabtype; module_id_array module_id_tabtype; -- A cursor that will find all modules with only one source function. CURSOR find_modules IS SELECT RTRIM(cimodu.short_name) module_short_name ,cimodu.id module_id FROM ci_modules cimodu ,ci_module_functions cimoduf ,ci_application_systems ciapps WHERE cimodu.short_name LIKE UPPER('&p_module_short_name') AND cimodu.id = cimoduf.module_reference AND ciapps.id = cimodu.application_system_owned_by AND ciapps.name = UPPER('&p_application_system_name') AND ciapps.version = &p_application_system_version GROUP BY RTRIM(cimodu.short_name), cimodu.id HAVING COUNT(*) = 1 ORDER BY RTRIM(cimodu.short_name); -- When learning a PL/SQL program, it's best to start with the Main -- program section at the bottom of this PL/SQL block, then refer back -- to the inline functions and procedures as needed. /************* Inline Function ***********************************/ FUNCTION find_function_id (p_search_module_id IN NUMBER) RETURN NUMBER IS new_function_id ci_functions.id%TYPE; BEGIN SELECT cifunc.id INTO new_function_id FROM ci_functions cifunc ,ci_module_functions cimoduf WHERE cimoduf.module_reference = p_search_module_id AND cifunc.id = cimoduf.function_reference; RETURN new_function_id; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error : ' || SQLCODE || ', ' || SQLERRM); DBMS_OUTPUT.PUT_LINE('Procedure aborted with errors!'); DBMS_OUTPUT.PUT_LINE('FIND_FUNCTION_ID : ' || p_search_module_id); END; /************* End of Inline Function ****************************/ /************* Inline Procedure ***********************************/ PROCEDURE find_function_data (p_function_id IN NUMBER ,p_function_label OUT VARCHAR2 ) IS BEGIN SELECT RTRIM(cifunc.function_label) function_label INTO p_function_label FROM ci_functions cifunc WHERE cifunc.id = p_function_id; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error : ' || SQLCODE || ', ' || SQLERRM); DBMS_OUTPUT.PUT_LINE('Procedure aborted with errors!'); DBMS_OUTPUT.PUT_LINE('FIND_FUNCTION_DATA : ' || p_function_id); END; /************* End of Inline Procedure ****************************/ /************* Inline Procedure ***********************************/ PROCEDURE update_module_short_name (p_module_id IN NUMBER ,p_new_module_short_name IN OUT VARCHAR2 ) IS module_to_update ciomodule.data; activity_status VARCHAR2(1); activity_warning VARCHAR2(1); BEGIN -- Open an activity -- This issues a savepoint, so incomplete changes can be rolled back to here. CDAPI.OPEN_ACTIVITY; -- Set module values and their indicators. module_to_update.v.short_name := UPPER(RTRIM(p_new_module_short_name)); module_to_update.i.short_name := true; -- Update the module record. CIOMODULE.UPD(p_module_id, module_to_update); -- Validate the module insert. CDAPI.VALIDATE_ACTIVITY(activity_status, activity_warning); -- Show violation messages here. FOR each_violation IN (SELECT * FROM ci_violations) LOOP DBMS_OUTPUT.PUT_LINE(CDAPI.INSTANTIATE_MESSAGE(each_violation.facility ,each_violation.code ,each_violation.p0 ,each_violation.p1 ,each_violation.p2 ,each_violation.p3 ,each_violation.p4 ,each_violation.p5 ,each_violation.p6 ,each_violation.p7 ) ); END LOOP; -- Attempt to close the activity. CDAPI.CLOSE_ACTIVITY(activity_status); -- If activity did not close, then abort and roll back changes. IF activity_status != 'Y' THEN CDAPI.ABORT_ACTIVITY; END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error : ' || SQLCODE || ', ' || SQLERRM); DBMS_OUTPUT.PUT_LINE('Procedure aborted with errors!'); IF cdapi.activity IS NOT null THEN cdapi.abort_activity; END IF; END; /************* End of Inline Procedure ****************************/ /************* Inline Procedure ***********************************/ PROCEDURE output_old_name (p_old_module_short_name IN VARCHAR2 ) IS BEGIN DBMS_OUTPUT.PUT_LINE(p_old_module_short_name); END; /************* End of Inline Procedure ****************************/ /************* Inline Procedure ***********************************/ PROCEDURE output_new_name (p_new_module_short_name IN VARCHAR2 ) IS BEGIN DBMS_OUTPUT.PUT_LINE('<---' || p_new_module_short_name); END; /************* End of Inline Procedure ****************************/ /************* Inline Function ***********************************/ FUNCTION check_module_short_names (p_module_id IN NUMBER ,p_old_short_name IN VARCHAR2 ,p_new_short_name IN VARCHAR2 ) RETURN VARCHAR2 IS number_of_duplicate_modules NUMBER; update_array_idx NUMBER; BEGIN number_of_duplicate_modules := 0; IF p_new_short_name = p_old_short_name THEN RETURN 'Old = New'; ELSE FOR array_idx IN 1 .. max_module_array_idx LOOP IF p_new_short_name = module_short_name_array(array_idx) THEN number_of_duplicate_modules := number_of_duplicate_modules + 1; END IF; IF p_module_id = module_id_array(array_idx) THEN update_array_idx := array_idx; END IF; END LOOP; IF number_of_duplicate_modules > 0 THEN RETURN 'Too Many'; ELSE -- update the name so that it is included in the checks. module_short_name_array(update_array_idx) := p_new_short_name; RETURN 'OK'; END IF; END IF; END; /************* End of Inline Function ****************************/ /************* Inline Procedure ***********************************/ PROCEDURE setup_module_array IS CURSOR find_module_short_names IS SELECT RTRIM(cimodu.short_name) module_short_name ,cimodu.id module_id FROM ci_modules cimodu ,ci_application_systems ciapps WHERE ciapps.id = cimodu.application_system_owned_by AND ciapps.name = UPPER('&p_application_system_name') AND ciapps.version = &p_application_system_version ORDER BY cimodu.id; BEGIN max_module_array_idx := 0; FOR module_record IN find_module_short_names LOOP max_module_array_idx := max_module_array_idx + 1; module_short_name_array(max_module_array_idx) := module_record.module_short_name; module_id_array(max_module_array_idx) := module_record.module_id; END LOOP; END; /************* End of Inline Procedure ****************************/ /************* Start of Main Program Body ************************/ BEGIN BEGIN -- Initialize the Designer/2000 API. IF NOT cdapi.initialized THEN -- Not already initialized. CDAPI.INITIALIZE(UPPER('&p_application_system_name') ,&p_application_system_version); END IF; EXCEPTION WHEN OTHERS THEN RAISE cannot_init_appsys; END; -- We use a PL/SQL array (table) to store all module short names. -- We check this array later for duplicate short names. SETUP_MODULE_ARRAY; -- Fetch qualifying modules. FOR module_record IN find_modules LOOP OUTPUT_OLD_NAME(module_record.module_short_name); LOOP -- This should not occur the first time in the loop per column. -- We have an ultimate ancestor column we can grab a name from! FIND_FUNCTION_DATA(FIND_FUNCTION_ID(module_record.module_id),tmp_short_name); OUTPUT_NEW_NAME(tmp_short_name); -- Is the new name too long to update? IF LENGTH(tmp_short_name) > 20 THEN DBMS_OUTPUT.PUT_LINE(' ERROR: New Module Short Name too long!'); ELSE new_short_name := tmp_short_name; -- Check for duplicate module short names. duplicate_msg := CHECK_MODULE_SHORT_NAMES(module_record.module_id ,module_record.module_short_name ,new_short_name ); IF duplicate_msg = 'Too Many' THEN DBMS_OUTPUT.PUT_LINE(' ERROR: Duplicate New Name : ' || new_short_name ); ELSIF duplicate_msg = 'Old = New' THEN DBMS_OUTPUT.PUT_LINE(' FYI: Same Name.'); ELSE DBMS_OUTPUT.PUT_LINE(' FYI: Changed Name.'); -- Update database if instructed to do so. IF UPPER('&p_update_or_show') = 'U' THEN UPDATE_MODULE_SHORT_NAME(module_record.module_id ,new_short_name); END IF; END IF; END IF; EXIT; END LOOP; END LOOP; -- All changes made. DBMS_OUTPUT.PUT_LINE('DONE'); EXCEPTION WHEN cannot_init_appsys THEN DBMS_OUTPUT.PUT_LINE('Cannot Initialize API for the Application System: ' || UPPER('&p_application_system_name') ); DBMS_OUTPUT.PUT_LINE('Procedure aborted with errors!'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error : ' || SQLCODE || ', ' || SQLERRM); DBMS_OUTPUT.PUT_LINE('Procedure aborted with errors!'); END; -- rename_modules / SPOOL OFF