Classic Report - Preserve Pagination after PPR

In this example you can see how easy it is to preserve report pagination after PPR. With a little jquery coding you can attach a function to the pagination request and this way "document" where the pagination goes. After a report refresh you may read those values and paginate back to where you were.

SQL Developer 4.0

I personally didn't like SQL Developer because of it's clumsy java interface and found TOAD much better. Now, the version 4.0 looks more professional, cleaner and nicer. The new look and feel has been improved a lot. You can now reorder editor tabs without any side effects. SQL Developer has a big advantage if you have to work on a device for which you have no administrative rights - it doesn't require an installation. It is almost perfect if there wouldn't be a funny logic for making it available. On the SQL Developer download page you can choose between several versions:

1. Windows 64-bit - zip file includes the JDK 7
2. Windows 32/64-bit
3. Mac OS X
4. Linux RPM
5. Other Platforms


Only the 64-bit version includes JDK. It is a mystery to me why the other installation versions do not include the same. If your system is a 32-bit only and you have no way to install anything, you will be faced with a problem. The 64-bit jdk will not run there. I helped myself with the following trick:

1. downloaded the jdk 7.0 from here
2. installed it on an another computer
3. copied the entire folder of the installation to the computer where I have no admin rights - jdk1.7.0_45 - and placed it into the SQL Developer folder
4. finally, I changed the sqldeveloper.conf file by changing the line for setting the java home

SetJavaHome D:\oracle_tools\sqldeveloper\4.0\sqldeveloper\jdk1.7.0_45

After this change it worked as expected.

APEX Listener and Excel Upload

If you are using APEX Listener version 2.0, you can download a sample application here. In this application I am showing how to:

1. create an excel upload page
2. manage multiple excel sheets after upload

This new APEX Listener feature is great and I hope they will extend it to the other excel file formats - currently it works for .xls only. If you are using APEX 4.2.3 this will probably not work since there is a bug in that version of APEX.

So, the current limitations are:

1. xls files only
2. APEX 4.2.3 is buggy related to this feature
3. you can upload up to 49 columns since it is using APEX Collection and one column is used for excel tab names during the upload

In addition, you will need to add these four lines of code to the defaults.xml file:

<entry key="apex.excel2collection">true</entry> <entry key="apex.excel2collection.onecollection">true</entry> <entry key="apex.excel2collection.name">EXCEL_COLLECTION</entry> <entry key="apex.excel2collection.useSheetName">true</entry>

Enjoy.

APEX and Session State Protection

Prior to configuring Session State Protection in your applications you need to be aware of one important thing. Setting it up and configuring the settings will change the settings for all the items in your application - even if the items were protected before that change using some other protection methods. Deactivating the protection will again remove the protection completely. At the end this could mean a lot of work - get a backup and manually restore the original settings.

Meine Präsentation bei Orbit - Oracle Day

Am 18.09.2013 organisiert die Firma Orbit eine Verantstalltung mit dem Namen "Oracle Day". Der Titel meiner Präsentation ist "APEX – Applikationen im Expressverfahren". Die Agenda ist ziemlich interessant und umfasst viele Themen bezüglich Backup-Strategien, APEX, Geodatenverarbeitung und Business Intelligence-Lösungen. Die Agenda kann hier gefunden werden. Die Veranstaltung ist selbstverständlich kostenlos. Also, meldet euch an und wir sehen uns dort.

APEX Presentations September and October

September and October 2013 are going to be tough. I am supposed to hold five presentations all together. Here is the schedule and the presentation titles:

04.09.2013 - MT AG (Ratingen, Germany) - "My Demo Application - die wohl populärste APEX Demo Anwendung"

18.09.2013 - Orbit (Bonn, Germany) - "APEX - Applikationen im Expressverfahren erstellen"

19.09.2013 - DOAG Regional (Mannheim, Germany) - "APEX, Installation, Bereitstellung, Schnittstellen und AddOns richtig verwalten"

22.10.2013 - SlOUG Slowenian Oracle User Group Conference (Ljubljana, Slowenia) - "Ten things you need to know about APEX – APEX Features, Deployment and Application Programming"

24.10.2013 - HROUG Croatian Oracle User Group Conference (Rovinj, Croatia) - "APEX 4.2 – Installation, Deployment and Application Management"

About Website Programming

I found this picture on this web page: http://xkcd.com/773/

It is funny but true.

Oracle APEX 4.2 New Features und Tipps aus der Praxis

