ORA-07445 _npierr+487 error in APEX runinng under a XE database

Von Tobias Arnhold 5.15.2009
I created a dynamic pl/sql report where the select was based on an external table (transcribed through a database link).

DECLARE
v_query varchar2(1000);
BEGIN
IF :P1_INSTANCE_NAME is not null THEN
v_query := 'SELECT NAME,
VALUE,
isdefault,
isses_modifiable,
issys_modifiable,
ismodified,
isadjusted,
description
FROM v$parameter@'||:P1_INSTANCE_NAME;
ELSE
v_query := 'SELECT 1 FROM dual WHERE 1=0';
END IF;
return(v_query);
END;

Tip: If you are more interested into dynamic reports! Then here comes a really handy explanation:
http://www.apex-blog.com/oracle-apex/dynamic-report-regions-tutorial-32.html

After the login into the APEX application builder a strange download error occurred. When I clicked on a APEX link a download window opened and showed the following arguments:


At the same time when the error occurred the following entries in the alertlog file got created:

Fri May 15 13:14:43 2009
Errors in file c:\oracle\admin\xe\bdump\xe_s000_1436.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_npierr+487] [PC:0x5F22C3] [ADDR:0x4] [UNABLE_TO_READ] []

Fri May 15 13:15:27 2009
found dead shared server 'S000', pid = (14, 2)
Fri May 15 13:17:07 2009
Errors in file c:\oracle\admin\xe\bdump\xe_s002_2492.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_npierr+487] [PC:0x5F22C3] [ADDR:0x4] [UNABLE_TO_READ] []

Fri May 15 13:17:24 2009
found dead shared server 'S002', pid = (16, 1)

I looked in Metalink, the Oracle forum and the web itself and came to the following conclusion:
That error occurs when you call a database link in a web application. There is no official workaround at least no one for the XE database. It should be patched in the following Oracle database versions: 11.2, 11.1.0.6.P11

Another hint came from the Oracle forum. They meant you should set up your browser to "en-us" language. I tried it but it didn't help either.
Anyway I created a application with German language so it wouldn't be such a good hotfix. ;D

My workaround:
Creating a new table where I import the information before via a pl/sql package and the dbms scheduler. It's not really what I wanted but the only way for now.

Here are the web links I rallied to it:
Metalink note: 6798427.8
Metalink note: 809366.1
Metalink bug no.: 6798427
XE forum: Beta 3 Bug: using database link freezes apex and xdb
XE forum: Universal edition choke on en-GB browser, core dumped, ORA-07445, _npierr+4
APEX forum: page not found, ORA-07445: exception encountered: core dump

Update 22.06.2009
I had the idea to use a function selecting the external data (with execute immediate) and save this data into a temporary table.
Then I created a new APEX "Before Header Process" and called this database function and made a select into my page variables.
But I still get this error... I give up!

4 Comments " ORA-07445 _npierr+487 error in APEX runinng under a XE database "

teedub 15 May, 2009 14:59

Try allocating additional memory to your XE instance.

Anonymous

alter system set SESSIONS=100 scope=spfile;
alter system set sga_target=512M scope=spfile;
alter system set pga_aggregate_target=128M scope=spfile;
alter system set "_kgl_large_heap_warning_threshold"=52428800
scope=spfile ;
seems to work for me

Tobias Arnhold 29 May, 2009 14:25

@teedub and @Anonymous
Thanks for your answers. I will test it soon.

Tobias Arnhold 18 June, 2009 14:39

@Anonymous
I followed your suggestion but I still get this error even if I just want to select something from v$instance.