[Oracle]SELECT Query 결과 파일로 저장하기(SPOOL)

Database/Oracle

[Oracle]SELECT Query 결과 파일로 저장하기(SPOOL)

retto9522 2025. 1. 25. 13:57

SPOOL

SPOOL이란 SQL*Plus 환경에서 SQL 실행 결과를 파일로 저장할 때 사용하는 명령어입니다. 이를 통해 쿼리 결과를 텍스트 파일로 출력하여 저장할 수 있습니다.

SPOOL의 기본적은 구조는 아래와 같습니다.

-- SET을 통해 출력 결과의 형식을 설정할 수 있습니다.
SET PAGESIZE 50000; 	-- 페이지 출력 크기 설정 (기본 : 14, 최대 : 50000)
SET ECHO OFF; 			  -- 명령어 에코 끔
SET COLSEP '|'; 		  -- 조회 결과 컬럼 간 구분자 '|' 설정
SET HEADING OFF;      -- 헤더 정보 제외

-- 1. SPOOL 시작 (결과를 저장할 파일 지정)
SPOOL C:\\output\\query_result.txt;

-- 2. SQL 실행
SELECT * FROM employees WHERE department_id = 10;

-- 3. SPOOL 종료
SPOOL OFF;

SPOOL 옵션

1. APPEND

기존 파일에 데이터를 추가로 기록합니다.

SPOOL [파일경로 및 파일명] APPEND;

2. CREATE

SPOOL [파일경로 및 파일명] CREATE

3. REPLACE (default)

SPOOL [파일경로 및 파일명] REPLACE;

SELECT 쿼리 결과 텍스트 파일로 추출하기

1. SQL 파일 작성

원하는 위치에 sql 파일(example.sql)을 생성하여 쿼리를 입력합니다. 당일 날짜를 출력하는 쿼리를 작성해보겠습니다. (sql 파일 말고 직접 쿼리문을 입력하고 싶다면 곧바로 3번으로 건너뛰셔도 됩니다.)

2. SQLPLUS로 접속

실행할 sql 파일이 있는 경로에서 sqlplus로 접속합니다.

3. 텍스트 파일로 추출하기

SPOOL을 이용하여 쿼리 결과를 텍스트 파일로 추출합니다.

SET pagesize 50000 -- 페이지 출력 크기 설정
SET echo off       -- 명령어 에코 off
SPOOL result.txt   -- 경로 지정도 가능 (ex. SPOOL /home/oracle/test/sql/result.txt)
@example.sql       -- 경로를 지정하여 sql파일 실행도 가능 (ex. @/home/oracle/test/sql/example.sql)
-- select sysdate from dual; (쿼리 직접 실행)
SPOOL off

4. 추출 결과 확인하기

이제 sqlplus에서 빠져나와 출력 결과를 확인합니다.

실행한 sql 파일과 같은 경로에 result.txt 파일이 잘 만들어져 있고, 쿼리 실행 결과가 잘 출력되어 저장되어 있습니다.

응용하기

직원들의 신상 정보가 담긴 employees 테이블이 있습니다.

이번에도 SPOOL을 이용하여 데이터들을 출력해보겠습니다. 근데 위 테스트와 좀 다르게 “SQL> @example.sql”, “spool off” 부분을 제외하고 정말 순수 테스트 결과만 출력해보도록 하겠습니다.

1. SQL 파일 생성

우선 employees 테이블을 조회하는 sql 파일(query.sql)을 생성합니다.

query.sql

SELECT
	EMPLOYEE_ID ||'|'||
	FIRST_NAME ||'|'||
	LAST_NAME ||'|'||
	EMAIL ||'|'||
	PHONE ||'|'||
	HIRE_DATE
FROM RETTO.EMPLOYEES
WHERE ROWNUM <= 5;

테이블을 조회하는 sql 파일(query.sql)을 실행하는 또 다른 sql 파일(execute.sql)을 생성합니다.

execute.sql

SET HEADING OFF;
SET PAGESIZE 1000;
SET LINESIZE 300;
SET ECHO OFF;

SPOOL /home/oracle/test/result/result_01.txt
@query.sql
SPOOL OFF

2. sql 파일 실행

sqlplus로 접속하여 execute.sql 파일을 실행합니다.

@execute.sql

3. 결과 확인

sqlplus에서 빠져나와 테스트 결과를 확인해보니 위 테스트와 다르게 “SQL> @example.sql”, “spool off” 명령어를 입력했던 부분을 출력되지 않고 순수한 데이터 값만 출력된 것을 확인할 수 있습니다.

이러한 식으로 sql 파일을 잘 이용하면 깔끔한 결과 값을 가져올 수 있고, 매번 지정해줘야 하는 SET 옵션들도 편리하게 다룰 수 있습니다.

참고

https://anomiea.tistory.com/m/37

https://ajdahrdl.tistory.com/61