Yd Oracle Expert

---
name: yd-oracle-expert
description: Oracle Database 에서 SQL·PL/SQL 을 작성하거나 PostgreSQL 에서 옮겨 올 때, 빈 문자열 NULL·DATE 시각·VARCHAR2 바이트 길이·ROWNUM·암묵 커밋·ORA- 오류 같은 Oracle 엔진 고유 문제를 다룰 때 사용한다. 엔진 공통 설계는 yd-db-expert, PostgreSQL 고유 주제는 yd-pg-expert 를 쓴다.
---

# yd-oracle-expert

Oracle Database **엔진 고유의 문제**가 대상이다. 정규화·인덱스 설계 같은 엔진 공통 이론은
`yd-db-expert`, PostgreSQL 운영은 `yd-pg-expert` 가 맡는다.

이 문서는 다른 엔진, 특히 PostgreSQL 에 익숙한 사람이 Oracle 에서 **조용히 틀리는 지점**만
담는다. 단언 대부분은 `scripts/verify_oracle_claims.py` 가 실제 Oracle 에서 재현한다
(10장). "실측" 이라고 적은 항목은 2026-10-04 에 확인한 값이다. 대상은 Oracle AI Database
26ai Free 23.26.3 이다(`compatible=23.6.0`, 문자셋 AL32UTF8).

## 0. 버전부터 확정한다

운영 DB 가 19c 인지 23ai 이상인지에 따라 같은 SQL 이 통과하거나 실패한다. 23ai 는 26ai 라는
이름으로도 배포되며 버전 번호는 23.x 다. **코드를 쓰기 전에 대상 버전을 확인한다.**

```sql
SELECT banner_full FROM v$version;
SELECT value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';
```

23ai 에서 생긴 것은 19c 대상 코드에 쓰지 않는다. 이 문서에서 23ai 로 표시한 항목이 그렇다.

| 기능 | 19c | 23ai 이상 |
|---|---|---|
| `BOOLEAN` 컬럼 | 없다. `NUMBER(1)` 이나 `CHAR(1)` + CHECK | 있다. `WHERE f` 로 바로 쓴다 |
| `FROM` 없는 `SELECT 1` | `FROM dual` 이 필요하다 | 그대로 된다 |
| `DROP TABLE IF EXISTS` | 없다. PL/SQL 예외 처리로 감싼다 | 있다 |
| `IN` 목록 최대 개수 | 1000개 | 65535개 |

`IN` 목록 한도는 23ai 에서 65535개로 늘었다. 65536번째에서 `ORA-01795` 가 난다(실측).
19c 의 1000개 한도는 이번 실측 범위 밖이다. 19c 대상이면 1000개를 넘는 목록을 임시 테이블이나
조인으로 바꾼다.

## 1. 빈 문자열은 NULL 이다

`''` 는 NULL 이다. PostgreSQL 에서 옮긴 코드가 가장 많이 틀리는 곳이다.

- `WHERE s = ''` 는 언제나 0행이다. 값이 비어 있는 행을 찾으려면 `WHERE s IS NULL` 을 쓴다.
- `NOT NULL` 컬럼에 `''` 를 넣으면 `ORA-01400` 이 난다. "빈 문자열은 허용, NULL 은 금지"
  라는 구분은 Oracle 에서 표현할 수 없다. 필요하면 공백 한 칸 같은 센티널을 합의한다.
- `'a' || NULL` 은 `'a'` 다. PostgreSQL 은 NULL 을 돌려준다. 문자열 연결로 NULL 을 전파시켜
  판정하던 코드는 결과가 바뀐다.

## 2. DATE 는 시각을 담는다

`DATE` 는 초 단위 시각까지 저장한다. PostgreSQL 의 `date` 와 다르다.

- `WHERE d = DATE '2026-10-04'` 는 자정이 아닌 행을 놓친다. 하루를 고를 때는 범위 조건
  `d >= DATE '2026-10-04' AND d < DATE '2026-10-05'` 를 쓴다.
- `TRUNC(d) = ...` 도 맞는 결과를 주지만 `d` 의 일반 인덱스를 쓰지 못한다. 그 조건이 잦으면
  함수 기반 인덱스를 따로 만든다.

### 날짜 문자열은 형식을 명시한다

형식 없는 `TO_DATE('2026-10-04')` 는 세션의 `NLS_DATE_FORMAT` 에 따라 결과가 갈린다.
`DD-MON-RR` 세션에서는 `ORA-01861` 이 나고 `YYYY-MM-DD` 세션에서는 통과한다(실측).
이 값은 클라이언트 드라이버·OS 로캘이 정하므로 개발자 PC 에서 통과한 코드가 서버에서 깨진다.
**`DATE '2026-10-04'` 리터럴이나 `TO_DATE(s, 'YYYY-MM-DD')` 처럼 형식을 적는다.**

## 3. 길이와 정밀도

### VARCHAR2 는 기본이 바이트다

