Thursday, January 29, 2015

ORA-01114 Not Recorded Correctly in Alert Log

A user reported an ORA-01114 and an ORA-27069 in a 3rd 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.

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