Full Text Error Messages

When programming with Pro*C, you can retrieve up to the first 70 characters of any Oracle error messages using the SQLCA structure. However there are times when you need the full text of the error message. My project had run into this need last year. For some reason the developers could not figure out how to do this. I wish they had read the Oracle Pro*C Programmer's Guide. It explains exactly how to do this. You need to call the sqlglm() function.

I could go through how you can use the sqlglm function. However I recommend you just consult the Oracle Pro*C Programmer's Guide. Isn't it like a developer to not read the manual? There is no excuse for this. Oracle provides the documentation free of charge on the web.

This reminds me of another huge problem I encountered this year with multi threaded applications having trouble managing multiple database contexts. At lot of high power developers thought up many theories about what our problems were. And there was a lot of guesses on how we could solve the problem. None of the guesses panned out. Luckily I read up the Pro*C Programmer's Guide and employed the Pro*C functions to explicitly manage database contexts for threads. Problem solved. Maybe I will give an example of how I did this in the future. The Pro*C Programmer's Guide is a good start. But it lacks some detailed examples.

Oracle Reports with 10g

My team is working to upgrade to Oracle 10g. On the back end, we are moving from an Oracle 9i to an Oracle 10g database. And we are upgrading from the ancient Oracle 8 client to the Oracle 10g client on the workstations. So I am busy modifying our Visual C++ projects to work with the next client. So far I am attending to the easy work, liking compiling our Pro*C code with the next client. But I think we are going to be chasing down a lot of problems when we do run time testing.

Currently one developer on our team is assigned the reports. We are sticking with the Oracle 6 Report Builder. We are also leaving the workstation with the Oracle 6 Report Server. Most of our reports just run with the Oracle 6 run time client. These seems to be working out OK. However a few reports go through the Oracle Report Server on the workstation. These are giving our developer a lot of trouble when run.

Specifically, our reports developer compiles the reports against the 10g database using the Oracle 6 report builder. The reports run fine from Oracle Report Builder. However the Oracle Report Server complains when it tries to run the report. It always states that the report contain uncompiled PL/SQL. This has been driving the reports developer nuts. We have tried looking into patches to the Report Builder. But I think the problem actually is in the Report Server. I am not sure if Oracle is going to be any help, since these are old products.

The development team is hoping that the kinks get worked out. We want to start using more recent products from Oracle. It is no fun being stuck in the past using outdated and unsupported products in our system. I will keep you posted on our progress. We have a big test scheduled with the customer in a month and a half. Good luck to us.

TNS Problems

Our production users were getting Oracle errors that had to do with TNS problems. I passed these on to our DBA Team. However I did some research on them and thought I would share some findings.

(1) ORA-12203 "TNS: unable to connect to destination"

• Potential cause is that the destination is not listening
• It may also be due to network problems
• You can issue a "lsnrctl status" at the host to see if the listener is running
• This may be related to the entry in the tnsname.ora file

(2) ORA-12535 "TNS: operation timed out"

• You should try tnsping first to start the diagnosis
• Look at the listener log on the host for more clues
• Check the sqlnet log files on the client side
• Run a statspack report, and check Top 5 timed events

Luckily the problem did not last long in production. The trouble ticket itself got closed out. But our DBA Team has an action item to determine why the problem happened, and prevent this from happening in the future.

Order in an Update Statement

I got the most unusual e-mail from a coworker today. She was trying to research a very difficult software bug in our system. The trouble was that she could not reproduce the problem. This happens to the best of us from time to time. However she was not on the hunt for things in the code which might be the cause of the problem. This is a very dangerous journey for even the best of software engineers. Her analysis and e-mail to me was concern over a SQL statement like this:

UPDATE tablename
SET column1=500, column2=column1;

Her plan was to reorder the two assignments in the SET clause. And the reasoning was that maybe Oracle would sometimes assign column1 to a value of 500, then assign column2 to that same value due to this UPDATE. Now this sounded quite preposterous to me. I told her that the switch in order would have no material change in the results, ever. I tried to be patient. I explained that Oracle would always take the values prior to the update, and then use those whenever an assignment was made referencing one of the columns in the table. She thought she could do some Googling on the Internet to find an example where somebody ran into this problem. She already searched our code and found places where the order in the SET clause was switched around.

I told the coworker that she could try to ship out the code change. It would make no difference in the results. And we would look a little funny if we tried to pass that off as the solution to the problem. At the very least I would have hoped one of our DBAs would have protested. Heck. I determined I should protest a little louder. Nothing like a little education to make a good impression though. So I looked up the Oracle documentation on the UPDATE statement. We are using Oracle 9i on the back end. So I found what I needed in the PL/SQL Programmer's reference for Oracle 9i. It defines columns on the right hand side in such UPDATE statements as referring to the old values of the columns in the row. Now I guess she could make the argument that Oracle states this is the case but it not following through. But that statement is probably as likely as stating that sometimes Oracle just forgets to follow through on the whole update. I was pleased when I got a message back thanking me for the information.

