如何处理在调用sqlplus中，因为sqlplus中不会设置ERRRORLEVEL对错误或失败的批处理文件错误？ [英] How do I handle errors in a batch file that calls sqlplus, because sqlplus doesn't set ERRRORLEVEL on error or failure?

问题描述

我HV这里概率已经该带我天去解决它。

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.

在我的Oracle的SQLDeveloper，我有一个包名UT_BETWNSTR包含：

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;

和包体是这样的：

    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;

和函数名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;

在我的C盘，我把文件名BETWNSTR.sql包含：

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

这是我的批处理文件（也在C盘），名try.bat包含：

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来这里的误差

ok here comes the error

当我运行try.bat，它将返回大失败的结果，因为我故意把

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'
     );

而不是

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

我这样做的目的，这样我希望CMD将回音了：

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

You Got Error

因为在我的$ C $遇到错误c..but的错误级别为0呼应这意味着它是成功的。

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

我知道，我的code现在取决于errorlevel..when ERRORLEVEL为0将回声出良好的工作..

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..

我怎么能回声出你有错误消息时，错误级别总是显示0 ..

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..

我希望有人HV我的问题的解决方案可以请回答我的问题。

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

在此先感谢！

推荐答案

我不知道这是否会工作，但也许是值得一试。 （我没有Windows机器上，所以我无法测试，但它看起来大约右）这将取决于SQL的写到标准输出的能力。我不知道甲骨文，所以我不知道你是否能得到它做到这一点，但是如果可以的话，下面的小黑客应该工作，如果你知道错误/警告信息的标准模式。如果/ NOLOG停止打印到标准输出，取出下面的代码片段。值得一试。祝你好运！ ：）

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!

这篇关于如何处理在调用sqlplus中，因为sqlplus中不会设置ERRRORLEVEL对错误或失败的批处理文件错误？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！