Sponsored Content
Top Forums UNIX for Beginners Questions & Answers Shell script to execute Oracle procedure and trigerring email on success and failure Post 303042196 by senmng on Wednesday 18th of December 2019 05:04:45 PM
Old 12-18-2019
Shell script to execute Oracle procedure and trigerring email on success and failure

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. Also can you help me out in the code logic as well?

PS - I have all the privileges to this file

Code:
#!/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" email< "${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" email < "${success}""
else
        Write_log "FileStatus Failure."
         mailx -s "FileStatus" email < "${failure}""
fi 
cleanup
exit 0;


Last edited by rbatte1; 12-19-2019 at 07:15 AM..
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Execute an Oracle stored procedure from a shell scrip

Here is a snippet of my code: if then echo "\n Deleting all reports older than 24 hours. \n" >> $logfile ls -l $FileName >> $logfile ... (1 Reply)
Discussion started by: mh53j_fe
1 Replies

2. Shell Programming and Scripting

run shell script from oracle store procedure

hi, this is urgent..can i run a shell script from store procedure without using java. (8 Replies)
Discussion started by: arnabb4u
8 Replies

3. Shell Programming and Scripting

Calling an Oracle Stored Procedure from Unix shell script

hai, can anybody say how to call or to execute an oracle stored procedure in oracle from unix... thanks in advance.... for ur reply.... by, leo (2 Replies)
Discussion started by: Leojhose
2 Replies

4. Shell Programming and Scripting

How to execute an Oracle procedure using shell

Hi , i have created an .sh file that has the following code: #!/bin/ksh sqlplus -s p1istuat/p1istuat@CWS_IST6 @Procedure_Execute.sql & sqlplus -s p1istuat/p1istuat@CWS_IST6 << EOF exit EOF The mentioned Procedure_Execute.sql file inside has the following code: exec TEST; ... (5 Replies)
Discussion started by: vins_san
5 Replies

5. Shell Programming and Scripting

Invoking Oracle stored procedure in unix shell script

Here's a shell script snippet..... cd $ORACLE_HOME/bin Retval=`sqlplus -s <<eof $TPDB_USER/april@$TPD_DBCONN whenever SQLERROR exit 2 rollback whenever OSERROR exit 3 rollback set serveroutput on set pages 999 var status_desc char(200) var status_code... (1 Reply)
Discussion started by: hidnana
1 Replies

6. Shell Programming and Scripting

Shell Script for call a procedure in Oracle DB

Hi everyone! I'm new with Shell Scripting, and I have to do a shell script to call a procedure, which have 2 input parameters, the directory(from server) and the txt file (which have informations to update/insert in DB). I have to create a shell script to execute that procedure for each txt... (5 Replies)
Discussion started by: renatoal
5 Replies

7. Shell Programming and Scripting

How to execute the stored procedure from shell script

How to execute the stored procedure from shell script and is there any possibility to print the dbms output in a log file. (2 Replies)
Discussion started by: dineshmurs
2 Replies

8. Shell Programming and Scripting

Execute a shell script from Oracle procedure

Hi Gurus, Want to execute a shell script from a oracle procedure and get the status of the same, any assistance in this regard will be appreciated. proc_data.sh is script name which I want to execute from oracle procedure It should work something like below procedure test begin... (1 Reply)
Discussion started by: palanisvr
1 Replies

9. Shell Programming and Scripting

Why i can't execute the procedure in shell script?

i have the following code inside a shell script .prog in oracle server when i call the program DBMS_OUTPUT.PUT_LINE(x_return_status|| ln_rep_req_id); will return 0 , it is very strange , i try to submit the concurrent request in oracle , and it can successfully executed, what am i missing ? i... (1 Reply)
Discussion started by: feilhk
1 Replies

10. Shell Programming and Scripting

Execute Oracle gather stats via shell script

Hi , I am trying to automate a gather stats in shell script #!/usr/bin/ksh export ORACLE_HOME=/orcl/app/oracle/product/11.2.0.1/db_1 export PATH="$PATH:$ORACLE_HOME/bin" export LD_LIBRARY_PATH="$LD_LIBRARY_PATH:$ORACLE_HOME/lib32" export TNS_ADMIN=/opt/netprobe/config... (1 Reply)
Discussion started by: neil.k
1 Replies
SCRIPT(1)						    BSD General Commands Manual 						 SCRIPT(1)

NAME
script -- make typescript of terminal session SYNOPSIS
script [-adfkpqr] [-F pipe] [-t time] [file [command ...]] DESCRIPTION
The script utility makes a typescript of everything printed on your terminal. It is useful for students who need a hardcopy record of an interactive session as proof of an assignment, as the typescript file can be printed out later with lpr(1). If the argument file is given, script saves all dialogue in file. If no file name is given, the typescript is saved in the file typescript. If the argument command is given, script will run the specified command with an optional argument vector instead of an interactive shell. The following options are available: -a Append the output to file or typescript, retaining the prior contents. -d When playing back a session with the -p flag, do not sleep between records when playing back a timestamped session. -F pipe Immediately flush output after each write. This will allow a user to create a named pipe using mkfifo(1) and another user may watch the live session using a utility like cat(1). -f Create file.filemon or typescript.filemon using filemon(4). -k Log keys sent to the program as well as output. -p Play back a session recorded with the -r flag in real time. -q Run in quiet mode, omit the start, stop and command status messages. -r Record a session with input, output, and timestamping. -t time Specify the interval at which the script output file will be flushed to disk, in seconds. A value of 0 causes script to flush after every character I/O event. The default interval is 30 seconds. The script ends when the forked shell (or command) exits (a control-D to exit the Bourne shell (sh(1)), and exit, logout or control-D (if ignoreeof is not set) for the C-shell, csh(1)). Certain interactive commands, such as vi(1), create garbage in the typescript file. The script utility works best with commands that do not manipulate the screen. The results are meant to emulate a hardcopy terminal, not an addressable one. ENVIRONMENT
The following environment variables are utilized by script: SCRIPT The SCRIPT environment variable is added to the sub-shell. If SCRIPT already existed in the users environment, its value is overwrit- ten within the sub-shell. The value of SCRIPT is the name of the typescript file. SHELL If the variable SHELL exists, the shell forked by script will be that shell. If SHELL is not set, the Bourne shell is assumed. (Most shells set this variable automatically). SEE ALSO
csh(1), filemon(4) (for the history mechanism). HISTORY
The script command appeared in 3.0BSD. The -d, -p and -r options first appeared in NetBSD 2.0 and were ported to FreeBSD 9.2. BUGS
The script utility places everything in the log file, including linefeeds and backspaces. This is not what the naive user expects. It is not possible to specify a command without also naming the script file because of argument parsing compatibility issues. When running in -k mode, echo cancelling is far from ideal. The slave terminal mode is checked for ECHO mode to check when to avoid manual echo logging. This does not work when the terminal is in a raw mode where the program being run is doing manual echo. If script reads zero bytes from the terminal, it switches to a mode when it only attempts to read once a second until there is data to read. This prevents script from spinning on zero-byte reads, but might cause a 1-second delay in processing of user input. BSD
December 4, 2013 BSD
All times are GMT -4. The time now is 03:47 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy