database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
ㆍPostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
PostgreSQL Q&A 9950 게시물 읽기
No. 9950
프로시저 호출시 파라메터 세팅
작성자
최진석
작성일
2018-02-27 17:52ⓒ
2018-02-28 07:51ⓜ
조회수
6,267

프로시저 호출을 로그에서 확인하면 아래와 같습니다.

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)

 

이 글에 대한 댓글이 총 1건 있습니다.

 substr() 함수의 인자로 사용되고 있는 length() 결과를 다 찍어보세요. 

왜 pgadmin에서는 양수고, jsp에서는 음수인지 알 수 있겠죠.

 

김상기(ioseph)님이 2018-02-28 10:55에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
9954데이터베이스 트리거를 이용해서 서버에 파일을 전송하려고 합니다. [2]
김창권
2018-03-12
5255
9953case when에서 결과 값이 없으면을 조건으로 달 수 있나요? [1]
심상호
2018-03-06
5449
9951데이터폴더의 log에 대해 질문.! [1]
test
2018-03-02
5367
9950프로시저 호출시 파라메터 세팅 [1]
최진석
2018-02-27
6267
9949도대체.. parallel query가 뭔가요?! [4]
test
2018-02-27
6164
994810.x partition table 두번째 질문드려요. [2]
test
2018-02-26
5314
9947DB 스키마 모델링 작업 어떤툴 쓰시나요? ㅇ.ㅇ [1]
test
2018-02-23
5415
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.054초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다