This content has been marked as final. Show 3 replies
Hi,1 person found this helpful
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.