whenever sqlerror … 여러 줄 의 sql 스크립트 문장 수행 시 중간 에서 에러 발생하면 rollback 처리 할 수 있게 하는 기능

확장자 *.sql 문장 시작 부분에  맨 첫줄 과 둘째 줄에 기술하여 준다.

whenever sqlerror exit rollback

whenever sqlerror exit commit

select * from err_test ;

truncate table err_test ;

<sqlerror_test.sql>

whenever sqlerror exit rollback

insert into err_test(c1,c2,c3,c4,c5) values(‘A’,’AA’,1,1,’AA’)

insert into err_test(c1,c2,c3,c4,c5) values(‘B’,’BB’,2,2,’BB’)

insert into err_test(c1,c2,c3,c4,c5) values(‘C’,’CC’,3,33,’CC’)

insert into err_test(c1,c2,c3,c4,c5) values(‘D’,’DDD’,4,4,’DD’)

insert into err_test(c1,c2,c3,c4,c5) values(‘E’,’EE’,5,5,’EE’)

commit;

<< test00 유저로 로그인 하여 sqlerror_test.sql 을 실행 >>

에러 발생으로 인하여 정상적인 데이터 조차도 insert 되지 못하고 rollback 됨과 동시에 sqlplus 를 exit 한다

다시 sqlplus test00 유저로 로그인 하여 err_test 테이블을 조화하여보면 데이터가 없음을 확인 할 수 있다.

PS D:\RMAN_BACKUP> sqlplus test00/test00

SQL*Plus: Release 11.2.0.1.0 Production on 일 5월 5 09:18:31 2019

Copyright (c) 1982, 2010, Oracle. All rights reserved.

다음에 접속됨:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORCL@TEST00> @sqlerror_test

1 개의 행이 만들어졌습니다.

1 개의 행이 만들어졌습니다.

insert into err_test(c1,c2,c3,c4,c5) values(‘C’,’CC’,3,33,’CC’)

*

1행에 오류:

ORA-01438: 이 열에 대해 지정된 전체 자릿수보다 큰 값이 허용됩니다.

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options에서 분리되었습니다.

PS D:\RMAN_BACKUP>

PS D:\RMAN_BACKUP> sqlplus test00/***

SQL> select * from err_test ;

0 rows selected.

whenever <condition> <action>

<condition>

1) SQLWARNING

2) SQLERROR

3) NOT FOUND — fetch, into 절에서 조건을 만족하는 행이 발견되지 않았을 경우

<action>

1) continue — 아무런 대응 없이 그냥 다음으로 진행, whenever를 사용하지 않은 것 과 동일

2) do 함수 명( ) — 오류에 대응하는 함수를 호출

, 이 함수가 종료하면 오류가 발생한 다음으로 제어가 진행됨

3) do break — 반복문의 break

4) do continue — 반복문의 continue

5) goto label name

6) stop — 프로그램 중지, 트랜잭션 롤백

<선언 예>

exec sql whenever sqlerror continue ;

exec sql whenever sqlwarning continue ;

exec sql whenever sqlwarning goto warning_label ;

warning_label;

dbms_output.put_line(“SQL 경고 발생”) ;

<Shell 로 실행시>

sqlplus -s test00/test00 << EOF

spool spool_123.out

set echo on

whenever sqlerror exit rollback

whenever sqlerror exit 1

whenever oserror exit 1

. . . . .

EOF

<crontab> 등록 — 매일 새벽 2 시에 실행

0 2 * * * su – oracle -c /ORA_JOB/test01.sh