Generic RTF Documents - Part 3 - Extract the RTF Document

Part 3.

In the last blog, we had put the process in place the sql associated with the fields we had imported. In this blog, we will now progress on to extracting the RTF with the fields replaced with data. I will do this in a step wise manner – first, we need to be able to extract the template in it’s “raw” state (we may want to change the default template). Once that is working, we’ll extend the process to start the substitutions.

Pre-cursor – We need to create a couple of application items to hold the parameters for the download process. I will call these F104_PARAM1 and F104_PARAM2 (Template_id and “Just Template” respectfully).

On Page 3, add a button “Download Template”. This button should be defined as dynamic with two actions.

1.       “Execute PL/SQL Code” - set the application items F104_PARAM1 to the template_id shown on the page, the second, F104_PARAM2 should be set to ‘Y’ – just get the template.

2.       “Execute JavaScript Code” – we want the RTF document to open in a popup window, not replace the current window. The popup page will be defined as page 13, so the Javascript is “javascript:window.open('f?p=&APP_ID.:13:&SESSION.::&DEBUG.:::'); “

Page 13 - Create as a HTML page, with a HTML region, and create a process that fires “on load before header”, with the following “process”.

begin

    reporting_rtf.get_rtf(:F104_PARAM1,:F104_PARAM2);

end;

We can now see, that when we request that the Template be downloaded (Download Template), 2 parameters get set, and then page 13 is popped up. Page 13 then calls the procedure “reporting_rtf.get_ref”, passing the two parameters. This procedure therefore must “somehow” extract the RFT and then invoke the display on the browser. Lets start to look at the code to get a better understanding of what is actually taking place. (Bear in mind, that we are initially looking at just the template, not the substitution of fields). The code it’self is quite simple – get the RTF, convert to BLOB, set the mime header and push the BLOB to the browser.

   PROCEDURE GET_RTF (P_TEMPLATE_ID      IN TEMPLATE.TEMPLATE_ID%TYPE,

    P_ONLY_TEMPLATE IN VARCHAR2 DEFAULT 'N') IS

      L_MIME                     VARCHAR2 (255);

      L_LENGTH                 NUMBER;

      L_FILE_NAME             VARCHAR2 (2000);

      L_LOB_LOC                BLOB;

      L_RTF                       CLOB;

      L_NAME                     TEMPLATE.TEMPLATE_NAME%TYPE;

      -- conversion variables

      L_SRC_OFFSET              NUMBER := 1;

      L_DEST_OFFSET            NUMBER := 1;

      L_LANG_CTX                 NUMBER := 0;

      L_WARNING                  VARCHAR2 (32767);

   BEGIN

      -- get name

      SELECT FILENAME, RTF

        INTO L_NAME, L_RTF

        FROM TEMPLATE

       WHERE TEMPLATE_ID = P_TEMPLATE_ID;

      -- convert to lob

      DBMS_LOB.CREATETEMPORARY (L_LOB_LOC, FALSE);

      DBMS_LOB.CONVERTTOBLOB (L_LOB_LOC,

                              L_RTF,

                              DBMS_LOB.GETLENGTH (L_RTF),

                              L_DEST_OFFSET,

                              L_SRC_OFFSET,

                              0,

                              L_LANG_CTX,

                              L_WARNING);

      L_LENGTH := DBMS_LOB.GETLENGTH (L_LOB_LOC);

      -- http header

      OWA_UTIL.MIME_HEADER ('application/rtf', FALSE);

      HTP.P ('Content-length: ' || L_LENGTH);

      IF SUBSTR (L_NAME, LENGTH (L_NAME) - 3) <> '.rtf' THEN

         -- Ensure that the filename ends with .rtf

         L_NAME := L_NAME || '.rtf';

      END IF;

      HTP.P ('Content-Disposition: attachment; filename="' || L_NAME);

      OWA_UTIL.HTTP_HEADER_CLOSE;

      -- download blob

      WPG_DOCLOAD.DOWNLOAD_FILE (L_LOB_LOC);

   END GET_RTF;

We now have the mechanism to get the RTF back out from the database, so the next step is to introduce the option to substitute the fields. Add another button to page 3, this time with the text “Download RTF”. Again, the button is dynamic, and has the same two steps, however, in this instance; we need to set F104_PARAM2 to the value ‘N’ (not just template).

