APEX-AT-WORK by Tobias Arnhold

Browsing "Older Posts"

OAS, APEX and the favicon

Von Tobias Arnhold → 11.24.2008

If you want to use a favicon in your APEX/OAS environment you need to copy the file to the following place:

%ORACLE_HOME%\Apache\Apache\htdocs

Example:

It needs to be a icon called favicon.ico with the size of 16x16 pixels.

ORA-

Solution for APEX import error ORA-20001, ORA-02047 (3)

Von Tobias Arnhold →

Just for the people who are curios about the APEX import error ORA-02047 I had the last couple of weeks.

On the next workday I followed the hint from Dietmar and changed the settings in my dads.conf:


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

After that the error did not occur again.
In this time I did around 20 to 40 import and exports without any problems. I would say: That's it!

You always have to use AL32UTF8 in your PlsqlNLSLanguage variable.

Update:
28.11.2008 - Error occurred again... For now I just restarted the OAS service and the import worked as well as before.

Update:
12.02.2009 - Error now occurred several times again. Last week I couldn't restart the OAS service but when I tried it this week again it worked. Without any changes except restart my client. This error drives me crazy. At least right now it works...

Update:
15.05.2009 - Now I could fix the issue ones by looking into the sessions of the external database. There I canceled all sessions for the database user which I was connecting through APEX. Afterwards the import run again without a OAS restart.
I came to the idea through some fabulous hints from Scott and Joel.
Link: Import err: ORA-20001,ORA-02047,alter session set nls_numeric_characters...

ORA-

Again import error ORA-02047: cannot join the distributed... (2)

Von Tobias Arnhold → 11.07.2008

Hi APEX folks!

Here some new happenings to error:


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 happend?
Just like yesterday! When I try to import from test to prod the error ORA-02047 comes up. I did a couple of imports today without problems before.

What did I do this time?
1. Changed the focus at the page:
Home>Application Builder>Application 306>Page 100>Edit Page>Display Attributes>Cursor Focus:
First item on page

2. Add a new Display as Text (does not save state) item without label (no label).

Is there something important to know?
No I didn't changed anything else. Application has just 4 pages!
App ID's just for better understanding:
ID: 105 (production system)
ID: 106 (that is where I develop in)
ID: 107 (another test app for import tests)

Here the results of my invalid objects select statement:


select all invalid object inside the database:
select owner, object_name, object_type from ALL_OBJECTS where status = 'INVALID';
---------------------------------------------------------------------------------
OWNER  OBJECT_NAME                OBJECT_TYPE
PUBLIC DBA_HIST_FILESTATXS        SYNONYM
PUBLIC DBA_HIST_SQLSTAT           SYNONYM
PUBLIC DBA_HIST_SQLBIND           SYNONYM
PUBLIC DBA_HIST_SYSTEM_EVENT      SYNONYM
PUBLIC DBA_HIST_WAITSTAT          SYNONYM
PUBLIC DBA_HIST_LATCH             SYNONYM
PUBLIC DBA_HIST_LATCH_MISSES_SUMMARY   SYNONYM
PUBLIC DBA_HIST_DB_CACHE_ADVICE   SYNONYM
PUBLIC DBA_HIST_ROWCACHE_SUMMARY  SYNONYM
PUBLIC DBA_HIST_SGASTAT           SYNONYM
PUBLIC DBA_HIST_SYSSTAT           SYNONYM
PUBLIC DBA_HIST_SYS_TIME_MODEL    SYNONYM
PUBLIC DBA_HIST_OSSTAT            SYNONYM
PUBLIC DBA_HIST_PARAMETER         SYNONYM
PUBLIC DBA_HIST_SEG_STAT          SYNONYM
PUBLIC DBA_HIST_ACTIVE_SESS_HISTORY   SYNONYM
PUBLIC DBA_HIST_TABLESPACE_STAT   SYNONYM
PUBLIC DBA_HIST_SERVICE_STAT      SYNONYM
PUBLIC DBA_HIST_SERVICE_WAIT_CLASS   SYNONYM
USER PRC_IMPORT_XXX               PROCEDURE

Recompiled PRC_IMPORT_XXX from other user.
New try, import into app id 107 instead of 105 > Error occurred again.

Recompiled all invalid objects:


SQL > @utlrp.sql;
---------------------------------------------
PL/SQL-Prozedur erfolgreich abgeschlossen.
...
OBJECTS WITH ERRORS                                                                                                                                                                                 
-------------------                                                                                                                                                                                                       0                                                                                                                                                                                 
0
...
ERRORS DURING RECOMPILATION                                                                                                                                                                         
---------------------------                                                                                                                                                                                                      0                                                                                                                                                                         
0

PL/SQL-Prozedur erfolgreich abgeschlossen.

Now I went on testing:
Made a new export. Import into app id 107 > Error occurred again.
Try to import the last working export into 107 > Error occurred again.
Log out of APEX and restart of Browser (Firefox 3). Start new instance of Firefox.
Go to app 107. Try to import the last working export into 107. Error occurred again.
You could get the feeling to give up by now... I DON'T!!!
Log out of APEX > Log in to other workspace in the same database.
Import a working export I made before > BOOM error occurred again.
By now I could say I just had luck yesterday! :O

What do i know now?
No more invalid objects! No import is possible anymore!

Lets go on:
Restart of OAS Service!
Import of not working export into id 107 > BOAH it worked again
Import of not working export into id 105 (prod app) > It worked too!

What does it mean?
It's not the exported file. Not even an invalid object problem. It doesn't seem to have to do with changes during the development on the application. What is it then?
Maybe it has to do with the PlsqlNLSLanguage I use. The last thing I did with the OAS was to patch to the newest patchset (Oct 2008) without any problems two weeks ago. I restarted the OAS but not the XE database!?

What's next to do?
I will write again if it happens again. I don't hope so but I almost sure that it will happen.

I got a hint from Dietmar to my last post APEX error ORA-20001 and ORA-02047 during application import (1)
I should use the following setting in my dads.conf:


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

I will try again on Monday!

ORA-

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.

APEX examples

APEX textareas with line breaks (CRLF)

Von Tobias Arnhold → 11.03.2008

Sometimes you could come in the situation to create automatic generated text. To show it in APEX you could use textarea items. In case you need to put up line breaks there are two ways I know about:

1. Use a PL/SQL process:


DECLARE
-- create line break variable
CRLF VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
BEGIN
-- set text
:P26_TEXTAREA := :P26_Text1 || CRLF || :P26_Text2;
END;

2. Use Javascript


function SET_MESSAGE_TEXT()
{
if ($x('P26_TEXTAREA_ACTION').value == 1){
$x('P26_TEXTAREA').value = 'Hello \n . next line';
}else {
$x('P26_TEXTAREA').value = '';
}}

More information:
Example application
APEX Forum entry

Thanks to Dimitri and Arie for the support when I had this problem the first time.