Untitled
def add_deploy_header(deployFile, schema, validation_schema): """ Add deployment header with environment settings and schema validation """ header = f"""spool Deploy_fk_trigger_{schema.lower()}.log set serveroutput on size unlimited set sqlblankline on set timing on ALTER SESSION SET CURRENT_SCHEMA={schema} / ALTER SESSION SET ddl_lock_timeout=1800 / -- deploy detail print SELECT '-----------------------------------------------------------------'||chr(10)|| 'DB name : '||sys_context('userenv','db_name')||chr(10)|| 'Instance name : '||sys_context('userenv','instance_name')||chr(10)|| 'Service name : '||sys_context('userenv','service_name')||chr(10)|| 'current schema : '||sys_context('userenv','current_schema')||chr(10)|| 'user : '||sys_context('userenv','current_user')||chr(10)|| 'started at : '||systimestamp||chr(10)|| '------------------------------------------------------------------' as deploy_detail from dual / BEGIN dbms_utility.compile_schema('REFDATA', FALSE); dbms_utility.compile_schema('NSRD', FALSE); dbms_utility.compile_schema('REPL_APM', FALSE); dbms_utility.compile_schema('APM', FALSE); dbms_utility.compile_schema('{validation_schema}', FALSE); END; / DECLARE v_type dba_objects.object_type%TYPE; v_schema dba_objects.owner%TYPE; BEGIN DBMS_OUTPUT.PUT_LINE('Invalid Objects'); DBMS_OUTPUT.PUT_LINE('==============='); FOR r IN (SELECT OBJECT_NAME, OBJECT_TYPE, owner FROM DBA_OBJECTS WHERE OWNER IN ('REFDATA', 'NSRD', 'REPL_APM', 'APM', '{validation_schema}') AND STATUS='INVALID' ORDER BY 2,1) LOOP IF v_schema != r.owner OR v_schema IS NULL THEN DBMS_OUTPUT.PUT_LINE(chr(10)); DBMS_OUTPUT.NEW_LINE(); DBMS_OUTPUT.PUT_LINE('----------------------'); DBMS_OUTPUT.PUT_LINE(''||r.owner); DBMS_OUTPUT.PUT_LINE('----------------------'); END IF; IF v_type != r.object_type OR v_type IS NULL THEN DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE(''||r.object_type); END IF; DBMS_OUTPUT.PUT_LINE('--'||r.object_name); v_type := r.object_type; v_schema := r.owner; END LOOP; END; / """ sql2script(deployFile, header, True) def add_deploy_footer(deployFile, validation_schema): """ Add deployment footer with final schema validation and timestamp """ footer = """ BEGIN dbms_utility.compile_schema('REFDATA', FALSE); dbms_utility.compile_schema('NSRD', FALSE); dbms_utility.compile_schema('REPL_APM', FALSE); dbms_utility.compile_schema('APM', FALSE); dbms_utility.compile_schema('{validation_schema}', FALSE); END; / DECLARE v_type dba_objects.object_type%TYPE; v_schema dba_objects.owner%TYPE; BEGIN DBMS_OUTPUT.PUT_LINE('Invalid Objects'); DBMS_OUTPUT.PUT_LINE('==============='); FOR r IN (SELECT OBJECT_NAME, OBJECT_TYPE, owner FROM DBA_OBJECTS WHERE OWNER IN ('REFDATA', 'NSRD', 'REPL_APM', 'APM', '{validation_schema}') AND STATUS='INVALID' ORDER BY 2,1) LOOP IF v_schema != r.owner OR v_schema IS NULL THEN DBMS_OUTPUT.PUT_LINE(chr(10)); DBMS_OUTPUT.NEW_LINE(); DBMS_OUTPUT.PUT_LINE('----------------------'); DBMS_OUTPUT.PUT_LINE(''||r.owner); DBMS_OUTPUT.PUT_LINE('----------------------'); END IF; IF v_type != r.object_type OR v_type IS NULL THEN DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE(''||r.object_type); END IF; DBMS_OUTPUT.PUT_LINE('--'||r.object_name); v_type := r.object_type; v_schema := r.owner; END LOOP; END; / SELECT SYSTIMESTAMP FROM DUAL / SPOOL OFF""".format(validation_schema=validation_schema) sql2script(deployFile, footer, True)
Leave a Comment