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
  • OAS 10 with Apache Server for APEX
  • DADS.conf extract

<Location /pls/xe>

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;

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:

cd C:\oracle\product\10.2.0\server\RDBMS\ADMIN
sqlplus "/ as sysdba" @C:\oracle\jobs\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';


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

spool off

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