sqlplus 엑셀 export

외부에서 sql devloper나 db 매니지먼트 프로그램으로 접속이 안되는 상황에서 사용자의 통계 데이터를 뽑아와야하는 상황이었다.(단순 데이터가 아니라 join을 통하여 여러 테이블에 걸쳐 복잡한 데이터를 뽑는 상황)

첫 번째로 생각한 방법은 sqlplus로 데이터베이스를 dump떠서 로컬 db로 불러온다음에 쿼리문을 로컬에서 작성해가며 데이터를 추출하려고 했었다.

하지만 sqlplus에서 덤프를 뜨고 로컬 oracle db에서 임포트를 했으나 에러만나서 포기했다.

두 번째 방법은 sqlplus에서 지원하는 spool 기능을 사용하는 것 이다.

문제 해결이 급급해서 spool 기능에 대해서 잘 찾아보지는 않았지만 현재 sqlplus에서 출력된 화면을 긁어서 원하는 방식으로 추출하는 명령어 인 것 같다.

참고 사이트(많은 도움이 되었다.)

https://stackoverflow.com/questions/643137/how-do-i-spool-to-a-csv-formatted-file-using-sqlplus

위의 엑셀처럼 데이터를 회원정보와 회원의 행동 데이터 등 복잡한 데이터를 제출해야한다.

sqlplus에 접속한다

이 글을 보면 어떻게 하는지 대충 나와있다.

colsep가 구분자다

작성자는 쉼표로 구분자를 넣으라고 하였는데 데이터에 쉼표가 들어가면 셀이 넘어가버리는 문제가 발생해서 나중에는는 쿼리단에서 replace로 쉼표를 없애주었다.

원하는 데이터를 추출하는 쿼리다.

아까 찾아본 명령어와 쿼리를 적절히 조합한다.

spool 뒤에는 저장될 파일의 경로와 파일명을 지정한다.

파일 저장할때 해당 경로에 대한 쓰기 권한이 없으면 저장이 안되니까 적절한 경로에 저장한다.

set colsep ,

set pagesize 50000

set trimspool on

set headsep off

set linesize 10000

spool /home/data.csv

select *

  from table;

명령어를 실행하면 그냥 평범하게 데이터를 조회하는 것처럼 보인다.

sftp로 봐보면 data가 뽑혔다.

인코딩 문제로 한글이 깨지는데 메모장으로 파일을 불러와서 인코딩을 바꿔준다.

utf-8에서 ANSI로 바꾼다.

그러면 데이터가 한글로 잘 나오지만 위 같은 경우는 데이터에 쉼표가 들어가면 셀이 넘어가버려서 아래처럼 되버린다.

데이터를 뽑을때 쉼표가 없어야한다.

아니면 구분자를 달리하면 될 것 같은데

구분자를 다르게했더니 데이터가 더 이상하게 꺠져버렸다.

이렇게 해서 쿼리를 실행해보면 데이터가 잘 추출된다.