ApEx - Replacing Binds Procedure

If you need to use the apex_collection.crate_collection_from_query package, you will need to replace your :P_ITEM binds with v('P_ITEM') in your SQL, in order to get it working. Sometimes, it could be a pain. Using the following procedure will do the work for you:

CREATE OR REPLACE PROCEDURE replace_binds (
   p_sql_in    IN       VARCHAR2,
   p_sql_out   OUT      VARCHAR2
)
IS
   v_sql      VARCHAR2 (32767);
   v_names    DBMS_SQL.varchar2_table;
   v_pos      NUMBER;
   v_length   NUMBER;
   v_exit     NUMBER;
BEGIN
   v_sql := p_sql_in;
   v_names := wwv_flow_utilities.get_binds (v_sql);

   FOR i IN 1 .. v_names.COUNT
   LOOP

      <<do_it_again>>
      v_pos := INSTR (LOWER (v_sql), LOWER (v_names (i)));
      v_length := LENGTH (LOWER (v_names (i)));
      v_sql :=
            SUBSTR (v_sql, 1, v_pos - 1)
         || v_names (i)
         || SUBSTR (v_sql, v_pos + v_length);
      v_sql :=
         REPLACE (v_sql,
                  UPPER (v_names (i)),
                  'v(''' || LTRIM (v_names (i), ':') || ''')'
                 );

      IF INSTR (LOWER (v_sql), LOWER (v_names (i))) > 0
      THEN
         GOTO do_it_again;
      END IF;
   END LOOP;

   p_sql_out := v_sql;
END replace_binds;

And you no longer need to worry about modifying your code.

Create Virtual Tables

This is one of frequently asked questions:

"I have only a few lines of data - mostly some dates with a duration from > to - and would like to use this as table to select from and populate a calendar. This calendar is supposed to show one entry for each day within a time period. How do I do that? Do I need to create a separate table?"

The answer is "No, you don't.". Using CONNECT BY LEVEL, you can create virtual tables and use those in your select statements. For example: there was a question in this post, how to show all Fridays within a certain period of time. Depending on your NLS settings you would do something like this to create a virtual table, showing all Fridays from the beginning of the year to the current day:

SELECT each_day "friday"
  FROM (SELECT       (  TRUNC (SYSDATE)
                      - TO_NUMBER (TO_CHAR (TO_DATE (SYSDATE), 'ddd'))
                     )
                   + LEVEL each_day
              FROM DUAL
        CONNECT BY LEVEL <= TO_NUMBER (TO_CHAR (TO_DATE (SYSDATE), 'ddd')))
 WHERE TO_CHAR (each_day, 'D') = '5'

Depending on your NLS settings, a Friday could be the fifth or the sixth day of the week.

In my demo application, you will find an interesting example on how to populate a calender for three events (three records) stored in a table, getting for each day within a given period of time, one entry in a calendar:

http://htmldb.oracle.com/pls/otn/f?p=31517:83

Almost too trivial for a post

I have been using SQL for years and from time to time I stumble upon a problem, where I needed to count the occurrence of a character or a string within another string. Searching for a standard function in Oracle gives me no results. Browsing in the good old SQL forum gave me an idea on how to create my own function:

CREATE OR REPLACE FUNCTION string_occurrence (
   p_string      IN   VARCHAR2,
   p_substring   IN   VARCHAR2
)
   RETURN NUMBER
IS
   v_occurrence   NUMBER;
BEGIN
   v_occurrence :=
        (  LENGTH (p_string)
         - NVL (LENGTH (REPLACE (p_string, 
                                 p_substring, '')), 0)
        )
      / LENGTH (p_substring);
   RETURN v_occurrence;
END string_occurrence;

The simplicity of the idea is fascinating:

1. since you can't count it directly, you count the total string,

2. after that you replace the string you search for with '' (nothing) and count again,

3. you subtract the second value from the first and

4. you divide the result by the length of the string you want to count the occurrence for.

And there we go.

Ajax - Query by Example

This is one of the frequently asked questions in the ApEx forum. I created an example on how to build a form which will work similar to the Forms QBE here.

The solution could be more generic. I have the idea to store the result of a query in a collection and access that collection instead of repeating the query for each click on the "Previous" and "Next" button. However, I encountered a problem creating a collection from an On Demand process. I posted this problem here and I'm waiting for an answer. If there is one, I could get rid of some lines of code in the original proposal.

Export to Excel - Unwrapped

The packages for Export to Excel for ApEx and XE are now unwrapped and available for download:

http://htmldb.oracle.com/pls/otn/f?p=31517:108

http://htmldb.oracle.com/pls/otn/f?p=31517:109







You may use them and modify as you need. It has been downloaded arround 300 times in the last two months. Shows that it works and it is needed, despite of the new BI publisher feature available for ApEx 3.0.

The method is quite simple. You need to generate HTML tables and it will be readable as a normal .xls file from 97 to the latest version. This gives an option to create multiple pages in excel and export multiple reports in one run as one file. Of course, it needs some modification within the package to do that.

Have fun.