If it will make you feel any better, SQL Developer isn't the only tool which doesn't like such a code: TOAD (quite popular in Oracle world) is also one of them.
I can't explain it officially- could try, though, using my own words: SQL*Plus is a command-line tool (the one which has a strictly defined prompt). TOAD and SQL Developer are not - there's, well, some kind of an editor which is also used to execute commands. Variables' declaration, as well as some SQL*Plus commands (PRINT is one of them; there are many others!) are not valid in this context.
Error message doesn't seem to be meaningful; however, you got it and, as far as I can tell, you'll have to live with it. Therefore, either run such things in SQL*Plus, or rewrite the code.
Not very helpful, I'm afraid ... But, wait a little bit more - perhaps you'll hear better news from someone else.
I don't have a copy of SQL Developer to hand, but it does not claim to support all SQL*Plus commands (as it makes clear here). VARIABLE, REFCURSOR and PRINT are from SQL*Plus and not necessarily supported by any other tools.
A code was working in sqlplus work sheet but not on sql promt.
we copied same code in Unix & found some characters, which were not visible in sqlprompt & work sheet. Removing that character solved the problem.
I am not sure but youcan give a try to find out any such unwanted charater.
[QUOTE=Littlefoot]If it will make you feel any better, SQL Developer isn't the only tool which doesn't like such a code: TOAD (quite popular in Oracle world) is also one of them.
I'm curious what version of TOAD you're using. I won't speak the the free version but licensed versions (I'm on 8.6) had no trouble executing the SCRIPT - script is the operative word. i.e. It won't execute as an SQL statement but it will execute as a script (lightening bolt on the script debugging toolbar or execute as script from the SQL editor toolbar.)
Can't help you out on making it work in SQL Developer - it's free and I have a copy collecting dust on my harddrive but it didn't take me long to figure out that SQL Developer is well behind other tool's cababilities.
If budget is the reason you're intent on SQL Developer you may be hosed. I don't know if Bethnic Software's offerings will run you scripts (such as Golden and PLEdit.) They are considerably more affordable than TOAD and may be worth a look. I have a colleague who prefers them to TOAD - you can get trial versions at their website.
Well, SQL Developer is a friendlier interface than Oracle's Enterprise Manager, which sucks loads in a uniquely Java sort of way, and that is why we've been trying out Developer. But I quickly found the SQL Developer lacks almost all admin functionality, which is definitely a minus.
I've used TOAD, which was OK, but I actually preferred another product that I tried out last year, of which I can't currently remember the name...
If it's not practically useful, then it's practically useless.
Isn't the current version 8.6? 7.6 must be from five years ago. In any case are you sure you are running it as a script? TOAD has a separate "Run As Script" button as jhebert mentioned.
PL/SQL Developer doesn't suppport REFCURSOR variables in its SQL*Plus emulator (Command Window), but you would just set it up in a Test Window and define bind variables in the lower panel of the GUI. You can also run multiple tests from the Test Manager which lights up red or green indicators depending on success or failure.