Recent Posts

Run dynamic action from report row and pass multiple variables

Von Tobias Arnhold → 2.23.2017
Execute a "Dynamic Action" by clicking on a button/link inside a report row is mostly handled by some triggering HTML class.

It actually works in 95% of all cases. But it is not the best way to do it. It is much more effective to execute the "Dynamic Action" with a custom event.

Reason is simple: You don't need to allocate unnecessary elements via a class by jQuery. You execute the "Dynamic Action" in the moment when it is needed. Like calling explicitly a Javascript function.
This matters if you show maybe 500 rows or more on a single page or you handle several dynamic actions in one report.

Running a dynamic action from inside a report is actually an old hat because there are a few guys which have been written about it. Anyway I'm still searching for it every time I need it and some of the code pieces are not up to date anymore. So I will show you the way I handle it today and probably tomorrow, too. :)

First I must thank Jeff Eberhard for his examples about this topic. He really inspired me using the technique in one of my projects where I had to suffer with many rows inside a report.

Blog posts (
Run Dynamic Action from JavaScript
Execute Dynamic Action From Report Column Link
Pass Multiple Values from Report to Dynamic Action

I prefer this way:

1. Set up a "Dynamic Action":
Custom Event: setIemsFromReport
Selection Type: Javascript Expression
Javascript Expression: document

1.1 Now add some action from type: "Execute Javascript Code"
In my example I set up three APEX items with data from my report row.

apex.item( "P1_DEVICE" ).setValue( );
apex.item( "P1_IP" ).setValue( );
apex.item( "P1_KOST" ).setValue( );

As you see the parameters are forwarded with specific variable names. The example came from Matt Nolan. I prefer it mostly because it is exact and it makes it easier to understand (maintainability).

1.2 To get the values into your APEX database session you add one more action from type: "Execute PL/SQL Code":

2. Report column
Inside my report I define a link column which looks like that:


javascript:apex.event.trigger(document, 'setIemsFromReport', [{device_name:'#NAME#', ip:'#IP#', kost:'#KOST#'}]);void(0);

apex.event.trigger executes the "Dynamic Action". 
[{...}] defines the variables to forward.
void(0) prevents the browser to do further actions.

Link Text:
<span class="fa fa-edit"></span> 

Link Attributes (not required):

By using some columns with case when clause I'm able to add some custom attributes like hide/show. Example:
    when SUBSTR(DEVICE,1,1) = 'T'
    then 'inline-block'
    else 'none' 

That is all you need to hand over attributes from your report row towards one or more APEX items on your page.

Using APEX_ERROR to manage custom error messages

Von Tobias Arnhold → 1.18.2017
Sometimes you just feel like you would be a newbie in coding business applications. Luckily it doesn't happen so often anymore. But this time it hit me hard. :)

During an application upgrade on Universal Theme I discovered an ugly workaround to create custom error messages I used in that time.

The old code looked like that:
  retval number;
  p_cust_id number;
  p_status varchar2(30);
  p_upduser varchar2(10);

  p_cust_id := :p1_cust_id;
  p_status  := 0;
  p_upduser := :app_user;

  retval := cust_apex_pkg.cust_apex_fnc ( p_cust_id, p_status, p_upduser );

  if retval = 1 then
    apex_application.g_print_success_message := '<span style="color: green;">Order was successfully published.</span>';
  elsif retval = 2 then
    apex_application.g_print_success_message := '<span style="color: red;">Error: Custom error 1 occurred.</span>';  
  elsif retval = 3 then
    apex_application.g_print_success_message := '<span style="color: red;">Error: Custom error 2 occurred.</span>';  
    apex_application.g_print_success_message := '<span style="color: red;">Error: Unknown error occurred.</span>';  
  end if;
As you can see I used the apex_application.g_print_success_message to overwrite the text color. Quite ugly but it worked as expected.
Anyway on Universal Theme it looked not so nice anymore because a "success message" has always a green background color. Putting some red text color above is not the user experience I would prefer.

I searched for about 3 minutes and found a really good article from Jorge Rimblas writing about the APEX_ERROR package. The blog post is from 2013 so this procedure must be available for a while now. What made me feeling like a jerk. The good side is that I now can start again to climb up on the iron throne. :)

