The Big SQL

Our client has an offical acceptance test team. They are all full-time employees of our client. Recenetly they found that one of the queries in our app was never producing results.

At first I helped the testers understand what inputs you need to give the query to get it to find records. But the testers said that they still were unable to get a single result record.

Unfortunately the test environment is on lock down. I cannot log in and run SQL queries myself to check up on the data. So I kept e-mailing the testers, trying to get them to run SQL for me to diagnose the problem.

After many queries I came to the conclusion that the application had to be able to get some results. So as a last effort I had them run the big SQL code that the app runs. That's when I found out the whole big query results in an ORA-01502. In other words "index unusable". I normally run the big SQL myself when diagnosing problems. But in this case I skipped it because I needed to get somebody else to run it. From now on I will just have to get them to key in or cut-n-paste all the SQL in the first place.

The good news is that broken indexes can be fixed the DBA Team. This leaves me free to get back to other tasks on hold. See you next time.

Database Assumptions

A developer got tasked with removing the hard coded references to database schema names in the code. Just delete the schema from the SQL was trivial. Testing the changes was a whole different problem.

I went to the developer to get help with my Clearcase problems. In return he asked if I could get him through his testing problems. The app kept giving him error messages stating that he did not have the appropriate database role. I had him trace the code that made the app display this error message.

One would think if you ran the same SQL as the app, you could determine the app results by interpreting the SQL results. This is not always so. I helped the developer make the decision to run all the SQL as himself (instead of doing it as the schema owner). As soon as he tried this, he could not even run the equivalent SQL. Turns out his database account does not even see the database tables used in the SQL.

Our task was not complete. I prodded the developer to delve a little deeper. So I had him check whether his database account could even see the stored procedure that executes the SQL. Now that he saw the pattern, I released him to compile a list of database objects he needed new synonyms created for. The DBAs could help create them. But we needed to do the analysis on which synonyms we needed.

This adventure was another example where you need to question all assumptions to get to the root cause of a problem.

Hard Coded Schema

Our development team has a common database schema where we can do unit testing. In addition, some of us have our own personal database schemas where we can do whatever we want. I needed to test out some performance enhancements. So I decided to create a lot of data in my personal schema.

It took about a half hour to run a script that created lots of records and set everything up. So I ran our app against this schema, hoping to first replicate the performance problem. Too bad everything ran pretty quick. Didn't take long for me to realize that the application was somehow running against the common schema.

So I dug into the code and found that our app hard codes the schema name. This is evil. It works because the offical quality and acceptance tests are run in an environment that have the same schema name. Production also has the same schema name. This only breaks down when we get to me using my personal schema to do really specialized testing.

Well I am a programmer. So I made a not to fix this hard-coded schema name everywhere in the app. But in the mean time, I guess I can do all my testing in the main common schema. Time is always of the essence for me on my project.

Using & In Scripts

A developer on my team tried to write a SQL script to populate a new database table for our system. One of the values in one of the columns needed an ampersand in the text. As you may already know, the ampersand is a special character in Oracle SQL scripts that represents a substitution variable and not the ampersand character itself.

There was a lot of commotion with this new script. It first got passed to the DBA Team for implementation. Of course it did not work. So the script came back to the developer. Another developer and a DBA tried to help get it to work with no success. Then they called in a consultant who recommended we do not use an ampersand.

I figured this had gone on long enough. So I suggested escaping the character by placing a backslash before it. I also said they needed to add a SET ESCAPE ON to the beginning of the script to ensure the backslash was treated as an escape character. This got them past the ampersand problem. But this only brought them to the next problem with the script that prevented it from running.

The lesson from this exercise was that it is acceptable to write and debug scripts using advanced tools such as PL/SQL Developer. However when you perform your unit tests, you better run the scripts with the same program that will be used in Production (Oracle SQL*Plus). Otherwise you have no way to tell whether your tests are valid.

Password Policy

Our system at work already has a policy for password use. There is a database profile which is common to all database users. This profile has a PASSWORD_VERIFY_FUNCTION set to a stand-alone function owned by sys. Trouble is this function is not complete. It does not implement all of our customer's security requirements. I am a programmer. So the DBAs turned to me to fix this problem.

Some of the changes were trivial. Ensure the password has certain characteristics. I just mowed through these requirements by writing PL/SQL code in the password verify function. Then came a troubling requirement - the password had to have upper and lower case characters. You would think this was no trouble for an experienced PL/SQL programmer. But coding these requirements into the function caused a lot of things to break.

To better understand the problem I traced how our applications actually changed the user passwords. Turns out they just issue an ALTER USER IDENTIFIED BY . I sprinkled debug statements all throughout the password verify function, writing the output to a database table. And the output showed me exactly what was wrong. Turns out that when Oracle calls the password verify function while processing the ALTER USER command, it converts the password to upper case first! Damn.

