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.

Dietmar Aust - Oracle ACE

It is now official - my friend Dietmar Aust is an Oracle ACE. Congratulations. Well deserved.

APEX - Statement of Direction

The new Statement of Direction is touching a couple of interesting things. Here are the three most important:

• New Multi-Row Edit Region Type – Define a new region type with a modern UI for updating multiple rows of data and allow multiple regions on one page

• Master / Detail / Detail – Provide a wizard interface to define declarative master/detail/detail regions.

• Multiple Interactive Reports – Allow any number of Interactive Reports to be defined on a single page.

I think this is quite exciting and I can hardly wait to see it working. Theses are of course only the most important details. We will probably see a lot of other smaller things changing and improving. 

I would like to add a couple of wishes to the list if not too late:

1. a wizard for creating tabular forms based on collections
2. a wizard for creating single record forms managed by a procedure (package)
3. an additional page element feature - update session state on change
4. a new page element - simple checkbox

My list doesn't end here but I need to be realistic. The APEX team is a small group of people working hard and producing already great results. And of course, the APEX story will not end with the version 5.0 :).

Package for Creating Custom Code

Sometimes you will need to create your own processes for fetching values, validating and updating tables in a form. In some situations the wizard generated form will not be good enough to cover all you need. In that case you need to write your own code. Some programs like TOAD have a routine which generates update or delete procedures for you. However, you need to extend that and it is quite a bit of work. In my Demo Application I made the code available, which does almost all the work for you. This package has a couple of functions able to generate the code which requires only some minor changes. If you ever need to go away from the standard APEX form, you can do that this way:

1. Create the package "create_pkg_from_table" in your schema by running the code from the page I mentioned,

2. Create a form using wizard,

3. Remove the automatic processes and change the standard settings in the page items and use the following setting:

a) Set the "Source Used" to "Only when current..."
b) Set the "Source Type" to "Static Assignment..."

4. Create the custom package related to the table you reference in your form, for fetching, validating, updating and deleting records by running this SQL and executing the output:

SELECT create_pkg_from_table.create_package ('EMP', 'EMPNO', 1)
  FROM DUAL;

5. Finally, run the following SQL to create the calls to the package functions and procedures you will need in your page processes and validations:

SELECT create_pkg_from_table.create_plsql_block ('EMP', 'EMPNO', 1)
  FROM DUAL;

The only thing you need to do now is to format that code and paste it in the appropriate process.

Goodbye apexblogs.info

http://www.apexblogs.info was a place I visited daily. A couple of days ago this domain was moved to http://www.odtug.com/apex. I am not sure I understand why but it doesn't matter. The only thing is that from the usability perspective this is now at least two steps back.

The current layout of the page is displaying a couple of logos, advertisements and some general information and the content is not in the focus - 75% of the page is not showing that, what you would actually expect. You need to scroll down to find the content and you need to scroll a lot. There are all together three scrollable regions including the page and if you don't watch you will easily loose the orientation.

Now, the question to ODTUG people would be if this is going to change in the near future. Hopefully there are plans to improve the usability of this popular resource. Otherwise the whole action doesn't make a lot of sense.

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:

CREATE OR REPLACE FUNCTION x_rep (p_string IN VARCHAR2)
   RETURN VARCHAR2
IS
   v_string   VARCHAR2 (4000);
BEGIN
   v_string :=
      REPLACE (TRANSLATE (p_string, 'x' || CHR (10) || CHR (13), 'x'),
               ';',
               '/'
              );
   RETURN v_string;
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN NULL;
END x_rep;
/

My Demo Application

The link to My Demo Application on apex.oracle.com has been changed from

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

to

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

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

APEX 4.2

Today, apex.oracle.com 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 apex.oracle.com 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.

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)
      RETURN VARCHAR2
   IS
      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>';
   BEGIN
      IF p_highlight IS NOT NULL
      THEN
         v_string := p_string;

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

            IF v_position > 0
            THEN
               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;
      ELSE
         v_newstring := p_string;
      END IF;

      RETURN v_newstring;
   END get_string_highlight;

Count Checked Rows in a Tabular Form

This is a quite simple but still frequently asked question. Here is an example on how to count checked rows in a tabular form. You may need this functionality prior to processing. For example, if you want to inform your user that they didn't select any rows after pressing the "Delete" button.

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

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":

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

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:

DECLARE
   vrow   BINARY_INTEGER;
BEGIN
   FOR i IN 1 .. apex_application.g_f01.COUNT
   LOOP
      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);
   END LOOP;
END;

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.

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
(String1:String2:String3).
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
)
   RETURN NUMBER
IS
   l_vc_arr2   apex_application_global.vc_arr2;
   v_count     NUMBER;
BEGIN
   l_vc_arr2 := apex_util.string_to_table (p_checkbox);

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

   IF v_count > 0
   THEN
      RETURN 1;
   ELSE
      RETURN 0;
   END IF;
END compare_checkbox_strings;

Now, you can use it in your SQL Query like this:

