This content has been marked as final. Show 3 replies
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)
3. To verify, paste the following into another worksheet and 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;
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.
set serveroutput on exec myproc ...to see the following in the Script Output pane... anonymous block completed &abc
SQL Developer Team
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 220.127.116.11.0 - Production
PL/SQL Release 18.104.22.168.0 - Production
"CORE 22.214.171.124.0 Production"
TNS for 32-bit Windows: Version 126.96.36.199.0 - Production
NLSRTL Version 188.8.131.52.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.
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.