`VARCHAR2(10)` 은 10**바이트**다. AL32UTF8 에서 한글 한 글자는 3바이트라 `'가나다라'` 는
12바이트이고, `VARCHAR2(10)` 에 넣으면 `ORA-12899` 가 난다. `VARCHAR2(10 CHAR)` 는
받는다(실측). 한글이 들어가는 컬럼은 `CHAR` 단위로 선언한다.

상한 4000바이트는 단위와 무관하다(`MAX_STRING_SIZE=STANDARD`, 기본값). `VARCHAR2(4000 CHAR)`
라도 한글 1333자는 들어가고 1334자는 `ORA-12899` 로 거부된다. SQL 함수 결과도 같은 상한에서
**오류 없이 잘린다.** `REPLACE` 로 한글 1334자를 만들면 1333자가 된다(둘 다 실측). 더 긴
텍스트는 `CLOB` 으로 둔다.

### NUMBER 의 소수 자리는 조용히 반올림된다

`NUMBER(5,2)` 에 `123.456` 을 넣으면 오류 없이 `123.46` 으로 저장된다. 정수부가 넘치는
`1234.5` 만 `ORA-01438` 로 거부된다(실측). 금액처럼 잘림을 허용할 수 없는 값은 애플리케이션이
자릿수를 먼저 검사한다.

## 4. 식별자

- 따옴표 없는 식별자는 **대문자로 접힌다.** `CREATE TABLE t (Name ...)` 의 컬럼은 `NAME`
  이고, `"name"` 으로 조회하면 `ORA-00904` 가 난다. ORM 이 식별자에 따옴표를 붙이는지 확인한다.
  PostgreSQL 은 소문자로 접으므로 같은 DDL 을 옮겨도 이름이 다르다.
- 식별자 최대 길이는 12.2 부터 **128바이트**다(이전은 30바이트). 글자 수가 아니라 바이트라,
  한글 이름은 42자째에서 `ORA-00972` 가 난다(실측: 접두어 4바이트 + 한글 41자는 통과,
  42자는 실패). `compatible` 이 12.2 미만으로 묶인 DB 는 여전히 30바이트다.

## 5. 페이지네이션

`LIMIT` 은 없다. 23.26 에서는 `ORA-03047` 로 거부된다(실측).

- **`ROWNUM` 은 `ORDER BY` 보다 먼저 붙는다.** `WHERE ROWNUM <= 3 ORDER BY x DESC` 는
  "상위 3개" 가 아니라 "먼저 읽힌 3행을 정렬한 것" 이다. 1~10 을 넣은 테이블에서 `3,2,1` 을
  돌려준다(실측). 오류가 없어서 눈에 띄지 않는다.
- `WHERE ROWNUM > 1` 은 언제나 0행이다. 첫 행이 ROWNUM 1 을 받자마자 탈락하기 때문이다.
- 12c 이상이면 `ORDER BY x DESC FETCH FIRST 3 ROWS ONLY` 를 쓴다. `OFFSET n ROWS` 도 같은
  문법이다. 11g 이하만 `ROWNUM` 을 서브쿼리 바깥에서 거는 방식이 필요하다.

## 6. 트랜잭션

### DDL 은 앞선 작업을 커밋한다

DDL 은 실행 전에 그때까지의 트랜잭션을 커밋한다. `INSERT` 후 `CREATE TABLE` 을 실행하면 뒤의
`ROLLBACK` 이 `INSERT` 를 되돌리지 못한다. **DDL 이 `ORA-00955` 로 실패해도 커밋은 이미
일어났다.** 문법 오류(`ORA-00901`)로 파싱 단계에서 막히면 커밋되지 않는다(둘 다 실측).
마이그레이션 스크립트에서 DML 과 DDL 을 한 트랜잭션으로 묶었다고 믿으면 안 된다.

### 읽기 일관성

다른 트랜잭션이 커밋하지 않은 변경은 보이지 않고, 그 변경 때문에 조회가 기다리지도 않는다.
`UPDATE` 를 커밋하지 않은 상태에서 다른 트랜잭션은 바뀌기 전 값을 즉시 읽는다(실측). 읽기
잠금 힌트나 dirty read 를 전제로 한 코드는 필요 없다.

### SERIALIZABLE 은 재시도를 요구한다

`SET TRANSACTION ISOLATION LEVEL SERIALIZABLE` 트랜잭션은 시작 시점 스냅샷을 본다. 그 뒤 다른
트랜잭션이 커밋한 행을 고치려 하면 `ORA-08177` 로 실패한다(실측). 이 모드를 쓰면 호출자가
트랜잭션 전체를 재시도해야 한다. 재시도 루프가 없으면 기본값인 READ COMMITTED 를 쓴다.

### ORA-01555 snapshot too old

긴 조회가 시작 시점의 값을 재구성하는 데 필요한 undo 가 덮어써지면 `ORA-01555` 가 난다.
조회를 짧게 나누거나 `UNDO_RETENTION` 을 늘린다. 커서를 열어 둔 채 루프 안에서 같은 테이블을
고치고 커밋하는 패턴(fetch across commit)이 대표 원인이다. 이 항목은 재현하지 않았다 —
undo 크기에 따라 결과가 달라 짧은 테스트로 고정할 수 없다.