The updated code looked like that:

  retval number;
  p_cust_id number;
  p_status varchar2(30);
  p_upduser varchar2(10);

  p_cust_id := :p1_cust_id;
  p_status  := 0;
  p_upduser := :app_user;

  retval := cust_apex_pkg.cust_apex_fnc ( p_cust_id, p_status, p_upduser );

  if retval = 1 then
    apex_application.g_print_success_message := 'Order was successfully published.';
  elsif retval = 2 then
      p_message => 'Error: Custom error 1 occurred.'
    , p_display_location => apex_error.c_inline_in_notification
  elsif retval = 3 then
      p_message => 'Error: Custom error 2 occurred.'
    , p_display_location => apex_error.c_inline_in_notification
      p_message => 'Error: Unknown error occurred.'
    , p_display_location => apex_error.c_inline_in_notification
  end if;


Thanks Jorges by blogging and sharing your knowledge.

APEX IR column only exportable for administrators

Von Tobias Arnhold → 1.13.2017
A few days ago I tweeted about a solution from Martin Giffy D'Souza to hide a specific column from export.

In my case I needed some username columns to be displayed during run-time but only exportable for administrators. The export itself was made with the default APEX CSV export.

Martin solutions was a really good starting point. I just had to add some security checks for the administrator role. And all together was put into a new authorization scheme which I added inside the conditional columns of my Interactive Report.

-- Authorization Scheme Name: ROLE_EXPORT_USERNAME
-- Validate authorization scheme: Once per page view

-- Code:
if pkg_security.has_user_role(:APP_USER,'ADMIN') = true or :REQUEST != 'CSV' then
  return true;
  return false;
end if;

A new year promises new possibilities!

Von Tobias Arnhold → 1.10.2017
The last year was quite successful even so it was a pain in the ass in many ways.
I have been on 3 conferences, made a few blog posts, got member at DOAG #NextGen, I initialized the "APEX Dashboard Competition" and I created a quite complicated example application which creates new solvable business cases for many of you: SVG in APEX.
This wouldn't be possible if I would have been alone. I worked with many different people in the community to get those activities done and I'm thankful for that.

Because I had the luck to talk to so many people I created a new idea regarding my main focus for this year "Getting students and trainees in touch with Oracle technology". This will not be just a simple presentation or some piece of code you create and publish. It is much more complicated and you can't do it alone.

By Kit from Pittsburgh, USA (Grads Absorb the News) [CC BY 2.0 (], via Wikimedia Commons

You may even have noticed it: Oracle will invest over 3 billion dollars for better education in the next 3 years. Only in Europe it will be around 1,4 billion. Something they may should have done 5 years ago. Anyway I'm curios if Oracle is just doing some great marketing slogan or if they start seeing the next generation of students as their most important invest after cloud.

In Germany we facing the problem finding new specialists since years. Except for a few companies we (the Oracle community) haven't done so much against it either.
Last year the German community woke up from their winter sleep by founding a new group inside DOAG called "DOAG #NextGen". This group focuses on students using Oracle products (Database, SQL, PL/SQL, APEX and more).

But why do we have the problem to find new students getting interested in Oracle software?
IMHO: Nowadays it is not enough to have a powerful engine and the fact that you can earn good money with it. It needs more to convince students to work with Oracle.

I tried it myself to get more students into Oracle APEX by making the APEX dashboard competition last year. Even so the competition was a success I have failed in my own focused main target: Getting new people interested into APEX.
I had do understand that it is much harder to get in touch with students then I thought. It is definitely not enough to post something in the most known IT event channels (Twitter, Eventbrite, XING, ...). It is actually the hardest and most time taking part to get in touch with them.

Luckily I don't give up so easily. At DOAG #NextGen we are planning a great event for students this year. More info's will follow.

I wish everyone a good start in 2017 and I will write about my next activities regarding students because I know that a lot of you understanding the need for Oracle specialists today, tomorrow and in 10 years.

SVG in APEX: Best Practices

Von Tobias Arnhold → 12.21.2016
In the last 8 month I was working on a new version of my SVG (Raphaël) example application. Of course I have a job (freelancer) and I have children. So the time to spend was diminished on only a few hours per week mostly on my way to work.
Some of the results were presented at the Swedish Oracle meetup in Summer '16. But the final application was made for DOAG 2016.

What leads me to an important point. To have the honor to present somewhere means for me also a passion to learn something new, create something new and share it with others to become a better developer. You may think 8 month of free time spend for a simple presentation is stupid but now I'm able to propose new business solutions to my customers which I didn't even thought were so easy to achieve.

And this is not all. One month before DOAG I was talking to a colleague "Sebastian Reinig" from another consulting company (Syntegris). A young men still studying for master grade and with a lot of passion towards Oracle APEX. He explained me that he worked on an APEX project using my former SVG solution from back in '14 and he enhanced my code by adding cool new features. (e.g. drag and drop in the harbor).
I mean those features were fitting perfectly in my example application. So I asked him to join my presentation at DOAG and show others what he was doing. An he did it. He added a new variant of my old example and helped me finishing the application.

That is why we should be presenting somewhere:
To become better by doing something new.
To become better by sharing your ideas with others and even get inspired by their ideas.

The result of this passion is the "SVG in APEX" application.

Example application URL:
User: demo
Password: demo


 1. Power grid (Great Britain including their main electricity pipelines)

2. Image tag editor

3. Harbor - Edit mask

4. Harbor - Drag and Drop

5. Mapael - Country visualization with edit mask

6. Mapael - Country visualization with canvas spark-line charts

7. Raphaël - Transform, integrate and interact with external SVG file
The application includes several slides describing the way how to transform an SVG file towards Raphaël.

8. SVG.js - Integrate and interact with external SVG files

SQL Developer: Quick Outline with SQL statements

Von Tobias Arnhold → 11.30.2016
Most of you probably know the "Quick Outline" function you have inside the SQL Developer.
It helps you to easily jump between different functions/procedures inside a package.

My colleague Holger told me about a bug in SQL Developer 3.x where you could use the "Outline" view with normal SQL files, too. Unfortunately in version 4 it didn't work anymore. So he stayed with version 3 for a long while. Otherwise he would had to scroll again instead of a short jump towards a specific SQL select.

A few days ago he asked me again if I knew a way how to easily jump between SQL statements inside a SQL file.
So I thought I talk to a SQL developer specialist "Sabine Heimsath". She knows all about those little tricks I have no clue about. But Sabine didn't know how to do it either.

My last chance was to ask on Twitter about a proper solution.

Even on Twitter nobody answered me. I guess they just started to implement such a feature. :)

