Prevents Oracle from doing any variable substitution.
If define is set to on and SQL*Plus finds the current substituion prefix, it asks for a string to be entered. In the following example, I entered: this string was entered. It might be annoying to see the following lines printed by SQL*Plus: This behavior can be turned off by setting verify off.  The client referred to in the statistics is SQL*Plus. Oracle Net refers to the generic process communication between SQL*Plus and the server, regardless of whether Oracle Net is installed. You cannot change the default format of the statistics report. For a more complete list of database statistics, see the Oracle Database Reference. You can automatically get a report on the execution path used by the SQL optimizer and the statement execution statistics. The report is generated after successful SQL DML (that is, SELECT, DELETE, UPDATE and INSERT) statements. variable = text. Defines (names) a substitution variable and assigns it a CHAR value. Enter DEFINE followed by variable to list the value and type of variable. Enter DEFINE with no clauses to list the values and types of all substitution variables. You can use UNDEFINE to remove a substitution variable definition and make it unavailable. Examples of Use of Predefined Variables. To change your SQL*Plus prompt to display your connection identifier, enter: To view the predefined value of the _SQLPLUS_RELEASE substitution variable, enter. You can also use the DEFINE command to redefine their values, or you can use the UNDEFINE command to remove their definitions and make them unavailable. To view a specific variable definition, enter. where variable is the name of the substitution variable whose definition you want to view. To view all predefined and user defined variable definitions, enter. All predefined and all user defined variable definitions are displayed. You can use UNDEFINE to remove a substitution variable definition and make it unavailable. Examples of Use of Predefined Variables. To change your SQL*Plus prompt to display your connection identifier, enter: To view the predefined value of the _SQLPLUS_RELEASE substitution variable, enter. The SET DEFINE OFF command in Oracle prevents Oracle from doing any variable substitution. Use the SET DEFINE OFF command in Oracle to prevent Oracle from interpreting the ampersand for variable substitution. The SET DEFINE ON command turns variable substitution back on.  SET DEFINE. 'SET DEFINE' is useful when you are working with sql*plus. There are three combinations of 'SET DEFINE' and those are: 1. set define x: set define x specifies the prefix-character for substitution variables. The default is the ampersand (&). set define &; select * from dba_objects where object_name like '%&object_name%'; 2. set define on: Turns on substitution variables. Full Definition of ORACLE. 1. a: a person (as a priestess of ancient Greece) through whom a deity is believed to speak b: a shrine in which a deity reveals hidden knowledge or the divine purpose through such a person c: an answer or decision given by an oracle. 2. Definition of ORACLE for Kids. 1. : a person (as a priestess in ancient Greece) through whom a god is believed to speak. 2. : the place where a god speaks through a person. 3. : an answer given by a person through whom a god speaks SET DEFINE OFF disables the parsing of commands to replace substitution variables with their values. Call this before the query: Alternatively, hacky: From Tuning SQL*Plus : SET DEFINE OFF disables the parsing of commands to replace substitution variables with their values.  This behavior can be turned off by setting verify off. 3. set define off Turns off substitution variables. set define off; select '&hello' from dual; '&HELL. ----------------. &hello. 1  set define on. 1. set define x: set define x specifies the prefix-character for substitution variables. The default is the ampersand (&). set define &; select * from dba_objects where object_name like '%&object_name%'; 2. set define on: Turns on
http://www.adp-gmbh.ch/ora/sqlplus/set_define.html http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch8.htm http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12017.htm http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12017.htm https://techsaints.wordpress.com/2008/07/31/oracle-set-define-off/ http://shaharear.blogspot.com/2009/01/set-define.html http://www.merriam-webster.com/dictionary/oracle http://stackoverflow.com/questions/2333994/how-to-avoid-variable-substitution-in-oracle-sql-developer-with-trinidad-toba http://shaharear.blogspot.com/2009/01/set-define.html
set define off meaning in oracle