Several users have reported that they are experiencing database crashes due to the new code added for compatibility with OWA_SEC operations. This code checks for an incoming Basic authentication header and, if one is present, decodes it and passes it to the OWA via a PL/SQL anonymous block containing two assignment statements. Unfortunately, if this statement is executed after a RESET_PACKAGE operation, PL/SQL will occasionally crash, taking down that database process and returning a 3113 error to mod_owa.

The symptoms can range from repeated challenges to log in to the site, even after you've logged in, to repeated "Internal Server Errors". In all observed cases, enabling the mod_owa diagnostic log reveals that the OCI is returning 3113 error codes (an ORA-3113 means that the connection to the database server has been severed, either because of a network outage or, as in this case, because the database server has crashed). Internally, the database is crashing with an ORA-600 error and dumping a core file.

The problem occurs sometimes when executing the assignment statements needed to set the OWA package globals for USER_ID and PASSWORD (these are where the OWA_SEC functions get the values from). Originally, I had included these two lines with the CGI environment call, thus:

begin
  OWA.INIT_CGI_ENV(:ecount, :namarr, :valarr);
  OWA.USER_ID := :usr;
  OWA.PASSWORD := :pwd;
end;

This was efficient and saved me from requiring an extra round-trip to the database. The code is needed to maintain compatibility with OAS behavior used my many sites.

Alas, users started to see crashes as a result of this change. I discovered that by moving these lines ahead of the INIT_CGI_ENV call, it avoided the crash in many, but not all, situations. Finally, I moved the two lines completely out of this block and into a separate database operation, and this seemed to "cure" the problem for all cases known at that time:

begin
  OWA.USER_ID := :usr;
  OWA.PASSWORD := :pwd;
end;

The problem has been observed on database versions as early as 8.0.5 and as recent as 8.1.7. The problem is certainly a bug in the PL/SQL engine. To date, the bug has not been fixed or even pinpointed. Even after Oracle finds/fixes it, there will be a lot of users who have versions of the database that still have this defect.

Unfortunately, another user has encountered a variant of this same problem, even using the new code. During my earlier testing, I had observed that the bug would not occur provided the assignment statements were not made directly, but were instead made by a procedure that was already in the database. It turned out that the only way to get this particular user running was to make similar modifications. The fix involves adding two APIs to the code of the OWA package.

• pubowa.sql:

  /* Package specification includes two new APIs: */
  procedure SET_USER_ID(USR in varchar2);
  procedure SET_PASSWORD(PWD in varchar2);
  

• privowa.sql:

  /* Package body includes these implementations: */
  procedure SET_USER_ID(USR in varchar2) is
  begin
    OWA.USER_ID := USR;
  end SET_USER_ID;
  procedure SET_PASSWORD(PWD in varchar2) is
  begin
    OWA.PASSWORD := PWD;
  end SET_PASSWORD;
  

Indeed, the package should probably have these APIs anyway, rather than requiring callers to do direct assignment statements.

On the mod_owa side, a corresponding change is also needed to the PL/SQL block:

begin
  OWA.SET_USER_ID(:usr);
  OWA.SET_PASSWORD(:pwd);
end;

The change separating the two lines appears to enable most users to avoid the problem. If you're not lucky enough to be one of them, I have no other option to offer you other than to require you to make PL/SQL-side source modifications, rebuild the package, and then flip the switch that causes mod_owa to run in this alternate mode. That switch is the oracle_alt directive SETSEC, which you can use in your Location configuration as follows:

oracle_alt OWA SETSEC

I've logged a bug against PL/SQL and the database team is investigating the problem. I've also asked the iAS team to update the OWA packages so that they contain these new APIs out-of-the-box. At this point, there's not much more I can do about the problem.