ORA-01114 Not Recorded Correctly in Alert Log

A user reported an ORA-01114 and an ORA-27069 in a 3^rd party application running against an Oracle 11.1 database:

ERROR 28/01/2015 10:32:37  INF2 LKPDP_10:READER_1_1  RR_4035    SQL Error [

ORA-01114: IO error writing block to file 201 (block # 524367)

ORA-27069: attempt to do I/O beyond the range of the file

Additional information: 524367

Additional information: 7

Additional information: 524289

ORA-01114: IO error writing block to file 201 (block # 524367)

ORA-27069: attempt to do I/O beyond the range of the file

Additional information: 524367

Additional information: 7

Additional information: 524289

I searched the database’s alert log but could not find either of the errors. I asked the user when this had happened and looked at the corresponding place in the alert log. Then I noticed that the ORA-01114 had been recorded without the leading zero:

Wed Jan 28 10:32:51 2015

ORA-1114 : opiodr aborting process unknown ospid (23531_1)

 

… but there was still no sign of the ORA-27069.

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):