Tuesday, January 20, 2015

A Difference Between SQL*Plus and SQL Developer

A third-party supplier delivered some SQL today but it did not work in SQL*Plus. We asked the supplier about this and it turned that the code had been tested in SQL Developer. The reason for the failure was as follows. If you end a line of SQL with a semi-colon then add a comment afterwards, SQL*Plus rejects it with an ORA-00911

SQL> @test1
SQL> set echo on
SQL> select 'Comment->' from dual; /*Andrew was here*/
  2  select 'More SQL' from dual;
select 'Comment->' from dual; /*Andrew was here*/
                            *
ERROR at line 1:
ORA-00911: invalid character
 
SQL>

To get the code to work, you need to include the comment before the semi-colon:

SQL> @test2
SQL> set echo on
SQL> select 'Comment->' from dual /*Andrew was here*/;
 
'COMMENT-
---------
Comment->
 
SQL> select 'More SQL' from dual;
 
'MORESQL
--------
More SQL
 
SQL>

However, if you try this in SQL Developer, both options work (as usual, click on the images to enlarge them and bring them into focus):

 
 

2 comments:

thatjeffsmith said...

You need to test the code in the tool where it's going to be ran in production.

Our new sql*plus engine doesn't get bothered by the comment post statement terminator either. Now the real question - is that an enhancement over the 'old' SQL*Plus?

Andrew Reid said...

Dear Jeff,

Thank you for taking the time to comment on my post. I agree with your idea that the same tool should be used for testing and production.

On this occasion, the code came from a supplier in the States and was passed to me by a colleague in our Windows team so nobody told me which tool to use to run it!

But looking on the positive side, it has made me install SQL Developer on my machine so when I get a moment I will try to learn how to use it properly.

Kind Regards,

Andrew