|
프로시저 호출을 로그에서 확인하면 아래와 같습니다.
DEBUG:[2018-02-27 16:55:27,969]java.sql.PreparedStatement - {pstm-100238} Executing Statement: { CALL SP2_DEFAULT_REQUEST(?,?,?,?,?,?)}
DEBUG:[2018-02-27 16:55:27,969]java.sql.PreparedStatement - {pstm-100238} Parameters: [admin, 19|, 13|, BUSINESS, 2017]
DEBUG:[2018-02-27 17:28:27,512]java.sql.PreparedStatement - {pstm-100296} Types: [java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String]
sql 매핑은 아래와 같습니다.
<parameterMap class="java.util.HashMap" id="callRequestDefaultParam">
<parameter property="ssUserCd" mode="IN" jdbcType="VARCHAR" javaType="java.lang.String"/>
<parameter property="seqStr" mode="IN" jdbcType="VARCHAR" javaType="java.lang.String"/>
<parameter property="jobStr" mode="IN" jdbcType="VARCHAR" javaType="java.lang.String"/>
<parameter property="gubunCd" mode="IN" jdbcType="VARCHAR" javaType="java.lang.String"/>
<parameter property="ssSysCd" mode="IN" jdbcType="VARCHAR" javaType="java.lang.String"/>
<parameter property="outMsg" mode="OUT" jdbcType="VARCHAR" javaType="java.lang.String"/>
</parameterMap>
<procedure id="approvalDAO.callRequestDefaultPro" parameterMap="callRequestDefaultParam" resultClass="java.util.HashMap">
{ CALL SP2_DEFAULT_REQUEST(?,?,?,?,?,?)}
</procedure>
프로시저 시작부분에 아래의 코드가 있습니다.
SELECT SUBSTR(sPlus, 1, POSITION('|' IN sPlus)-1) INTO sSeq ;
SELECT SUBSTR(sPlus, POSITION('|' IN sPlus)+1, LENGTH(sPlus)) INTO sPlus ;
SELECT SUBSTR(sPlus2, 1, POSITION('|' IN sPlus2)-1) INTO sJob ;
SELECT SUBSTR(sPlus2, POSITION('|' IN sPlus2)+1, LENGTH(sPlus2)) INTO sPlus2 ;
와 같은부분이 있습니다.
jsp 화면에서 프로시저를 호출하면 "SP2_DEFAULT_REQUEST[공통결재프로시저(기본):(999):]22011,substring에서 음수 길이는 허용하지 않음" 라는 에러 메세지가 출력 됩니다.
pgAdmin4 에서 select SP2_DEFAULT_REQUEST('admin', '19|', '13|', 'BUSINESS', '2017'); 이렇게 호출하면 에러메세지 없이 실행 됩니다.
뭐가 잘못된 걸까요???
==============
프로시저도 아래 붙여 넣기 했습니다.
CREATE OR REPLACE FUNCTION public.sp2_default_request(
v_user_cd character varying,
v_seqstr character varying,
v_jobstr character varying,
v_program_cd character varying,
v_sys_cd character varying,
OUT v_outmsg character varying)
RETURNS character varying
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$
DECLARE
sCnt INTEGER := 0;
sPlus TEXT := V_SEQSTR;
sPlus2 TEXT := V_JOBSTR;
sSeq VARCHAR(100) := '';
sJob VARCHAR(100) := '';
sFirstAppYn VARCHAR(1) := '';
sSecondAppYn VARCHAR(1) := '';
sProgram_id VARCHAR(100) := 'SP2_DEFAULT_REQUEST';
sProgram_name VARCHAR(100) := '공통결재프로시저(기본)';
CNT NUM%ROWTYPE;
BEGIN
BEGIN
SELECT USER1, USER2
INTO sFirstAppYn,sSecondAppYn
FROM T_CODE
WHERE SYS_CD = V_SYS_CD
AND IDX_CD = 'APP_MENU'
AND SUB_CD = V_PROGRAM_CD;
END;
FOR CNT IN ( SELECT N FROM NUM WHERE N <= (SELECT (LENGTH(V_SEQSTR)-LENGTH(REPLACE(V_SEQSTR,'|',''))))) LOOP
RAISE NOTICE 'for loop 시작 sPlus %',sPlus;
RAISE NOTICE 'for loop 시작 sPlus2 %',sPlus2;
SELECT SUBSTR(sPlus, 1, POSITION('|' IN sPlus)-1) INTO sSeq ;
SELECT SUBSTR(sPlus, POSITION('|' IN sPlus)+1, LENGTH(sPlus)) INTO sPlus ;
RAISE NOTICE 'for loop 시작 sSeq %',sSeq;
RAISE NOTICE 'for loop 시작 sPlus %',sPlus;
SELECT SUBSTR(sPlus2, 1, POSITION('|' IN sPlus2)-1) INTO sJob ;
SELECT SUBSTR(sPlus2, POSITION('|' IN sPlus2)+1, LENGTH(sPlus2)) INTO sPlus2 ;
RAISE NOTICE 'for loop 시작 sJob %',sJob;
RAISE NOTICE 'for loop 시작 sPlus2 %',sPlus2;
BEGIN
WITH UPSERT AS (
UPDATE T_APPROVAL_LINE SET
STATE_CD = '0001'
, FIRST_USER_CD = CASE WHEN sFirstAppYn='Y' THEN (SELECT APP_EVAL1
FROM T_JOB
WHERE SYS_CD = V_SYS_CD
AND JOB_CD = sJob
) ELSE '' END
, SECOND_USER_CD = CASE WHEN sSecondAppYn='Y' THEN (SELECT APP_EVAL2
FROM T_JOB
WHERE SYS_CD = V_SYS_CD
AND JOB_CD = sJob
) ELSE '' END
, ADMIN_USER_CD = (SELECT USER3
FROM T_CODE
WHERE SYS_CD = V_SYS_CD
AND IDX_CD = 'APP_MENU'
AND SUB_CD = V_PROGRAM_CD
)
, UPDATER = V_USER_CD
, UPDATE_DT = NOW()
WHERE SYS_CD = V_SYS_CD
AND APPROVAL_SEQ = CAST(sSeq AS INTEGER)
AND GUBUN_CD = V_PROGRAM_CD
RETURNING *
)INSERT INTO T_APPROVAL_LINE
(
SYS_CD
, APPROVAL_SEQ
, GUBUN_CD
, FIRST_USER_CD
, SECOND_USER_CD
, ADMIN_USER_CD
, STATE_CD
, WRITER
, WRITE_DT
)
SELECT
V_SYS_CD
, CAST(sSeq AS INTEGER)
, V_PROGRAM_CD
, CASE WHEN sFirstAppYn='Y' THEN (SELECT APP_EVAL1
FROM T_JOB
WHERE SYS_CD = V_SYS_CD
AND JOB_CD = sJob
) ELSE '' END
, CASE WHEN sSecondAppYn='Y' THEN (SELECT APP_EVAL2
FROM T_JOB
WHERE SYS_CD = V_SYS_CD
AND JOB_CD = sJob
) ELSE '' END
, (SELECT USER3
FROM T_CODE
WHERE SYS_CD = V_SYS_CD
AND IDX_CD = 'APP_MENU'
AND SUB_CD = V_PROGRAM_CD
)
, '0001'
, V_USER_CD
, NOW()
WHERE NOT EXISTS (SELECT * FROM UPSERT);
EXCEPTION
WHEN OTHERS THEN
v_OUTMSG := '공통결재프로시저(기본) 승인요청 에러';
END;
BEGIN
INSERT INTO T_APPROVAL_LOG
(
SYS_CD
, APPROVAL_SEQ
, GUBUN_CD
, LOG_SEQ
, STATE_CD
, WRITER
, WRITE_DT
)
VALUES
(
V_SYS_CD
, CAST(sSeq AS INTEGER)
, V_PROGRAM_CD
, (SELECT COALESCE(MAX(TO_NUMBER(LOG_SEQ,'99')),0)+1 FROM T_APPROVAL_LOG WHERE SYS_CD = V_SYS_CD AND APPROVAL_SEQ = CAST(sSeq AS INTEGER) AND GUBUN_CD = V_PROGRAM_CD)
, '0001'
, V_USER_CD
, NOW()
);
EXCEPTION
WHEN OTHERS THEN
v_OUTMSG := '공통결재프로시저(기본) 승인요청_이력추가 에러';
END;
sCnt := sCnt + 1;
v_OUTMSG := '已有[' || sCnt || ']?申?。';
END LOOP;
EXCEPTION WHEN OTHERS THEN
v_OUTMSG := sProgram_id || '[' || sProgram_name || ':(999):]' || SQLSTATE || ',' || SQLERRM;
END;
$BODY$;
ALTER FUNCTION public.sp2_default_request(character varying, character varying, character varying, character varying, character varying)
|
