953495 wrote:Whether it's at the beginning of a script, later in the script, or not in a script at all, the SQL*Plus command
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?
tells you if DEFINE is ON or OFF.
If DEFINE is OFF, it will produce
define "&" (hex 26)
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
Regardless of whether DEFINE was ON or OFF before you issue that command, you can be sure it will be OFF after that.
SET DEFINE OFF