I googled the web for information on this suspicious behavior. Could not really find much info on it. I already knew that Oracle passwords were not case sensitive. But who knew they would be doing an UPPER behind the scenes before my password verify function got called. Note that this did not happen when trying to change the password in SQL*Plus by executing the PASSWORD command. Go figure.

In the end I talked this over with our database manager. He looked at some password verify function samples from Oracle. None of them did upper/lower case enforcement. So we decided to skip implementation of this requirement. Normally I would protest, since us developer should be able to do anything. But I am on a tight deadline at work, and researching this problem has already taken too much time.

P.S. "Oracle PL/SQL Programming" written by Feurstein and published by O'Reilly is excellent . I have around 50 books on my bookcase at work. But I keep this book on a separate bookshelf reserved for the best.

Client Version Matters

Previously I had problems using my Oracle 8 client to connect to an Oracle 9i database as sysdba. But a DBA told me they had no problem doing this. The only difference seemed to be that the DBA was using the Oracle 10g client. It was worth a try.

I was shocked to see the Oracle 10g client download was 475 Meg. What the heck do they got in there? Had to leave the download running overnight. Normally I choose custom installs to ensure I only get what I want. I was able to choose things I thought I would need from the Oracle 10g client. But a post-installation configuration was hard to bypass. Had to kill the Oracle installer process using Task Manager.

So I fired up the Oracle 10g SQL*Plus. Voila. I could successfully log into our Oracle 9i database as sysdba. So I guess the Oracle 9i database does not support you logging in as sysdba with an Oracle 8 client. I imagine this is documented somewhere in the Oracle docs. But I would have liked something other than an "ORA-01017: invalid username/password; logon denied" to let me know that.

I guess I can't complain too much. The Oracle 10g client is a free download. Heck. Even the Oracle 10g database is a free download for non-commercial uses. And if I did not encounter weird Oracle problems, I would not have anything to write about. So now that I could log in as sysdba, it was time to get my password policy changes working.

Client Server Mismatch

I needed to modify some of our system's password policies. So I updated the password verify function which our users' common profile points to. Unfortunately this function must be compiled under sys. So I started handing it off to our DBAs to compile for me. That got old after the 3rd or 4th time I needed to make change during debugging.

So I talked our DBA manager to give me the sys password to a local Oracle 9 database running on one of our Windows servers. Since we use a lot of legacy tools on our project, I fired up the version of SQL*Plus that comes with our ancient Oracle 8.1.6 client. I was surprised at the result.

SQL> connect sys@the_database as sysdba
Enter password: *******
ERROR:
ORA-01017: invalid username/password; logon denied

At first I thought I had the wrong password. But a DBA came to my desk and had the same problem. The database is Oracle 9i (Release 9.2.0.8). Maybe you can't mix an Oracle 8 client with an Oracle 9i client when you log on as sysdba. I don't know.

The next thing I am going to try is to install the Oracle 10g client to see it makes a difference. Stay tuned and I will fill you in with my results.

Database Access

Our team came to the realization that we were not going to make our first delivery of the software. A quick analysis determined the problem holding us back was the code to retrieve and store data to the database. Current efforts were being done using the Active Template Library (ATL) database classes. The developer doing the work had never used ATL before. Thus the delay.

My team lead asked me for suggestions. I said that my plate was full so I could not take over the task. And even though I had done ATL programming before, it has been a long time. ATL was the initial choice because the work is an addition to a small app that was written with ATL. A couple other choices were MFC database access and Pro*C. My recommendation was to go with Pro*C because that was the second most used database access technology in our application suite.

There is potentially a huge amount of data being retrieved from the database in the new app. So the function written to get this data could not get it all at once. We ended up having one function to initialize the data retrieval, and another function which got one record at a time. The back end Pro*C code used a trick to make the retrieval code easy with only one open cursor. I have provided the details in a later post on Pro*C Cursors.

Password Reuse

Our client has a detailed set of security requirements for all applications. When my team's application suite was developed long ago, they implemented a subset of these requirements. Recently this has come up as part of an audit. So our new directive is to get in compliance with the security requirements.

One area the requirements cover is that of password reuse. I thought this would be easy to implement since all our users already have a database profile which we manage. This should have been a matter of setting some limits in the profile. Unfortunately nothing is ever as easy as it seems.

Some web sites recommended setting password reuse limits like this.

ALTER PROFILE common LIMIT password_reuse_max 5,
password_reuse_time UNLIMITED;

The idea is that users could reuse their passwords after they chang their password 5 times. But this would not work. After much investigation and pain, I finally got the 411 on password reuse.