REM Program: keyreseq.sql

REM Author : David Wendelken

REM          Tactics, Inc.  (404) 248-1226

REM          Part of the Tactics' Designer/2000 Toolbox

REM Genesis: 26-Nov-1995

REM

REM Purpose: To make sure that primary, unique and foreign key constraint component

REM          columns are sequenced from 1 to N on a per constraint basis.  It is an

REM          enabling program for the d2pkchk.sql and d2fkchk.sql utilities.

REM Warning: Do not run this if others are the above mentioned constraintsin the 

REM          same application.

REM Warning: If you have duplicate sequence numbers for columns in a key constraint,

REM          they will be resequenced.  Naturally, this means that one will follow 

REM          the other.  Garbage In, Garbage Out!



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 Table Name or % for ALL

DEFINE p_table_name='&3'

PROMPT Enter the output filename (with the pathname but without the .lis extension)

DEFINE p_filename='&6'



set linesize  80

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 Adjust buffer size up or down as needed.

SET SERVEROUTPUT ON SIZE 50000

SPOOL &p_filename..lis



DECLARE -- resequence key component columns.

  cannot_init_appsys   EXCEPTION;        -- Application not available or no access.

  next_sequence_number ci_key_components.sequence_number%TYPE;

  last_constraint_id   ci_constraints.id%TYPE;



-- A cursor that will return 1 row per column that participates in a key constraint.

  CURSOR find_kc_columns IS

  SELECT  cicon.id               constraint_id

         ,cikeyc.id              key_component_id

         ,cikeyc.sequence_number key_component_sequence_number

         ,citabd.name            table_name

         ,cicon.name             constraint_name

  FROM    ci_key_components      cikeyc

         ,ci_constraints         cicon

         ,ci_table_definitions   citabd

         ,ci_application_systems ciapps

  WHERE   citabd.name    LIKE UPPER('&p_table_name')

    AND   citabd.id      =    cicon.table_reference

    AND   cicon.id       =    cikeyc.constraint_reference

    AND   ciapps.id      =    citabd.application_system_owned_by

    AND   ciapps.name    =    UPPER('&p_application_system_name')

    AND   ciapps.version =    &p_application_system_version

  ORDER BY citabd.name, cicon.name, cikeyc.sequence_number;



/************* Inline Procedure ***********************************/

PROCEDURE update_kc_sequence_number

(p_kc_id              IN  NUMBER

,p_kc_sequence_number IN  NUMBER

) IS



kc_to_update        ciokey_component.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;

  -- We update regardless of whether the old and new sequence numbers are equal.



  -- Set key component values and their indicators.

  kc_to_update.v.sequence_number := p_kc_sequence_number;

  kc_to_update.i.sequence_number := true;

  -- Update the key component record.

  CIOKEY_COMPONENT.UPD(p_kc_id, kc_to_update);

  -- Validate the key component update.

  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;

END;

/************* End of Inline Procedure ****************************/



/****************** Main Program ********************/

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;

  -- Fetch specified attributes 

  FOR kc_record IN find_kc_columns

  LOOP

    IF last_constraint_id IS NULL

    OR last_constraint_id != kc_record.constraint_id THEN

       last_constraint_id := kc_record.constraint_id;

       next_sequence_number := 1;

       DBMS_OUTPUT.PUT_LINE('Table     : ' || kc_record.table_name );

       DBMS_OUTPUT.PUT_LINE('Constraint: ' || kc_record.constraint_name );

    ELSE

       next_sequence_number := next_sequence_number + 1;

    END IF;

    DBMS_OUTPUT.PUT_LINE('Old Seq.  : ' || kc_record.key_component_sequence_number);

    DBMS_OUTPUT.PUT_LINE('New Seq.  : ' || next_sequence_number);

    UPDATE_KC_SEQUENCE_NUMBER(kc_record.key_component_id, next_sequence_number);

  END LOOP;

  -- All changes made.

  DBMS_OUTPUT.PUT_LINE('DONE');

EXCEPTION

  WHEN cannot_init_appsys THEN

    DBMS_OUTPUT.PUT_LINE('ERROR: 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;  -- resequence key component columns

/

/****************** End of Main Program ************************/



SPOOL OFF