1 Reply Latest reply: Aug 19, 2012 8:58 PM by Frank Kulash RSS

    Is there any way to know that status of DEFINE variable?

    956498
      Hi,

      I have a list of scripts that is executed to create my baseline database. At the beginning of the script execution, we have a SET DEFINE OFF and at the end we put SET DEFINE ON. During one of the scripts, we deliberately SET DEFINE ON and we forgot to SET DEFINE OFF at the end of the script. Consequently, the following scripts started failing without error notification because they contained '&' in the INSERT query. I have the following queries:

      1. Is there any way to know the status of the DEFINE variable at the beginning of the script?
      2. Since the oracle gives a user interrupted error, the script execution process does not terminate with error. Instead, it continues executing the next scripts. Is there a general way to capture such errors and terminate the script execution. We already have WHENEVER SQLERROR and WHENEVER OSERROR in our script execution process.

      Regards
      Kunal
        • 1. Re: Is there any way to know that status of DEFINE variable?
          Frank Kulash
          Hi, Kunai,

          Welcome to the forum!
          953495 wrote:
          Hi,

          I have a list of scripts that is executed to create my baseline database. At the beginning of the script execution, we have a SET DEFINE OFF and at the end we put SET DEFINE ON. During one of the scripts, we deliberately SET DEFINE ON and we forgot to SET DEFINE OFF at the end of the script. Consequently, the following scripts started failing without error notification because they contained '&' in the INSERT query. I have the following queries:

          1. Is there any way to know the status of the DEFINE variable at the beginning of the script?
          Whether it's at the beginning of a script, later in the script, or not in a script at all, the SQL*Plus command
          SHOW DEFINE
          tells you if DEFINE is ON or OFF.
          If DEFINE is ON, it will produce something like:
          define "&" (hex 26)
          If DEFINE is OFF, it will produce
          define OFF
          2. Since the oracle gives a user interrupted error, the script execution process does not terminate with error. Instead, it continues executing the next scripts. Is there a general way to capture such errors and terminate the script execution. We already have WHENEVER SQLERROR and WHENEVER OSERROR in our script execution process.
          Do you really need to capture those errors? Why not just prevent them? It sounds like you want to make sure DEFINE is OFF at a certain point. To do that, just say
          SET  DEFINE  OFF
          Regardless of whether DEFINE was ON or OFF before you issue that command, you can be sure it will be OFF after that.

           

          I hope this answers your question.
          If not, post a complete test script (or set of scripts) that people can run to re-create the problem and test their ideas.
          Point out where the scripts you post are not doing what you want, and describe, with specific examples, what you want it to do in those places.