We now need to extend the “REPORTING_RTF.GET_RTF” procedure to substitute the fields if required. This is done by adding a call to a procedure to generate the RTF if required. The following commands should be added after the initial select.

      -- generate document

      IF P_ONLY_TEMPLATE = 'N' THEN

         L_RTF := REPORTING_RTF.GEN_RTF (P_TEMPLATE_ID, L_RTF);

      END IF;

Again, working in a step wise manner (makes it easier to both debug and explain), we will first handle the replacement of “SINGLE” fields.

   FUNCTION GEN_RTF (P_TEMPLATE_ID IN TEMPLATE.TEMPLATE_ID%TYPE,

 P_RTF                IN TEMPLATE.RTF%TYPE)

      RETURN CLOB IS

      CURSOR C_TEMPLATE_SQL IS

         SELECT *

           FROM TEMPLATE_SUB_FIELDS

          WHERE TEMPLATE_ID = P_TEMPLATE_ID

   AND SUBSTITUTION_TYPE = ‘SINGLE’;     -- just for initial test

      L_RTF                              TEMPLATE.RTF%TYPE;

      TYPE L_TYP_TAB_TEMP_SUB_FIELD IS TABLE OF TEMPLATE_SUB_FIELDS%ROWTYPE

         INDEX BY BINARY_INTEGER;

      L_TAB_TEMP_SUB_FIELDS              L_TYP_TAB_TEMP_SUB_FIELD;

   BEGIN

      L_RTF := P_RTF;

      -- fetch then substitution variables

      OPEN C_TEMPLATE_SQL;

      FETCH C_TEMPLATE_SQL

        BULK COLLECT INTO L_TAB_TEMP_SUB_FIELDS;

      CLOSE C_TEMPLATE_SQL;

      -- replace substitution variables with actual values

      FOR I IN L_TAB_TEMP_SUB_FIELDS.FIRST .. L_TAB_TEMP_SUB_FIELDS.LAST LOOP

         L_RTF :=

            REPLACE (

               L_RTF,

               L_TAB_TEMP_SUB_FIELDS (I).FIELD_NAME,

               GET_RTF_SUBST (L_TAB_TEMP_SUB_FIELDS (I).FIELD_NAME,

                              L_TAB_TEMP_SUB_FIELDS (I).SQL_STATEMENT,

                              L_TAB_TEMP_SUB_FIELDS (I).SUBSTITUTION_TYPE,

                              L_TAB_TEMP_SUB_FIELDS (I).FIELD_ID));

      END LOOP;

      RETURN L_RTF;

   END;

The above code will pick up all of the “SINGLE” substitution field records and then substitute the “field” with the result returned from the procedure “GET_RTF_SUBSTR”

   FUNCTION GET_RTF_SUBST ( P_FIELD_NAME                IN TEMPLATE_SUB_FIELDS.FIELD_NAME%TYPE,

                                                P_SQL_STATEMENT       IN TEMPLATE_SUB_FIELDS.SQL_STATEMENT%TYPE,

                                                P_SUBSTITUTION_TYPE IN TEMPLATE_SUB_FIELDS.SUBSTITUTION_TYPE%TYPE,

                                                P_FIELD_ID                   IN TEMPLATE_SUB_FIELDS.FIELD_ID%TYPE)

      RETURN CLOB IS

      L_SQL              TEMPLATE_SUB_FIELDS.SQL_STATEMENT%TYPE;

      L_RET              CLOB;

   BEGIN

      IF P_SUBSTITUTION_TYPE = 'SINGLE' THEN                         -- fetch the statement

         L_SQL := P_SQL_STATEMENT;

         -- get substitution value

         IF L_SQL IS NOT NULL THEN

            BEGIN

               -- get substitution value

               EXECUTE IMMEDIATE L_SQL INTO L_RET;

            EXCEPTION

               WHEN OTHERS THEN

                  L_RET := SQLCODE || ': ' || SQLERRM;

            END;

         END IF;

      END IF;

      -- return substitution value

      RETURN L_RET;

   END;

 

The above function will take the SQL, execute it, and return the result for substitution.

We now have the process working for “SINGLE” fields, so we need to extend it to work for “TABLES” and for “FIELDS” – that will be the basis of the next blog.