Generic RTF Documents - Part 5 - The End

As promised, I’ve added some validation (more could be added, but there always could, and this blog is more geared to enable the process, than production-ise it). I’ve also revisited an area of the code I wasn’t happy with, and removed the kludge (Actually, I think it was my problem, in the first place with bad code, but at least I admit it).

I have uploaded the example onto apex.com – link – please don’t amend the existing samples, but create your own as you see fit. 

Code can be obtained here – the zip file contains the apex application, schema objects creation and the package spec/body.

So, what extra did I add and why ?.

First, I went back to the screen that I used to record the fields associated with the standard letter. I added some validation to ensure that the only “fields” that began with “FIELD”,”SINGLE” or “TABLE”, and for the first two, had 3 digits – the easiest way of doing this, is via a reg-exp validation to the field_name column as follows.

   CASE SUBSTR(:FIELD_NAME, 1, 1)

      WHEN 'F' THEN

         IF NOT REGEXP_LIKE(:FIELD_NAME, '(^FIELD\d{3}$)') THEN

            RETURN FALSE;

         ELSE

            RETURN TRUE;

         END IF;

      WHEN 'S' THEN

         IF NOT REGEXP_LIKE(:FIELD_NAME, '(^SINGLE\d{3}$)') THEN

            RETURN FALSE;

         ELSE

            RETURN TRUE;

         END IF;

      WHEN 'T' THEN

         IF NOT REGEXP_LIKE(:FIELD_NAME, '(^TABLE*)') THEN

            RETURN FALSE;

         ELSE

            RETURN TRUE;

         END IF;

      ELSE

         RETURN FALSE;

   END CASE;

The reason for insisting on 3 digits for the field, is so that when we come to replace, we replace the correct entry, otherwise, FIELD1 would replace FIELD10 – FIELD19 and all the FIELD1nn entries – obvious now, but not when I started.

Next, I ensured that when I read in the template, any field had to exist in the list defined for the standard letter. This meant adding a section of code in the EXTRACT_VARIABLES procedure, along with raising an error and aborting the template load. The following code was inserted just after extracting the field_name and before inserting the record into the TEMPLATE_SUB_COLUMNS table.

        SELECT NVL(COUNT(S.FIELD_NUMBER), 0)

           INTO L_COUNT

           FROM STANDARD_LETTER_FIELDS S, TEMPLATE T

          WHERE T.TEMPLATE_ID = P_TEMPLATE_ID

            AND T.LETTER_ID = S.LETTER_ID

            AND S.FIELD_NAME = L_FIELD_NAME;

         IF L_COUNT = 0 THEN

            --

            -- The retrieved Field is not listed as being a field available for that report / letter

            --

            RAISE_APPLICATION_ERROR(-20000, 'Field name ' || L_FIELD_NAME || ' has not been defined in the template. ');

         END IF;

And then, again just before the insert, I ensured that the “SUBSTITUTION_TYPE” field is correctly initialised by adding the following code

         CASE SUBSTR(L_FIELD_NAME, 1, 1)

            WHEN 'F' THEN

               L_SUBSTITUTION_TYPE   := 'FIELD';

            WHEN 'S' THEN

               L_SUBSTITUTION_TYPE   := 'SINGLE';

            ELSE

               L_SUBSTITUTION_TYPE   := 'TABLE';

         END CASE;

Due to the fact that the field has already been validated against the list allowed for the standard letter (which in turn validated that only fields of type “FIELD”,”SINGLE” and “TABLE” are entered), I can get away with the “else”.

To get the above code to work however, I had to revisit the UPLOAD_RTF procedure. In the blog (part 1) I mentioned that I had to work around a deficiency and did so by the use of a “kludge”, well, I’m here to eat my own words and say that it was due to my coding. Once the code was corrected, it worked correctly, and the dynamic actions on the button could be removed and all the processing put where it should have been in the first place – that is, in the submit processing. In doing so, raising the error was correctly trapped and any transactional work rolled back.

The next issue I encountered wasn’t with APEX, it was our beloved Microsoft Word.  For some unfathomable reason, when creating layouts with aligned text, the first couple of fields are ok, then it starts to insert all sort of garbage rtf codes at random, often within the “field” name, thus blowing the guts of this whole blog. Needless to say, OpenOffice doesn’t have this problem, and I could continue to blog.

I hope the journey has been of interest and inspired you. There is no doubt a lot more that could be done to extend the RTF functionality, and if anyone does, I would be interested to see what you have done.