HiAll-
Here is the shell script which need to trigger a stored procedure and when the record count is 0 then it should capture in the log that 0 duplicate records are found and if it's more than 0 then log with no of records. Also if there is any sqlerror then it should write the same in the log file and it should send the communication in all scenarios. We have funct file which has declared with all directories and we are referring here for the log file path directory. But when i tried to execute it getting this error message "not found [No such file or directory]" - ./local/dir1/funct.sh
PS - i have all the privileges to this file
#!/bin/ksh
./local/dir1/funct.sh
sqlplus -s "${ORAUSER}"/"${ORAPASSWD}"@"${ORASRVC}" <<EOF > "$LOG_TEXT"
set head off
set serveroutput on
SELECT TO_CHAR(SYSDATE,'MMDDRRRRHH24MISS') FROM DUAL;
EOF
TIME_STAMP=$(cat "${LOG_TEXT}")
LOG_FILE_NAME='FileStatus'${TIME_STAMP}'.log'
LOG_FILE=${LOGFILEDIR}'/FileStatus/'${LOG_FILE_NAME}
write_log "Database Timestamp is $TIME_STAMP"
write_log "Executing FileStatus now..."
chmod 777 "${LOG_FILE}"
sqlplus -s "${ORAUSER}"/"${ORAPASSWD}"@"${ORASRVC}" <<EOF >> "${LOG_TEXT}"
set feedback off
set heading off
DECLARE
v_count NUMBER;
BEGIN
select count(*) INTO v_count from (select col1,col2,count(col3) from Tab1 group by col1,col2 having count(col3)>1);
whenever sqlerror exit -1
whenever oserror exit -1
execute FileStatus;
END;
EOF
if [[ ${v_count} -eq 0 ]]; then
write_log "FileStatus executed successfully."
write_log "No Duplicate records Found: $LOG_FILE"
mailx -s "No Duplicate records Found" XXXXX@gmail.com < "${success}""
elif [[ ${v_count} -gt 0 ]];
then
write_log "FileStatus executed successfully."
write_log "Number of duplicate records found::$v_count:$LOG_FILE"
mailx -s "Number of duplicate records found:$v_count" XXXXX@gmail.com < "${success}""
else
Write_log "FileStatus Failure."
mailx -s "FileStatus" XXXXX@gmail.com < "${failure}""
fi
cleanup
exit 0;