모바일 소스코드 삽입확인요
CREATE OR REPLACE PROCEDURE PD_BACKUP
/*--------------------------------------------------------------------------------------------------* TYPE : PROCEDURE* LANGUAGE : PL/SQL* BUSINESS :* DESCRIPTION : DEV껄 운영으로 옴기는 작업.* HISTORY : 2019.10.02 CREATE : LSJ**------------------------------------------------------------------------------------------------*/(P_OBJECT IN VARCHAR2, P_DR_DIV IN VARCHAR2 DEFAULT 'DEV'--, O_RESULT OUT NOCOPY VARCHAR2 --OK OR NG--, O_MSG OUT NOCOPY VARCHAR2 --처리결과 메시지)ISUSER_DEF_EXP EXCEPTION;V_ERROR_MSG VARCHAR2(4000) := NULL; --에러 메시지V_PROCEDURE_NAME VARCHAR2(40) := $$PLSQL_UNIT;V_DTTM DATE; --TD_BAKCUP.DTTM%TYPE;V_SQLSTR CLOB;V_CHK VARCHAR2(10);
BEGIN-- STEP 1.V_SQLSTR := '';
IF P_DR_DIV = 'DEV' THENBEGIN-- STEP 0. 변경된 것이 있는지 체크BEGINSELECT MAX(B.LAST_DDL_TIME) -- TO_CHAR( B.LAST_DDL_TIME, 'YYYY-MM-DD HH24:MI:SS' ) AS LAST_DDL_TIME--, NVL2(MAX(A.DTTM),'Y','N') AS CHK, CASE WHEN MAX(B.LAST_DDL_TIME) = MAX(A.LAST_DTTM) THEN 'Y'ELSE 'N' END AS CHKINTO V_DTTM, V_CHKFROM TD_BACKUP ARIGHT OUTER JOIN USER_OBJECTS BON ( A.OBJECT_NAME = B.OBJECT_NAMEAND A.DR_DIV = 'DEV')WHERE 1=1--AND B.OBJECT_TYPE IN ( 'FUNCTION', 'PROCEDURE', 'PACKAGE BODY', 'TRIGGER' )--AND TO_CHAR( B.LAST_DDL_TIME, 'YYYY-MM-DD HH24:MI:SS' ) > '2019-10-06 00:00:00'AND B.OBJECT_NAME = P_OBJECT --'PKG_PMBIZ';
EXCEPTION WHEN NO_DATA_FOUND THENV_CHK := NULL;END;
IF NVL(TRIM(V_CHK),'N') = 'Y' THENRETURN;END IF;
-- STEP 1.FOR CUR_LIST IN (SELECT *FROM USER_SOURCE -- @DL_DEVWHERE NAME = P_OBJECTORDER BY TYPE, LINE ASC) LOOPV_SQLSTR := V_SQLSTR || CUR_LIST.TEXT;
END LOOP;
EXCEPTION WHEN NO_DATA_FOUND THENV_ERROR_MSG := 'NO DATA';
WHEN OTHERS THENV_ERROR_MSG := 'DEV BACKUP ERR' || SQLCODE || ' / ' || SQLERRM;END;
ELSIF P_DR_DIV = 'REAL' THENBEGIN-- STEP 0. 변경된 것이 있는지 체크BEGINSELECT MAX(B.LAST_DDL_TIME) -- TO_CHAR( B.LAST_DDL_TIME, 'YYYY-MM-DD HH24:MI:SS' ) AS LAST_DDL_TIME--, NVL2(MAX(A.DTTM),'Y','N') AS CHK, CASE WHEN MAX(B.LAST_DDL_TIME) = MAX(A.LAST_DTTM) THEN 'Y'ELSE 'N' END AS CHKINTO V_DTTM, V_CHKFROM TD_BACKUP A -- @DL_REALRIGHT OUTER JOIN USER_OBJECTS@DL_REAL BON ( A.OBJECT_NAME = B.OBJECT_NAMEAND A.DR_DIV = 'REAL')WHERE 1=1--AND B.OBJECT_TYPE IN ( 'FUNCTION', 'PROCEDURE', 'PACKAGE BODY', 'TRIGGER' )--AND TO_CHAR( B.LAST_DDL_TIME, 'YYYY-MM-DD HH24:MI:SS' ) > '2019-10-06 00:00:00'AND B.OBJECT_NAME = P_OBJECT --'PKG_name';
EXCEPTION WHEN NO_DATA_FOUND THENV_CHK := NULL;END;
IF NVL(TRIM(V_CHK),'N') = 'Y' THENRETURN;END IF;
-- STEP 1.FOR CUR_LIST IN (SELECT *FROM USER_SOURCE@DL_REAL -- @DL_DEVWHERE NAME = P_OBJECTORDER BY TYPE, LINE ASC) LOOPV_SQLSTR := V_SQLSTR || CUR_LIST.TEXT;
END LOOP;
EXCEPTION WHEN NO_DATA_FOUND THENV_ERROR_MSG := 'NO DATA';
WHEN OTHERS THENV_ERROR_MSG := 'REAL BACKUP ERR' || SQLCODE || ' / ' || SQLERRM;END;
END IF;
--V_ERROR_MSG := V_ERROR_MSG || 'MOJI';IF TRIM(V_ERROR_MSG) IS NOT NULL THENRAISE USER_DEF_EXP;END IF;
BEGININSERT INTO TD_BACKUP (OBJECT_NAME, DTTM, TEXT, LAST_DTTM, DR_DIV) VALUES (P_OBJECT, SYSDATE --V_DTTM -- SYSDATE, V_SQLSTR, V_DTTM, P_DR_DIV --'REAL');EXCEPTION WHEN OTHERS THENNULL;END;
EXCEPTIONWHEN USER_DEF_EXP THEN--O_RESULT := 'NG';
RAISE_APPLICATION_ERROR(-20001, V_ERROR_MSG);
WHEN OTHERS THEN--O_RESULT := 'NG';V_ERROR_MSG := V_PROCEDURE_NAME || ' => ' || SQLCODE || ' / ' || SQLERRM;
RAISE_APPLICATION_ERROR(-20002, V_ERROR_MSG);END;
CREATE OR REPLACE PROCEDURE PD_BACKUP_ALL/*-------------------------------------------------------------------------------------------------* TYPE : PROCEDURE* LANGUAGE : PL/SQL* BUSINESS :* DESCRIPTION : DEV껄 운영으로 옴기는 작업.* HISTORY : 2019.10.02 CREATE : LSJ**------------------------------------------------------------------------------------------------*/ISUSER_DEF_EXP EXCEPTION;V_ERROR_MSG VARCHAR2(4000) := NULL; --에러 메시지V_PROCEDURE_NAME VARCHAR2(40) := $$PLSQL_UNIT;
V_SQLSTR CLOB;
BEGIN-- STEP 1.V_SQLSTR := '';BEGIN-- STEPFOR CUR_LIST IN (SELECT DISTINCT NAME AS NAMEFROM USER_SOURCE -- @DL_DEV) LOOP--V_SQLSTR := V_SQLSTR || CUR_LIST.TEXT;PD_BACKUP(CUR_LIST.NAME);
END LOOP;
-- 운영 데이터 백업FOR CUR_LIST IN (SELECT DISTINCT NAME AS NAMEFROM USER_SOURCE@DL_REAL -- @DL_DEV) LOOP--V_SQLSTR := V_SQLSTR || CUR_LIST.TEXT;PD_BACKUP(CUR_LIST.NAME, 'REAL');
END LOOP;
EXCEPTION WHEN NO_DATA_FOUND THENV_ERROR_MSG := 'NO DATA';
WHEN OTHERS THENV_ERROR_MSG := 'XXXXXXXXXXXXXXXXXXXXXX' || SQLCODE || ' / ' || SQLERRM;END;--V_ERROR_MSG := V_ERROR_MSG || 'MOJI';IF TRIM(V_ERROR_MSG) IS NOT NULL THENRAISE USER_DEF_EXP;END IF;
EXCEPTIONWHEN USER_DEF_EXP THEN--O_RESULT := 'NG';
RAISE_APPLICATION_ERROR(-20001, V_ERROR_MSG);
WHEN OTHERS THEN--O_RESULT := 'NG';V_ERROR_MSG := V_PROCEDURE_NAME || ' => ' || SQLCODE || ' / ' || SQLERRM;
RAISE_APPLICATION_ERROR(-20002, V_ERROR_MSG);END;