This discussion is archived
1 Reply Latest reply: Aug 19, 2012 6:58 PM by Frank Kulash RSS

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

956498 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points