3 Replies Latest reply: Apr 18, 2013 10:30 AM by Gary Graham-Oracle RSS

    set define off.with ; or with out semicolen .

    947771
      hi,

      SET DEFINE OFF

      CREATE OR REPLACE PROCEDURE myproc
      IS
      v_txt VARCHAR (10);
      BEGIN
      v_txt := '&abc';
      END;

      1)i want to keep this in file, please tel me should i use "SET DEFINE OFF;" OR "SET DEFINE OFF".
      that is with out ";".

      2) and should i put / after SET DEFINE OFF or not.

      3) should i put / in last of stored proc. i keep only one stored proc in one file.

      yours sincerely

      Edited by: 944768 on Apr 17, 2013 10:58 PM

      Edited by: 944768 on Apr 17, 2013 11:03 PM

      Edited by: 944768 on Apr 17, 2013 11:11 PM
        • 1. Re: set define off.with ; or with out semicolen .
          Gary Graham-Oracle
          Hi,

          You do not say which version of SQL Developer you use and the answer may depend upon it. For the latest production version, 3.2.20.09.87, the following works fine:

          1. Create a file called myproc.sql with the contents as shown below
          2. Run the file either by...
          _a. File -> Open;  select connection;  Run Script (F5)
          _b. Copy/paste contents of myproc.sql into an open, connected worksheet; Run Script (F5) 
          SET DEFINE OFF
          
          CREATE OR REPLACE PROCEDURE myproc
          IS
          v_txt VARCHAR (10);
          BEGIN
          v_txt := '&abc';
          dbms_output.put_line(v_txt);
          END;
          3. To verify, paste the following into another worksheet and Run Script (F5)
          set serveroutput on
          exec myproc
          
          ...to see the following in the Script Output pane...
          
          anonymous block completed
          &abc
          Note that set define on|off|<value> works, but show define always displays define "<value>" regardless of whether off or on is in effect, so SQL Developer has a slight incompatibility with SQL*Plus there.

          Regards,
          Gary
          SQL Developer Team
          • 2. Re: set define off.with ; or with out semicolen .
            947771
            1)can I use same thing in sql plus also?

            i have sql devloper Version 3.1.07 and oracle

            Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
            PL/SQL Release 11.2.0.2.0 - Production
            "CORE     11.2.0.2.0     Production"
            TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
            NLSRTL Version 11.2.0.2.0 - Production

            2)and i think people might be using 10 or 9 so it should be compitable with that also.

            my script may be run on those systems also?

            so please tel me both 1) and 2) sepratlly.

            yours sincerely
            • 3. Re: set define off.with ; or with out semicolen .
              Gary Graham-Oracle
              1)can I use same thing in sql plus also?
              Almost. To use the exact same file for both SQL Developer and SQL*Plus, just add "/" terminator after the procedure. SQL*Plus requires it, and will compile it once; SQL Developer will compile it twice, but no harm done.
              2)and i think people might be using 10 or 9 so it should be compitable with that also. my script may be run on those systems also?
              I do not have time to test for you (of course, you should do that!), but I suspect the rules about SQL and PL/SQL statement termination have been stable for a very long time.

              -Gary