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

