3 Replies Latest reply on Apr 18, 2013 3:30 PM by Gary Graham-Oracle

    set define off.with ; or with out semicolen .

    947771
    947771 Apr 18, 2013 6:11 AM
      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
      • 5414 Views
      • Tags:
        • 1. Re: set define off.with ; or with out semicolen .
          Gary Graham-Oracle
          Gary Graham-Oracle Apr 18, 2013 7:49 AM (in response to 947771)
          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
          1 person found this helpful
          • 2. Re: set define off.with ; or with out semicolen .
            947771
            947771 Apr 18, 2013 2:22 PM (in response to Gary Graham-Oracle)
            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
              Gary Graham-Oracle Apr 18, 2013 3:30 PM (in response to 947771)
              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