오라클 에러 메시지 모음

1.1 ORA-00001 : 유일성 제약조건에 위배됩니다.

테이블에 PK가 있거나 UNIQUE INDEX가 있을 때, 중복되게 INSERT 하면 발생한다. 

 

1.2 ORA-00942 : 테이블 또는 뷰가 존재하지 않습니다.

개발자의 오타에 의한 경우도 있고, 실제로 테이블이 생성됐는지 확인도 필요하다. 오라클은 테이블 생성 시 대소문자 구분은 없으며며 자동으로 대문자로 생성된다. 하지만 따옴표로 감싸서 소문자로 생성 시 테이블명은 소문자로 생성된다. (CREATE TABLE 'aaa' -> 소문자 테이블 생성 aaa)

 

1.3 ORA-00904 : 열명이 부족합니다.

존재하지 않는 컬럼명을 쿼리 구문에 사용할 경우 발생하는 에러 메시지 간혹 컬럼은 실제로 존재하나 SELECT 절에 없는  컬럼을 ORDER BY 절에 사용해서 발생하는 경우도 있어서 유의해야 한다.

SELECT 고객아이디, 고객명
FROM 
(
    SELECT CUST_ID AS 고객아이디, CUST_NM AS 고객명
    FROM 고객
)
ORDER BY CUST_NM

1.4 ORA-01017 : 유효하지 않는 사용자/패스워드에 의한 접근을 제한합니다.

오라클 접속 시 사용자 ID나 패스워드가 일치하지 않아서 발생한다. 오라클 11g부터는 대소문자를 구분하기 때문에 주의해야 한다.

 

1.5 ORA-01722 : 수치가 부적합합니다.

INSERT 혹은 UPDATE 시에 컬럼의 타입에 맞지 않는 값을 입력할 때 발생한다. 숫자 컬럼에 문자값을 입력하거나 문자 컬럼에 숫자값을 입력하는 경우가 그러하다. 즉 컬럼의 타입 전환 시에 문제가 발생한다고 볼 수 있다.

1.6 ORA-01555 : 스냅샷이 너무 오래됐습니다.(롤백 세그먼트가 너무 작습니다.)

사용자가 필요로 하는 롤백 세그먼트의 정보가 다른 트랜잭션에 의해 오버라이트돼 존재하지 않을 때 발생한다. 결국 롤백 세그먼트가 너무 작다는 의미인데 가장 쉽게 해결하는 방법은 롤백 세그먼트를 크게 하는 것이다. 대용량 처리시에 빈번한 COMMIT을 사용을 자제하거나, 처리를 한가한 시간대로 돌리는 것도 좋은 효과를 낼 수 있다.

 

1.7 ORA-00911 : 문자가 부적합합니다.

쿼리 구문을 잘못 작성해서 발생하는 에러라는 생각이 들 것이다. 하지만 대부분은 구문의 끝에 세미클론(;)을 사용해서 발생하는 경우이다. 

 

1.8 ORA-12541 : 리스너가 존재하지 않습니다.

오라클의 리스너는 네트워크를 통해 클라이언트에게 오라클 서버로 접근하는 것을 관리하는 기능을 수행한다. 원격 데이터베이스 서버에 접근하기 위해서는 원격 서버에 리스너가 구동되어 있어야 한다. 주로 리스너가 구동되어 있지 않을 때에 발생하는 에러 메시지이다. 

 

1.9 ORA-03113 : 통신 채널에 EOF가 있습니다.

서버의 고장이나 네트워크가 불안정할 때 발생하는 에러로, 다량의 데이터를 INSERT 하거나 UPDATE할 때 발생한다. 이 에러는 원인이 명확하지 않아서 대처하는게 어렵다. 이러한 에러를 만나면 네트워크 상태를 점검하기도 하고 방화벽을 의심해 보기도 한다.

 

1.10 : ORA-01476 : 제수가 0 입니다.

쿼리 구문의 나눗셈에서 분모가 0일 때 발생한다. 

-- 에러 발생
SELECT  1/0
FROM DUAL 

-- 다음과 같이 해결
SELECT DECODE(분모, 0, 0, 분자/분모) FROM ...
SELECT NVL(분자 / DECODE(분모, 0, NULL, 분모), 0) FROM ...

