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:
What I wanted to know was how I could solve that issue. The environment I work with looks like that:
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.
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
Filename: RECOMPILE_INVALID_OBJECTS.sql
What to do now??
Wait until it happens again and then you will get to know about it.
ORA-20001: GET_BLOCK Error. ORA-20001: Execution of the statement was unsuccessful.
ORA-02047: cannot join the distributed transaction in progress < ;pre> ;
begin execute immediate 'alter session set nls_numeric_characters='''||wwv_flow_api.g_nls_numeric_chars||''''; end; < ;/pre> ;
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.
