Friday 25 May 2012

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.


Thursday 24 May 2012

Generic RTF Documents - Part4 TABLEs, FIELDs and BINDs


In the prior blog, we had started to substitute the “SINGLE” fields. We now need to extend it to work for “TABLE” and for “FIELD”. To start with, we will work on the basis that the SQL provided can be fully satisfied without any bind variables.

Extending for “TABLE”. We need to alter the select in the procedure “GEN_RTF” to also cater for “TABLE”.

      CURSOR C_TEMPLATE_SQL IS
         SELECT *
           FROM TEMPLATE_SUB_FIELDS
          WHERE TEMPLATE_ID = P_TEMPLATE_ID
           AND SUBSTITUTION_TYPE IN ('SINGLE','TABLE');

The remainder of the procedure is still ok. In the procedure GET_RTF_SUBST we now need to cater for “TABLE” also. Change the IF statement to make a call to “GEN_RTF_TABLE”

      IF P_SUBSTITUTION_TYPE = 'TABLE' THEN
         L_RET := GEN_RTF_TABLE (P_FIELD_ID);
      ELSIF P_SUBSTITUTION_TYPE = 'SINGLE' THEN                       -- fetch the statement

The code behind this procedure is not complex, but it will occupy a significant space in the blog. Suffice to say, that the code provided by HÃ¥vard Kristiansen in his original blog link is more or less intact, other than changes for the table names and the addition of a where clause to prevent RTF corruption errors if a table column is selected to be not displayed.

First some additional Package variables

   -- rtf table assumptions
   G_TWIPS                          CONSTANT NUMBER := 571.4285714;  -- = 1 centimeter
   G_MAX_TABLE_WIDTH       CONSTANT NUMBER := 15;                 -- in centimeters
   G_INDENT_TABLE               CONSTANT NUMBER := 0;                  -- table indentation in centimeters
   G_TAB_START                   CONSTANT VARCHAR2 (99) := '{\trowd\trgaph100\trleft-100';
   G_TAB_END                       CONSTANT VARCHAR2 (10) := '}';
   G_TAB_ROW                     CONSTANT VARCHAR2 (10) := '\row';
   G_TAB_CELL                      CONSTANT VARCHAR2 (10) := '\cell';
   G_TAB_CELL_DEF               CONSTANT VARCHAR2 (10) := '\cellx';
   G_CELL_BORDER                CONSTANT VARCHAR2 (99)
      :=    '\clbrdrt\brdrs\brdrw15'
         || CHR (10)
         || '\clbrdrl\brdrs\brdrw15'
         || CHR (10)
         || '\clbrdrr\brdrs\brdrw15'
         || CHR (10)
         || '\clbrdrb\brdrs\brdrw15'
         || CHR (10) ;
   G_B                                  CONSTANT VARCHAR2 (99) := '\b';
   G_B0                                CONSTANT VARCHAR2 (99) := '\b0';
   G_CELL_LEFT_ALIGN          CONSTANT VARCHAR2 (99) := '\pard\intbl\ql';
   G_CELL_RIGHT_ALIGN        CONSTANT VARCHAR2 (99) := '\pard\intbl\qr';
   G_CELL_CENTER_ALIGN      CONSTANT VARCHAR2 (99) := '\pard\intbl\qc';

   -- type declarations
   TYPE TYP_TAB_TEMP_SUB_FIELD_COLS IS TABLE OF TEMPLATE_SUB_FIELD_COLUMNS%ROWTYPE
      INDEX BY BINARY_INTEGER;