SELECT *
  FROM your_table
 WHERE compare_checkbox_strings (:p1_your_checkbox, your_column) = 1;

You can find a working example here:
http://apex.oracle.com/pls/otn/f?p=31517:275

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
   SELECT *
     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
   SELECT *
     FROM dbt_images@remote_db
     WHERE 1 = 2;

2. create two types for storing the information

CREATE OR REPLACE TYPE object_row_type AS OBJECT (
   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

CREATE OR REPLACE FUNCTION get_remote_blob (p_id IN NUMBER)
   RETURN object_table_type PIPELINED
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO dbt_images
      SELECT *
        FROM dbt_images@remote_db
       WHERE ID = p_id;

   COMMIT;

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

   DELETE FROM dbt_images
         WHERE ID = p_id;

   COMMIT;
   RETURN;
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.

APEX 4.0 / 4.1 New Features

Our next training is scheduled for March 2012. You can find the deatils on www.opal-consulting.de.

-------------------------------------------------------------------------------------

Wir (Denes Kubicek und Dietmar Aust) freuen uns, das nächste APEX Training bekannt geben zu können.

Es findet am 26.03.2012 bis zum 27.03.2012 erneut in Bensheim bei Frankfurt statt, im Aleehotel.

Nutzen Sie das Wissen und die Erfahrung von

* Dietmar Aust, erfahrener Oracle Consultant mit Spezialisierung auf Oracle Apex, aktiv in den OTN Foren zu Apex und Oracle XE, mit Präsentationen auf den letzten DOAG Veranstaltungen und
* Denes Kubicek, langjährige Projekterfahrung in den Bereichen Oracle und APEX mit multisite Applikationen, Preisträger des "Oracle APEX Developer of the Year 2008" Awards des Oracle Magazines, ein Oracle ACE Director und sehr bekannt im Oracle APEX OTN Forum für seine Beispielapplikation,

um die Nuntzung von APEX 4.0 und 4.1 New Features aus vielen erfolgreichen APEX Projekten zu lernen.

Neben einer Fülle an Informationen, die wir in vielen erfolgreichen APEX Projekten erarbeitet haben, sind insbesondere die abendlichen Sessions (von 19:00-21:00) als Highlight zu nennen.

Dort stehen wir Ihnen für zusätzliche Hands-On Sessions zur Verfügung, um die Beispiele des Tages durchzuarbeiten oder auch, um konkrete Fragestellungen in Ihren aktuellen Projekten zu diskutieren.

Weitere Details zum Kurs sowie die Anmeldung finden Sie auf www.opal-consulting.de.

Wir freuen uns, dieses Mal Peter Raganitsch als Gastredner bei uns im Kurs zu haben. Er ist in der APEX Community ebenfalls sehr bekannt und hält regelmäßig Vorträge zur Oracle APEX. Er wird das Thema "Mobile APEX: Anwendungen für Smartphones und Tablets entwickeln" behandeln.

Working with Tabs

Creating two level tabs page layout in APEX is still a problem. I just had an application where I created a parent tab set and then a standard tab. Opeining the application would show the parent tab set and after choosing it the whole parent tab set would dissappear from the screen, showing only the newly created standard tab set. If this happens to you, you will need to do one more thing to get it working:

1. edit your standard tab,
2. go to "Parent Tab Set" section and
3. choose the parent tab set (probably you will have only one there)
4. save the changes

Invalid PL/SQL function in the branch conditional processing

If you use PL/SQL functions in your branch conditional processing you should be sure your code works. I just had a case where I added something to my code and I didn't test it. It took me a while to discover that the code doesn't compile. The branch still worked ignoring the condition.

Expert Oracle APEX - New Book

I almost forgot to blog about this. You may have seen this already - there is a new book out there written by several people verry well known in the APEX comunity. I was also asked if I would like to participate and decided to write on Tabular Forms. I think this is a great book and if you are interested in APEX then you should get one. All the funds will be donated to the families of two of our passed away colleagues:

- Carl Backstrom
- Scott Spadafore

The links to the book you can find at Amazon or at Apress.



Enjoy.

SSO with IBM TAM

In my second project involving IBM Tivoli Access Manger SSO I experienced a problem with Interactive Report sorting and utilization of different jQuery techniques. There is a nice description on how to set up APEX to work with IBM TAM SSO available here:

http://www.estherhoppe.de/tom/tipps/ibm-tam/how-to-INTEGRATE-APEX-WITH-IBM-TAM.html

One more thing needs to be added to this document. Probably, your IBM Admin will set it up using junctions. As I said, this will cause problems with Ajax since IBM TAM will add a small javascript to the end of the HTTP response. Something like:

<script>
document.cookie = "IV_JCT=%2Fjunction_name";
</script>

and APEX will have problems with interpreting that response. Json, used for IR sorting will not work at all.

To work around this problem, you will need to talk to the administrators and let them set it up using mapping tables as described in this document:

http://publib.boulder.ibm.com/infocenter/tivihelp/v2r1/index.jsp?topic=/com.ibm.itame2.doc_5.1/am51_webseal_guide91.htm