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.