Now some more Functions / Procedures

   /*
   *     get description of sql query
   */
   FUNCTION GET_DESCRIPTION (P_QUERY IN VARCHAR2)
      RETURN DBMS_SQL.DESC_TAB IS
      L_COUNT                 NUMBER;
      L_DESC_TAB              DBMS_SQL.DESC_TAB;
      L_CUR                   INTEGER;
   BEGIN
      L_CUR := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE (L_CUR, P_QUERY, DBMS_SQL.NATIVE);
      DBMS_SQL.DESCRIBE_COLUMNS (L_CUR, L_COUNT, L_DESC_TAB);
      DBMS_SQL.CLOSE_CURSOR (L_CUR);
      RETURN L_DESC_TAB;
   EXCEPTION
      WHEN OTHERS THEN
         IF DBMS_SQL.IS_OPEN (L_CUR) THEN
            DBMS_SQL.CLOSE_CURSOR (L_CUR);
         END IF;

         RAISE;
   END;

   /*
   *     convert centimeters to twips
   */
   FUNCTION CM_TO_TWIPS (P_CM IN NUMBER)
      RETURN NUMBER IS
   BEGIN
      RETURN ROUND (P_CM * G_TWIPS);
   END;

   /*
   *     generate start of rtf table
   */
   FUNCTION INIT_RTF_TABLE (P_RTF_COLUMNS IN TYP_TAB_TEMP_SUB_FIELD_COLS)
      RETURN VARCHAR2 IS
      L_RET                VARCHAR2 (32767);
      L_X_POS              NUMBER := CM_TO_TWIPS (G_INDENT_TABLE);
   BEGIN
      L_RET := G_TAB_START || CHR (10);

      FOR I IN 1 .. P_RTF_COLUMNS.COUNT LOOP
         L_RET := L_RET || G_CELL_BORDER;
         L_X_POS := CM_TO_TWIPS (P_RTF_COLUMNS (I).WIDTH) + L_X_POS;
         L_RET := L_RET || G_TAB_CELL_DEF || L_X_POS || CHR (10);
      END LOOP;

      RETURN L_RET;
   END;

   /*
   *     generate end of rtf table
   */
   FUNCTION END_RTF_TABLE
      RETURN VARCHAR2 IS
   BEGIN
      RETURN G_TAB_END;
   END;

   /*
   *     generate rtf table cell
   */
   FUNCTION RTF_CELL (P_CELL_VALUE IN VARCHAR2, 
                                  P_ALIGN IN VARCHAR2 DEFAULT 'L', 
                                  P_BOLD IN VARCHAR2 DEFAULT 'N')
      RETURN VARCHAR2 IS
      L_RET                VARCHAR2 (32767);
      L_START              VARCHAR2 (255);
      L_END                VARCHAR2 (255);
   BEGIN
      IF P_ALIGN = 'L' THEN
         L_START := G_CELL_LEFT_ALIGN;
      ELSIF P_ALIGN = 'R' THEN
         L_START := G_CELL_RIGHT_ALIGN;
      ELSIF P_ALIGN = 'C' THEN
         L_START := G_CELL_CENTER_ALIGN;
      END IF;

      IF P_BOLD = 'Y' THEN
         L_START := G_B;
         L_END := G_B0;
      END IF;

      L_RET := L_START || ' ' || P_CELL_VALUE || L_END || G_TAB_CELL || CHR (10);
      RETURN L_RET;
   END;

   /*
   *     generate end of row
   */
   FUNCTION END_RTF_ROW
      RETURN VARCHAR2 IS
   BEGIN
      RETURN G_TAB_ROW || CHR (10);
   END;

   /*
   *     generate rtf table column headings
   */
   FUNCTION RTF_TABLE_HEADING (P_RTF_COLUMNS IN TYP_TAB_TEMP_SUB_FIELD_COLS)
      RETURN VARCHAR2 IS
      L_RET              VARCHAR2 (32767);
   BEGIN
      FOR I IN 1 .. P_RTF_COLUMNS.COUNT LOOP
         IF P_RTF_COLUMNS (I).SHOW_COLUMN = 'Y' THEN
            L_RET := L_RET || RTF_CELL (P_RTF_COLUMNS (I).COLUMN_TITLE, 'L', 'Y');
         END IF;
      END LOOP;

      L_RET := L_RET || END_RTF_ROW;
      RETURN L_RET;
   END;

   /*
   *     generate rtf table
   */
   FUNCTION GEN_RTF_TABLE (P_FIELD_ID TEMPLATE_SUB_FIELDS.FIELD_ID%TYPE)
      RETURN CLOB IS
      L_CUR                                 INTEGER;

      CURSOR C_SUB_FIELD_COLUMNS IS
         SELECT *
           FROM TEMPLATE_SUB_FIELD_COLUMNS
          WHERE FIELD_ID = P_FIELD_ID
            AND SHOW_COLUMN = 'Y'
         ORDER BY SORT_ORDER;

      L_QUERY_STMT                          TEMPLATE_SUB_FIELDS.SQL_STATEMENT%TYPE;
      L_SHOW_TITLE                          TEMPLATE_SUB_FIELDS.SHOW_TABLE_HEADINGS%TYPE;
      L_TAB_TEMPLATE_SUB_FIELD      TYP_TAB_TEMP_SUB_FIELD_COLS;
      L_DEF_COL_TYPE                        VARCHAR2 (4000);
      L_COL_VALUE                             VARCHAR2 (4000);
      L_IGNORE                                   INTEGER;
      L_RET                                        CLOB;
      L_DESC_TAB                              DBMS_SQL.DESC_TAB;
   BEGIN
      SELECT SQL_STATEMENT, SHOW_TABLE_HEADINGS
        INTO L_QUERY_STMT, L_SHOW_TITLE
        FROM TEMPLATE_SUB_FIELDS
       WHERE FIELD_ID = P_FIELD_ID;

      L_CUR := DBMS_SQL.OPEN_CURSOR;

      DBMS_SQL.PARSE (L_CUR, L_QUERY_STMT, DBMS_SQL.NATIVE);

      -- define columns
      OPEN C_SUB_FIELD_COLUMNS;

      FETCH C_SUB_FIELD_COLUMNS
        BULK COLLECT INTO L_TAB_TEMPLATE_SUB_FIELD;

      CLOSE C_SUB_FIELD_COLUMNS;

      FOR I IN 1 .. L_TAB_TEMPLATE_SUB_FIELD.COUNT LOOP
         IF L_TAB_TEMPLATE_SUB_FIELD (I).SHOW_COLUMN = 'Y' THEN
            DBMS_SQL.DEFINE_COLUMN (L_CUR,
                                    I,
                                    L_DEF_COL_TYPE,
                                    4000);
         END IF;
      END LOOP;

      -- execute query and fetch rows
      L_IGNORE := DBMS_SQL.EXECUTE (L_CUR);
      -- rtf table init
      L_RET := L_RET || INIT_RTF_TABLE (L_TAB_TEMPLATE_SUB_FIELD);

      IF L_SHOW_TITLE = 'Y' THEN
         L_RET := L_RET || RTF_TABLE_HEADING (L_TAB_TEMPLATE_SUB_FIELD);
      END IF;

      LOOP
         IF DBMS_SQL.FETCH_ROWS (L_CUR) > 0 THEN
            -- loop for fetching each column
            FOR I IN 1 .. L_TAB_TEMPLATE_SUB_FIELD.COUNT LOOP
               -- check if column should be displayed
               IF L_TAB_TEMPLATE_SUB_FIELD (I).SHOW_COLUMN = 'Y' THEN
                  -- get column value
                  DBMS_SQL.COLUMN_VALUE (L_CUR, I, L_COL_VALUE);
                  -- wrap column in rtf code
                  L_RET := L_RET || RTF_CELL (L_COL_VALUE, L_TAB_TEMPLATE_SUB_FIELD (I).TEXT_ALIGN);
               END IF;
            END LOOP;

            L_RET := L_RET || END_RTF_ROW;
         ELSE
            EXIT;                                                                                                           --exit loop
         END IF;
      END LOOP;

      L_RET := L_RET || END_RTF_TABLE;
      DBMS_SQL.CLOSE_CURSOR (L_CUR);
      RETURN L_RET;
   EXCEPTION
      WHEN OTHERS THEN
         IF DBMS_SQL.IS_OPEN (L_CUR) THEN
            DBMS_SQL.CLOSE_CURSOR (L_CUR);
         END IF;

         RAISE;
   END;

