ORA-03113: end-of-file on communication channel. This was the error that greeted me after many hours work converting some PL/SQL scripts to run as a package of functions, views and stored procedures.
My first thought was actually that something or someone had terminated my connection to the database. Given that I was working on a DEV/TEST instance, I wouldn’t necessarily have expected to be notified about a momentary disruption to service. After reconnecting and repeating the same and other queries, it became clear the only thing causing this was one of the procedures in a package I had written.
I tried a few changes without any success and then decided to call it a day, with the intention of speaking to the one of the DBAs the following day to see if they could provide any further information.
As it turned out, I didn’t have get in touch with the DBAs… The next morning they were already looking for me!
It turns out that the error which had been unexpectedly terminating my connection had been causing an access violation followed by a core dump on the server. Of course this is the kind of thing that raises all kind of alerts even on a development/test instance.
Continue reading ORA-07445 Error in a MERGE query
In a previous post, I wrote about partitioned outer joins in Oracle.
The simple schema and queries in that post are similar to those I used when first learning about this feature. At the time, I came upon an unexpected error which caused some Oracle instances to throw an ORA-600 error from a very simple query.
The original test environment I had been using was Oracle Database 11g Enterprise Edition 220.127.116.11.0 running on Solaris.
I had created a simple schema to experiment with, and written a couple of queries which returned results designed to illustrate the effect of a partitioned join as compared with other types of joins.
Once I had them doing what I wanted, I decided to discuss them with a colleague, who tried running the on his local instance of Oracle Express 18.104.22.168.0 on Windows.
Instead of the expected results, he received the following error:
ORA-00600: internal error code, arguments: [kkoipt:invalid aptyp], , , , 
Continue reading ORA-600 Error in a Partitioned Join
From 10g onwards Oracle has had a type of join called a Partitioned Outer Join, providing a convenient syntax which can be used to fill gaps in sparse data.
The documentation describes the result of a partitioned outer join as “a UNION of the outer joins of each of the groups in the logically partitioned table with the table on the other side of the join.”
To demonstrate what this means in practice, I will run through a simple example.
We will start with two very simple tables, products and sales, which look like this:
Continue reading Partitioned Outer Joins in Oracle
UTL_FILE.GET_RAW fails on long lines if the file is opened in text mode.
Today while debugging a previously developed PL/SQL package, I had to investigate an ORA-29284: file read error.
The cause was not immediately apparent, as the procedure had been reading in and parsing other files ok, and had read in this particular file just fine up until it reached a certain point.
The file being read was text rather than binary, but the code was using GET_RAW not GET_LINE since the process was not interested in line breaks. With a little investigation, it turned out that the place where the error occurred was when the procedure began trying to read from a line which was 175,000 characters long.
If you’ve used the functions in UTL_FILE, then you’ll know that functions like GET_LINE support a maximum line size and buffer size of 32767 bytes. However in this case, the call to GET_RAW was specifying a much smaller number of bytes to read. So why did it fail? Continue reading ORA-29284 File Read Error with UTL_FILE.GET_RAW