모바일 소스코드 삽입확인요
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 --처리결과 메시지
)
IS
USER_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' THEN
BEGIN
-- STEP 0. 변경된 것이 있는지 체크
BEGIN
SELECT 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 CHK
INTO V_DTTM
, V_CHK
FROM TD_BACKUP A
RIGHT OUTER JOIN USER_OBJECTS B
ON ( A.OBJECT_NAME = B.OBJECT_NAME
AND 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 THEN
V_CHK := NULL;
END;
IF NVL(TRIM(V_CHK),'N') = 'Y' THEN
RETURN;
END IF;
-- STEP 1.
FOR CUR_LIST IN (
SELECT *
FROM USER_SOURCE -- @DL_DEV
WHERE NAME = P_OBJECT
ORDER BY TYPE, LINE ASC
) LOOP
V_SQLSTR := V_SQLSTR || CUR_LIST.TEXT;
END LOOP;
EXCEPTION WHEN NO_DATA_FOUND THEN
V_ERROR_MSG := 'NO DATA';
WHEN OTHERS THEN
V_ERROR_MSG := 'DEV BACKUP ERR' || SQLCODE || ' / ' || SQLERRM;
END;
ELSIF P_DR_DIV = 'REAL' THEN
BEGIN
-- STEP 0. 변경된 것이 있는지 체크
BEGIN
SELECT 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 CHK
INTO V_DTTM
, V_CHK
FROM TD_BACKUP A -- @DL_REAL
RIGHT OUTER JOIN USER_OBJECTS@DL_REAL B
ON ( A.OBJECT_NAME = B.OBJECT_NAME
AND 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 THEN
V_CHK := NULL;
END;
IF NVL(TRIM(V_CHK),'N') = 'Y' THEN
RETURN;
END IF;
-- STEP 1.
FOR CUR_LIST IN (
SELECT *
FROM USER_SOURCE@DL_REAL -- @DL_DEV
WHERE NAME = P_OBJECT
ORDER BY TYPE, LINE ASC
) LOOP
V_SQLSTR := V_SQLSTR || CUR_LIST.TEXT;
END LOOP;
EXCEPTION WHEN NO_DATA_FOUND THEN
V_ERROR_MSG := 'NO DATA';
WHEN OTHERS THEN
V_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 THEN
RAISE USER_DEF_EXP;
END IF;
BEGIN
INSERT 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 THEN
NULL;
END;
EXCEPTION
WHEN 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
*
*------------------------------------------------------------------------------------------------*/
IS
USER_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
-- STEP
FOR CUR_LIST IN (
SELECT DISTINCT NAME AS NAME
FROM 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 NAME
FROM 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 THEN
V_ERROR_MSG := 'NO DATA';
WHEN OTHERS THEN
V_ERROR_MSG := 'XXXXXXXXXXXXXXXXXXXXXX' || SQLCODE || ' / ' || SQLERRM;
END;
--V_ERROR_MSG := V_ERROR_MSG || 'MOJI';
IF TRIM(V_ERROR_MSG) IS NOT NULL THEN
RAISE USER_DEF_EXP;
END IF;
EXCEPTION
WHEN 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;
