This discussion is archived
3 Replies Latest reply: Apr 18, 2013 8:30 AM by Gary Graham RSS

set define off.with ; or with out semicolen .

947771 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points