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.

APEX examples

APEX "Display as Text" items with border and background-color

Von Tobias Arnhold → 10.26.2008

I don't know if you have noticed it yet. But the behavior of "Text Field (Disabled, save state)" items show up different from Firefox to IE or Opera. In Firefox (what I think looks much more standard like) the background color of the item value is gray and in all other browsers its white.

I couldn't find any solution for this item type. But I found another way to let a value look gray in all browsers using the
"Display as Text" item.

How to:
Create item as: "Display as Text (saves state)"
Edit item > Element > Element Table Cell Attributes

style="border: 1px solid rgb(204, 204, 204); width: 80px; background-color: rgb(225, 225, 225); padding: 2px;"

To see in an example app: http://apex.oracle.com/pls/otn/f?p=28737:6

Here are the different browser views (more or less adjust to FF3):

Internet Explorer 7:

Opera 9.51:

Internet Explorer 6 (with MultipleIE):

Firefox 3:

It's crazy how different such small details can be...

APEX examples

APEX Interactive Reports with dynamic filters

Von Tobias Arnhold → 10.20.2008

A couple of days ago I worked with dynamic filters in Interactive Reports (In my case: filters which get created at the page start). I used them in pop up pages to show up just a couple of rows depending on the clicked value.

I created an example: http://apex.oracle.com/pls/otn/f?p=28737:4

I must say I couldn't fix that problem myself but with the terrific APEX community I found a solution.
Look at the whole entry: Set Interactive Report in popup with start search value and submit
Thanks again to Andy who made this useful tip possible.

How to do it?
In your pop up site where your Interactive report is go
Home>Application Builder>Application 28737>Page 5>Edit Page> HTML Header
and add this javascript.


<script language="JavaScript" type="text/javascript">

function set_ir_search_val() {
init_gReport();
var v_stop_js = document.getElementById('P5_STOP_JS');
if (v_stop_js.value != 1) {
var v_page = 'P4_SELECT';
var v_from = opener.document.getElementById(v_page);
var v_to = document.getElementById('apexir_SEARCH');
v_to.value = v_from.value;
v_stop_js.value = 1;
gReport.search('SEARCH');
}
}
addLoadEvent(set_ir_search_val);

</script>

Now go Home>Application Builder>Application 28737>Page 5>Edit Region> Region Footer
and add this script:


<script language="JavaScript" type="text/javascript">
  set_ir_search_val();
</script>

Finally you need to create a hidden item, in my case: P5_STOP_JS.

I think when you use Interactive Reports a lot then this tip can be really useful for you.

Development tools

Oracle SQL Developer Data Modeling database re-engineering with APEX features

Von Tobias Arnhold → 10.13.2008

I tried a bit around with the new Oracle SQL Developer Data Modeling. First I tried to get it to run on my USB stick without the included JRE.
After it started I used some APEX features to re-engineer a database model.

How to (tested on Windows XP):
1. Download the software and extract/install it
OSDM:
Download from: Oracle_Download_Page
If you use the usb stick copy it to: %YOUR_USB_DRIVE%\PortableApps\ORACLE\oracle_data_modeller

PStart (if you want to use your OSDM from an USB stick in a comfortable way)
Download from pegtop.net

2. Get the new OSDM to run

Either you start it from your standard installation, for example
C:\ORACLE\ADMINISTRATION\oracle_data_modeller\bin\osdm.exe

Or you create an own batch if you want to use it from an USB stick
REM Start osdm with nojre installed
REM (standalone from USB stick)

REM 1. set variables
REM 1.1 Path Variable
REM set PATH=\PortableApps\ORACLE\INSTANT_CLIENT;%PATH%

REM 1.2 ORACLE_HOME variable
REM set ORACLE_HOME=\PortableApps\ORACLE\INSTANT_CLIENT

REM 1.3 JAVA variable
set PATH=\PortableApps\RUNTIME\JRE\bin;%PATH%
set JAVA_HOME=\PortableApps\RUNTIME\JRE\bin

REM 1.4 TNS_ADMIN variable
REM set TNS_ADMIN=\PortableApps\ORACLE\INSTANT_CLIENT

REM 1.5 NLS_LANG Variable
REM set NLS_LANG=German_Germany.WE8MSWIN1252

