Tuesday, July 27, 2010

ORA-06508 error calling package in different schema

I had a PL/SQL package (let's call it PACKAGE_A) in schema SCHEMA_A which called a procedure in PACKAGE_B in SCHEMA_B. When I added a new parameter to the procedure in PACKAGE_B, I got some very strange errors showing up in PACKAGE_A. After trying all combinations of compiling PACKAGE_A and PACKAGE_B and REVOKEing and GRANTing EXECUTE rights to PACKAGE_B, I was consistently coming up with this Oracle error when I ran PACKAGE_A:

ORA-06508: PL/SQL: could not find program unit being called

Note that PACKAGE_A would compile fine, it just wouldn't run. I finally managed to get rid of this error by logging off my PL/SQL session in SCHEMA_A and then logging back in. After that, my PACKAGE_A call worked fine. "When in doubt, reboot!" A pretty old-school solution but it was the answer in this case!

NB. Oracle Version 10g Enterprise Edition

Monday, February 22, 2010

"The form required to view this message cannot be displayed" error accessing Organizational Forms Library in Outlook 2003

I had a VB6 program running on a server using Outlook 2003 client to create appointments. The appointments used a custom Outlook form stored in the Organizational Forms Library. Suddenly the program started spitting out this error:

"The form required to view this message cannot be display. Contact your administrator."

If I used the Outlook Client (on the server) to try to design the form via Tools-Forms-Design Form-Organizational Forms Library-{shift}+Open then I got the following error message:

"The form you selected cannot be displayed. The form required to view this message cannot be display. Contact your administrator."

The solution was to clear the Outlook Forms cache. This can be done via:

  1. Click on the "Tools" menu

  2. Click on "Options"

  3. Click on the "Other" tab

  4. Click the "Advanced Options" button

  5. Click the "Custom Forms" button

  6. Click the "Manage Forms" button

  7. Click the "Clear Cache" button

  8. Close and OK through all Outlook Property/Option windows

Credit for these steps from http://www.experts-exchange.com/Software/Server_Software/Email_Servers/Exchange/Q_24342937.html

Wednesday, February 17, 2010

Compile All Invalid Oracle objects

If you add a column to a table that invalidates lots of packages, triggers, views etc then you'll need to compile all of these invalidated objects. And each of these objects might in turn invalidate more - so this can take a few cycles.

I use the SQL statement below to generate the COMPILE statements for all invalid objects. I then copy and paste the COMPILE statements into a SQL session and run them (compiling them). Then I run the code below again (and compile again...etc) until no more are found.

||decode(object_type,'PACKAGE BODY','PACKAGE', object_type)
||' '||object_name||' COMPILE'
||decode(object_type,'PACKAGE BODY',' BODY', '')||';'
FROM user_objects
WHERE status = 'INVALID'
AND object_type <> 'SYNONYM'

The output from this (which I paste into a SQL window to run) will be something like: