1 TYPE BODY "JTF_DIAG_TABLE" AS
2 MEMBER FUNCTION CONSTRUCT_NODE RETURN VARCHAR2 IS
3 STR varchar2(20000);
4 BEGIN
5 STR := '<node>';
6 STR := STR || ' <heading>' || SELF.HEADING || '</heading>';
7 IF COL_HEADERS IS NOT NULL THEN
8 STR := STR || ' <row>';
9 for x in COL_HEADERS.FIRST .. COL_HEADERS.LAST
10 LOOP
11 STR := STR || ' <col>';
12 STR := STR || DBMS_XMLGEN.CONVERT(COL_HEADERS(x) ,0);
13 STR := STR || ' </col>';
14 END LOOP;
15 STR := STR || ' </row>';
16 END IF;
17 STR := STR || ' <noOfRows>' || SELF.NO_OF_ROWS || '</noOfRows>';
18 IF SELF.FOOTER IS NOT NULL THEN
19 STR := STR || ' <footer>' || SELF.FOOTER || '</footer>';
20 ELSE
21 STR := STR || ' <footer></footer>';
22 END IF;
23 STR := STR || ' </node>';
24 RETURN STR;
25 END CONSTRUCT_NODE;
26 MEMBER PROCEDURE INSERT_ROW_INTO_DB(row1 IN OUT NOCOPY JTF_DIAG_ROW) IS
27 STR varchar2(20000);
28 BEGIN
29 STR := row1.CONSTRUCT_NODE;
30 INSERT INTO JTF_DIAGNOSTIC_REPORT
31 (Execution_ID,UI_Node_ID,type,xmldata,parent_node_id)
32 VALUES(SELF.EXEC_ID
33 ,ROW1.UI_Node_ID
34 ,'row'
35 ,xmltype(STR)
36 ,SELF.UI_NODE_ID);
37 row1.SET_ADDED_TO_PARENT;
38 SELF.NO_OF_ROWS := NO_OF_ROWS + 1;
39 END INSERT_ROW_INTO_DB;
40 MEMBER PROCEDURE ADD_ROW(row1 IN OUT NOCOPY JTF_DIAG_ROW) IS
41 BEGIN
42 IF QUERY_BASED = 0 THEN
43 IF ADDED_TO_PARENT = 0 THEN
44 IF row1 IS NOT NULL THEN
45 IF row1.ADDED_TO_PARENT <> 1 THEN
46 IF row1.GET_NO_OF_COLS = SELF.NO_OF_COLS THEN
47 INSERT_ROW_INTO_DB(row1);
48 ELSE
49 RAISE_APPLICATION_ERROR(-20100,'The number of columns/headers is
50 inconsistent in table' || SELF.HEADING);
51 END IF;
52 ELSE
53 RAISE_APPLICATION_ERROR(-20100,'The Row component cannot be added to the
54 table again');
55 END IF;
56 ELSE
57 RAISE_APPLICATION_ERROR(-20100,'Rows are empty in table' || SELF.HEADING);
58 END IF;
59 ELSE
60 RAISE_APPLICATION_ERROR(-20100, 'table has already been added to the
61 report');
62 END IF;
63 ELSE
64 RAISE_APPLICATION_ERROR(-20100, 'The method used is not consistent with the
65 way in which the Table has been created');
66 END IF;
67 END ADD_ROW;
68 MEMBER PROCEDURE CONSTRUCT_TABLE_FROM_QUERY IS
69 cursor_id number;
70 columns_describe dbms_sql.desc_tab;
71 column_count number;
72 column_name varchar2(1000);
73 loop_counter binary_integer default 1;
74 v_dummy integer;
75 hold_string varchar2(32767);
76 hold_length varchar2(40);
77 row_counter integer;
78 row1 JTF_DIAG_ROW;
79 row_node_id number;
80 column_headers JTF_VARCHAR2_TABLE_4000;
81 T_VARCHAR2 constant integer := 1;
82 T_NUMBER constant integer := 2;
83 T_LONG constant integer := 8;
84 T_ROWID constant integer := 11;
85 T_DATE constant integer := 12;
86 T_RAW constant integer := 23;
87 T_CHAR constant integer := 96;
88 T_TYPE constant integer := 109;
89 T_CLOB constant integer := 112;
90 T_BLOB constant integer := 113;
91 T_BFILE constant integer := 114;
92 BEGIN
93 column_headers := JTF_VARCHAR2_TABLE_4000();
94 cursor_id := DBMS_SQL.OPEN_CURSOR;
95 DBMS_SQL.PARSE(cursor_id, QUERY, DBMS_SQL.V7);
96 DBMS_SQL.DESCRIBE_COLUMNS(cursor_id, column_count, columns_describe);
97 for loop_counter in 1..column_count loop
98 if columns_describe(loop_counter).col_type = T_LONG then
99 hold_length := 25000;
100 else
101 hold_length :=
102 to_number(columns_describe(loop_counter).col_max_len);
103 end if;
104 --Allow only column types that can be displayed in the table
105 if columns_describe(loop_counter).col_type in (T_DATE, T_VARCHAR2,
106 T_NUMBER, T_CHAR, T_ROWID) then
107 DBMS_SQL.DEFINE_COLUMN(cursor_id, loop_counter,
108 hold_string, greatest(TO_NUMBER(hold_length),30));
109 else
110 column_name := columns_describe(loop_counter).col_name;
111 RAISE_APPLICATION_ERROR(-20100, 'The '|| column_name ||' column''s
112 type is not supported for display in the
113
114 table');
115 end if;
116 end loop;
117 -- Execute the query and fetch the records
118 v_dummy := DBMS_SQL.EXECUTE(cursor_id);
119 row_counter := 0;
120 loop
121 if DBMS_SQL.FETCH_ROWS(cursor_id) = 0 then
122 NO_OF_ROWS := row_counter;
123 exit;
124 else
125 row_counter := row_counter + 1;
126 end if;
127 select JTF_DIAGNOSTIC_REPORT_S.nextval into row_node_id from dual;
128 row1 := JTF_DIAG_ROW(EXEC_ID, row_node_id,NULL,0);
129 for loop_counter in 1..column_count
130 loop
131 DBMS_SQL.COLUMN_VALUE(cursor_id,loop_counter,hold_string);
132 hold_string := nvl(hold_string,' ');
133 row1.ADD_COLUMN(hold_string);
134 end loop;
135 INSERT_ROW_INTO_DB(row1);
136 end loop;
137 DBMS_SQL.CLOSE_CURSOR(cursor_id);
138 END CONSTRUCT_TABLE_FROM_QUERY;
139 MEMBER PROCEDURE SET_ADDED_TO_PARENT IS
140 BEGIN
141 SELF.ADDED_TO_PARENT := 1;
142 END SET_ADDED_TO_PARENT;
143
144 MEMBER PROCEDURE SET_FOOTER(FOOTER VARCHAR2) IS
145 BEGIN
146 IF ADDED_TO_PARENT = 0 THEN
147 IF FOOTER is not null then
148 SELF.FOOTER := DBMS_XMLGEN.CONVERT(FOOTER,0);
149 END IF;
150 ELSE
151 RAISE_APPLICATION_ERROR(-20100, 'Table component has already been added to
152 the report');
153 END IF;
154 END SET_FOOTER;
155 END;
[Home] [Help]