5 Replies Latest reply on Mar 1, 2012 6:59 AM by Vidhya

    SET DEFINE OFF

    roboracle
      I had an insert script with data strings having '&' character. While executing it on sqlplus it prompted for a value at each occurrence of '&' .

      Simple solution was to put a "SET DEFINE OFF" at the beginning of the script. It suppressed the prompt for value.

      However, it left me with a question. What if my insert script require both things...
      1. Insert data strings having '&' character.
      2. Prompt for a value at few places.

      How would I achieve that?

      Thanks for your inputs.

      --Rob                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
        • 1. Re: SET DEFINE OFF
          730428
          Letting DEFINE ON and escaping the & when it is used as a standard character:
          SQL> set escape \
          SQL> select '\& as a character' xxx
            2  from dual
            3  where dummy = '&val';
          Enter value for val: X
          old   3: where dummy = '&val'
          new   3: where dummy = 'X'
          
          XXX
          ----------------
          & as a character
          Max
          http://oracleitalia.wordpress.com
          1 person found this helpful
          • 2. Re: SET DEFINE OFF
            Frank Kulash
            Hi, Rob,

            Besides ESCAPE, you can SET DEFINE ON and OFF as often as you like; after every INSERT statement, if you want.

            You can also change the DEFINE character to something other than &. For example, if your data never uses the ~ character:
            SET  DEFINE  ~
            ...
            INSERT INTO table_x (emp_name,       firm_name)
                         VALUES (~employee_name, 'Dewey, Cheatham & Howe');
            Edited by: Frank Kulash on Feb 21, 2010 3:27 PM
            1 person found this helpful
            • 3. Re: SET DEFINE OFF
              728534
              Hi Massimo,
              SQL> select '& as a character' xxx
                2  from dual
                3  where dummy='&val';
              Works without the escape character too.
              Believe to demonstrate what you exactly meant it should be something like this
              SQL> select '&asacharacter' xxx
                2  from dual
                3  where dummy='&val';  /* put Y as value for asacharacter and X for VAL */
              
              XXX
              ---
              Y
              
              SQL> select '\&asacharacter' xxx
                2  from dual
                3  where dummy='&val';/* put  X for VAL , would not ask value for asacharacter since we have the excape character there*/
              Cheers!!!
              Bhushan
              • 4. Re: SET DEFINE OFF
                730428
                Believe to demonstrate what you exactly meant it should be something like this
                yes, you're right.

                Thanks,
                Max
                • 5. Re: SET DEFINE OFF
                  Vidhya
                  If you set as SET DEFINE ON,Then it will ask the variable value .
                  If you set as SET DEFINE OFF,Then it will not ask the variable value .

                  Example,

                  if you set - SET DEFINE ON

                  select &a from dual - (it will show the popup window)

                  if you set -SET DEFINE OFF

                  select &a from dual - (it will not show the popup window)