REM 2. Connect app path
cd \PortableApps\ORACLE\oracle_data_modeller\bin

REM 3 Import java files into path variable
set cwdcp=..\conf
set cwdcp=%cwdcp%;..\lib\osdm.images.jar
set cwdcp=%cwdcp%;..\lib\osdm.exports.jar
REM this string you need to add
REM if you create it from the Linux script
set cwdcp=%cwdcp%;..\lib\osdm.imports.jar
set cwdcp=%cwdcp%;..\lib\osdm.sets.jar
set cwdcp=%cwdcp%;..\lib\osdm.model.jar
set cwdcp=%cwdcp%;..\lib\osdm.gui.jar
set cwdcp=%cwdcp%;..\lib\osdm.utils.jar
set cwdcp=%cwdcp%;..\lib\log4j.jar
set cwdcp=%cwdcp%;..\lib\lf\jlfgr.jar
set cwdcp=%cwdcp%;..\lib\poi\poi.jar
set cwdcp=%cwdcp%;..\lib\jimi\JimiProClasses.jar
set cwdcp=%cwdcp%;..\lib\jdbc\oracle\ojdbc5.jar
set cwdcp=%cwdcp%;..\lib\oracleaw\awxml.jar
set cwdcp=%cwdcp%;..\lib\oracleaw\olap_api.jar
set cwdcp=%cwdcp%;..\lib\oracleaw\xmlparserv2.jar
set cwdcp=%cwdcp%;..\lib\ohj\help4.jar
set cwdcp=%cwdcp%;..\lib\ohj\ohj-jewt.jar
set cwdcp=%cwdcp%;..\lib\ohj\oracle_ice.jar

REM 4. start application
java -Xmx1024M -Xms258M -classpath %cwdcp% oracle.dbtools.crest.swingui.ApplicationView

3. Create DDL with APEX

Go Home > Utilities > Generate DDL
Select your schema
Under "Object Type" click on "Check all" and Output to "Save As Script File"
Now click "Generate DDL" and give it a name like NEW_DDL_SCRIPT

Download your generated script: Home > SQL Workshop > SQL Scripts > Script Editor
Click on your script and download it as "NEW_DDL_SCRIPT.sql".

4. Integrate DDL
Start the OSDM (via batch or the osdm.exe)
File > Import > DDL File

Select your APEX generated DDL file
Select your database version, for example: Oracle Database 10g
Import runs and log-file will be generated

Oracle SQL Developer Data Modeling Version: 1.5.1 Build: 518
Data Modeling Import Log
Date and Time: 2008-10-13 15:50:26
Design Name: test
RDBMS: Oracle Database 10g

All Statements: 75
Imported Statements: 75
Failed Statements: 0
Not Recognized Statements: 0

The generated model looked really well and I can only hope that this tool will be free at the end of the development.

APEX examples

APEX radio group with dynamic help text

Von Tobias Arnhold → 10.12.2008

I came across a problem with help text information for the values of a radio group (LOV). I wanted to pop up help information when you move over the output items of the radio group.

To include this ability you have to a enhance your LOV query.

Go Edit Page Item > List of Values > List of values definition:

select
('<span style="cursor:help" title="' || product_description || '">'
|| PRODUCT_NAME || '</span>') as show_value,
PRODUCT_ID return_value
from DEMO_PRODUCT_INFO
order by 1

It will look like:

See it in action: http://apex.oracle.com/pls/otn/f?p=28737:3

There are a couple of other resources about this topic available:

Completely different example how to show up some help (Just click on a red text title):

German Oracle Users site

TREE

Advanced APEX trees

Von Tobias Arnhold → 9.26.2008

Last couple of weeks/days when i went through the Oracle APEX forum there was quite a lot of questions about the APEX trees.
I really worked a lot with them and want to describe how to use them.

1. This how to will base on the following table ddl:

CREATE TABLE  "LOG_SYSTEMS"
( "S_ID" NUMBER NOT NULL ENABLE,
"S_NAME" VARCHAR2(100) NOT NULL ENABLE,
"S_DESCRIPTION_SHORT" VARCHAR2(100),
"S_PARENT" NUMBER NOT NULL ENABLE,
"S_RESPONSIBLE_DEPARTMENT" VARCHAR2(5),
"S_CONTACT_PERSON" VARCHAR2(100),
"S_TYPE" VARCHAR2(20)
CONSTRAINT "PK_S_ID" PRIMARY KEY ("S_ID") ENABLE
)

