In the
previous posting, I outlined the problem that I was trying to address, and got
to the point of loading the RTF into the database. I then made reference to a
database procedure to perform the extraction of the “variables”. In this post,
I will explain further the steps in this procedure.
PROCEDURE UPLOAD_RTF (P_TEMPLATE_NAME IN TEMPLATE.TEMPLATE_NAME%TYPE,
P_FILE_NAME IN TEMPLATE.FILENAME%TYPE,
P_DESCRIPTION IN TEMPLATE.DESCRIPTION%TYPE,
P_LETTER_ID IN TEMPLATE.LETTER_ID%TYPE) IS
L_CLOB_CONTENT CLOB;
L_BLOB_CONTENT BLOB;
L_W PLS_INTEGER;
L_D_OFFSET PLS_INTEGER := 1;
L_S_OFFSET PLS_INTEGER := 1;
L_LC PLS_INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
L_ID NUMBER;
L_TEMPLATE_ID TEMPLATE.TEMPLATE_ID%TYPE;
I NUMBER := 0;
BEGIN
-- prepare and get blob from the apex view where the filenames match
DBMS_LOB.CREATETEMPORARY
(LOB_LOC
=>
L_CLOB_CONTENT, CACHE => TRUE);
WHILE I = 0 LOOP
BEGIN
SELECT ID,
BLOB_CONTENT, 1
INTO L_ID,
L_BLOB_CONTENT, I
FROM
APEX_APPLICATION_FILES
WHERE
FILENAME =
P_FILE_NAME
ORDER BY
CREATED_BY DESC;
EXCEPTION
WHEN NO_DATA_FOUND THEN
--
-- this is a kludge - the load is done
asynchronously, so need to wait until loaded
-- there MUST be a better way though !
--
dbms_lock.sleep(1);
END;
END LOOP;
-- convert to clob
DBMS_LOB.CONVERTTOCLOB
(DEST_LOB => L_CLOB_CONTENT,
SRC_BLOB =>
L_BLOB_CONTENT,
AMOUNT => DBMS_LOB.LOBMAXSIZE,
DEST_OFFSET => L_D_OFFSET,
SRC_OFFSET =>
L_S_OFFSET,
BLOB_CSID => DBMS_LOB.DEFAULT_CSID,
LANG_CONTEXT => L_LC,
WARNING => L_W);
--
-- we have now loaded the blob and converted it to clob. so create the
record
-- insert
INSERT INTO TEMPLATE (TEMPLATE_NAME,
FILENAME,
DESCRIPTION,
RTF,
LETTER_ID)
VALUES (P_TEMPLATE_NAME,
P_FILE_NAME,
P_DESCRIPTION,
L_CLOB_CONTENT,
P_LETTER_ID)
RETURNING TEMPLATE_ID
INTO L_TEMPLATE_ID;
--
-- tidy up by deleting the entry in the apex table
--
DELETE FROM APEX_APPLICATION_FILES
WHERE FILENAME = P_FILE_NAME;
DBMS_LOB.FREETEMPORARY
(L_CLOB_CONTENT);
--
-- now that we have uploaded the template, we need to extract each of
the variable fields
--
REPORTING_RTF.EXTRACT_VARIABLES (L_TEMPLATE_ID);
COMMIT;
END UPLOAD_RTF;
See previous
post for the reason why I have the “kludge” in the retrieval of the blob.
As stated in
Part 1, we have stipulated the start and end delimiters, and these will be
required in a number of procedures, so we create the definition as part of the
package body
-- handle both literal and rtf encoded values of our substitution variables
which
-- are §¤ and ¤§ respectfully
G_SUBST_RTF_START VARCHAR2 (20) := '\''a7\''a4';
G_SUBST_RTF_END VARCHAR2 (20) := '\''a4\''a7';
To extract
the “variables” from the RTF we have just loaded, the procedure calls “EXTRACT_VARIABLES” passing the template id. The
procedure will loop through the RTF text, finding the variables, inserting an
entry in the TEMPLATE_SUB_FIELDS tables for each entry, and then move on to the
next.
PROCEDURE EXTRACT_VARIABLES (P_TEMPLATE_ID
IN TEMPLATE.TEMPLATE_ID%TYPE) IS
L_RTF CLOB;
L_START INTEGER;
L_END INTEGER;
L_LENGTH_IDENT NUMBER;
PROCEDURE SET_SUBST_VARS IS
L_RTF_START NUMBER;
BEGIN
-- find the start of the first variable
L_RTF_START := INSTR (L_RTF,
G_SUBST_RTF_START);
IF L_RTF_START
> 0 THEN
L_START :=
L_RTF_START;
L_END := INSTR (L_RTF,
G_SUBST_RTF_END);
L_LENGTH_IDENT := (L_END + LENGTH (G_SUBST_RTF_END)) -
L_START;
ELSE -- no more variables
L_START := 0;
L_END := 0;
L_LENGTH_IDENT := 0;
END IF;
END;
BEGIN
-- get the clob
SELECT RTF
INTO L_RTF
FROM TEMPLATE
WHERE TEMPLATE_ID = P_TEMPLATE_ID;
-- gather all the substitution strings
SET_SUBST_VARS;
WHILE L_START > 0
AND L_END > 0 LOOP
INSERT INTO TEMPLATE_SUB_FIELDS (TEMPLATE_ID,
FIELD_NAME,
SHOW_TABLE_HEADINGS)
VALUES (P_TEMPLATE_ID,
TO_CHAR (SUBSTR (L_RTF,
L_START,
L_LENGTH_IDENT)),
'N');
L_RTF := REPLACE (L_RTF,
TO_CHAR (SUBSTR (L_RTF,
L_START,
L_LENGTH_IDENT)),
NULL);
SET_SUBST_VARS;
END LOOP;
END EXTRACT_VARIABLES;
Ok, so now
we have a set of processes and pages that will upload the RTF and extract each
of the variables. At this point, all we know are the variable names – we don’t
know what type of variable they actually are (Field, Single or Table), nor the
method of populating the value (SQL or other). So let’s move on to those pages.
Page 3 -
This page should be based on the table “TEMPLATE” (single row), with the only
editable field being the description. A region below this will contain a list
of all the extracted “Variables” and an edit/select button. On selecting a row
for edit, pass the details to page (4).
NOTE: The
column “Field_Name” will need to be modified for display, as it will contain
the RTF encoded substitution variable definition – this can be achieved by
selecting substr(field_name,9,length(field_name)-16)
Page 4 -
This page is used to display sql used to substitute the variable with (SQL
should only be entered for substitution types “SINGLE” and “TABLE”, as “FIELD”
will be populated via a different mechanism). Again, when displaying the
field_name, the RTF encapsulating text will need to be stripped.
The “Apply Changes” button
performs a submit, and the data in updated in the table. The table has an
on-update trigger that processes the SQL via calling the package procedure
below.
PROCEDURE UPDATE_SQL_COLUMNS (P_FIELD_ID
IN TEMPLATE_SUB_FIELDS.FIELD_ID%TYPE,
P_TEMPLATE_ID IN TEMPLATE_SUB_FIELDS.TEMPLATE_ID%TYPE,
P_SQL_STATEMENT IN TEMPLATE_SUB_FIELDS.SQL_STATEMENT%TYPE) IS
L_DESC_TAB DBMS_SQL.DESC_TAB;
L_DEL_COL VARCHAR2 (32676);
L_COL_TITLE VARCHAR2 (90);
BEGIN
-- describe query
L_DESC_TAB := RTF_TABLE_P.GET_DESCRIPTION (P_SQL_STATEMENT);
-- check if new
IF L_DESC_TAB.COUNT = 0 THEN
RAISE_APPLICATION_ERROR (-20000, 'No colunms to describe');
END IF;
-- ins/upd colunms
FOR I IN 1 .. L_DESC_TAB.COUNT LOOP
IF L_DESC_TAB (I).COL_NAME IS NULL THEN
RAISE_APPLICATION_ERROR (-20000, 'Column has no alias');
END IF;
L_COL_TITLE := INITCAP (REPLACE (L_DESC_TAB
(I).COL_NAME, '_', ' '));
-- check if exist
BEGIN
INSERT INTO TEMPLATE_SUB_FIELD_COLUMNS (TEMPLATE_ID,
FIELD_ID,
COLUMN_NAME,
COLUMN_TITLE,
SORT_ORDER)
VALUES (P_TEMPLATE_ID,
P_FIELD_ID,
L_DESC_TAB (I).COL_NAME,
L_COL_TITLE,
I);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
-- it exists, that's OK, but set the sort order
UPDATE TEMPLATE_SUB_FIELD_COLUMNS
SET SORT_ORDER = I, COLUMN_TITLE = L_COL_TITLE
WHERE
FIELD_ID =
P_FIELD_ID
AND
COLUMN_NAME =
L_DESC_TAB (I).COL_NAME;
END;
L_DEL_COL :=
L_DEL_COL || '''' ||
L_DESC_TAB (I).COL_NAME
|| ''',';
END LOOP;
-- del existing rtf_subs_column not part of new query
L_DEL_COL :=
'delete from TEMPLATE_SUB_FIELD_columns where field_id = '
|| P_FIELD_ID
|| ' and column_name not in ('
|| RTRIM (L_DEL_COL, ',')
|| ')';
EXECUTE IMMEDIATE L_DEL_COL;
END UPDATE_SQL_COLUMNS;
This procedure in turn calls
another procedure (GET_DESCRIPTION) which is defined below.
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;
In the next blog, we'll look at getting the report produced.
No comments:
Post a Comment