데이터베이스 질문과 답변
by 소연짱 [2017.10.26 18:05:35]
안녕하세요.
기존에 사용했던 쿼리에 동적으로 데이터 값을 받아와야 할 부분이 있어서
EXECUTE IMMEDIATE 사용 하였는데.. 처음 보는 에러가 발생하여 문의 드립니다.
에러내용 :
[ERR0000]The Promote method returned an invalid value for the distributed transaction.
[Source]System.Transactions
해당 볼트체 부분을 주석처리 하면 에러없이 실행이 잘됩니다.
그리고 1개의 데이터를 저장 할 경우 에러없이 저장이 잘되지만
루프 2개 이상 데이터를 저장 할 경우 해당 에러가 발생합니다.
원인이 무엇인지 정말 모르겠습니다. 선배님들의 조언 부탁드리겠습니다
감사합니다.
프로시져
CREATE OR REPLACE PROCEDURE MES.P_MCC_PROD_CONFIRM_PLAN_S
(
V_P_WORK_TYPE IN VARCHAR2,
V_P_TPS_LINE_PLAN_ID IN TBL_MCC_PROD_CONFIRM_PLAN.TPS_LINE_PLAN_ID%TYPE,
V_P_TPS_LINE_CODE IN TBL_MCC_PROD_CONFIRM_PLAN.TPS_LINE%TYPE,
V_P_TPS_DATE IN TBL_MCC_PROD_CONFIRM_PLAN.TPS_DATE%TYPE,
V_P_MODEL_CODE IN TBL_MCC_PROD_CONFIRM_PLAN.MODEL_CODE%TYPE,
V_P_STYLE_CODE IN TBL_MCC_PROD_CONFIRM_PLAN.STYLE_CODE%TYPE,
V_P_GENDER_CODE IN TBL_MCC_PROD_CONFIRM_PLAN.GENDER_CODE%TYPE,
V_P_PART_CODE IN TBL_MCC_PROD_CONFIRM_PLAN.PART_CODE%TYPE,
V_P_PART_NAME IN TBL_MCC_PROD_CONFIRM_PLAN.PART_NAME%TYPE,
V_P_MATERIAL_CODE IN TBL_MCC_PROD_CONFIRM_PLAN.MATERIAL_CODE%TYPE,
V_P_MATERIAL_NAME IN TBL_MCC_PROD_CONFIRM_PLAN.MATERIAL_NAME%TYPE,
V_P_SIZE_CODE IN TBL_MCC_PROD_CONFIRM_PLAN.SIZE_CODE%TYPE,
V_P_TPS_QTY IN TBL_MCC_PROD_CONFIRM_PLAN.TPS_QTY%TYPE,
V_P_TOTAL_CONFRIM_QTY IN TBL_MCC_PROD_CONFIRM_PLAN.TOTAL_CONFIRM_QTY%TYPE,
V_P_CONFIRM_QTY IN TBL_MCC_PROD_CONFIRM_PLAN.CONFIRM_QTY%TYPE,
V_P_EQUIPMENT_TYPE IN TBL_MCC_PROD_CONFIRM_PLAN.EQUIPMENT_TYPE%TYPE,
V_P_EQUIPMENT_NAME IN TBL_MCC_PROD_CONFIRM_PLAN.EQUIPMENT_CODE%TYPE,
V_P_USER_ECRP IN TBL_MCC_PROD_CONFIRM_PLAN.CREATE_USER%TYPE,
V_P_ERROR_CODE OUT VARCHAR2, -- Return error codes
V_P_ROW_COUNT OUT NUMBER, -- Record number of lines that run / return
V_P_ERROR_NOTE OUT NVARCHAR2, -- Custom String
V_P_RETURN_STR OUT NVARCHAR2, -- Custom Return
V_P_ERROR_STR OUT NVARCHAR2, -- Error Messages
V_ERRORSTATE OUT VARCHAR2, -- Error number / severity / error status code line number of the error occurring routines
V_ERRORPROCEDURE OUT NVARCHAR2
)
IS
BEGIN
DECLARE
V_P_SEQ VARCHAR2(20);
V_P_CNT NUMBER(10);
V_P_F_CLOSE VARCHAR2(2);
V_SELECT_TPS_QTY VARCHAR2(2000);
P_TPS_SIZE_QTY NUMBER := 0;
P_GATHER_QTY NUMBER := 0;
BEGIN
IF V_P_WORK_TYPE = 'S' THEN
V_SELECT_TPS_QTY := 'SELECT TPS_S' || V_P_SIZE_CODE || '' || ' FROM MES.TPS_LINE_PLAN@INF_M_E' || ' WHERE TPS_LINE_PLAN_ID = ' || ''''
|| V_P_TPS_LINE_PLAN_ID || ''''; // DB링크
EXECUTE IMMEDIATE V_SELECT_TPS_QTY INTO P_TPS_SIZE_QTY;
IF P_TPS_SIZE_QTY > V_P_CONFIRM_QTY THEN
P_GATHER_QTY := P_TPS_SIZE_QTY - V_P_CONFIRM_QTY;
ELSIF V_P_TPS_QTY = V_P_CONFIRM_QTY THEN
V_P_F_CLOSE := 'Y';
ELSIF V_P_TPS_QTY < V_P_CONFIRM_QTY THEN
V_P_ERROR_CODE := 'P_MCC_PROD_CONFIRM_PLAN_S_001';
RETURN;
END IF;
SELECT COUNT(MCC_PLAN_SEQ)
INTO V_P_CNT
FROM TBL_MCC_PROD_CONFIRM_PLAN
WHERE SUBSTR(MCC_PLAN_SEQ, 0, 8) = TO_CHAR(SYSDATE, 'YYYYMMDD');
IF V_P_CNT = 0 THEN
SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') || 'C' || '001'
INTO V_P_SEQ
FROM DUAL;
ELSE
SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') || 'C' || LPAD(SUBSTR(MAX(MCC_PLAN_SEQ), 10, 3) + 1, 3, 0)
INTO V_P_SEQ
FROM TBL_MCC_PROD_CONFIRM_PLAN;
END IF;
BEGIN
-----------------------------------------------------------------------------------------------------------------------
INSERT INTO TBL_MCC_PROD_CONFIRM_PLAN
(
MCC_PLAN_SEQ,
TPS_LINE_PLAN_ID,
TPS_LINE,
TPS_DATE,
MODEL_CODE,
STYLE_CODE,
GENDER_CODE,
PART_CODE,
PART_NAME,
MATERIAL_CODE,
MATERIAL_NAME,
SIZE_CODE,
TPS_QTY,
TOTAL_CONFIRM_QTY,
CONFIRM_QTY,
GATHER_QTY,
EQUIPMENT_TYPE,
EQUIPMENT_CODE,
F_CLOSE,
CREATE_DATE,
CREATE_USER
)
SELECT V_P_SEQ,
V_P_TPS_LINE_PLAN_ID,
V_P_TPS_LINE_CODE,
V_P_TPS_DATE,
V_P_MODEL_CODE,
V_P_STYLE_CODE,
V_P_GENDER_CODE,
V_P_PART_CODE,
SUBSTR(V_P_PART_NAME, 0, (INSTR(V_P_PART_NAME, '*') - 4)),
V_P_MATERIAL_CODE,
SUBSTR(V_P_MATERIAL_NAME, 0, (INSTR(V_P_MATERIAL_NAME, '*') - 4)),
V_P_SIZE_CODE,
V_P_TPS_QTY,
V_P_TOTAL_CONFRIM_QTY,
V_P_CONFIRM_QTY,
P_GATHER_QTY,
V_P_EQUIPMENT_TYPE,
V_P_EQUIPMENT_NAME,
V_P_F_CLOSE,
SYSDATE,
V_P_USER_ECRP
FROM DUAL;
-- Get the number of rows run record
V_P_ROW_COUNT := SQL%ROWCOUNT;
-- Normal processing in the absence of an error in progress
IF V_P_ROW_COUNT > 0 THEN
V_P_ERROR_CODE := 'MSG0002'; -- It has been successfully registered.
ELSE
V_P_ERROR_CODE := 'MSG0008'; -- No registration record.
END IF;
END; -- If the job class insert (N) end
END IF;
-----------------------------------------------------------------------------------------------------------------------
EXCEPTION
WHEN OTHERS THEN
BEGIN
V_P_ERROR_CODE := CASE SUBSTR(V_P_WORK_TYPE, 1, 1)
WHEN 'Q' THEN 'ERR0006' -- When a query error occurred.
WHEN 'N' THEN 'ERR0008' -- The registration error occurred.
WHEN 'U' THEN 'ERR0009' -- The modified when the error occurred.
WHEN 'D' THEN 'ERR0010' -- An error occurred while deleting.
ELSE 'ERR0000'
END;
V_ERRORSTATE := CAST(SQLCODE AS VARCHAR2) || '|' || CAST(NULL AS VARCHAR2) || '|' || CAST(NULL AS VARCHAR2) || '|' || CAST
(
NULL AS VARCHAR2
);
V_ERRORPROCEDURE := 'PRC=' || CAST(NULL AS VARCHAR2);
V_P_ERROR_STR := SQLERRM;
END;
END;
END;
/
동적쿼리와 DB링크가 결합된 걸 루프 돌려서 나는 에러 인 듯 합니다.
다른 방식으로 개발해야 할 것 같아요.
오라클 오류는 아닌 듯 하고... 혹시 원격DB가 MSSQL 인가요?
언제나 친절한 답변 감사드립니다. !! 마농님~
DB는 오라클 사용중입니다.
그래서 저도 DB링크가 문제인거 같아
임시테이블에 잠깐 저장하여 비교해서 처리 해야 할거 같습니다.
정말로 감사드립니다 !!! 좋은 하루 되세요.
지금 ms-sql 쓰는데 오라클쪽이랑 오픈쿼리로 연결하면 가끔 분산트랜잭션 어쩌구 나더군요...
) 버튼을 클릭하여 작성 하시면 됩니다.