Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Checking plsql flag status in UNIX

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Checking plsql flag status in UNIX
# 1  
Old 06-19-2015
Checking plsql flag status in UNIX
Hi,I have a stored procedure to verify no of months since current fiscal.I want a shell script to exit with returtn code when the verification fails,I have shell script below but this doesnt work

Code:
sqlplus / << EOF
WHENEVER SQLERROR exit 1
WHENEVER OSERROR  exit 2

DECLARE
v_time_period number;
v_months number;
v_error_flag number;
BEGIN

SELECT COUNT (DISTINCT (TIME_PERD)) into  v_time_period 
FROM table
WHERE TIME_PERD > (select to_date('30-06' ||to_char(sysdate, 'YYYY') ,'dd-mm-yyyy') from dual);

        
SELECT TRUNC (
MONTHS_BETWEEN (SYSDATE, TO_DATE ('0106'||(to_char(sysdate, 'YYYY')-1) ,'dd-mm-yyyy')))
Months into v_months  FROM DUAL;


IF v_time_period  != v_months
  THEN
  v_error_flag := 1;
Sp_Dr_Send_Email_test ('No of months since current fiscal','Verification failed',
'myname@email.com');
 END IF;
END;
/

exit;
EOF`

if [ "$error_flag" = "1" ] ; then

echo $?
exit 1

fi

Last edited by jim mcnamara; 06-19-2015 at 11:58 AM..
# 2  
Old 06-19-2015
You can't set an environment variable in that fashion; that, technically is not a shell script. It is sqlplus script. You can assign the results of the sqlplus operation to an environment variable and then test. What ever you echo to standard output in the sqlplus script will be assigned to the variable
Code:
foo=`sqlplus......`

See unix - How to store result from SQLPlus to a shell variable - Stack Overflow
# 3  
Old 06-19-2015
All you have to do is the the $? variable on exit of sqlplus. zero == okay, anything else is an error - in your case 1 and 2.

Code:
if [ $? -ne 0 ] ; then
  # do something here to notify error status exists.
fi

Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Unix Shell Scripting( Calling from Unix to PLSQL)

Hello Experts, I have the following questions to be discussed here at this esteemed discussion forum. I have two Excel sheets which contain Unix Commands llike creating directory the structure/ftp/Copy/Zip etc to basically create an environment. I need help in understanding some of... (1 Reply)
Discussion started by: faizsaadq
1 Replies

2. Shell Programming and Scripting

Checking directory status

Can I use -ctime/-mtime to verify if a particular directory has been updated or not? I don't care about number of days. I just want to perform some operations only if the folder is modified (or it's metadata is modified), i.e. some files are added to the directory. This thread has a more... (1 Reply)
Discussion started by: Pramit
1 Replies

3. Shell Programming and Scripting

checking the status of sendmail

Hi All, I like to check the status of sendmail and take the appropriate action based on success / failure etc. I have gone through one of the thread where a suggestion is made to use RC for return code Following is the code: ================================== #!/usr/bin/bash export... (0 Replies)
Discussion started by: tmanda
0 Replies

4. Shell Programming and Scripting

Pass a value from Unix to PLSQL

Hi all, I need to pass a value from Unix to plsql block so that i can use the variable to in where caluse to get the desired output. I tried useing $variable_name, but it doesn't work. Any kind of help is appreciated. Thanks in advance. (3 Replies)
Discussion started by: bankimmehta
3 Replies

5. Shell Programming and Scripting

Checking Status of PID

I'm developing a script that spawns many background processes that will be executing concurrently and need to check the exit status of each spawned process after it terminates. After starting the background process I can get the pid from $! which I can store in an associative array but how do I... (2 Replies)
Discussion started by: twk
2 Replies

6. Shell Programming and Scripting

checking for a flag in a subscript?

I want to incorportae a subscript in a job script which is used for loading purposes. What i require is that before a job runs it should check for a flag ,if flag is not present then create it and the loading should start. Once loading finishes it should delete the flag. So if any other load... (2 Replies)
Discussion started by: bathla
2 Replies

7. AIX

Checking for flag in a subscript?

I want to incorportae a subscript in a job script which is used for loading purposes. What i require is that before a job runs it should check for a flag ,if flag is not present then create it and the loading should start. Once loading finishes it should delete the flag. So if any other load... (1 Reply)
Discussion started by: bathla
1 Replies

8. Shell Programming and Scripting

Shell to run one after another checking any flag or file

a.ksh & b.ksh run at the same time and takes huge resource and time.I want to put a technique so that one wil run after another. eg put a flag so that each script will check if it running , then sleep and wait it to finish. Can some one advise (3 Replies)
Discussion started by: konark
3 Replies

9. Shell Programming and Scripting

Checking Exit Status

I hope one of you smart people out there can help me with what seems like a real simple questing but I can't quite figure out. In a script I am doing a cmp on two files. I am trying to check the exit status with an if statement but can't seem to figure out the syntax. If the exit status is 1 I... (4 Replies)
Discussion started by: PrimeRibAndADew
4 Replies
Login or Register to Ask a Question