REM Program: samecmt.sql REM Author : Chester West II REM Tactics, Inc. (704) 423-0220 REM [Charlotte, NC Office] REM Part of the Tactics' Designer/2000 Toolbox REM Genesis: 15-Sep-1996 REM REM Purpose: To copy a domain's description, if the first character REM is a tilda '~', to the entity attribute comment text REM and to the table column comment and hint text, for items REM in the domain. REM REM Note: 1)When this is run prior to DB-Design Wizard run, the entity REM attribute comment text will be copied to the table column REM comment and hint text automatically by the DB-Design Wizard. REM 2)Restrict your domain description to 80 characters or less REM since this is all that is allowed in the comment text for REM attributes. REM REM Warning: Although this can be run repeatedly for a given application, REM it will override any manually crafted attribute comment text REM and column comment and hint text. 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 Domain Name or % for ALL DEFINE p_domain_name='&3' PROMPT Enter Entity Name or % for ALL DEFINE p_entity_name='&4' PROMPT Enter Table Name or % for ALL DEFINE p_table_name='&5' PROMPT (U)pdate names or just (S)how and tell DEFINE p_update_or_show='&6' PROMPT Enter the output filename (without the .lis extension) DEFINE p_filename='&7' 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 -- Distribute domain comment text cannot_init_appsys EXCEPTION; CURSOR get_domains IS SELECT ci_dom.id id, ci_dom.name name, ci_dom.description description FROM ci_domains ci_dom, ci_application_systems ci_apps WHERE ci_apps.id = ci_dom.application_system_owned_by AND ci_apps.name = UPPER('&p_application_system_name') AND ci_apps.version = &p_application_system_version AND ci_dom.name LIKE UPPER('&p_domain_name') AND SUBSTR(RTRIM(LTRIM(ci_dom.description)),1,1)='~'; activity_status VARCHAR2(1); activity_warning VARCHAR2(1); /************* Inline Function ***********************************/ FUNCTION init_api RETURN boolean IS 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; RETURN TRUE; EXCEPTION WHEN OTHERS THEN RETURN FALSE; END; /************* End of Inline Function ****************************/ /************* Inline Procedure ***********************************/ PROCEDURE update_attributes (dom_id IN ci_domains.id%TYPE, dom_txt IN ci_attributes.notes%TYPE) IS attribute_to_update CIOATTRIBUTE.DATA; CURSOR get_attrs IS SELECT ci_attr.id id, ci_attr.name name, ci_ent.name ent_name FROM ci_attributes ci_attr, ci_entities ci_ent, ci_application_systems ci_apps WHERE ci_apps.id = ci_ent.application_system_owned_by AND ci_apps.name = UPPER('&p_application_system_name') AND ci_apps.version = &p_application_system_version AND ci_ent.id = ci_attr.entity_reference AND ci_ent.name LIKE UPPER('&p_entity_name') AND ci_attr.domain_reference = dom_id ORDER BY ci_ent.name, ci_attr.name; BEGIN FOR curr_attr IN get_attrs LOOP DBMS_OUTPUT.PUT_LINE('Attribute==>'||curr_attr.ent_name||'.'||curr_attr.name); IF UPPER(RTRIM('&p_update_or_show')) = 'U' THEN -- Open an activity -- This issues a savepoint, so incomplete changes -- can be rolled back to here. CDAPI.OPEN_ACTIVITY; -- Set attribute values and their indicators. attribute_to_update.v.notes := dom_txt; attribute_to_update.i.notes := TRUE; -- Update the attribute record. CIOATTRIBUTE.UPD(curr_attr.id, attribute_to_update); -- Validate the attribute 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; END IF; END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error : ' || SQLCODE || ', ' || SQLERRM); DBMS_OUTPUT.PUT_LINE('Procedure UPDATE_ATTRIBUTES aborted with errors!'); IF cdapi.activity IS NOT null THEN cdapi.abort_activity; END IF; END; /************* End of Inline Procedure ****************************/ /************* Inline Procedure ***********************************/ PROCEDURE update_columns (dom_id IN ci_domains.id%TYPE, dom_txt IN ci_columns.remark%TYPE) IS column_to_update CIOCOLUMN.DATA; CURSOR get_cols IS SELECT ci_col.id id, ci_col.name name, ci_tbl.name tbl_name FROM ci_columns ci_col, ci_table_definitions ci_tbl, ci_application_systems ci_apps WHERE ci_apps.id = ci_tbl.application_system_owned_by AND ci_apps.name = UPPER('&p_application_system_name') AND ci_apps.version = &p_application_system_version AND ci_tbl.id = ci_col.table_reference AND ci_tbl.name LIKE UPPER('&p_table_name') AND ci_col.domain_reference = dom_id; BEGIN FOR curr_col IN get_cols LOOP DBMS_OUTPUT.PUT_LINE('Column==>'||curr_col.tbl_name||'.'||curr_col.name); IF UPPER(RTRIM('&p_update_or_show')) = 'U' THEN -- Open an activity -- This issues a savepoint, so incomplete changes -- can be rolled back to here. CDAPI.OPEN_ACTIVITY; -- Set column values and their indicators. column_to_update.v.remark := dom_txt; column_to_update.i.remark := TRUE; column_to_update.v.help_text := SUBSTR(dom_txt,1,132); column_to_update.i.help_text := TRUE; -- Update the attribute record. CIOCOLUMN.UPD(curr_col.id, column_to_update); -- Validate the column 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; END IF; END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error : ' || SQLCODE || ', ' || SQLERRM); DBMS_OUTPUT.PUT_LINE('Procedure UPDATE_COLUMNS aborted with errors!'); IF cdapi.activity IS NOT null THEN cdapi.abort_activity; END IF; END; /************* End of Inline Procedure ****************************/ /************* Start of Main Program Body ************************/ BEGIN IF NOT init_api THEN RAISE cannot_init_appsys; ELSE FOR curr_domain IN get_domains LOOP DBMS_OUTPUT.PUT(curr_domain.name); DBMS_OUTPUT.PUT('->'); DBMS_OUTPUT.PUT_LINE(curr_domain.description); -- Process attributes in domain. update_attributes(curr_domain.id, SUBSTR(RTRIM(LTRIM(curr_domain.description)),2,81)); -- Process columns in domain. update_columns(curr_domain.id, SUBSTR(RTRIM(LTRIM(curr_domain.description)),2,240)); END LOOP; 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; /