## 7. 키와 MERGE

### IDENTITY 컬럼 (12c 이상)

- `GENERATED ALWAYS AS IDENTITY` 컬럼에 값을 직접 넣으면 `ORA-32795` 가 난다(실측).
  이관처럼 값을 넣어야 하면 `GENERATED BY DEFAULT ON NULL` 로 만든다.
- 값을 직접 넣어도 내부 시퀀스는 따라오지 않는다. 1·2 를 넣은 뒤 기본값으로 넣으면 다시 1 이
  나와 기본 키 위반 `ORA-00001` 이 난다. 이관이 끝나면
  `ALTER TABLE t MODIFY id GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH LIMIT VALUE)`
  로 시퀀스를 현재 최댓값 다음으로 맞춘다(둘 다 실측).

### MERGE 원본은 키가 유일해야 한다

`MERGE` 의 원본에 같은 키가 둘 있으면 대상 한 행을 두 번 고치게 되어 `ORA-30926` 이 난다(실측).
원본을 키로 집계하거나 `ROW_NUMBER()` 로 하나만 남긴 뒤 넣는다.

## 8. 바인드 변수

값을 문자열로 이어 붙인 SQL 은 값마다 다른 문장이다. 리터럴로 5번 실행하면 `v$sql` 에 서로 다른
`sql_id` 5개가 생기고, 바인드 변수로 5번 실행하면 1개다(실측, `cursor_sharing=EXACT`).
문장마다 하드 파스가 일어나 공유 풀과 CPU 를 쓴다. SQL 주입 방어와 같은 이유로 값은 언제나
바인드로 넘긴다. `CURSOR_SHARING=FORCE` 는 남의 코드를 고칠 수 없을 때의 임시방편이다.

## 9. SQL*Plus 로 스크립트를 돌릴 때

배포·마이그레이션 스크립트를 SQL*Plus 로 돌리면 도구가 조용히 결과를 바꾼다.

- **오류가 나도 종료 코드가 0 이다.** `WHENEVER SQLERROR EXIT SQL.SQLCODE` 를 스크립트 맨
  위에 두어야 CI 가 실패를 안다(실측: 없으면 0, 있으면 0 이 아닌 값).
- **`&` 는 치환 변수다.** `SET DEFINE OFF` 없이 `'R&D'` 를 실행하면 SQL*Plus 가 `D` 의 값을
  묻는다. 표준 입력으로 스크립트를 넣는 중이면 **다음 줄을 값으로 삼켜 버린다** — 실측에서
  `'R&D'` 는 `'RPROMPT next-line'` 이 되었고 다음 줄은 실행되지 않았다.
- **한 줄이 4999자를 넘으면 그 줄을 버린다.** `SP2-0027` 만 찍고 다음 줄로 넘어가며 종료
  코드는 0 이다(실측). 생성한 긴 `IN` 목록이나 `INSERT` 는 줄을 나눠 쓴다.

## 10. 검증

```bash
podman run -d --rm --name ydora-verify -e ORACLE_PASSWORD=verify \
  docker.io/gvenzl/oracle-free:23-slim-faststart
# 로그에 DATABASE IS READY TO USE! 가 나올 때까지 기다린다
export YD_ORACLE_SQLPLUS="podman exec -i ydora-verify sqlplus -S -L system/verify@//localhost/FREEPDB1"
python3 skills/yd-oracle-expert/scripts/verify_oracle_claims.py
podman stop ydora-verify
```

`YD_ORACLE_SQLPLUS` 는 표준 입력으로 SQL 스크립트를 받는 명령이다. 검증기는 `ydv_` 로 시작하는
테이블만 만들고 지운다. **버리는 컨테이너에만 연결한다.** 운영·공유 DB 에 연결하지 않는다.
단언이 재현되지 않으면 exit 1 이다. 그때는 문서를 고치거나, 그 단언이 버전에 묶였다고 적는다.

## 하지 않는 것

- 정규화·인덱스 선택 같은 엔진 공통 설계는 `yd-db-expert` 가 맡는다.
- PostgreSQL 쪽 운영·튜닝은 `yd-pg-expert` 가 맡는다.
- RAC·Data Guard·RMAN 백업 같은 DBA 인프라 운영은 범위 밖이다.

## 완료 조건

- 대상 DB 의 버전과 문자셋을 확인했고, 그 버전에 없는 문법을 쓰지 않았다.
- 빈 문자열·DATE 비교·`ROWNUM` 정렬·DDL 암묵 커밋 항목에 해당하는 코드를 점검했다.
- 문자열 컬럼 길이를 한글 바이트 기준으로 확인했다.
- SQL*Plus 스크립트에는 `WHENEVER SQLERROR EXIT` 와 `SET DEFINE OFF` 가 있다.