The search goes on for the source of the strange customer problem. I have already provided the customer with the grim projection that we shall not fix this problem any time this week. It might be time to guide this developer to tried and true software maintenance methodologies. A fitting example would be that you cannot fix a problem until you can reproduce it. The justification is that you can never know whether your fix works if you do not know how to make the problem happen in the first place. Another idea I had was that maybe it was time for a little Oracle database training in this case. I have taken a class or two on Oracle technology. But I have also read a ton of books and a lot of other information on the web. However I think my best instructor has been many years of hands-on maintenance in front of a huge Oracle database. Sometimes I overlook this experience because I take it for granted.

OWB Skills

Our project at work has a bunch of data that we load into the system on a yearly basis. In the past, we had some UNIX shell scripts and C programs which handled this load. Over time the code was ported to Oracle Warehouse Builder (OWB).

Another company has won the software maintenance contract for our project. So we are transitioning out. Our OWB developer has already left the project. We are planning to turn the OWB source code over to the new contractor. They were looking for some OWB training from us. However we were only obligated to provide them with the source code. It is not in our statement of work to provide technical training to our replacement. This is just tough luck.

If I were working for the winning contractor, I would take these OWB projects and move the code into something more common. Personally I would recommend straight Oracle PL/SQL and maybe some Korn shell scripts. You don't want to be locked into some little used tool that nobody knows how to use. This is a recipe for trouble. Heck. You could even convert the projects to a common programming language such as Java. The problem is that there is not a strong business case for this effort. In the end, the customer will just get something that does what the existing OWB project does. No net benefit other than ease of maintenance. I wonder what the future holds for this annual loading software.

Slow System

This week our users complained that the problem system had stopping responding. It was the second time that this had happened. Our DBA Team and Performance Engineering Team had some ideas about the problem. But in the end their analysis said that a strange process ran on the server that consumed all of the CPU. This answer did not generate a lot of faith from the user community.

When the problem happened a second time, the application development team had to step up to the task. This time around the DBA Team analyzed the Oracle trace files and found that there was an ORA-00060 error "deadlock detected". But this did not make any sense to me. The deadlock error happened at the end of an hour long system halt. Oracle should be able to detect such a deadlock within seconds, and at the most minutes.

Development took this idea and ran with it. We determined where in the code we execute the SQL that was reported to be in deadlock. Then we analyzed how the application can cause that line of code to be in a deadlock situation. The analysis was not too conclusive. We did some tests to try to reproduce the problem with this knowledge. But we were unsuccessful.

I scanned a database table where our application logs exceptional events. Strangely enough I found a lot of ORA-03113 errors "end of file on communications channel". This by itself does not indicate anything. When the system is hung, anybody can kill our application and cause their session to terminate. However they were coming from the same stored procedure in the database. I inspected this stored procedure and found some very suspicious code which does the following:

• Declares a cursor with a SELECT ... FOR UPDATE clause
• Loops through the cursor
• Updates each record
• Performs a COMMIT and the end of all the updates

Now it appears that multiple users were all hung on this stored procedure. I assumed that the first user to open the cursor would get a lock on all the records (due to the FOR UPDATE). However maybe Oracle only locks the records as the records are fetched from the cursor one by one. And if so, suppose all users were selecting the same records. Furthermore let us assume that some users locked some records, while others locked the rest. This would indeed be a deadlock situation. But I would still expect that Oracle could recognize this and give everybody except the first user an ORA-00060 and kill their locks.

My plan for now is to have a developer simulate the situation where multiple users all execute this stored procedure at the same time. In addition, I want there to be a lot of records to update, thereby increasing the odds that we can conflict with each other's transactions. If this turns out to be the problem, I will (1) get the database guys to configure Oracle to detect this easily, and (2) rewrite the stored procedure to get the locks one record at a time.

Subquery WHERE Clause

I was working on a trouble ticket. Traced the problem down to some SQL code that was actually in a Pro*C file. Here was the update statement:

UPDATE table1 one
SET dummy=200
WHERE table1.ROWID IN
(
SELECT innerone.ROWID
FROM table1 innerone,
table2 two
WHERE innerone.id = two.id
);

Now I wanted to limit the subquery to only choose those rows that had the code column of table1 equal to 5. Therefore I added another criteria to the inner query WHERE clause. My natural instinct was to use the alias from the inner query itself like this:

UPDATE table1 one
SET dummy=200
WHERE table1.ROWID IN
(
SELECT innerone.ROWID
FROM table1 innerone,
table2 two
WHERE innerone.id = two.id
AND innerone.code = 5
);

My reasoning was that it would be faster to reference just the inner subquery tables. I did not want to reference the outer query table, as that would make it a correlated subquery. However another developer came in behind me and changed my code to reference the outer query table alias:

UPDATE table1 one
SET dummy=200
WHERE table1.ROWID IN
(
SELECT innerone.ROWID
FROM table1 innerone,
table2 two
WHERE innerone.id = two.id
AND one.code = 5
);

Shouldn't these both equate to the same results? This other developer is out of the office now. But I am hoping to get an explanation when he gets back. What do you think?