Saturday, 1 December 2012

Dietmar Aust - Oracle ACE


APEX - Statement of Direction




  1. a wizard for creating tabular forms based on collections
  4. a new page element - simple checkbox

Monday, 5 November 2012

Package for Creating Custom Code



2. Create a form using wizard,




Friday, 26 October 2012




Sunday, 14 October 2012

APEX Reporting on Comment Columns

This function may help you if you need to report on columns with a lot of text. It will get rid of the carriage return and will help not to break your reports imported into Excel:

   v_string   VARCHAR2 (4000);
   v_string :=
      REPLACE (TRANSLATE (p_string, 'x' || CHR (10) || CHR (13), 'x'),
   RETURN v_string;
END x_rep;

Monday, 1 October 2012

My Demo Application

The link to My Demo Application on has been changed from


While trying to open the old link you will receive a funny message:

Sunday, 2 September 2012

APEX 4.2

Today, was updated to 4.2. My first impression was good - I haven't seen the early adopter this time. If you have an account at you can have a look at the new layout of the apex builder. There are a lot of new features to explore in this version. The focus of this version is mobile development. Enjoy.

Sunday, 26 August 2012

Highlight Search Term

APEX can highlight the search string in a report if you enter the substitution string in the column property of your report - Highlight Words. Unfortunatelly it will higlight the first occurence only. If you want to highlight more then only one occurence then you can use this function. The only limitation is the number of characters you can return per column (4000):
   FUNCTION get_string_highlight (p_string IN VARCHAR2, p_highlight IN VARCHAR2)
      v_string           VARCHAR2 (4000);
      v_substring        VARCHAR2 (4000);
      v_trailingstring   VARCHAR2 (4000);
      v_newstring        VARCHAR2 (32000);
      v_position         NUMBER           := 0;
      v_length           NUMBER           := LENGTH (p_highlight);
      v_span_start       VARCHAR2 (400)
                               := '<span style="color:red;font-weight:bold">';
      v_span_end         VARCHAR2 (400)   := '</span>';
      IF p_highlight IS NOT NULL
         v_string := p_string;

         FOR i IN 1 .. 20
            v_position := INSTR (UPPER (v_string), UPPER (p_highlight));

            IF v_position > 0
               v_substring := SUBSTR (v_string, 1, v_position + v_length);
               v_substring :=
                     SUBSTR (v_string, 1, v_position - 1)
                  || v_span_start
                  || SUBSTR (v_string, v_position, v_length)
                  || v_span_end;
               v_string := SUBSTR (v_string, v_position + v_length);
               v_newstring := v_newstring || v_substring;
            END IF;

            EXIT WHEN v_position = 0;
         END LOOP;

         v_newstring := v_newstring || v_string;
         v_newstring := p_string;
      END IF;

      RETURN v_newstring;
   END get_string_highlight;

Sunday, 8 July 2012

Count Checked Rows in a Tabular Form


Thursday, 28 June 2012

Text Area with Character Counter in a Tabular Form

This small example shows how to create a textarea in a tabular form with a character counter, similar to the one for the page item of type "Textarea":

APEX Tabular Form and ORA-01403: no data found

If you get the "ORA-01403: no data found" error after running your code while updating or validating a tabular form, you will need to find out which array is causing it. This error occurs if the referenced array does not exist - apex_application.g_f01..g_f50. For example, if you use a PL/SQL block similar to this:
   FOR i IN 1 .. apex_application.g_f01.COUNT
      vrow := apex_application.g_f01 (i);

      UPDATE dept
         SET dname = apex_application.g_f04 (vrow),
             loc = apex_application.g_f05 (vrow)
       WHERE empno = apex_application.g_f02 (vrow);
The easiest way to debug is to use:

1. Firefox
2. Firebug

Activate the firebug and use the HTML option to go over the elements (columns) in your tabular form. Firebug will show the associated array number and you can use that information to correct you code.

Sunday, 24 June 2012

Comparing Strings

I just received an interesting question regarding report filtering. The problem was the following:

1. There was a checkbox with multiple choices in the form.
2. The data would be saved as a concatenated string of values
3. The problem was in filtering that column since the filter would also be the same checkbox containing multiple values.

The solution for that is quite simple. All you need to do is to create a function which will compare the two strings and return something if it finds a match.
CREATE OR REPLACE FUNCTION compare_checkbox_strings (
   p_checkbox   IN   VARCHAR2,
   p_column     IN   VARCHAR2
   l_vc_arr2   apex_application_global.vc_arr2;
   v_count     NUMBER;
   l_vc_arr2 := apex_util.string_to_table (p_checkbox);

   FOR i IN 1 .. l_vc_arr2.COUNT
      EXIT WHEN v_count > 0;
      v_count := INSTR (':' || p_column || ':', ':' || l_vc_arr2 (i) || ':');

   IF v_count > 0
      RETURN 1;
      RETURN 0;
   END IF;
END compare_checkbox_strings;
Now, you can use it in your SQL Query like this:
  FROM your_table
 WHERE compare_checkbox_strings (:p1_your_checkbox, your_column) = 1;
You can find a working example here:

Monday, 18 June 2012

BLOB over DB Link

Using a DB Link to query BLOB's from a remote database could be a problem. Recently, I had to show images comming from a remote server in an APEX application. Selecting from a remote table would result in an error. Creating a copy of the remote table by issuing the following statement worked without any problems:
INSERT INTO dbt_images
     FROM dbt_images@remote_db
    WHERE ID = p_id;

After searching for a solution I found an interesting way to get it working:

Jiri's Microblog

Basically, all you need to do is to:

1. create a local copy of the table you get the data from as
CREATE TABLE dbt_images AS
     FROM dbt_images@remote_db
     WHERE 1 = 2;

2. create two types for storing the information
   ID        NUMBER,
   NAME      VARCHAR2 (256),
   image     BLOB,
   creator   NUMBER,
   created   DATE
CREATE OR REPLACE TYPE object_table_type AS TABLE OF object_row_type;

3. create a pipelined function to get the required row
   RETURN object_table_type PIPELINED
   INSERT INTO dbt_images
      SELECT *
        FROM dbt_images@remote_db
       WHERE ID = p_id;


   FOR cur IN (SELECT ID, NAME, image, creator, created
                 FROM dbt_images)
      PIPE ROW (object_row_type (cur.ID,

   DELETE FROM dbt_images
         WHERE ID = p_id;

END get_remote_blob;

Now, selecting from the function using the following SQL:
SELECT ID, image, NAME, DBMS_LOB.getlength (image)
  FROM TABLE (getblob (p_id));

will get that BLOB for you.