Anyway yesterday Holger told me that he found a way to get it to run on SQL Developer 4.1.
Reason enough for me to share the awesome idea.

create or replace package body "" as 
/* ************************************************************************************************************************************************ */
function "SQL example 1";
-- SQL Statement

/* ************************************************************************************************************************************************ */
function "SQL example 2 - no character capping";
-- SQL Statement

/* ************************************************************************************************************************************************ */
function SQL_example_3_without_double_quote;
-- SQL Statement


Finally a little GIF movie showing you the usage in action:

Tablespaces verkleinern (TEMP, USER_TS, ORA-03297)

Von Tobias Arnhold → 11.28.2016

Die Select-Statements in diesem Blogpost habe ich von anderen Webseiten kopiert. Daher ist dieser Beitrag eher als Zusammenfassung unterschiedlicher Lösungsversuche zu sehen und dient mir als schnelle Hilfe bei der Verkleinerung eines zu großen Tablespaces. Schaut euch die Quellen an, die sehr viel detaillierter auf die jeweiligen Probleme eingehen.

Wer kennt nicht die Situation? Der DBA ruft an und meint der TEMP Tablespace verbraucht mehrere hundert Gigabyte an Speicher.

Was ist in solch einer Situation zu tun?
In dem Moment wo ein TEMP Tablespace überproportional ansteigt, muss eine Session diesen Anstieg verursachen. Mit dem folgenden Select erfahren Sie welche Session wie viel Speicher im TEMP-Tablespace verbraucht.

-- Source:
select   b.tablespace
       , b.segfile#
       , b.segblk#
       , round (  (  ( b.blocks * p.value ) / 1024 / 1024 ), 2 ) size_mb
       , a.sid
       , a.serial#
       , a.sql_id
       , a.username
       , a.osuser
       , a.program
       , a.status
    from v$session a
       , v$sort_usage b
       , v$process c
       , v$parameter p
   where = 'db_block_size'
     and a.saddr = b.session_addr
     and a.paddr = c.addr
order by b.tablespace
       , b.segfile#
       , b.segblk#
       , b.blocks;

Über die SQL_ID können Sie wenn vorhanden auch auf das Verursacher-Select zugreifen:
-- Source:
select sql_text 
from v$sql 
where sql_id='b6kta08q9jj3f';

Über den SQL Developer > Tools > Monitor Sessions können Sie die betroffene SID killen.

Anschließend wird der Verbrauch des TEMP-Tablespace zwar wieder zurückgefahren, aber die Größe bleibt bestehen.

Prüfen Sie daher zunächst die aktuelle verwendete Größe im TEMP-Tablespace:
-- Source:
select b.total_mb,
       b.total_mb - round(a.used_blocks*8/1024) current_free_mb,
       round(used_blocks*8/1024)                current_used_mb,
      round(max_used_blocks*8/1024)             max_used_mb
