Oracle bug 3744836 while using table function in 9205

Recently I use a pipelined table function in a package to tabularize multiple rows into columns. It's my first time to write pipelined table function. It's very cool.

However, after refine the package and recompile it several times, I start to hit an Oracle bug 3744836. The symptom is:

While compiling the package at 1st time in a session, I encountered ORA-04043 error. The error message is something like: object SYS_PLSQL_84730_110_1 does not exist. If I attempt to recompile it again and again in the same session, ORA-00600 internal error will appear. You won't be able to drop the package either.

After searching on Oracle metalink, I found out the Note:3744836.8 that matches the symptom. Actually, it's an Oracle bug 3744836. In the note, the bug is said to be fixed in several Oracle patchsets including 9207. Since I don't wanna apply Oracle 9207 patchset at the time, I downloaded the interim patch for the bug as there is and applied to the problematic 9205 server. After applying the patch, the problem seemed to be solved as I was able to either recompile/drop the package. But it's not true actually! Some time later, while trying to recompile the package, I got ORA-04043 and ORA-00600 errors again! I felt very frustrated of getting these errors after the patch had already been applied. It's supposed to work but it doesn't! Still, I don't wanna apply the patchset 9207 yet due to some reasons. Finally, I figure out a workaround.

The root cause of ORA-04043 is from the PLSQL TYPEs (Record, Table) defined in the package which are used by the pipelined table function. Every time the package is compiled, Oracle will generate a new version of these TYPEs. It's so-called Versioned Objects. These versioned objects can be selected from user_objects.

select * from user_objects where object_type = 'TYPE';

They are something like SYS_PLSQL_%s_%c_%v. The last substiute variable is the VERSION. Oracle manages the relationship between packages and their verioned objects. Due to the bug, the relationship is somehow broken. Even the versioned objects are there in user_objects, Oracle would tell you they don't exist while dropping/compiling the package. e.g. before applying the interim patch, SYS_PLSQL_%s_%c_1 and SYS_PLSQL_%s_%c_2 could co-exist and Oracle would tell you SYS_PLSQL_%s_%c_1 doesn't exist when trying to drop/compile the package; after applying the interim patch, there is only SYS_PLSQL_%s_%c_2. It's getting better coz Oracle knows how to drop the version 1 objects. Unfortunately, Oracle still associates the package with SYS_PLSQL_%s_%c_1 rather than SYS_PLSQL_%s_%c_2. Since the version 1 objects have been dropped and are not there any more, Oracle will still stubbornly tell you SYS_PLSQL_%s_%c_1 doesn't exist and you won't be able to compile/drop the package.

Here is a simple way to bypass the bug before 9207 patchset is applied. Convert those PLSQL TYPEs which are used by table function in the package to Oracle Stored TYPEs. This way, you take control on packages, types and their relationships. Oracle won't version the stored TYPEs automatically as it does for the packaged TYPEs.

Working with SVN on windows

Before using the version control, I encourage you to read the documentation first. Here you can get it: SubVersion

To start your journey on version control, you may need to install some client software first if you are using windows platform. If you are using Linux/Unix, things become very simple. Depending on your experience and preference, the requirements may vary. Here I'd like to give you several solutions.

1. Check-in/out locally on SVN server and map the directory to your local drive on windows probably using SAMBA
This is the one I am using currently. :-) Coz I like to use VIM editor in windows more than the one in Solaris. It's more colorful.

2. Install cygwin package including both SSH & SVN on your windows platform
You may need to generate RSA/DSA keys and install the public key into your directory on SVN server. Otherwise, you will probably get frustrated to input your password coz it will prompt you to input the password many times.
Below is how to check-out remotely over SSH protocol in a cygwin shell

$ svn co svn+ssh://scott@myhost/svn_repository/myproj/trunk

3. Using a SVN GUI client called TortoiseSVN

The well-known free SSH client PUTTY is required. You can obtain the package here: PUTTY . Please make sure you get the whole package but not the PUTTY.exe only! Especially plink.exe which we are going to use with the SVN client.

You can get the GUI client here. TortoiseSVN

First, install the SVN client and reboot your windows, you'll see the options integrated into your context menu in the window explorer.
In TortoiseSVN -> Settings -> Network, configure SSH client to use plink.exe which you installed in the previous step
Seems like using plink.exe without the public-key authentication doesn't work, so you need to generate RSA/DSA keys using puttygen.exe and install the public part into your directory on the SVN server
Finally, in the local directory into which you want to check-out, choose SVN checkout ... in the pop-up context menu and type in the URL, what you want to check-out should be put in the local directory. Everything is done.
URL: svn+ssh://scott@myhost/svn_repository/myproj/trunk