에러 처리

SQL*Plus의 에러 처리 기능을 살펴보자.

테스트를 위해 아래와 같이 테이블을 생성하자.

-- 1
DROP TABLE t1 PURGE;
DROP TABLE t1_t PURGE;

CREATE TABLE t1 AS SELECT 1 AS c1 FROM XMLTABLE ('1 to 10');
CREATE TABLE t1_t (c1 NUMBER);

아래는 t1 테이블을 재구성(reorganization)하기 위한 쿼리다. 해당 내용으로 s1.sql 파일을 생성하자.

-- 2
CREATE TABLE t1_t AS SELECT * FROM t1;
TRUNCATE TABLE t1;
INSERT INTO t1 SELECT * FROM t1_t ORDER BY c1;

s1.sql 파일을 수행해보자. t1_t 테이블이 존재하므로 첫 번째 쿼리에서 에러가 발생했고, 이후 쿼리가 수행되어 t1 테이블의 데이터가 유실되었다.

-- 3
@s1

CREATE TABLE t1_t AS SELECT * FROM t1
             *
1행에 오류:
ORA-00955: 기존의 객체가 이름을 사용하고 있습니다.

테이블이 잘렸습니다.

0 행이 생성되었습니다.

SQL*Plus의 에러 처리 옵션은 WHENEVER SQLERROR 명령어로 설정한다. 기본값이 CONTINUE NONE이기 때문에 에러가 발생하더라도 이후 쿼리가 수행된 것이다.

-- 4
WHENEVER SQLERROR

사용법: WHENEVER SQLERROR
     { CONTINUE  [ COMMIT | ROLLBACK | NONE ]
     | EXIT  [ SUCCESS | FAILURE | WARNING | n | <variable> | :<bindvariable> ]
             [ COMMIT | ROLLBACK ] }

아래와 같이 옵션을 설정하고 s1.sql 파일을 수행해보자. 에러가 발생하면 SQL*Plus가 종료되고 이후 쿼리가 수행되지 않는 것을 확인할 수 있다.

-- 5
WHENEVER SQLERROR EXIT 1 ROLLBACK

@s1

CREATE TABLE t1_t AS SELECT * FROM t1
             *
1행에 오류:
ORA-00955: 기존의 객체가 이름을 사용하고 있습니다.

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options에서 분리되었습니다.

에러가 발생한 구문은 SET ERRORLOGGING 명령어을 통해 로깅할 수 있다.

-- 6
SET ERRORL[OGGING] {ON | OFF} [TABLE [schema.]tablename] [TRUNCATE] [IDENTIFIER identifier]

테스트를 위해 아래와 같이 에러 로그 테이블을 생성하자.

-- 7
DROP TABLE sperrorlog PURGE;

CREATE TABLE sperrorlog (
    username   VARCHAR(256)
  , timestamp  TIMESTAMP
  , script     VARCHAR(1024)
  , identifier VARCHAR(256)
  , message    CLOB
  , statement  CLOB);

아래와 같이 SET ERRORLOGGING 명령어를 수행한 후 s1.sql 파일을 수행해보자.

-- 8
WHENEVER SQLERROR EXIT 1 COMMIT

SET ERRORLOGGING ON IDENTIFIER i1

@s1

CREATE TABLE t1_t AS SELECT * FROM t1
             *
1행에 오류:
ORA-00955: 기존의 객체가 이름을 사용하고 있습니다.

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options에서 분리되었습니다.

sperrorlog 테이블을 조회해보면 에러와 관련된 내용을 확인할 수 있다.

-- 9
SELECT * FROM sperrorlog;

USERNAME TIMESTAMP  SCRIPT IDENTIFIER MESSAGE                                            STATEMENT
-------- ---------- ------ ---------- -------------------------------------------------- -------------------------------------
TUNA     2050-01-01 s1.sql i1         ORA-00955: 기존의 객체가 이름을 사용하고 있습니다. CREATE TABLE t1_t AS SELECT * FROM t1

1개의 행이 선택되었습니다.