crontab에서 plsql 실행

Database/Oracle 2009. 9. 6. 19:09

[참조] smartsql.tistory.com

sqlplus 실행시 @를 붙여 별도의 sql문을 실행하면서 해도 되지만
파일을 별도로 관리해야 되는 번거로움 때문에 쉘스크립트상에서 실행하기로 했음

crontab 에 등록시
4 19 * * * /oracle/shell/call_proc_profit_dist.sh >> /oracle/shell/log/proc_profit_dist.log

file: call_proc_profit_dist.sh

#!/bin/bash
export ORACLE_BASE=/oracle
export ORACLE_HOME=/oracle/10.2.0
export ORACLE_OWNER=oracle
export ORACLE_SID=kumc
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORA_NLS10=$ORACLE_HOME/nls/data
export NLS_LANG=KOREAN_KOREA.KO16MSWIN949
export NLS_NCHAR=KOREAN_KOREA.UTF8
export NLS_DATE_FORMAT=YYYYMMDD
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:$ORACLE_HOME/rdbms/demo:$ORAClE_HOME/oracm/lib:/lib:/usr/lib:/usr/local/lib
export LDPATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
export PATH=$ORACLE_HOME/JRE/bin:$ORACLE_HOME/bin:.:$PATH
export TEMPDIR=/tmp
export THREADS_FLAG=native
export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/oracm/bin
export CLASSPATH=$CLASSPATH:$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib
export EDITOR=vi
export LANG=ko_KR.eucKR
export PATH ORACLE_BASE ORACLE_HOME ORACLE_OWNER ORACLE_SID  TNS_ADMIN NLS_LANG LD_LIBRARY_PATH ORA_NLS33  PATH LD_ASSUME_KERNEL TEMPDIR THREADS_FLAG PATH CLASSPATH EDITOR LANG NLS_DATE_FORMAT DISABLE_HUGETLBFS LDPATH


sqlplus -s kumc_cost/kumc_cost << __END__
	WHENEVER SQLERROR EXIT 1
	WHENEVER OSERROR EXIT 1
	SET TIMING ON
	SET SERVEROUTPUT ON
	SET PAGESIZE 0
	SET FEEDBACK OFF
	SET VERIFY OFF
	SET HEADING OFF
	SET ECHO OFF

	DECLARE
		vMsg	VARCHAR2(4000)	:= NULL;
		vSQL	VARCHAR2(4000)	:= NULL;
		vCheck	BOOLEAN			:= TRUE;
		vSeq	NUMBER;
	BEGIN
		DBMS_OUTPUT.ENABLE('10000000000');

		SELECT	cpi_seq
		INTO	vSeq
		FROM	COST_PROCESS_INFO
		WHERE	cpi_status = 'P';

		PROC_PROFIT_DIST(vCheck, vMsg);
		--vSQL := 'CALL PROC_PROFIT_DIST(:vCheck, :vMsg)';
		--EXECUTE IMMEDIATE vSQL USING IN vCheck, OUT vMsg;
		--DBMS_OUTPUT.PUT_LINE(vMsg);
		
		EXCEPTION
			WHEN NO_DATA_FOUND THEN
				DBMS_OUTPUT.PUT_LINE('진행중인 절차가 없습니다.!!');
	END;
	/
__END__


EXITCODE=$?
export EXITCODE

if [ "$EXITCODE" -ne 0 ]
then
	echo "Error: SQL*Plus exit code : $EXITCODE"
	echo ""
	echo "===== Procedure run aborted at `date` !!! ===="
	echo ""
	exit 1
fi

echo ""
echo "===== Procedure Finished at `date` !!! ===="
echo ""

call_proc_profit_dist.sh