Making the Connection

I got a call for help from a fellow developer. She had installed the Oracle 11g client. And she could not connect via SQL*Plus or SQL Developer. So I went over to see what was wrong.

The first problem was that she was using the SQL*Plus that comes with the client. This is different than the old Windows sqlplusw. You only get asked for username and password. If you only enter those two, SQL*Plus assumes you are trying to connect to a local database on your workstation. It turns out she was getting a TNS protocol adaptor error.

I had her type in username@Dbname. That did the trick. She was able to get into SQL*Plus. Then we looked at her connection in SQL Developer. She was using the default that also tried to connect to localhost on port 1521. She typed in the actual host name and port. Then she was good to go for SQL Developer. She could have alternatively changed the connection type to TNS. We set up a TNS_ADMIN variable that points to a "tnsnames.ora" file. That would have made life easier.

Just now I got another email for help from a different developer. He was wondering whether we have PL/SQL Developer on our new machines. I told him to try out Oracle's SQL Developer. Let's see how he fares.

No Place Like Home

After having installed the new Oracle 11g client, I wanted to put PL/SQL Developer on my machine. Ooops. I forgot that my PL/SQL Developer required a bunch of passwords for installation. I dug up my printout with all those passwords. Once I installed PL/SQL Developer, I launched it hoping to execute some queries. No luck. It kept complaining that SQL*Net was not installed.

This seemed vaguely familiar. I googled this problem. Some people recommended that I hack up the Oracle entries in the Windows registry. That did not seem too encouraging. Instead I tried applying some settings in the PL/SQL Preferences tab. I hard coded the Oracle Home directory in there. Still no help.

Finally I did some more googling on the problem. Someone smart advised me to put in the full path for the OCI Library. Bamm. That did the trick. I am recording my experience here so I can come back the next time I install PL/SQL Developer and maybe a new Oracle client.

First Look at 11g

We finally got around to installing the Oracle 11g client for our new development. This is an upgrade from Oracle 10g. Our first test was to try to log into the 10g database uses the 11g client. But we could not find the Windows SQL*Plus client (sqlplusw.exe).

Doh. Oracle is no longer shipping this product. They want you to use SQL Developer. Weak. I am contemplating bringing the one from the 10g client. Come on Oracle. We like sqlplusw. Yeah they ship the normal SQL*Plus. The DBAs use it. But us developers want our Windows version.

Script Mystery

I got to work and had a lot of email to deal with. Another developer dropped back and said he was having problem with a script he wrote. It was supposed to insert 180+ records. But only 15 records were working. I told him I was busy, but he should run the thing from SQL*Plus to ensure some tool as not confusing him.

Later the developer returned and said he needed help badly. People were calling him asking him for the completed script. I took a look and indeed only 15 records were in the table after he ran the script. I went through he whole script and found an exception handler at the end. It logged and error and exited.

The developer thought the script was running successfully. But the exception handler was masking the true error. A primary key constraint was being violated. As soon as he got rid of the duplicate inserts in his code, the thing worked fine. You can start with another sample script to speed development. But make sure you know what you are starting with.

Could Not Find Program Unit Being Called

I was told to write a stand alone script to clear out a backlog of work that needed done. It was caused by a slow nightly process that had to get aborted. I coded a new procedure in one of our packages. My script was very simple. It logged the start and end times of the script. The only other thing it did was call the stored procedure with the correct parameters.

Now I wanted to make sure the performance of the stand alone script was acceptable. So I looked in the log to see the start and end times. I was shocked to find tons of ORA-06508 errors being logged by one of the triggers in the database. Now this error means some stored procedure could not be found, or was invalid. This darn trigger was calling all kinds of procedures in many different packages. I guess I could have recompiled them all.

I did find a gem of an idea on Akdora's blog. Instead of just logged the error message in the exception handler, you can store the output of DMBS_UTLITY.FORMAT_ERROR_STACK. This tells you exactly what object is missing or invalid. Thanks Akdora. You helped me get the problem solved and still get home in time.

PL/SQL Review

I just went through a recap of the PL/SQL programming language. It was good. PL/SQL is the programming language for the Oracle database. It can improve application performance but compiling stored procedure code into the database.

PL/SQL is arranged in blocks. Blocks with names are procedures or functions. You can run PL/SQL from the Oracle SQL*Plus tool. You can also run it through an IDE like Oracle SQL Developer.

I hear that new developers continue to come into the PL/SQL programming community.

Oracle Database Firewall

Oracle has released a database firewall product. It analyzes SQL to determine whether to block or log the activity. This functionality comes from F5 Corporation, with which Oracle has struck a partnership.

Normally this is the territory of database activity monitoring products. Companies with these products are downplaying the security of the Oracle database firewall.

There are ways to get around the firewall. For example, our UNIX guys log into their UNIX accounts on the machine that hosts the database. Then they use a local connection to the database. Once they do that, all they SQL bypasses the firewall. Ooops.

Users are Blocked

I was working on some low priority customer problems for a manager. Then all holy heck broke loose. I was getting emails and calls like crazy. The system supporting all our customers was slowing down to a crawl. That should not be possible as we have massive hardware.

The DBA team was on hand to monitor what was going on. User row-level locks were in contention. Our applications were hanging. The DBA teams used some tools to identify one of the first locks that happened in the morning. I knew the business logic behind the SQL.

We got in touch with the user. She killed her application that had been hung all morning. This automatically released the locks and the system returned to a normal state. The lead DBA asked us developers to look at the locking strategy used by the application. He recommended a potential redesign of the app. I took a note, then ran off to the next set of emergencies.