from v$sort_segment a,
 (select round(sum(bytes)/1024/1024) total_mb from dba_temp_files ) b;

-- Oder:
select * from dba_temp_free_space;

Wenn der Wert (current_used_mb) entsprechend klein ist, dann können Sie den TEMP-Tablespace verkleinern, andernfalls haben Sie nicht die richtige Session gekillt.

TEMP-Tablespace verkleinern:
select file_name,bytes,blocks from dba_temp_files;
-- .../tempfile/temp.911 564863696896 68953088

alter tablespace temp shrink space;

select file_name,bytes,blocks from dba_temp_files;
-- ../tempfile/temp.911 289513472 35341

Es kann aber auch vorkommen das eine normaler Tablespace zu groß wurde und dadurch viel mehr Platz verbraucht als es aktuell verwendet.
Um darüber einen Überblick zu erhalten, führen Sie folgendes Select aus:
 -- Source: Nicht mehr bekannt :(
  ,nvl(round(100 * freemb / sizemb,1),0) free_prozent
   ,round(tbs_size,2) as sizemb
   ,a.free_space freemb
      ,round(sum(bytes)/1024/1024 ,2) as free_space 
     from dba_free_space group by tablespace_name
    ) a
      sum(bytes)/1024/1024 as tbs_size 
     from dba_data_files group by tablespace_name
       sum(bytes)/1024/1024 tbs_size
     from dba_temp_files
     group by tablespace_name 
    ) b
  where a.tablespace_name(+)=b.tablespace_name
  ) mb
order by free_prozent;

Bei einer solchen Situation muss anstelle des Tablespaces die Datendatei verkleinert werden.
Nun benötigen Sie dafür noch den richtigen Dateinamen, um die korrekte Datei zu verkleinern:
  bytes/1024/1024 AS size_mb
FROM   v$datafile

Statement zum verkleinern der Datendatei:

Die Verkleinerung kann aber in einem ORA-03297 Fehler enden.

Error report -
SQL Error: ORA-03297: file contains used data beyond requested RESIZE value

Jetzt bleiben Ihnen 3 Schritte um mit geringem Aufwand diese Datei doch noch zu verkleinern:

1. Fragmentierung bereinigen
Die Datendatei wurde fragmentiert und eine Tabelle liegt am Ende der Datei und verhindert dadurch die Verkleinerung.

Die Datendatei ist 90 GB groß und tatsächlich werden nur 2 GB verwendet.
Eine Tabelle liegt von der Verteilung her zwischen 82-83 GB. Heißt, ich könnte die Datendatei nur auf 84G verkleinern. Also müssen Sie in solch einem Fall das Objekt ausfindig machen und löschen. Sinnvollerweise kopiere ich vorher die Tabelle in einen anderen Tablespace, um diese anschließend wieder herstellen zu können. :)

Um die Blockverteilung analysieren zu können, muss vorher die richtige File-ID ausgelesen werden:
  s.tablespace_name, s.owner, s.segment_name, s.segment_type,
  sum(s.bytes) size_in_bytes,
  round(sum(s.bytes) / 1024 / 1024, 2) size_in_m,
  sum(round(sum(s.bytes) / 1024 / 1024, 2)) over() as size_in_m_gesamt,
from sys.dba_segments s, sys.dba_data_files f
where f.tablespace_name = s.tablespace_name
and f.file_id = s.header_file
and s.tablespace_name in ('NTDC03')
group by s.tablespace_name, s.owner, s.segment_name, s.segment_type, f.file_id, f.file_name
order by s.tablespace_name, s.owner, s.segment_name;

Das folgende Select zeigt die Blockverteilung mit den verwendeten DB-Objekten innerhalb der Datendatei:
  blocks*8192/1024/1024 as mb,
  owner||'.'||segment_name as object_name,
  block_id*8192/1024/1024 as position_mb
from sys.dba_extents
where file_id = 206
  blocks*8192/1024/1024 as mb, 
  'Free' as object_name,
  block_id*8192/1024/1024 as position_mb
from sys.dba_free_space
where file_id = 206
order by 1,2,3;

Wenn Sie die betroffenen Tabellen gelöscht haben, dann klappt auch die Verkleinerung wieder:  
Database datafile '.../DATAFILE/my_schema.033.123331' altered.

2. Recycle Bin löschen
purge recyclebin;

3. Coalesce Tablespace
alter tablespace fred coalesce;

Info: TEMP Tablespace auf Unlimited setzen
alter database tempfile '.../TEMPFILE/temp.910.901132571' autoextend on next 250m maxsize unlimited;