Unser nächster Kurs wird im November, am 11.11 und 12.11 in Bensheim stattfinden. Das Thema ist ziemlich spannend - APEX 4.2 New Features und unsere Erfahrungen aus der Praxis mit der neuen Version. Wir werden diesmal sehr viel über dem APEX Listener, Einsatz von jQuery und über Restfull Web Services reden und natürlich, praktisch üben. Dieser Link informiert Sie über alle weiteren Details zum Kurs.

Create a Success Message using Dynamic Action - Second

You can extend this simple example by adding a counter to it and close it after the specified number of seconds:

var my_counter = 5;

var success_message = $('#P299_MESSAGE').val() + '<br/>' 
+ 'This message will close in ' + 
'<span id="my_sec">' + my_counter + '</span>' + ' seconds.';
$('.t10messages').empty();
$('.t10messages').append(<div class="t10success" style="display: none;">
</div>
');
$('.t10success').append(success_message);
$('.t10success').fadeIn(1000);

var time_in_seconds = setInterval(function() {

my_counter--;

$('#my_sec').empty();
$('#my_sec').append(my_counter);

if (my_counter == 0) {
    clearInterval(time_in_seconds);
    $('.t10success').fadeOut(1000);
    }
}, 1000);

Using jQuery this is quite easy to acomplish.

Create a Success Message using Dynamic Action

This simple example is showing how to create a success message using dynamic actions. One thing needs to be mentioned though. The last one of the three actions depends on your current template:

var success_message = $('#P299_MESSAGE').val();

$('.t10messages').empty();
$('.t10messages').append('<div class="t10messages"><div class="t10success" 
style="display: block;"></div></div>');
$('.t10success')
.append(success_message)
.slideDown('slow');

The best thing is either to open the template and have a look at the structure of the success message part or to use firebug and inspect the HTML structure on your page.

Enjoy.

Enable and Disable a Checkbox in a Tabular Form

This simple example is showing how to use a simple checkbox column in a tabular form to enable/disable or check/uncheck another checkbox column.

APEX Tabular Form - Instant Update

Yesterday an interesting question regarding tabular forms, collections and instant updates was asked in the Oracle APEX Forum. This example in my Demo Application shows how you can create a tabular form based on a collection and update this collection instantly. The whole example consists of three main parts:

1. save changes instantly
2. add rows and
3. delete rows

The whole code and the steps required to get it working are explained in the Code section.

Enjoy.

Getting Interactive Report Query

I know this is nothing new but I had a hard time to find out how to get the SQL of the currently viewed Interactive Report. In one of my projects I needed the exact query including the filtered values and sorting  in order to save the data into a PL/SQL collection and process it further. I tried using the existing IR Application Views but those do not provide all the information I needed. I stumbled upon this documentation page

http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35127/apex_ir.htm#BABEFDJE

but was confused by the statement for getting the IR Query:

DECLARE
   l_report  apex_ir.t_report;
   l_query   varchar2(32767);
BEGIN     
    l_report := APEX_IR.GET_REPORT (
                    p_page_id   => 1,
                    p_region_id => 2505704029884282,
                    p_report_id => 880629800374638220);
    l_query := l_report.sql_query;
    for i in 1..l_report.binds.count
    loop
        dbms_output.put_line(i||'. '||
                             l_report.binds(i).name||
                             '='||l_report.binds(i).value);
    end loop;
END; 

If you run this statement, you will receive a concatenated string of binds used in for the filtering and the corresponding values and not the actual query (it is just not printed out). In addition to that, you need to combine this statement with the one for getting the last viewed report id:

DECLARE
    l_report_id number;
BEGIN     
    l_report_id := APEX_IR.GET_LAST_VIEWED_REPORT_ID (
        p_page_id   => 1,
        p_region_id => 2505704029884282);
END;

After talking to Patrick Wolf I realized that this statement delivers almost everything you need in order to get the complete query. I combined the two statements and created a function which you can use to get a query for any of your interactive reports including replaced binds. The function code is:

CREATE OR REPLACE FUNCTION get_report_sql (
   p_app_id     IN   NUMBER,
   p_page_id    IN   NUMBER,
   p_all_cols   IN   BOOLEAN DEFAULT TRUE
)
   RETURN VARCHAR2
IS
   v_report_id   NUMBER;
   v_region_id   NUMBER;
   v_report      apex_ir.t_report;
   v_query       VARCHAR2 (32767);
   v_column      VARCHAR2 (4000);
   v_position    NUMBER;
BEGIN
   SELECT region_id
     INTO v_region_id
     FROM apex_application_page_regions
    WHERE application_id = p_app_id
      AND page_id = p_page_id
      AND source_type = 'Interactive Report';

   v_report_id :=
      apex_ir.get_last_viewed_report_id (p_page_id        => p_page_id,
                                         p_region_id      => v_region_id
                                        );
   v_report :=
      apex_ir.get_report (p_page_id        => p_page_id,
                          p_region_id      => v_region_id,
                          p_report_id      => v_report_id
                         );
   v_query := v_report.sql_query;

   FOR i IN 1 .. v_report.binds.COUNT
   LOOP
      v_query :=
         REPLACE (v_query,
                  ':' || v_report.binds (i).NAME,
                  '''' || v_report.binds (i).VALUE || ''''
                 );
   END LOOP;

   IF p_all_cols
   THEN
      FOR c IN (SELECT   *
                    FROM apex_application_page_ir_col
                   WHERE application_id = p_app_id AND page_id = p_page_id
                ORDER BY display_order)
      LOOP
         v_column := v_column || ', ' || c.column_alias;
      END LOOP;

      v_column := LTRIM (v_column, ', ');
      v_position := INSTR (v_query, '(');
      v_query := SUBSTR (v_query, v_position);
      v_query := 'SELECT ' || v_column || ' FROM ' || v_query;
   END IF;

   RETURN v_query;
EXCEPTION
   WHEN OTHERS
   THEN
      v_query := SQLERRM;
      RETURN v_query;
END get_report_sql;

You can call this function in your application or in a PL/SQL package run from an application session like this:

DECLARE
   v_sql   VARCHAR2 (4000);
BEGIN
   v_sql := get_report_sql (:app_id, :app_page_id, FALSE);
   HTP.prn (v_sql);
END;

Setting the parameter

p_all_cols 

to TRUE would export all columns used in the IR SQL.

Enjoy.

Moving Elements in a Cascading Shuttle

Since APEX 4.0 cascading feature is a part of the standard. Not only a "normal" select list can be cascading but also a shuttle element. In this example I am showing how to filter a shuttle box and move the filtered values to the right side using just one click (change of the first select list).

CLOB Plugin

There is a new plugin available which makes it possible to save and render contents larger than 32k. This limitation was one of the most critical in APEX. I remember several projects where we had to create workarrounds to solve that issue. Now, there is no need to write your own code any more. Dan McGhan created a new plugin for that and you can find it here:

CLOB Load Plugin

The current help regarding asynchronous submit is missing a detail at this point:

"Next, create one more dynamic action. Set Event to CLOB(s) Submit Complete [Enkitec CLOB Load] and set the action to Execute JavaScript Code. Set the Code field to something like the following:

doSubmit(v('PX_MY_HIDDEN_ITEM'));"

It should also say that the "Selection Type" of the corresponding Dynamic Action is "DOM Object" and the "DOM Object" is "document".

Also, you can leave the hidden element out and modify the javascript call to:

doSubmit('SAVE');

Try it out. It works great.





APEX Deep Linking, Authentication and Special Characters

It seem that there is a bug in APEX regarding deep linking, authentication and parsing special characters. See this thread. In this example I am showing a simple workarround which could be also applied in other similar situations where you need to transfer information using URL.

jQuery - my first functions

I still remember how hard it was to make something like this working before the APEX version 4.0. You needed to create a function and then attach it to each column in your tabular form. Debugging and making changes is the next issue you had to think about. Now, using jQuery it is just a two three simple steps solution with almost no hardcoding and tricks. Also, the number of code lines you need to write is quite moderate. See this example in my demo application:

https://apex.oracle.com/pls/apex/f?p=31517:279

Select List with Dynamic LOV and ORA-06502: PL/SQL: numeric or value error: character string buffer too small

If you need to create a dynamic SQL for a select list or any other type of a LOV, your query should not exceed 4000 characters. In case it does, you will get the "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" error. That is the internal limitation of the APEX table storing the item attribute information. In case you can't rewrite your SQL to satisfy this limitation, you may use the package I created for that purpose. It is quite simple and it does the following:

1. It will use the apex_collection package to parse the query and crate a collection

2. It will query the collection and use a pipelined function to return a simple LOV (SELECT d, r FROM table)

You can also use the get_long_dynamic_query function in the package to generate your dynamic SQL. The example at apex.oracle.com I provided extends the query with "dummy" conditions in order to make it long enough and prove the concept.

Formating and Exporting a Report Column

Sometimes you need to format a report column and if you need to do that in the report SQL, you will be faced with a problem while exporting the report - the formatted values will be exported 1:1 and you will see those ugly html tags among the exported data. In early days of APEX people recommended using two columns - one to display in a report and hide it while exporting and one hidden column used for exports only instead. You would then use a condition like this:

apex_application.g_excel_format = FALSE

to says not to export or the opposite one:

apex_application.g_excel_format = TRUE

to say to export.This solution may work with a standard report but it can't be used efficiently with Interactive Reports.

Just in case you didn't know, you can solve that problem easily. See this example at apex.oracle.com. There, I am using a function to format a string and use the value of the current request to see if the report is simply getting rendered on the page or exported. If the report is exported, I will not use the formatting tags and return the values as it was parsed.

APEX Collections and Joins

APEX Collections is one of the best features of APEX. There are situations where solutions wouldn't be possible if this feature would not be available. However, the collections have some limitations. One of the limitations is if you need to store number values and use those values to join a collection with other tables. The consequences may be that you receive something like

ORA-01722: invalid number

The strange thing with this error is that it may occur sporadic and you can't realy understand why it happens. If you operate on different systems (development, testing, production) you may receive this error on one of the instances and never on the other one. I asked Patrick Wolf about the reasons and he answered that this has to do with the way the cost based optimizer works. If it uses a different plan from the one you would expect, it may find non numeric values in the same column comming from a different collection. In that case the solution is to use the numeric columns in the collection (n0xx) for storing numeric values used in later joins.

Upgrade to APEX 4.2.1.00.08 - First Impressions

Today one of my customers upgraded their APEX 4.0 to 4.2.1.00.08. The whole process was completed without any issues. After the upgrade there was only one problem we could notice. Our translated applications didn't work for the other languages then the primary language. Trying to run any of the translated applications would result in an internal error. The reason was that the image path wasn't there for any of the tranlations:

After entering the image path and repeating the neccessary translation steps everything worked well.

Reading a file from an URL and storinig it as a BLOB

In this example at apex.oracle.com you can se how you can use an URL (which normaly provides a save/download dialog for saving or opening a document) to store the document directly in your own table as a BLOB. Unfortunatelly the ACL settings at apex.oracle.com do not allow to get this working there and the working example is just a fake, showing only how this should normaly work.

Oracle and APEX

This is the text of an email I received as a response after creating an account for my workspace and my demo application at apex.oracle.com:

"Thank you so much. Oracle and APEX has the most amazing network of help and solutions. It is impressive."

APEX Training 15.04. - 17.04.2013

Wie jedes Jahr in den letzten sechs Jahren, veranstalten wir (Dietmar Aust und ich) unser

Oracle APEX: Knowhow aus der Praxis

 

Training in Bensheim an der Bergstrasse. Wir werden unsere bisherigen Themen überarbeiten und einige neue Themen hinzufügen. So werde ich auch folgende neue Themen in das Programm der Schulung aufnehmen:

- jQuery (Beispiele und Übungen)
- APEX Collections
- Erstellung von komplexen Forms
- APEX und Mehrsprachigkeit

Wir haben dieses Mal einen ganz speziellen Gast zu der Schulung eingeladen - Christian Rokitta aus den Niederlanden. Er ist ein Experte in Sachen Layoutgestalltung und Mobile Applikationen. Die Teilnehmer der Schulung werden sich gemeinsam für eins dieser Themen entscheiden und Christian wird es vortragen.

Unser Highlight sind auf jeden Fall die abendlichen  Q & A Session, in denen die Teilnehmer die Gelegenheit bekommen ihre eigenen Projekte vorzustellen und ihre konkrete Probleme mit uns zu diskutieren.

Die Anmeldung zur Schulung finden Sie hier.


                              

                              

 

Count Substring Occurrences in a String

If you write a lot of PL/SQL code, sooner or later you will be faced with a requirement to count the number of occurrences of a substring in a string. In that case you may use this example to help yourself:

http://apex.oracle.com/pls/apex/f?p=31517:282

It will search not only for a single character withing a string but also for a substring of any length up to 4000 characters.

APEX Authentication Function

Did you know that an authentication function in APEX is not used the way a function actually should be used?  Normally, this function has two input parameters and it will return TRUE or FALSE. However, you will name this function in your authentication schema by typing it into a box, without providing any parameters. I have never looked into that but my assumption is that APEX will probably create a kind of a dynamic function call by providing the parameters using login items (:p101_username, :p101_password) from your login page (101).

Now, it may happen to you that you not just only copy / paste the code in your new application but you decide for some reason to write your authentication function from scratch. There is one important thing you shouldn't forget: You can not name the function input parameters as you like. Otherwise, the function will not work. It will cause an error like this:

This error message is a bit confusing and it may make you busy for a while. At least it does that with me from time to time.