Hi Gurus,

I have a requirement where i need to capture the values in Oracle table. The Column name is Status with values either 'F' or 'S' i.e., Failure or Success. Now i need to find if the table contains value 'F'. For failure, it has to return value 0 and for a success then it has to return 2.

lets say for example,

i fire a query on oracle table: select status from ctrl_table;

and if it returns the values S S F S S

then my script should return a failure message with code 0.

and if the query returns S S S S S

then the script should return a success message with code 2.

Please throw a light on this problem as i am a newbie to Shell Scripts.

Rgds

-S

You can use the "exit" SQL Plus command to set the status:

Code:

VAR RC NUMBER;
BEGIN
 Select Decode(Status,'SSFSS',0,'SSSS',2,1) Into :RC
   From Status_Table Where Status_Key='MYPROC1';
END;
/
EXIT :rc

NOTE: Normally status 0 means OK and other that 0 means Failure.