How do I handle errors in a batch file that calls sqlplus, because sqlplus doesn't set ERRRORLEVEL on error or failure?

Question

i hv a prob here which already took me days to solve it..

my explanation will be quite lengthy but i try to keep it short.

in my Oracle SQLdeveloper, i have a package name UT_BETWNSTR which contain:

create or replace
PACKAGE "UT_BETWNSTR" 
IS
PROCEDURE ut_setup;
PROCEDURE ut_teardown;

PROCEDURE ut_betwnstr;
END UT_BETWNSTR;

and the package body is like this:

    create or replace
PACKAGE BODY      "UT_BETWNSTR" 
IS
   PROCEDURE ut_setup IS
   BEGIN
      NULL;
   END;

   PROCEDURE ut_teardown
   IS
   BEGIN
      NULL;
   END;

   PROCEDURE ut_BETWNSTR IS
      BEGIN
       utAssert.eq (
            'Typical valid usage',
             BETWNSTR(
             STRING_IN => 'abcdefg',
             START_IN => 3,
            END_IN => 5)
           ,
         'abc'
         );
         utAssert.isnull (
           'NULL start',
             BETWNSTR(
             STRING_IN => 'abcdefg',
             START_IN => NULL,
             END_IN => 5)
           );
           utAssert.isnull (
              'NULL end',
            BETWNSTR(
            STRING_IN => 'abcdefg',
            START_IN => 2,
             END_IN => NULL)
           );
           utAssert.isnull (
                  'End smaller than start',
                   BETWNSTR(
                   STRING_IN => 'abcdefg',
                   START_IN => 5,
                   END_IN => 2)
          );
          utAssert.eq (
                  'End larger than string length',
                   BETWNSTR(
                   STRING_IN => 'abcdefg',
                   START_IN => 3,
                   END_IN => 200)
           ,
         'cdefg'
         );

           END ut_betwnstr;

END UT_BETWNSTR;

and the function name BETWNSTR is like this:

create or replace
FUNCTION BETWNSTR (
   string_in   IN   VARCHAR2,
   start_in    IN   INTEGER,
   end_in      IN   INTEGER
)
   RETURN VARCHAR2
IS
   l_start PLS_INTEGER := start_in;
BEGIN
   IF l_start = 0
   THEN
      l_start := 1;
   END IF;

   RETURN (SUBSTR (string_in, l_start, end_in - l_start + 1));
END;

in my C drive, i put a file name BETWNSTR.sql which contain:

connect hr/hr
SET SERVEROUTPUT ON
EXEC UTPLSQL.TEST('BETWNSTR',Recompile_in=>FALSE);
exit

and this is my batch file (also in C drive), name try.bat which contain:

@sqlplus /nolog @C:\betwnstr.sql

echo %errorlevel%
if errorlevel 0 goto Success
echo You Got Error

:Success
echo Good Job!!

pause

ok here comes the error

when i run try.bat, it will return the big FAILURE result as i purposely put

PROCEDURE ut_BETWNSTR IS
  BEGIN
   utAssert.eq (
        'Typical valid usage',
         BETWNSTR(
         STRING_IN => 'abcdefg',
         START_IN => 3,
        END_IN => 5)
       ,
     'abc'
     );

instead of

PROCEDURE ut_BETWNSTR IS
  BEGIN
   utAssert.eq (
        'Typical valid usage',
         BETWNSTR(
         STRING_IN => 'abcdefg',
         START_IN => 3,
        END_IN => 5)
       ,
     'cde'
     );

i did that in purpose so that i hope the cmd will echo out:

You Got Error

because got error in my code..but the errorlevel is echoing 0 which mean it is a success..

i know, my code now is depending on errorlevel..when errorlevel is 0 it will echo out Good Job..

what i want right now is, when i run the batch file, when it encounter an error, i can echo out You Got Error message..

how can i echo out "You Got Error" message when the errorlevel is always showing 0..

in short, i want to echo out error message without depending on errorlevel..

i am hoping anybody hv the solution for my problem could pls answer my question..

Thanks In Advance!

So your actual question is "How do I handle errors in a batch file that calls sqlplus, because sqlplus doesn't set ERRRORLEVEL on error or failure?" — Ken White
– Ken White, Commented Dec 2, 2011 at 1:30

Gerrit Griebel · Accepted Answer · 2014-01-29 20:17:51Z

3

All previous answers answered: "How do I handle errors in a batch file that calls sqlplus, when sqlplus doesn't set ERRRORLEVEL on error or failure?"

But you can actually make sqlplus exit on errors with a non-zero exit code using following sqlplus command:

whenever sqlerror exit sql.sqlcode

See also http://voidtech.wordpress.com/2013/03/06/execute-an-script-with-sqlplus-and-get-the-exitcode-to-the-environment/ I use it in this way on a windows machine with Oracle 10g:

sqlplus some_user/pw@tns_name @ some_script.sql
IF %ERRORLEVEL% NEQ 0 call some_action_on_error.vbs

answered Jan 29, 2014 at 20:17

Gerrit Griebel

4634 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

kikuchiyo · Accepted Answer · 2011-12-02 02:11:02Z

1

I am not sure if this will work, but perhaps it is worth a shot. ( I do not have a windows machine, so I cannot test, but it looks about right ) This will depend on sql's ability to write to standard output. I don't know Oracle, so I don't know if you can get it to do this, but if you can, the little hack below should work, if you know the standard pattern of error/warning messages. If /nolog stops printing to standard output, take it out of the below snippet. Worth a shot. Good luck! :)

setlocal enabledelayedexpansion

some_text_signifying_error=whatever you can get sql to display to Standard Output
set msg=''

For 'eol=; tokens=1 delims=' %%e in ('@sqlplus /nolog @C:\betwnstr.sql ^| findstr /i /c:"!some_text_signifying_error!"') do (
    set msg=!msg! %%e
)

if NOT !msg!='' echo !msg!

answered Dec 2, 2011 at 2:11

kikuchiyo

3,4213 gold badges26 silver badges30 bronze badges

7 Comments

kikuchiyo Over a year ago

See the first response of the following link which uses the same approach I suggested, with a little extra sqlplus content to help you fill in the blanks... dbforums.com/oracle/1044496-sqlplus-return-value-help.html

user1050754 Over a year ago

looks like ur answer is helping me a lot.. now im trying to digest it..i let u know if it is a success..

kikuchiyo Over a year ago

I am pleased to hear it is helping in some way. I wish I could help more, but I have 0 oracle experience >.< . If you end up just being stuck on a general problem or batch issue, feel free to ask for help. Good luck! :)

user1050754 Over a year ago

ok now i am able to catch the failure of my code! =D i am using For /F.. may i know how to echo out if it detect Failure, it will echo out You Got Prob, if it didnt detect any failure, it will return none..before this i am depending on errorlevel to echo out errorMessage..now i am no more depending on the errorlevel, thats why im a lil bit confuse here.. Thanks!

user1050754 Over a year ago

ok here is my code at the moment: setlocal enabledelayedexpansion For /F "tokens=* delims= " %%e in ('@sqlplus /nolog @C:\btwn.sql ^| findstr /i /c:"FAILURE"') do ( set msg=!msg! %%e ) PAUSE

|

Collectives™ on Stack Overflow

How do I handle errors in a batch file that calls sqlplus, because sqlplus doesn't set ERRRORLEVEL on error or failure?

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related