APEX-AT-WORK no image

APEX error ORA-20001 and ORA-02047 during application import (1)

Von Tobias Arnhold 11.06.2008
Today I came across a quite known error, at least if I count the forum entries to it. When I tried to import a application from the test to a production environment in my case the same server and the same schema/user. I got the following error:

ORA-20001: GET_BLOCK Error. ORA-20001: Execution of the statement was unsuccessful.
ORA-02047: cannot join the distributed transaction in progress &lt ;pre&gt ;
begin execute immediate 'alter session set nls_numeric_characters='''||wwv_flow_api.g_nls_numeric_chars||''''; end; &lt ;/pre&gt ;

What I wanted to know was how I could solve that issue. The environment I work with looks like that:
  • WinXP SP2 with
  • OracleXE database with
  • APEX 3.1.2 and PL/SQL Web Toolkit 10.1.2.0.6
  • OAS 10 with Apache Server for APEX
  • DADS.conf extract

<Location /pls/xe>
...
PlsqlNLSLanguage GERMAN_GERMANY.WE8MSWIN1252
...
</Location>

What did I do? Did I changed anything on my environment?
I worked in this environment since half a year with a lot of app imp- and exports without any errors or problems. I didn't change anything on the environment but I did work on the application. Error just came up today when I tried to import one application export. I exported the application several times and tried to import with no success. Then I tried to import it into a new application id instead of overwriting the production app. Again the same error...

How could I fix it?
I found a quite good post at the Oracle APEX forum: Application import error
What I did then was to check for the version of the PL/SQL Web Toolkit.

select owa_util.get_version from dual;
----------------------------------------------
10.1.2.0.6

Was the right version. Now I let the utlrp.sql run to recompile all invalid object in my XE database.
SQL> @rdbms\admin\utlrp.sql
All went all right no errors.
I tried again to import the application and again the error occurred. Then I tried to import another application (same database just another schema/user) and that worked fine. I compared the error app with an older version (via WinMerge) and came across a comment I made:
Home>Application Builder>Application 101>Page 1>Edit Page Item>Element>Pre Element Text

I took this out (via APEX) and made a new export file. Now I tried with the new application export and finally I could succeed getting it to work. No error occurred. Strange thing was I made another import with an export where the comment was still in it (no changes or anything) and also that worked fine.

What does it all mean??
I guess it has to do with invalid objects. Normally every weekend a batch job runs which corrects invalid objects inside the database. Job looks like that:
Filename: RECOMPILE_INVALID_OBJECTS.bat

set ORACLE_SID=XE
C:
cd C:\oracle\product\10.2.0\server\RDBMS\ADMIN
sqlplus "/ as sysdba" @C:\oracle\jobs\RECOMPILE_INVALID_OBJECTS.sql

Filename: RECOMPILE_INVALID_OBJECTS.sql

spool C:\oracle\log\recompile_invalid_objects.log
set heading on
set verify on
set term on
set serveroutput on size 1000000
set wrap on
set linesize 200
set pagesize 1000

select 'Session started at '||to_char(sysdate,'dd.mm.yyyy HH24:MI') from dual;
select instance_name from v$instance;
select * from ALL_OBJECTS where status = 'INVALID';

@utlrp.sql;

select * from ALL_OBJECTS where status = 'INVALID';
select 'Session finished at '||to_char(sysdate,'dd.mm.yyyy HH24:MI') from dual;

spool off
exit

What to do now??
Wait until it happens again and then you will get to know about it.

2 Comments " APEX error ORA-20001 and ORA-02047 during application import (1) "

daust_de 07 November, 2008 20:43

Hi Thomas,

you should change the DAD configuration.

The setting should be:
<Location /pls/xe>
PlsqlNLSLanguage GERMAN_GERMANY.AL32UTF8
</Location>

It need the Unicode characterset, no matter what character set the database is configured in.

This does not necessarily solve your problem, but the wrong configuration will cause some problems, definitely.

Regards,
~Dietmar.

Tobias Arnhold 07 November, 2008 21:19

Hi Dietmar,

thanks a lot for your hint. I didn't know that but will try on Monday! I got the error again today and will write down my impressions to it.

By the way I guess you meant "Hi Tobias" instead of Thomas! ;D