Tuesday, September 13, 2011

Differences between PL/SQL Developer and SQL*Plus



Tested on an Oracle 11 database. I have been trying out PL/SQL Developer, a product by allroundautomations. You can see details here: http://www.allroundautomations.com/ If you use it,  you need to be aware of subtle differences between this product and Oracle’s SQL*Plus.
 
If you have a file like this:
 
set echo on
prompt -
prompt - Check the date:
prompt -
select sysdate
from dual
/
 
And you try to run it in SQL*Plus, it will fail:
 
SQL> get todays_date
  1  set echo on
  2  prompt -
  3  prompt - Check the date:
  4  prompt -
  5  select sysdate
  6* from dual
SQL> @todays_date
SQL> set echo on
SQL> prompt -
> prompt - Check the date:
prompt - Check the date:
SQL> prompt -
> select sysdate
select sysdate
SQL> from dual
SP2-0042: unknown command "from dual" - rest of line ignored.
SQL> /
SP2-0552: Bind variable "PROMPT" not declared.
SQL>
 
That’s because SQL*Plus treats single hyphens as continuation characters so Oracle thinks that the select sysdate is part of the prompt on the preceding line. PL/SQL Developer does not do this and is able to execute the SQL (as usual, click the image to display it at its correct size and bring it into focus):


No comments: