Loading ...
Sorry, an error occurred while loading the content.

accept parameter from sqlplus script

Expand Messages
  • Abhijat Singh
    Hi, When I run individual sql scripts from DOS batch file one at a time, I do : call %fpath% 1_A.sql %2 if %errorlevel% GTR 0 then (goto :ERRBLOCK) and it
    Message 1 of 6 , Sep 26 11:12 AM
    • 0 Attachment
      Hi,
      When I run individual sql scripts from DOS batch file one at a time, I do : call %fpath%\1_A.sql" %2
      if %errorlevel% GTR 0 then (goto :ERRBLOCK)

      and it works fine.
      fpath has oracle credentials and path of the sql file.
      %2 is used to catch the error code received from sqlplus session it had invoked.
      Now I want to run 3 scripts simultaneously. So I do:

      call %fpath%\1_A.sql" %2 | %fpath%\2_B.sql" %2 | %fpath%\3_C.sql" %2.
      if %errorlevel% GTR 0 then (goto :ERRBLOCK)

      The idea is to go to ERRBLOCK even if any one of those 3 scripts failed.
      However, here I only get the error level of the sql sript that finished last. In other words, the problem is - even if two of the scripts failed and exited but if the last script to return to DOS batch file was successful, I will get 0 as errorlevel.

      Would you know a way to fix this please?

      Thanks very much.

      Kind Regards,
      Abhijat.
    • John O'Regan
      Hi Abhijat! Why is there a lone quote in the line call %fpath% 1_A.sql %2 Are you sure it s not causing problems? John
      Message 2 of 6 , Sep 26 3:31 PM
      • 0 Attachment
        Hi Abhijat!

        Why is there a lone quote in the line

        call %fpath%\1_A.sql" %2

        Are you sure it's not causing problems?

        John
      • Abhijat Singh
        Hi John, Thanks for looking into it. The lone quote has its pair in %fpath%. I am confident its not causing any problems because it works like that when I
        Message 3 of 6 , Sep 26 4:21 PM
        • 0 Attachment
          Hi John,
          Thanks for looking into it.
          The 'lone' quote has its pair in %fpath%. I am confident its not causing any problems because it works like that when I run the sql scripts one by one.
          Also, using the pipe'd command as I showed does work and opens three sql plus sessions simultaneously. Its just that the errorlevel returned is the errorlevel of the sql scripts thats last to finish.

          Kind Regards,
          Abhijat.

          --- In batchworld@yahoogroups.com, "John O'Regan" <john.a.oregan@...> wrote:
          >
          > Hi Abhijat!
          >
          > Why is there a lone quote in the line
          >
          > call %fpath%\1_A.sql" %2
          >
          > Are you sure it's not causing problems?
          >
          > John
          >
        • John O'Regan
          Hello Again, Thanks for clarifying the lone quote issue. However, I had another look and have more questions. You use %2, but there s no mention of %1.
          Message 4 of 6 , Sep 27 4:50 AM
          • 0 Attachment
            Hello Again,

            Thanks for clarifying the lone quote issue. However, I had another
            look and have more questions. You use %2, but there's no mention of
            %1. Also, you say %2 is used to catch the error code of the SQL
            session, but %2 is a read-only parameter. What am I missing here?

            John
          • Abhijat Singh
            ... Hi John, %2 is used to receive errorlevel from the sql plus session. I am not sure how it does it (I found it on google) but I have been using it for
            Message 5 of 6 , Sep 27 7:20 PM
            • 0 Attachment
              --- In batchworld@yahoogroups.com, "John O'Regan" <john.a.oregan@...> wrote:
              >
              > Hello Again,
              >
              > Thanks for clarifying the lone quote issue. However, I had another
              > look and have more questions. You use %2, but there's no mention of
              > %1. Also, you say %2 is used to catch the error code of the SQL
              > session, but %2 is a read-only parameter. What am I missing here?
              >
              > John
              >
              Hi John,
              %2 is used to receive errorlevel from the sql plus session. I am not sure how it does it (I found it on google) but I have been using it for months now and it works fine. If I don't use anything or use %1 or any other number, it doesn't work.
              Please note that there is a parallel arrangement that I have to make in sqlplus too to return the error back to the parent program.

              Hope this clarifies.

              Kind regards,
              Abhijat.
            • John O'Regan
              Hi Abhijat! I see you got some help resolving your issue on the alt.msdos.batch.nt newsgroup. Hope everything is working well for you now, John
              Message 6 of 6 , Sep 29 3:33 PM
              • 0 Attachment
                Hi Abhijat!

                I see you got some help resolving your issue on the
                alt.msdos.batch.nt newsgroup. Hope everything is working well for you
                now,

                John
              Your message has been successfully submitted and would be delivered to recipients shortly.