How to handle oracle sql error in Unix shell script?

---   inpfile.sql  

SET FEEDBACK OFF
SET HEADING OFF
SET ECHO OFF
SET VERIFY OFF
SET PAGESIZE 0
SET TERMOUT OFF
whenever sqlerror exit 9
SPOOL 'test.txt'
declare
v number;
begin
     SELECT EMPNO into v
       FROM ABC
      WHERE DENO = 999 ;
end;
SPOOL OFF;
EXIT;



--- unix file
 sqlplus scott/tiger@orcl  @inpfile.sql
 if [ $? -eq 0 ]
 then
       echo "last command exit status needs to be checked"
exit
 else
      echo "last command exit status is successful"
 fi


When I run the Unix shell script then it stopped in sqlplus .
In sql query there is no data present and I do not handle it in exception block.
So I am going to capture the error through whenever sqlerror exit 9.
It does not return the message "last command exit status needs to be checked".

When unix shell script executed then the cursor stops on sqlplus.

What is wrong with the code ?

Please help.
digs developerAsked:
Who is Participating?
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
My expertise is Oracle.  I don't know how you would do that with SQL Server.  I would suggest redirecting output to a file and searching the file like the examples here, but I don't know what to look for in the log file.  You would essentially need to change the grep command to look for a standard tag that SQL Server puts on an error message.
0
 
johnsoneSenior Oracle DBACommented:
On a quick look, you are missing a / to signal the end of the PL/SQL block.

SET FEEDBACK OFF
SET HEADING OFF
SET ECHO OFF
SET VERIFY OFF
SET PAGESIZE 0
SET TERMOUT OFF
whenever sqlerror exit 9
SPOOL 'test.txt'
declare
v number;
begin
     SELECT EMPNO into v
       FROM ABC
      WHERE DENO = 999 ;
end;
/
SPOOL OFF;
EXIT;

Open in new window


I will try to set up a test case to verify that is the only problem, but that looks like the first one.
0
 
slightwv (䄆 Netminder) Commented:
In an anonymous pl/sql block, you need the results of a select to go somewhere.  A straight select like that will error all the time.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
slightwv (䄆 Netminder) Commented:
I think it needs to be more complex that you are making it.

sqlplus ran fine even with no_data_found so the return code from sqlplus will likely always return 0.

You'll probably need to grep the spooled output for an Oracle exception and take action based on that.
0
 
digs developerAuthor Commented:
thank you !
Yes , I did /  But in unix it is not showing any message through echo comand
Its showing
./test_28.sh: line 9: syntax error: unexpected end of file

I want see the messages.

Please help.
0
 
johnsoneSenior Oracle DBACommented:
You need to make a change in your shell script.  You are checking the return code backwards.  The code needs to be this:

 sqlplus scott/tiger@orcl  @inpfile.sql
 if [ $? -ne 0 ]
 then
       echo "last command exit status needs to be checked"
exit
 else
      echo "last command exit status is successful"
 fi

Open in new window


The reason you are not seeing any messages on your screen is because of the SPOOL command.  The messages are in the test.txt file that you are generating.

@slightwv, there is an into clause on the select.  The error is not there.  The issue is that the block isn't running because of the missing /.
0
 
slightwv (䄆 Netminder) Commented:
>>sqlplus ran fine even with no_data_found so the return code from sqlplus will likely always return

Correct that...  the whenever sqlerror does seem to work.

>>there is an into clause on the select

Yep...  I'm wrong all around on this one.  I'll leave it to you since I don't have Unix.
0
 
johnsoneSenior Oracle DBACommented:
Sorry, I misread the error message you posted.  This error:

./test_28.sh: line 9: syntax error: unexpected end of file

is due to some problem in your shell script.  Not sure what it is because I don't have yours.  The 2 scripts that I have posted give the results that you should be looking for.
0
 
digs developerAuthor Commented:
Please see below code stil I am not capturing the sqlerror code in unix shell script.


sqlplus scott/tiger@orcl @test.sql
        echo "Connecting Oracle DB"
     
    if [ $? -eq 0 ]
    then
        echo "test.sql successfully run."        
    else
        echo "There is error while running the test.sql"
        exit
    fi

   
--Below is test.sql
-- IT GIVES TOO MANY ROWS ERROR
SET FEEDBACK OFF
SET HEADING OFF
SET ECHO OFF
SET VERIFY OFF
SET PAGESIZE 0
SET TERMOUT OFF
SET DEFINE OFF
SET SERVEROUTPUT ON
WHENEVER SQLERROR EXIT 9
declare
v number;
BEGIN

       SELECT no into v
       FROM abc
       where no in ( 5406,5447,5506,5538 );
    END ;
    /
    exit;
0
 
slightwv (䄆 Netminder) Commented:
remove the echo after sqlplus.

$? is the return value from the previous command, which is the echo.
0
 
johnsoneSenior Oracle DBACommented:
As mentioned, remove the echo after the sqlplus command.

Alternately, you could do it this way.  I prefer to put the return code in a variable, that way you don't have a problem and can use it in your echo statements within the if.

sqlplus scott/tiger@orcl @test.sql
sqlret=$?
        echo "Connecting Oracle DB"
      
    if [ $sqlret -eq 0 ]
    then
        echo "test.sql successfully run."        
    else
        echo "There is error while running the test.sql"
        exit
    fi

Open in new window


Also, the message from the echo statement Connecting Oracle DB is really misleading.  At the point where that happens, the script has already run to completion.
0
 
digs developerAuthor Commented:
Thank you !

But I want to show oracle error and message .
i.e. too_many_rows instead of  "There is error while running the test.sql"
0
 
digs developerAuthor Commented:
Hello Johnsone,

Please help !

Thank you!
0
 
johnsoneSenior Oracle DBACommented:
The way I see it, there are 2 things you can do.

Instead of returning 9 on error return sql.sqlcode.  That should return you the error number into your return value, but it may not be exactly what you are looking for.  Some errors are negative which return strange looking numbers and some overflow the size of the return code variable and give strange results.  If you can figure out a reliable translation of the error numbers, then it should work, but it will only give you a number, not the text.

I think the better way is to probably search your spool file for errors.  Maybe something like this:

sqlplus scott/tiger@orcl @test.sql
sqlret=$?
echo "Connecting Oracle DB"
      
if [ $sqlret -eq 0 ]
then
  echo "test.sql successfully run."        
else
  echo "There is error while running the test.sql"
  echo "Errors found in test.txt:"
  grep ORA- test.txt | sed 's/^/        /'
  exit
fi

Open in new window


That assumes that you spooled all the output into test.txt.  If that is not the name of your file, then you need to change it on the 2 lines where it is referenced.
0
 
digs developerAuthor Commented:
Thank you!

How can we capture the error message ?
0
 
johnsoneSenior Oracle DBACommented:
I showed you how to capture the message in the log file and then get it out.  What exactly are you looking for?  If you are looking for custom message processing, then a SQL script isn't going to do it.
0
 
digs developerAuthor Commented:
Thank you so much !!
I appreciate your help.


There is one more question is open which is same as this.
Instead of oracle error I want MS sql server error.

Could you please look and help me.

http://www.experts-exchange.com/OS/Unix/Q_28497703.html

Thank you!
0
All Courses

From novice to tech pro — start learning today.