프로시저 호출을 로그에서 확인하면 아래와 같습니다. 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'); 이렇게 호출하면 에러메세지 없이 실행 됩니다. 뭐가 잘못된 걸까요??? ============== 프로시저도 아래 붙여 넣기 했습니다. -- FUNCTION: public.sp2_default_request(character varying, character varying, character varying, character varying, character varying) -- DROP FUNCTION public.sp2_default_request(character varying, character varying, character varying, character varying, character varying); 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 --SADMINYN VARCHAR(1) :='N'; --운영자 확인단계 유무 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 --APP_MENU(결재프로그램 코드에 등록되어있는 운영자사번을 셋팅) 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 --APP_MENU(결재프로그램 코드에 등록되어있는 운영자사번을 셋팅) 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) OWNER TO postgres;