2. How does the standard SQL selection of a APEX tree look like

select "S_ID" id, -- Primary key
"S_PARENT" pid, -- Parent key
"S_NAME" name, -- Displayed value (node description)
'f?p=&APP_ID.:1:&SESSION.::NO::P1_SYSTEM:'||"S_ID" link, -- linked page
null a1,
null a2
from "#OWNER#"."LOG_SYSTEMS"

3. How to improve the displayed value (node)
You can concat several columns:
-- name with description
"S_NAME" || ' (' || "S_DESCRIPTION" || ')' AS name,

-- name with html (big)
'<b>' || "S_NAME" || '</b>' AS name,

-- name with icon in front of it
'<img src="#WORKSPACE_IMAGES#tree_group.png" border="0" style="vertical-align:middle" alt="Group">&nbsp ' || "S_NAME" AS name,

-- Using of the case command to see different node values
CASE
WHEN "S_TYPE" = 'GROUP' THEN '<b>' || "S_NAME" || '</b>'
WHEN "S_TYPE" = 'SYSTEM' THEN "S_NAME" || ' (' || "S_DESCRIPTION" || ')' END AS name,

4. How to use the link column
-- Standard link column would link to page 1 and overwrite the
-- value of :P1_SYSTEM
'f?p=&APP_ID.:1:&SESSION.::NO::P1_SYSTEM:'||"S_ID" link,

-- Using several values to overwrite in page
'f?p=&APP_ID.:1:&SESSION.::NO::P1_SYSTEM,P1_S_NAME,P1_FROM_TREE:'|| "S_ID"||','||"S_NAME"||',YES' AS link,

-- Link to a specified page via node value
'f?p=&APP_ID.:' ||"S_ID" || ':&SESSION.::NO::' AS link,

-- Link to a specified page via node value and javascript
'javascript:change_page(' || "S_ID" || ')'

Go Edit Page > HTML Header
<script language="JavaScript" type="text/javascript">
function change_page(v_page) {
var v_url = 'f?p=&APP_ID.:' + v_page + ':&SESSION.::::';
window.location.href = v_url;
}
</script>

