꿈꾸는 개발자, DBA 커뮤니티 구루비

프로시저 등록 에러 내용좀 봐주세요..

by 이재호 [PL/SQL] [2017.02.06 12:53:13]

안녕하세요 이번이 3번째 글인데.. 어디에 요청구하기가 쉽지 않네요..

프로시저 등록시 에러가는데 확인좀 부탁드립니다. ORA-01036 잘못된 변수명/번호 이렇게 나는데.. 어디가 잘못된걸까요..ㅜㅜ

create or replace procedure splex_oos_ls3(
V_TABLE_NAME in varchar2, --테이블명
V_ROWID in varchar2) -- rowid

is
V_CNT1 number(10);
V_CNT1 varchar2(1000);
V_CNT1 varchar2(1000);
V_SQL1 varchar2(4000);
V_SQL2 varchar2(4000);
V_SQL3 varchar2(4000);
V_SQL4 varchar2(4000);
V_SQL5 varchar2(4000);
V_SQL6 varchar2(4000);
V_SQL7 varchar2(4000);
V_SQL8 varchar2(4000);
V_SQL9 varchar2(4000);

begin
select COUNT(1) INTO V_CNT1 from EAP.TEAP01 where rowid=:V_ROWID ;
select COUNT(1) INTO V_CNT2 from WSD.TSD01 where rowid=:V_ROWID ;
select COUNT(1) INTO V_CNT2 from WSD.TSD05 where rowid=:V_ROWID ;

--첫번째 테이블 삭제, insert
if V_TABLE_NAME='TEAP01'
then
if IF V_CNT1 > 0 THEN
then
V_SQL1 := 'delete from ecm.TEAP01 where APPR_DOC_SER in (select APPR_DOC_SER from EAP.TEAP01@SPLEX_CAB_VLS3 where rowid=:V_ROWID)';
execute immediate V_SQL1 using V_ROWID;
commit;

V_SQL2 := 'insert into ecm.TEAP01 select * from EAP.TEAP01@SPLEX_CAB_VLS3 where rowid=:V_ROWID';
execute immediate V_SQL2 using V_ROWID;
commit;

V_SQL3 := 'select * from ecm.TEAP01 where APPR_DOC_SER in (select APPR_DOC_SER from EAP.TEAP01@SPLEX_CAB_VLS3 where rowid=:V_ROWID)';
execute immediate V_SQL3 using V_ROWID;

exception
when others then
dbms_output.put_line('error : ' || sqlerrm);
end if;
end if;

--두번째 테이블 삭제, insert
if V_TABLE_NAME='TSD01'
then
if IF V_CNT2 > 0 THEN
then
V_SQL4 := 'delete from ecm.TSD01 where (BUB_CD, SA_NO, CR_DAY, WOO_NO) in (select BUB_CD, SA_NO, CR_DAY, WOO_NO from WSD.TSD01@SPLEX_CAB_VLS3 where rowid=:V_ROWID)';
execute immediate V_SQL4 using V_ROWID;
commit;

V_SQL5 := 'insert into ecm.TSD01 select * from EAP.TSD01@SPLEX_CAB_VLS3 where rowid=:V_ROWID';
execute immediate V_SQL5 using V_ROWID;
commit;

V_SQL6 := 'select * from ecm.TSD01 where (BUB_CD, SA_NO, CR_DAY, WOO_NO) in (select BUB_CD, SA_NO, CR_DAY, WOO_NO from WSD.TSD01@SPLEX_CAB_VLS3 where rowid=:V_ROWID)';
execute immediate V_SQL6 using V_ROWID;

exception
when others then
dbms_output.put_line('error : ' || sqlerrm);
end if;
end if;

--세번째 테이블 삭제, insert
if V_TABLE_NAME='TSD05'
then
if IF V_CNT3 > 0 THEN
then
    V_SQL7 := 'delete from ecm.TSD05 where (BUB_CD, SA_NO) in (select BUB_CD, SA_NO from WSD.TSD05@SPLEX_CAB_VLS3 where rowid=:V_ROWID)';
    execute immediate V_SQL7 using V_ROWID;
    commit;

     V_SQL8 := 'insert into ecm.TSD05 select * from EAP.TSD05@SPLEX_CAB_VLS3 where rowid=:V_ROWID';
    commit;
    execute immediate V_SQL8 using V_ROWID;

    V_SQL9 := 'select * from ecm.TSD05 where (BUB_CD, SA_NO) in (select BUB_CD, SA_NO from WSD.TSD05@SPLEX_CAB_VLS3 where rowid=:V_ROWID)';
    execute immediate V_SQL9 using V_ROWID;

exception
    when others then
        dbms_output.put_line('error : ' || sqlerrm);
end if;
end if;

end;

by 마농 [2017.02.06 13:14:34]

1. 변수 선언이 중복되었네요.
    - V_CNT1
2. 바인드 변수 사용법이 틀렸네요.
    - SQL 에서는 콜론(:)으로 변수 사용하지만
   - PL/SQL 에서는 콜론 없이 변수명 그대로 사용합니다.
3. 블럭 단위 사용이 오류네요.
    - Exception 구문은 Begin ~ End 블럭 에서 사용되어야 합니다.
    - 즉, Begin ~ Exception ~ End 가 하나의 블럭단위입니다.
    - Begin 은 하나 뿐인데 Exception 이 두번 나오네요.
4. PL/SQL 에서 Select 단독 사용 못합니다.
    - 1건 조회시엔 Into 사용하고
   - 여러건일 때는 커서 형태로 사용해야 합니다.
    - v_sql3, v_sql6, v_sql9 이 이에 해당됩니다.
5. V_SQL 사용에 대해서
   - 테이블명 변수로 받아 처리하기 위해서라면? 동적 쿼리 필요하지만
   - 위처럼 IF 문으로 분기처리할 거라면? 굳이 동적 쿼리 필요 없습니다.
6. ROWID 에 대해서
   - ROWID 는 테이블 레코드의 고유주소인데요.
   - 테이블을 다른 서버로 이관하면 ROWID 는 달라집니다.
   - 위에서처럼 작업서버와 링크서버의 테이블에 동일 rowid 조건을 준다면?
   - 둘 중 하나는 잘못된 조건인거죠.
7. 테이블 유저가 뒤죽박죽이네요.
   - 테이블 유저(ECM, WSD, EAP) 이 일관되게 사용되어야 하는데.
   - 뒤죽박죽 섞여서 사용되고 있네요.

총평.
    - 전반적으로 엉망입니다.
    - 한방에 모든 로직 다 넣어서 프로그램 짜려고 하지 마시고.
    - 일부 간단 로직부터 작성하여 정상 동작하는지 확인 한 후에 로직을 추가해 나가세요.

by jkson [2017.02.06 13:42:35]

간단하게 줄여봤습니다. 작동하는 것은 직접 확인하시구요. V_TABLE_NAME은 대문자로 입력하세요.

V_SQL3 SELECT 쿼리는 왜 쓰신 건지요?


CREATE OR REPLACE PROCEDURE SPLEX_OOS_LS3(V_TABLE_NAME IN VARCHAR2, --테이블명
                                          V_ROWID IN VARCHAR2) -- rowid
IS
 V_CNT1 NUMBER;
 V_DELCNT NUMBER;
 V_KEY VARCHAR2(100);
 V_SQL1 VARCHAR2(4000);
 V_SQL2 VARCHAR2(4000);
 
BEGIN
  SELECT LISTAGG(COLUMN_NAME, ',') WITHIN GROUP (ORDER BY POSITION)
    INTO V_KEY
    FROM DBA_CONS_COLUMNS C, DBA_CONSTRAINTS S
   WHERE C.CONSTRAINT_NAME = S.CONSTRAINT_NAME
     AND S.CONSTRAINT_TYPE = 'P'
     AND C.TABLE_NAME = V_TABLE_NAME;
 
  IF V_KEY IS NOT NULL THEN
     
      V_SQL1 := 'delete from ecm.' || V_TABLE_NAME || ' where ('|| V_KEY ||') in (select '|| V_KEY ||' from EAP.' || V_TABLE_NAME || '@SPLEX_CAB_VLS3 where rowid=:V_ROWID)';

      EXECUTE IMMEDIATE V_SQL1 USING V_ROWID;
      
      V_DELCNT := SQL%ROWCOUNT;
      
      IF V_DELCNT > 0 THEN
          V_SQL2 := 'insert into ecm.' || V_TABLE_NAME || ' select * from EAP.' || V_TABLE_NAME || '@SPLEX_CAB_VLS3 where rowid=:V_ROWID';

          EXECUTE IMMEDIATE V_SQL2 USING V_ROWID;

          COMMIT;
      ELSE 
        DBMS_OUTPUT.PUT_LINE('error : 존재하지 않는 rowid -> '||V_ROWID);
      END IF;
           
  ELSE 
     DBMS_OUTPUT.PUT_LINE('error : 존재하지 않는 테이블 -> '||V_TABLE_NAME);
  END IF;
EXCEPTION
 WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('error : ' || SQLERRM);
END;

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변