Shell에서 Oracle SQL문 실행

 

SQL문 수행하기 위해서는 편리한 Toad, Orange 등의 Tool을 이용합니다. 이 들 tool은 PC에서 실행되는 client 프로그램입니다. 그러나 이런 tool들은 라이센스 문제로 구매하기 힘들거나 UNIX/LINUX의 telnet 환경에서 직접 작업해야하는 경우에는 사용이 불편하지만 Oracle에서 제공하는 sqlplus를 사용하게 됩니다. 간단한 SQL문을 반복해서 수행해야 하는 경우에는 shell에서 SQL문을 실행하기를 원할 것입니다. shell에서 SQL문을 실행할 수 있으면 crontab에 등록하여 주기적으로 실행할 수도 있습니다. shell에서 sqlplus 통하여 간단한 SQL문을 실행하는 방법에 대해서 적어봅니다. (개인적으로는 C/Pro*C 등으로 개발을 주로 해서 shell 프로그래밍은 잘 못하지만 유용하게 copy & paste하여 사용할 수 있을 것 같아서 정리하였습니다.)

 

Shell에서 sqlplus 실행 기본 format 

sqlplus -s id/passwd@sid << EOF

# option 설정

......

실행할 SQL문1 ;

실행할 SQL문2 ;

......

exit
EOF

위와 같이 sqlplus -s id/passwd@sid << EOF 를 입력하면 로그인 후에 SQL문 입력을 기다리는 것과 같습니다. 그리고 실행하려는 SQL 문을 입력하고 반드시 ;을 붙여서 하나의 SQL문이 끝났음을 표시해야 합니다. exit 는 sqlplus를 종료한다는 의미입니다.

 

---

INSERT / UPDATE / DELETE문 실행

sqlplus -s id/passwd@sid << EOF

INSERT INTO 
...  ;


UPDATE 
...  ;


DELETE
... ;


commit;


exit
EOF

위와 같이 기본 format에 실행하고자 하는 SQL문을 입력하고 commit;을 입력하여 처리된 데이터를 commit합니다.

 

 

---

 

SELECT문 실행

sqlplus -s id/passwd@sid << EOF

set pagesize 10000
set linesize 140
set heading off
set feedback off

spool output.txt

SELECT .... FROM ...
WHERE ...
;

spool off

exit
EOF

SELECT문을 실행하는 목적은 대체로 SQL문을 실행한 결과를 file로 저장하기 위한 목적이 커기 때문에 위와 같이 약간은 복잡합니다.

set pagesize 10000는 fetch할 데이터의 건수를 10000으로 설정합니다. 
set linesize 140은 한 record를 display할 크기를 140으로 하겠다는 설정입니다. 
    select와 from절 사이의 column들의 사이즈를 봐서 값을 설정하면 됩니다.
set heading off는 sqlplus로 실행할 때에 column 이름 목록과 -------가 출력되고 데이터가 출력되는 데, 
    이것을 없애겠다는 의미입니다.
set feedback off는 sqlplus에서 fetch가 끝나면 표시되는 몇건 조회되었다는 문구가 표시되지 않게 합니다.
spool output.txt 는 fetch한 데이터를 저장할 파일명을 설정하는 것입니다. 
spool off는 파일로 저장하는 것을 끝내라는 의미입니다.

복잡한 업무는 Pro*C등으로 개발을 하시고 간단한 SQL문은 이렇게 shell로 짜서 사용하면 효율적일 것 같습니다.