-- If you use your tree as a pop up tree and want to give back a node
-- value to the parent page with submit and close the pop up
-- page automatically afterward
-- Info: I used it as a self made pop up tree item
'javascript:close_page(' || "S_ID" || ',''' || "S_NAME" || ''')' AS link,

-- I also used a hidden item in the pop up page to fill with the parent
-- page id &P101_WHEREFROM.
Go Edit Page > HTML Header
<script language="JavaScript" type="text/javascript">
function close_page(v_id,v_value) {
var v_page = 'P' + '&P100_WHEREFROM.' + '_S_ID';
var v_page_display = 'P' + '&P101_WHEREFROM.' + '_S_NAME';
var l_field_id = opener.document.getElementById(v_page);
l_field_id.value = v_id;
if(l_field_id.getAttribute('onchange') || l_field_id.onchange) {l_field_id.onchange()}
var l_field_value = opener.document.getElementById(v_page_display);
l_field_value.value = v_value;
if(l_field_value.getAttribute('onchange') || l_field_value.onchange) {l_field_value.onchange()}
close();
window.opener.doSubmit('POPUP_RELOAD');
}
</script>

I used these examples in my application TIA: http://apex.oracle.com/pls/otn/f?p=25500:1
Login: guest Password: apexuser
Download it from: http://www.oracle-apex-award.de/TIA-Technische-Infrastruktur.70.0.html
The application is in German but the developments (used variables, hints) are made in English.

Hope you can use it in your application and of course you are welcome http://www.blogger.com/img/blank.gifto give me some feedback.

Update 12.09.2011:
The example application doesn't work anymore. I currently work on a new version which includes the new APEX tree as well:
http://apex-at-work.blogspot.com/2011/09/logbuch.html
I will try to extend the view of the standard APEX tree as well as I did with the old one. Of course I will write about it. :)

Update 26.09.2011:
I just saw a nice example application about APEX trees:
http://apex.oracle.com/pls/apex/f?p=36648:27

APEX examples

APEX_MAIL with UTL_TCP under XE database

Von Tobias Arnhold → 9.24.2008

If you want to use the features of the UTL_TCP package you need to publish it to your APEX application user. (I tested it under APEX 3.1.2)

How to:

-- sqlplus
GRANT EXECUTE ON "SYS"."UTL_TCP" TO "APEX_USER"

-- sqlplus
create or replace synonym UTL_TCP for SYS.UTL_TCP;

-- an APEX process procedur
-- apex_mail procedure call
apex_mail.send(
p_to => 'user@company.com',
p_from => 'info@company.com',
p_body => 'New message cerated from ' || :p1_user || '.' || utl_tcp.crlf ||
'Description: ' || utl_tcp.crlf || :p1_description,
p_subj => 'New message! ' utl_tcp.crlf);

-- push the e-mail queue for immediate delivery
wwv_flow_mail.push_queue(
P_SMTP_HOSTNAME => 'ip',
P_SMTP_PORTNO => 'port');

Update (08.09.2009):
There was a interesting question about the APEX_MAIL function and pushing the email queue in the Oracle forum which you may be interested on: apex mail - mail queue

APEX examples

APEX DoSubmit button with js before pl/sql processes

Von Tobias Arnhold → 9.17.2008

Sometimes you need to use javascript after click on a button before the APEX validations and processes starts.
(For example you have a report with editable fields and maybe a save button on every row. In these special cases you could need something like that.)

Go on edit Button > URL Redirect > change/add
Target is: URL
URL Target: javascript:BUTTON_ACTION();

Edit Page > HTML Header > add the new javascript

<script language="JavaScript" type="text/javascript">
function RR_ACTION_UPDATE() {
// P1_ACTION field into JS variable
var l_field_id = document.getElementById("P1_ACTION");
// Now you are able to put in every type of code
// example: set value for field l_field_id.value =
// 'UPDATE';
// calculate: l_field_id.value = 5 + 5;

// doSubmit action
doSubmit('DOSUBMIT');
}
</script>

APEX examples

APEX Report with changing icons on row level

Von Tobias Arnhold → 9.10.2008

If you want to show different icons based on a column value in your report.
Create a selection set for your report which works with the decode function (I used the example table emp):

Source code:

SELECT
e.empno,
e.ename,
decode
(e.job,
'PRESIDENT',
'<a title="President"><img src="#WORKSPACE_IMAGES#president.png" border="0" alt="president"></img></a>',
'MANAGER',
'<a title="Manager"><img src="#WORKSPACE_IMAGES#manager.png" border="0" alt="manager"></img></a>',
'ANALYST',
'<a title="Analyst"><img src="#WORKSPACE_IMAGES#analyst.png" border="0" alt="anaylst"></img></a>',
'<a title="Others"><img src="#WORKSPACE_IMAGES#other.png" border="0" alt="other"></img></a>')
AS only_with_job_icon,
decode(e.job,
'PRESIDENT',
'<a href="javascript:PopUpPage2_REPORT(''' || e.job ||
''');" title="President"><img src="#WORKSPACE_IMAGES#president.png" border="0" alt="president"></img></a>',
'MANAGER',
'<a href="javascript:PopUpPage2_REPORT(''' || e.job ||
''');" title="Manager"><img src="#WORKSPACE_IMAGES#manager.png" border="0" alt="manager"></img></a>',
'ANALYST',
'<a href="javascript:PopUpPage2_REPORT(''' || e.job ||
''');" title="Analyst"><img src="#WORKSPACE_IMAGES#analyst.png" border="0" alt="anaylst"></img></a>',
'<a href="javascript:PopUpPage2_REPORT(''' || e.job ||
''');" title="Others"><img src="#WORKSPACE_IMAGES#other.png" border="0" alt="other"></img></a>') AS link_via_js_and_job_icon
FROM emp e

Column description:

only_with_job_icon - Only show an icon without linking to any page

link_via_js_and_job_icon - Show an icon with linking to another page via javascript

Create the javascript for the link column: link_via_js_and_job_icon

Go into Page Attributes > HTML Header and add a javascript like:

Source code:

<script language="JavaScript" type="text/javascript">
function PopUpPage2(v_job){
url = "f?p=&APP_ID.:2:&SESSION.::::P2_JOB:" + v_job;

w = open(url,"winLov","resizable=yes,
scrollbars=yes,status=no,width=1152,height=768");

if (w.opener == null)
w.opener = self;
w.focus();
}
</script>

APEX examples

Using CSS in APEX pages and regions

Von Tobias Arnhold → 9.02.2008

If you want to use your own style sheets in your application here an example how to do it:

Go Edit Page Attributes --> HTML Header --> Add css text and Apply Changes

Go Edit Region --> Title: <h1 >Headline </h1 > and Apply Changes

In my case I made a select list item with a list of values to change the css in the page title dynamically.

In my example application you can see how it would look: http://apex.oracle.com/pls/otn/f?p=25472

Edited 07.10.2008:
I had the problem in one of my headlines that I wanted to use some text with bold and some without. It always put in a line break in between the title.
How to avoid that:

Go Edit Page Attributes --> HTML Header --> Add css text for the span command

Go Edit Region --> Title: <h1>Headline: <span>(Detailed information)</span> </h1>

APEX examples

Implement your APEX application into another website with IFRAME

Von Tobias Arnhold → 8.23.2008

To implement your APEX application into another website (for example an intranet) then you need to use the following iframe script in the html body section:
iframe src="YOUR_SERVER/pls/otn/f?p=APP_ID:PAGE_ID:SESSION_ID:::::" width="400" frameborder="0" height="150" frameborder="0"

APEX examples

Implementation of a flash movie (screen cast) into your APEX application

Von Tobias Arnhold → 8.13.2008

I would like to give a workaround about how to add screen casts into your APEX application.

Last month I got second place at the first ORACLE-APEX-AWARD in Germany and one of the optional requirements was a screen cast for a help system in the application.
Unfortunately I didn’t have the time to integrate it into my application.
After the competition I followed the interesting aspect about screen casts and the integration into APEX.

Here an example how to do it:

1. Create a new template page without any logos

Go “Shared Components” > Templates
Copy the “Printer Friendly” page and give a new template name like “Help_Page”
Edit the Template “Help_Page” > In the Body area there should be a value like: valign="top">#LOGO##REGION_POSITION_06#
Take away the value #LOGO#: valign="top">#REGION_POSITION_06#

2. Create a new empty page
3. Inside the new page go > edit page attributes > Display Attributes > Page Template > set “Help_Page”
4. Add this under the HTML Body Attributes (this will make the connection between your file and the page):

5. Upload the Flash file (.swf)

Shared Components > Static Files > Create
Browse > choose the *.swf file > Open

6. Add a new Navidation Bar and at the second page you need these values:

Target is a = URL
URL Target= javascript:void( window.open( 'f?p=&APP_ID.:YOUR_PAGE:&SESSION.:::::', 'popup', 'toolbar=no,width=1280,height=982, resizable=yes,top=40,scrollbars=yes'));

7. Click on your new navigation bar and the page with the flash movie in it should start.

APEX examples

How to handle item values in a region title

Von Tobias Arnhold → 8.12.2008

If you want to have a dynamic region title in your application you need to do the following steps:

Go to Edit Region > Identification > Title: "Headline: &P1_NAME." > Apply Changes

If you start your application now and the item :P1_NAME would have the value “Monster” it would look like that:

FREELANCER

Ein paar Fakten über meine Person

Von Tobias Arnhold → 8.11.2008

Mein Name ist Tobias Arnhold. Ich möchte Ihnen meine Fähigkeiten und Aktivitäten in der Community in diesem Post kurz aufzeigen. Weitere Informationen erhalten Sie über mein Beraterprofil, dass ich Ihnen jederzeit zukommen lassen kann.

AKTUELLE SCHWERPUNKTE MEINER TÄTIGKEITEN

Implementierung, Beratung und Projektleitung in den Bereichen

Oracle Application Express (APEX, formals HTML DB)
Oracle SQL und PL/SQL Programmierung
Oracle Datenbankentwicklung und -design
Schnittstellen (Webservices, Files, DB-Links)
Migration

APEX 5
Oracle REST
Datenbankablösung (SQL Server, MySQL, Access, Excel)

Business Intelligence

Datamining-Analysen

Fortgeschrittene SQL Analysetechniken
SPSS Clementine

Datensteuerungsprozesse

SQL und PL/SQL
SPSS Clementine

KNOWHOW IN PROGRAMMIERSPRACHEN & BESONDEREN ANWENDUNGEN
(Klassifizierung von 1 Nicht vorhanden - 10 Experte)

Oracle SQL

Oracle APEX

Oracle PL/SQL

HTML / CSS

jQuery / Javascript

SPSS Clementine

JSON / XML

Oracle REST & ORDS

Oracle DB Administration

MS Access und MS Excel

KNOWHOW IN SOFT SKILLS
(Klassifizierung von 1 Sehr schlecht - 10 Sehr gut)

Kommunikative Kompetenz

Selbstbewusstsein

Einfühlungsvermögen

Teamfähigkeit

Kritikfähigkeit

Analytische Kompetenz

Vertrauenswürdigkeit

Selbstdisziplin/Selbstbeherrschung

Neugierde

Konfliktfähigkeit

Durchsetzungsvermögen

Die Definition von dem jeweiligen Softskill finden Sie in diesem Artikel:
Die elf wichtigsten Soft Skills

VORTRÄGE AUF KONFERENZEN

2018

APEX Einführungsvortrag, TH Bingen (Bingen)

2017

Grafische Interaktion in webbasierten Geschäftsanwendungen, IT-Tage 2017 (Frankfurt)
SVG in webbasierten Businessanwendungen, FH Trier (Trier)
What is standard APEX?, POUG 2017 (Krakau)
What is standard APEX?, SWEOUG - APEX Day 2017 (Stockholm)
What is standard APEX?, Oracle Finland - APEX Day (Helsinki)
#NextGEN - Erreiche die Mitarbeiter von Morgen!, DOAG APEX Connect (Berlin)

2016

SVG in APEX: Best Practices an Live Beispielen, DOAG Konferenz (Nürnberg)
Working with interactive SVG graphics in APEX , Stockholm Oracle Meetup group (Stockholm)
APEX Dashboard Competition, Stockholm Oracle Meetup group (Stockholm)
Charts und Auswertungen in APEX, DOAG Usergruppe (Hamburg)
Fortgeschrittene SQL Techniken in APEX, Video, DOAG APEX Connect (Berlin)

2015

Die APEX 5 Migration – Universal Theme, DOAG Konferenz (Nürnberg)
APEX & SQL The Reporting Solution, DOAG Konferenz (Nürnberg)
APEX 5 Migration, APEX Summerschool
APEX Plugins maßgerecht verwenden, DOAG APEX Connect (Düseldorf)

2014

RaphaelJS – Interaktion mit grafischen Objekten in APEX, DOAG Konferenz (Nürnberg)
APEX & Pivot Tabellen, DOAG Usergruppe (Frankfurt)
Regeln für die Entwicklung von APEX Anwendungen, DOAG Development (Düsseldorf)

2012

APEX & Pivot Tabellen – DOAG SIG Development (Karlsruhe)
APEX Plugin Examples – DOAG Usergruppe (Dresden)

2011

APEX Plugin Examples – DOAG Usergruppe (Frankfurt, Mannheim)

FACHARTIKEL / BÜCHER

2017

DOAG #NextGEN - Vorstellung , Red Magazine (DOAG Zeitschrift)

2016

APEX Universal Theme - Die neuen Hürden mit dem Responsive Layout , Red Magazine (DOAG Zeitschrift)

2015

APEX Mythen, DOAG News Zeitschrift

2012

Erstellung eines APEX Plugins, DOAG News Zeitschrift

ORACLE AUSZEICHNUNGEN

2017

Oracle ACE

2016

Oracle ACE Associate
APEX.WORLD Member of the month: January

2009

Platz (4-12) Oracle APEX Developer Competition

2008

Platz 2 Oracle APEX Award

Personal stuff

First apex-at-work blog

Von Tobias Arnhold → 8.10.2008

In my first blog I want to give a preview what you can expect from this blog in the future.

This blog spins around Oracle APEX and all you can imagine could have to do with it, for example: