[Oracle] 프로시저

ssh 에서

1. 파일명.sql   (path : /home/oracle/query/dailyBatchJob.sql )

SET SERVEROUTPUT ON;

CALL USER_PROC();

CALL PROC_CHANGE_INACTIVE();

CALL PROC_QUIT_MEMBER();

EXIT

 

2. 쉘 스크립트로 만든다.

 

   1)  run.sh // sql 실행하는 스크립트 (/home/oracle/query/run.sh)

#!/bin/bash ORACLE_BASE=/oracle/app/oracle; export ORACLE_BASE ORACLE_HOME=/oracle/app/oracle/product/11.2.0/dbhome_1; export ORACLE_HOME LD_LIBRARY_PATH_64=$ORACLE_HOME/lib; export LD_LIBRARY_PATH_64 LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib:$ORACLE_HOME/lib32:/usr/lib; export LD_LIBRARY_PATH CLASSPATH=$CLASSPATH:$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib: export CLASSPATH PATH=$ORACLE_HOME/OPatch:$ORACLE_HOME/bin:/usr/ccs/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:/sbin:/bin:/usr/ucb:/usr/dt/bin:/usr/openwin/bin:.; export PATH NLS_LANG=AMERICAN_AMERICA.AL32UTF8; export NLS_LANG

sqlplus db아이디/\"db패스워드\"@db알리아스 @/home/oracle/query/dailyBatchJob.sql

 

   2) runMoveLog.sh  // 로그 현재날짜로 업데이트하는 스크립트 (/home/jj/runMoveLog.sh)

#!/bin/bash
TODAY=`date +%Y%m%d`

cat /home/oracle/logs/daily.log > /home/jj/logs/daily.log.$TODAY