CREATE OR REPLACE PROCEDURE del_text ( v_workarea IN VARCHAR2 ,v_app_name IN VARCHAR2 ,b_full_log IN BOOLEAN := FALSE ,b_delete IN BOOLEAN := FALSE ) IS --============================================================================ -- Anonymous Submission to OracleTricks.coom; --============================================================================= -- -- This script checks for all multi-line pproperties of all -- general modules in an application systeem, if one of the lines -- contains the single character '.', thiss line will be deleted -- -- Example based on CI_GENERAL_MODULES -- -- This procedure is called with 4 parametters: -- v_workarea = Name of the workarea -- v_app_name = Name of the application system in the workarea -- b_full_log = If TRUE a detailed logfiile will be written -- otherwise only module shhortnames will be -- mentioned. -- Default is FALSE. -- b_delete = If TRUE the lines will bbe really deleted -- otherwise only objects wwill be listed. -- Default is FALSE. -- -- How to run this procedure: -- -- SET SERVEROUTPUT ON -- SPOOL del_text.log -- EXECUTE del_text('WorkArea','Applicattion System'); -- SPOOL OFF -- -- P.S. A tip: replace "rmotextp.truncalll(i_mod_irid, v_txt_type);" with your purge -- or force purge call. Use IRID in the ffirst case and IVID in the second. -- Remove or replace cursors and other sttuff as necessary. -- Explanation: Sorry, I'm not publishingg my actual purge scripts here, -- to avoid their use by an unexperiencedd Repository user... -- --============================================================================= -- MODIFICATION HISTORY -- Person Date Comments -- --------- ---------- -------------------------------------------- -- Anonymous 08/28/2002 Initial Submissiion to OracleTricks.com; --============================================================================= /* Variables for API */ v_status VARCHAR2 (1); /* status of api-transaction */ v_warnings VARCHAR2 (240); /* text warnings */ v_text VARCHAR2 (240); /* text line */ i_appsys_ivid INTEGER; /* Application System Version Identifier */ i_mod_irid INTEGER; /* Module Internal Record Identifier IRID */ i_mod_ivid INTEGER; /* Module Version Identifier */ v_mod_short_name VARCHAR2 (20); /* Module Short Name */ i_txt_irid INTEGER; /* Irid of Modules current Text Line */ v_txt_type VARCHAR2 (10); /* Text Type of Modules current Text Line */ v_element_type_for VARCHAR2 (6); /* Element Type for current Text Line */ -- Cursor for Target Application System IDD CURSOR cur_app IS SELECT ivid FROM ci_application_systems WHERE NAME = v_app_name; -- Cursor for all Modules in Application SSystem CURSOR cur_mod IS SELECT p.irid ,p.ivid ,p.short_name FROM i$sdd_object_versions po ,ci_general_modules p ,sdd_folder_members mem WHERE p.ivid = po.ivid AND mem.member_object = p.irid AND mem.parent_ivid = i_appsys_ivid ORDER BY p.short_name; -- Cursor for all Text Lines of current Moodule, -- ONLY delete Lines containing a single DDot CURSOR cur_text IS SELECT irid ,txt_type ,element_type_for FROM rm_text_lines WHERE parent_ivid = i_mod_ivid AND txt_text = '.' ORDER BY txt_type; BEGIN DBMS_OUTPUT.ENABLE (1000000); /* Maximum Buffer size = 1.000.000 Bytes */ /* Set Context to Workarea according to Parameter v_workarea */ jr_context.set_workarea (v_workarea); /* assign Application System Id */ OPEN cur_app; FETCH cur_app INTO i_appsys_ivid; IF (cur_app%NOTFOUND) THEN DBMS_OUTPUT.put_line ('!!! Appsys ' || v_app_name || ' not found'); RETURN; END IF; CLOSE cur_app; DBMS_OUTPUT.put_line ('Appsys: ' || v_app_name); /* Initialize Designer Api with Fokus to Target Appsys and Version */ cdapi.initialize (v_app_name); /* Loop for all Modules of Appsys */ FOR cur_mod_rec IN cur_mod LOOP /* Current Module Ivid and Short Name */ i_mod_irid := cur_mod_rec.irid; i_mod_ivid := cur_mod_rec.ivid; v_mod_short_name := cur_mod_rec.short_name; DBMS_OUTPUT.put_line (i_mod_ivid || ' ' || v_mod_short_name); /* Loop for all Text Lines of current Module */ FOR cur_text_rec IN cur_text LOOP /* Modules current Text Irid and Type */ i_txt_irid := cur_text_rec.irid; v_txt_type := cur_text_rec.txt_type; v_element_type_for := cur_text_rec.element_type_for; /* If cleanup Flag is set */ IF (b_delete = TRUE) THEN DBMS_OUTPUT.put_line ( i_txt_irid || ' ' || v_txt_type || ' ' || v_element_type_for ); cdapi.open_activity; rmotextp.truncall (i_mod_irid, v_txt_type); /* Validate the activity to turn up outstanding constraint violations */ cdapi.validate_activity (v_status, v_warnings); /* Report all violations regardless of the activity status */ FOR viol IN (SELECT * FROM ci_violations) LOOP IF viol.warning = 'Y' THEN v_text := 'WNG: '; ELSE v_text := 'ERR: '; END IF; DBMS_OUTPUT.put_line ( v_text || cdapi.instantiate_message ( viol.facility ,viol.code ,viol.p0 ,viol.p1 ,viol.p2 ,viol.p3 ,viol.p4 ,viol.p5 ,viol.p6 ,viol.p7 ) ); END LOOP; /* Check for any non-fatal messages that might have been posted */ WHILE cdapi.stacksize > 0 LOOP DBMS_OUTPUT.put_line (cdapi.pop_instantiated_message); END LOOP; /* viol */ cdapi.close_activity (v_status); IF v_status <> 'Y' THEN cdapi.abort_activity; DBMS_OUTPUT.put_line ( '!!! Activity aborted with constraint violations' ); ELSE IF b_full_log = TRUE THEN DBMS_OUTPUT.put_line ('Deleted Text Line successfully'); END IF; /* Full Log */ END IF; END IF; /* Flag to delete Text Lines B_DELETE is True */ END LOOP; /* Loop for all Text Lines of Module */ END LOOP; /* Loop for all Modules of Application System */ EXCEPTION WHEN OTHERS THEN -- If any messages have been posted on the stack, then print them now -- and then roll back all changes made during the activity IF cdapi.stacksize > 0 THEN -- Print all messages on the API stack WHILE cdapi.stacksize > 0 LOOP DBMS_OUTPUT.put_line (cdapi.pop_instantiated_message); END LOOP; IF cdapi.activity IS NOT NULL THEN cdapi.abort_activity; DBMS_OUTPUT.put_line ('Activity aborted with API errors'); ELSE DBMS_OUTPUT.put_line ('API Session aborted with API errors'); END IF; -- Otherwise, this must have been an ORACLE SQL or internal error so -- roll back all changes made during the activity and re-raise the -- exception ELSE IF cdapi.activity IS NOT NULL THEN cdapi.abort_activity; DBMS_OUTPUT.put_line ( 'Activity aborted with ORACLE internal errors' ); ELSE DBMS_OUTPUT.put_line ( 'API Session aborted with ORACLE internal errors' ); END IF; RAISE; END IF; END del_text; /* Procedure del_text: Delete Text Lines containing '.' */ /