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".