END;

We now have the mechanism to create the table from the SQL.

The outstanding requirements are :-
Process “FIELD” variables, and cater for BIND variables.

FIELD
To recap what a “FIELD” variable is – A field variable will be a piece of information that is available for that particular report. It *may* be based on data from the database, however, rather than fire SQL to retrieve the value, the value will be within a package variable when the report is invoked. (In my application, I need to produce different letters through the workflow progress, at each stage, more or less data is available, such as Customer_name, address, rental_start_date, number_of_days etc. These values will be initialised just prior to the report being called, in a similar manner to the first step in our dynamic action on the button, but via a database procedure.

NOTE: We need to ensure that the fields are all defined with the name “FIELD” and followed with a number, e.g. FIELD1, FIELD99

I defined a Package array to hold the values for Fields (1..n) which are populated with values applicable to the report/letter being called. (Initialised as part of the package body)

   FIELD_PARAMETER_ARRAY              DBMS_SQL.VARCHAR2_TABLE;

To indicate which fields each report/letter can make use of, I’ve extended the data model as below

Now, for each report/letter, we define what the fields are called (FIELD1..FIELD99) and what the field relates to. We will later be able to extend the functionality to ensure that only “FIELD”s specified actually exist in the template, but I’ll leave that functionality for later, suffice to say, that we *could* validate now.

To inject the substitution for the FIELDs, we need to add a call to a procedure to replace the fields in the GEN_RTF procedure. Add the following line BEFORE we process the SINGLE and TABLE entries.

      L_RTF := SUBSTITUTE_FIELD_COLUMNS (P_TEMPLATE_ID, L_RTF);

And create the new procedure

FUNCTION SUBSTITUTE_FIELD_COLUMNS (P_TEMPLATE_ID IN TEMPLATE.TEMPLATE_ID%TYPE, P_RTF IN TEMPLATE.RTF%TYPE)
      RETURN CLOB IS
     
      CURSOR C_LETTER (P_LETTER_ID IN STANDARD_LETTER.LETTER_ID%TYPE) IS
         SELECT FIELD_NAME
           FROM STANDARD_LETTER_FIELDS
          WHERE LETTER_ID = P_LETTER_ID;

      L_LETTER_ID      STANDARD_LETTER.LETTER_ID%TYPE;
      I                        NUMBER := 0;
      L_RTF                 TEMPLATE.RTF%TYPE;
   BEGIN
      L_RTF := P_RTF;

      SELECT LETTER_ID
        INTO L_LETTER_ID
        FROM TEMPLATE
       WHERE TEMPLATE_ID = P_TEMPLATE_ID;

      FOR EACH_RECORD IN C_LETTER (L_LETTER_ID) LOOP
         I := SUBSTR (EACH_RECORD.FIELD_NAME, 6);
         --
         -- need to replace the template field identified by each_record.field_name with the text
         -- held in field_parameter_array(i)
         -- due to looking at the standard_letter_definition, we will only process fields associated
         -- with that letter
         --
         L_RTF := REPLACE (L_RTF, G_SUBST_RTF_START || EACH_RECORD.FIELD_NAME || G_SUBST_RTF_END, FIELD_PARAMETER_ARRAY (I));
      END LOOP;

      RETURN L_RTF;
   END SUBSTITUTE_FIELD_COLUMNS;


BINDS
HÃ¥vard Kristiansen’s example worked for simple sql, however, it didn’t cater for a selection based bind variables, so I have added this capability. Based on the same mechanism as FIELDs, I have defined a global array to hold the bind variables. (Initialised as part of the package body)

   BIND_PARAMETER_ARRAY              DBMS_SQL.VARCHAR2_TABLE;

And in the same manner as populating the FIELD array, the bind variables will be initialised. Whilst this is not as “neat” as I would have liked, it is still practical, as, when we select the letter/report we are going to print, we can infer the standard letter type, and therefore which “initialisation” needs to occur.

In the GEN_RTF_TABLE procedure, we need to replace any bind variables with the correct text. Add the following code before the DBMS_SQL.PARSE command.

      IF INSTR (L_QUERY_STMT, ':BIND') > 0 THEN
         L_QUERY_STMT := REPLACE_BIND_VARIABLES (L_QUERY_STMT);
      END IF;

NOTE: We need to ensure that the binds are all defined with the name “:BIND” and followed with a number, e.g. :BIND1, :BIND9

The function REPLACE_BIND_VARIABLES is shown below.
   FUNCTION REPLACE_BIND_VARIABLES (P_QUERY_STMT IN TEMPLATE_SUB_FIELDS.SQL_STATEMENT%TYPE)
      RETURN TEMPLATE_SUB_FIELDS.SQL_STATEMENT%TYPE IS
      L_STMT                        TEMPLATE_SUB_FIELDS.SQL_STATEMENT%TYPE;
      I                             NUMBER;
      L_OFFSET                      NUMBER;
      L_SEARCH_STRING               VARCHAR2 (6);     -- will be a maximum of 9 BIND variables
      L_REPLACE_STRING              VARCHAR2 (6);                    
   BEGIN
      L_STMT := P_QUERY_STMT;
      L_OFFSET := INSTR (L_STMT, ':BIND');

      WHILE L_OFFSET > 0 LOOP
         L_SEARCH_STRING := SUBSTR (L_STMT, L_OFFSET, 6);
         I := SUBSTR (L_SEARCH_STRING, 6, 1);
         L_REPLACE_STRING := BIND_PARAMETER_ARRAY (I);
         L_STMT := REPLACE (L_STMT, L_SEARCH_STRING, L_REPLACE_STRING);
         L_OFFSET := INSTR (L_STMT, ':BIND');
      END LOOP;

      RETURN L_STMT;
   END REPLACE_BIND_VARIABLES;

Finally, we have the bare backbones of the package. Yes, we still need to build in error trapping and validation that the letter / report only contains FIELDs that have been defined, but for now, we’ve a working model.

To prove the replacement of FIELDs and BINDs, I created a simple procedure called PROOF_OF_CONCEPT that is called prior to requesting the report.

   PROCEDURE PROOF_OF_CONCEPT IS
   BEGIN
      FIELD_PARAMETER_ARRAY (1) := 'Fred';
      FIELD_PARAMETER_ARRAY (2) := 'Bloggs';

      BIND_PARAMETER_ARRAY (1) := '2';
   END PROOF_OF_CONCEPT;

In the next blog, i'll revisit the code, add some error validation and try and remove any "kludges".