2. 사라진 날짜를 찾아라 오라클에서 달력 다루기

우리는 쿼리에서 다음과 같은 조건을 빈번하게 사용한다.

WHERE 일자 BETWEEN ? AND ?

 

조건의 시작과 끝에 대해 고민한 적이 있는가? 오라클에서는 날짜의 끝이 존재한다.

SELECT TO_DATE('99991231', 'YYYMMDD') + 1 FROM DUAL

==============================
ORA-01841
==============================


SELECT TO_DATE('00001231', 'YYYMMDD') FROM DUAL

==============================
ORA-01841
==============================

오라클은 수학같이 확실한 정답이 있는 것이 아니라 규칙이 있을 뿐이고 그것을 지킬 뿐이다. 참고로 mysql, 오라클에서는 다음과 같이 날짜를 다룬다.

-- MYSQL DATE
1. '2022-08-28 10:00:00'

-- ORACLE DATE
1. TO_DATE(SYSDATE, 'YYYYMMDDHH24MISS')
2. TO_DATE('20220828 235959', 'yyyymmdd hh24miss')

3. 오라클 랜덤 함수와 사용자 정의 함수

오라클이 제공하는 DBMS_RANDOM 패키지는 랜덤값을 제공하는 함수 기능을 포함하고 있다. 이 패키지는 8버전부터 지원했는데 사용 분야가 다양하다.

-- 1 ~ 10 사이의 값을 추출
SELECT DBMS_RANDOM.VALUE(1, 10) FROM DUAL

===================================
3.4571231252353463456345345345345
===================================

소수점 이하 값을 포함하는 1에서 10사이의 값을 리턴한다. (실행할 때 마다 다른 값을 리턴)



-- 쇼핑몰에서 물품 구매 시 SMS 인증에 사용하는 6자리 번호 추출
SELECT ROUND(DBMS_RANDOM.VALUE(100000, 999999), 0) FROM DUAL

===================================
731122
===================================

100000 ~ 999999 사이의 값을 리턴하는 데 ROUND 함수를 사용해 소수점 이하 값을 제외한 6자리 숫자를
랜덤하게 리턴

DBMS_RANDOM.VALUE 함수를 통해 랜덤 숫자를 구했다. 랜덤 문자를 구하려면 DBMS_RANDOM.STRING이다.

SELECT DBMS_RANDOM.STRING('U', 10) FROM DUAL -- 대문자 10자리
SELECT DBMS_RANDOM.STRING('L', 10) FROM DUAL -- 소문자 10자리
SELECT DBMS_RANDOM.STRING('A', 10) FROM DUAL -- 대소문자 10자리
SELECT DBMS_RANDOM.STRING('X', 10) FROM DUAL -- 대문자 및 숫자 10자리
SELECT DBMS_RANDOM.STRING('P', 10) FROM DUAL -- 대소문자 및 특수문자

오라클에서 제공하는 함수 중에는 문자열을 역순으로 리턴하는 함수가 있다.

SELECT REVERSE('ABCD') FROM DUAL

==============================
DCBA
==============================


SELECT REVERSE('1234') FROM DUAL

==============================
4321
==============================


-- 숫자형인 경우 오라클 에러 발생
SELECT REVERSE(1234) FROM DUAL

-- 한글이나 한글 자모인 경우 오라클 에러 발생
SELECT REVERSE('대한') FROM DUAL 
SELECT REVERSE('ㄱㄴ') FROM DUAL

사용자에 따라 직접 만든 함수를 사용자 정의 함수라고 한다. ISNUMERIC 함수는 SQL Server에서는 제공하지만 오라클에서는 제공하지 않는다. ISNUMERIC 함수는 숫자이면 1을 리턴하고 아니면 0을 리턴하는 함수이다. 오라클에서는 제공하지 않는 함수라서 직접 만들어야 한다.

CREATE OR REPLACE FUNCTION ISNUMERIC (P_NUM IN VARCHAR2) RETURN NUMBER
AS V_NUM NUMBER
BEGIN
	V_NUM := TO_NUMBER(P_NUM);
    return 1;
EXCEPTION
	WHEN OTHERS THEN
    	RETURN 0;
END;

---

참조

http://www.yes24.com/Product/Goods/61583758

개발자를 위한 인덱스 생성과 SQL 작성 노하우 - YES24