Oracle10g – Bug 5458753

Friday, September 29, 2006

I read a post on the Oracle-L yesterday regarding Bug 5458753 where “SQL can execute in wrong Schema”. According to Metalink Note 392673.1, there is a patch currently available for it and the problem should be fixed in 10.2.0.3.

Might not be an issue in most Production environments, but in a shared development environment like ours where developers are using the same object names in different schemas, it could be quite a problem. Imagine issuing a query and getting unexpected results. I mean sometimes you expect the unexpected when developing, but not to this extreme.

Update (23-Oct-2006)
I applied patch 5458753 on Friday (20-Oct-2006) and it appears to have resolved the problem in my 10.2.0.2 installation on Solaris 9 (64-bit).

Update (17-Oct-2006)
I actually reviewed Metalink Note 391116.1. The way I understand it, the problem is expected to be fixed when 10.2.0.3 is released. Guess we’ll have to wait and see for sure.

Update (13-Oct-2006)
An entry on the Pythian Group Blog mentions Bug 5458753 is not in the list of bugs fixed in 10.2.0.3 according to Metalink Note 391116.1.


Oracle “relink all”

Wednesday, September 27, 2006

Back in July when I applied CPUJul2006, it failed to set the correct file permissions on some of the Oracle executables (e.g. imp/exp, sqlplus). This is Oracle9i running on Solaris 9 64-bit. Developers were getting the error below when invoking SQL*Plus.

ld.so.1: $ORACLE_HOME/bin/sqlplus: fatal:
$ORACLE_HOME/lib/libclntsh.so.9.0:
Permission denied
Killed

Their Unix account does not belong to the Unix dba group, which explains why I don’t see the problem when I login to my Unix account which is a member of the dba group.

I fixed the original occurrence by executing “relink all”. After applying the 9.2.0.8 patch about a month ago, the same error appeared this morning when one of the developers went to invoke SQL*Plus. Again, the problem was fixed with “relink all”.

Searching Metalink to see if this is a known issue. At the moment, I’m left wondering how many more times I will need to “relink all” after applying future CPUs.

Note: 9.2.0.8 is the terminal release for Oracle9i, so no worries about applying a future upgrade patch.


Unit Testing PL/SQL Code

Monday, September 25, 2006

Presentations from the New York Metro User Group Day are available. This was a well attended event. The keynote speech was an overview of Oracle Fusion Middleware. I found it to be informative from a high-level point of view.

Steven Feuerstein presented “Six Steps to Unit Testing Happiness“. Among other steps, test cases are developed prior to writing any code in the program unit. Also referred to as “test-driven development” or TDD. The six steps are listed below as shown in Steven’s presentation.

  1. Describe fully the required functionality of the program.
  2. Elaborate the test cases for the program.
  3. Define the header of the program (name, parameter list, return value).
  4. Build test code that implements all test cases.
  5. Write the program unit.
  6. Test, debug, fix, test, debug, fix, test, debug….

Available tools for unit testing PL/SQL code are utPLSQL and Quest Code Tester for Oracle which is in Beta and available for download to try.


MySQL Database Character Set

Monday, September 18, 2006

I have been working with MySQL databases, not exactly on a regular basis, for about 2 years now. This morning I was asked by a team member if the database she created was setup to use the UTF8 character set. The command to do this is shown below, but she had used a GUI and needed confirmation.

create database jiradb_363 default character set utf8;

I was aware when the database is backed up, the backup file contains the CREATE DATABASE statement. Since this was a newly created database, just execute mysqldump and look at the backup file. I also did some additional searching of the MySQL documentation and found I could easily query the information using the SHOW command below.

mysql> show create database jiradb_363;
+———————————–+—————————————————————————————————————–+
| Database   | Create Database                      |
+———————————–+—————————————————————————————————————–+
| jiradb_363 | CREATE DATABASE jiradb_363 40100
DEFAULT CHARACTER SET utf8                          |
+———————————–+—————————————————————————————————————–+
1 row in set (0.00 sec)


Hello world!

Sunday, September 17, 2006

Welcome to my blog at WordPress.com. This is my first blog post, ever! I’ve seen others blogging for years now and I have finally decided to jump on the bandwagon. My initial goal is to have fun and if I learn a few things along the way, then great.