1 PACKAGE BODY EPS AS
2 /* $Header: epsuserb.pls 115.2 2002/11/08 19:08:18 dkang ship $ */
3 --
4 -- Get error text
5 --
6 FUNCTION getErrorMsg(status NUMBER)
7 RETURN VARCHAR2 IS
8 msg VARCHAR2(2000) := '' ;
9 BEGIN
10
11 IF xrbGetMessage(status, msg, 2000) = 0 THEN
12 msg := 'Unknown error code or message file missing' ;
13 END IF;
14 RETURN msg ;
15
16 END getErrorMsg ;
17
18 --
19 -- Query express
20 --
21 FUNCTION query (express_server VARCHAR2,
22 qry0 VARCHAR2,
23 qry1 VARCHAR2,
24 qry2 VARCHAR2,
25 qry3 VARCHAR2,
26 qry4 VARCHAR2,
27 qry5 VARCHAR2,
28 qry6 VARCHAR2,
29 qry7 VARCHAR2,
30 qry8 VARCHAR2,
31 qry9 VARCHAR2) RETURN EPS_express_list_t AS
32 report_id NUMBER := 0;
33 t EPS_express_list_t := EPS_express_list_t();
34 i NUMBER := 1;
35 status NUMBER := 0;
36 map VARCHAR2(100) := '';
37 buffer VARCHAR2(4000) := '';
38 nrows NUMBER := 0;
39 ncols NUMBER := 0;
40 BEGIN
41 --
42 -- Generate a unique id for the query
43 --
44 SELECT EPS_report_id.NEXTVAL
45 INTO report_id
46 FROM DUAL;
47
48 --
49 -- Prepare the query
50 --
51 status := XPPrepare(report_id, express_server, qry0) ;
52 IF status <> 0 THEN
53 DECLARE
54 msg VARCHAR2(2000);
55 BEGIN
56 msg := getErrorMsg(status) ;
57 RAISE_APPLICATION_ERROR(-status, msg);
58 END;
59 RETURN t;
60 END IF;
61
62 --
63 -- Fetch from the query
64 --
65 DECLARE
66 buffer_size NUMBER := 4000;
67 buffer_ptr NUMBER := 0;
68 obj EPS_express_t := EPS_express_t(NULL,NULL,NULL,NULL,NULL,
69 NULL,NULL,NULL,NULL,NULL,
70 NULL,NULL,NULL,NULL,NULL,
71 NULL,NULL,NULL,NULL,NULL,
72 NULL,NULL,NULL,NULL,NULL,
73 NULL,NULL,NULL,NULL,NULL,
74 NULL,NULL,NULL,NULL,NULL,
75 NULL,NULL,NULL,NULL,NULL);
76 curr_row NUMBER := 0;
77 BEGIN
78 LOOP
79 --
80 -- Fetch N Rows
81 --
82 status := XPFetchN(report_id, buffer_size, buffer, ncols, nrows);
83
84 --
85 -- Put the rows into the table of objects
86 --
87 IF (status = 1403 OR status = 0) AND
88 nrows > 0
89 THEN
90 --
91 -- Parse each row
92 --
93 curr_row := 1;
94 buffer_ptr := 1;
95 t.EXTEND(nrows);
96
97 LOOP
98 t(i) := ParseRow(buffer,buffer_size,buffer_ptr,ncols,nrows);
99 i := i + 1;
100 curr_row := curr_row + 1;
101
102 IF curr_row > nrows THEN
103 EXIT; -- LOOP
104 END IF;
105 END LOOP;
106 ELSE
107 DECLARE
108 msg VARCHAR2(2000);
109 BEGIN
110 IF status = 1403 THEN
111 EXIT ; -- LOOP
112 END IF ;
113 msg := getErrorMsg(status);
114 RAISE_APPLICATION_ERROR(-status, msg);
115 END;
116 END IF;
117
118 IF status = 1403 THEN
119 EXIT; -- LOOP
120 END IF;
121 END LOOP;
122 EXCEPTION
123 WHEN NO_DATA_FOUND THEN
124 NULL;
125 END;
126
127 --
128 -- Return the results
129 --
130 RETURN t;
131 END;
132
133 --
134 -- Express DLL callout functions
135 --
136 --
137 -- OCI Callout - XPPrepare - Prepare an Express Query
138 --
139 FUNCTION XPPrepare(in_report_id IN NATURAL,
140 in_express_server IN VARCHAR2,
141 in_qry IN VARCHAR2)
142 RETURN NATURAL AS
143 EXTERNAL
144 LIBRARY EPS_lib
145 NAME "XPPrepare"
146 LANGUAGE C
147 WITH CONTEXT;
148
149 --
150 -- OCI Callout - XPFetch - Fetch a row from an Express Query
151 --
152 -- Return 0 SUCCESS else Exception return code if fail
153 --
154 FUNCTION XPFetch(in_report_id NATURAL,
155 c0 IN OUT NOCOPY VARCHAR2,
156 c1 IN OUT NOCOPY VARCHAR2,
157 c2 IN OUT NOCOPY VARCHAR2,
158 c3 IN OUT NOCOPY VARCHAR2,
159 c4 IN OUT NOCOPY VARCHAR2,
160 c5 IN OUT NOCOPY VARCHAR2,
161 c6 IN OUT NOCOPY VARCHAR2,
162 c7 IN OUT NOCOPY VARCHAR2,
163 c8 IN OUT NOCOPY VARCHAR2,
164 c9 IN OUT NOCOPY VARCHAR2,
165 c10 IN OUT NOCOPY VARCHAR2,
166 c11 IN OUT NOCOPY VARCHAR2,
167 c12 IN OUT NOCOPY VARCHAR2,
168 c13 IN OUT NOCOPY VARCHAR2,
169 c14 IN OUT NOCOPY VARCHAR2,
170 c15 IN OUT NOCOPY VARCHAR2,
171 c16 IN OUT NOCOPY VARCHAR2,
172 c17 IN OUT NOCOPY VARCHAR2,
173 c18 IN OUT NOCOPY VARCHAR2,
174 c19 IN OUT NOCOPY VARCHAR2,
175 c20 IN OUT NOCOPY VARCHAR2,
176 c21 IN OUT NOCOPY VARCHAR2,
177 c22 IN OUT NOCOPY VARCHAR2,
178 c23 IN OUT NOCOPY VARCHAR2,
179 c24 IN OUT NOCOPY VARCHAR2,
180 c25 IN OUT NOCOPY VARCHAR2,
181 c26 IN OUT NOCOPY VARCHAR2,
182 c27 IN OUT NOCOPY VARCHAR2,
183 c28 IN OUT NOCOPY VARCHAR2,
184 c29 IN OUT NOCOPY VARCHAR2,
185 c30 IN OUT NOCOPY VARCHAR2,
186 c31 IN OUT NOCOPY VARCHAR2,
187 c32 IN OUT NOCOPY VARCHAR2,
188 c33 IN OUT NOCOPY VARCHAR2,
189 c34 IN OUT NOCOPY VARCHAR2,
190 c35 IN OUT NOCOPY VARCHAR2,
191 c36 IN OUT NOCOPY VARCHAR2,
192 c37 IN OUT NOCOPY VARCHAR2,
193 c38 IN OUT NOCOPY VARCHAR2,
194 c39 IN OUT NOCOPY VARCHAR2)
195 RETURN NATURAL AS
196 EXTERNAL
197 LIBRARY EPS_lib
198 NAME "XPFetch"
199 LANGUAGE C
200 WITH CONTEXT;
201
202 --
203 -- OCI Callout - XPFetchN - Fetch multiple rows from an Express Query
204 --
205 -- Return 0 SUCCESS else Exception return code if fail
206 --
207 FUNCTION XPFetchN(in_report_id NATURAL,
208 buffer_size NATURAL,
209 buffer IN OUT NOCOPY VARCHAR2,
210 num_cols OUT NOCOPY NATURAL,
211 num_rows OUT NOCOPY NATURAL)
212 RETURN NATURAL AS
213 EXTERNAL
214 LIBRARY EPS_lib
215 NAME "XPFetchN"
216 LANGUAGE C
217 WITH CONTEXT;
218
219
220 --
221 -- OCI Callout - xrbGetMessage - Get EPS error message
222 --
223 FUNCTION xrbGetMessage(msgno IN NATURAL,
224 buffer IN OUT NOCOPY VARCHAR2,
225 bufsize IN NATURAL)
226 RETURN NATURAL AS
227 EXTERNAL
228 LIBRARY EPS_lib
229 NAME "xrbGetMessage"
230 LANGUAGE C ;
231
232
233
234 FUNCTION ParseRow(buffer IN OUT NOCOPY VARCHAR2,
235 buffer_size IN NUMBER,
236 buffer_ptr IN OUT NOCOPY NUMBER,
237 ncols IN NUMBER,
238 nrows IN NUMBER)
239 RETURN EPS_express_t IS
240 t EPS_express_t := EPS_express_t(NULL,NULL,NULL,NULL,NULL,
241 NULL,NULL,NULL,NULL,NULL,
242 NULL,NULL,NULL,NULL,NULL,
243 NULL,NULL,NULL,NULL,NULL,
244 NULL,NULL,NULL,NULL,NULL,
245 NULL,NULL,NULL,NULL,NULL,
246 NULL,NULL,NULL,NULL,NULL,
247 NULL,NULL,NULL,NULL,NULL);
248 BEGIN
249 IF ncols >= 1 THEN
250 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c0);
251 END IF;
252 IF ncols >= 2 THEN
253 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c1);
254 END IF;
255 IF ncols >= 3 THEN
256 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c2);
257 END IF;
258 IF ncols >= 4 THEN
259 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c3);
260 END IF;
261 IF ncols >= 5 THEN
262 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c4);
263 END IF;
264 IF ncols >= 6 THEN
265 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c5);
266 END IF;
267 IF ncols >= 7 THEN
268 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c6);
269 END IF;
270 IF ncols >= 8 THEN
271 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c7);
272 END IF;
273 IF ncols >= 9 THEN
274 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c8);
275 END IF;
276 IF ncols >= 10 THEN
277 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c9);
278 END IF;
279 IF ncols >= 11 THEN
280 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c10);
281 END IF;
282 IF ncols >= 12 THEN
283 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c11);
284 END IF;
285 IF ncols >= 13 THEN
286 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c12);
287 END IF;
288 IF ncols >= 14 THEN
289 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c13);
290 END IF;
291 IF ncols >= 15 THEN
292 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c14);
293 END IF;
294 IF ncols >= 16 THEN
295 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c15);
296 END IF;
297 IF ncols >= 17 THEN
298 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c16);
299 END IF;
300 IF ncols >= 18 THEN
301 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c17);
302 END IF;
303 IF ncols >= 19 THEN
304 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c18);
305 END IF;
306 IF ncols >= 20 THEN
307 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c19);
308 END IF;
309 IF ncols >= 21 THEN
310 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c20);
311 END IF;
312 IF ncols >= 22 THEN
313 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c21);
314 END IF;
315 IF ncols >= 23 THEN
316 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c22);
317 END IF;
318 IF ncols >= 24 THEN
319 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c23);
320 END IF;
321 IF ncols >= 25 THEN
322 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c24);
323 END IF;
324 IF ncols >= 26 THEN
325 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c25);
326 END IF;
327 IF ncols >= 27 THEN
328 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c26);
329 END IF;
330 IF ncols >= 28 THEN
331 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c27);
332 END IF;
333 IF ncols >= 29 THEN
334 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c28);
335 END IF;
336 IF ncols >= 30 THEN
337 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c29);
338 END IF;
339 IF ncols >= 31 THEN
340 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c30);
341 END IF;
342 IF ncols >= 32 THEN
343 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c31);
344 END IF;
345 IF ncols >= 33 THEN
346 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c32);
347 END IF;
348 IF ncols >= 34 THEN
349 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c33);
350 END IF;
351 IF ncols >= 35 THEN
352 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c34);
353 END IF;
354 IF ncols >= 36 THEN
355 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c35);
356 END IF;
357 IF ncols >= 37 THEN
358 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c36);
359 END IF;
360 IF ncols >= 38 THEN
361 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c37);
362 END IF;
363 IF ncols >= 39 THEN
364 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c38);
365 END IF;
366 IF ncols >= 40 THEN
367 buffer_ptr := ParseCol(buffer,buffer_size,buffer_ptr,t.c39);
368 END IF;
369 RETURN t;
370 END;
371
372 FUNCTION ParseCol(buffer IN OUT NOCOPY VARCHAR2,
373 buffer_size IN NUMBER,
374 buffer_ptr IN NUMBER,
375 col_val IN OUT NOCOPY VARCHAR2)
376 RETURN NUMBER IS
377 buffer_ptr_new NUMBER := 0;
378 buffer_ptr_old NUMBER := 0;
379 BEGIN
380 -- the column lies between single \,
381 -- double \\s indicate a \ in the value and not a delimeter
382 buffer_ptr_new := buffer_ptr;
383 LOOP
384 buffer_ptr_old := buffer_ptr_new;
385 buffer_ptr_new := INSTR(buffer, '\', buffer_ptr_old, 1);
386
387 --
388 -- Test for error finding the \
389 --
390 IF buffer_ptr_new = 0 THEN
391 DECLARE
392 msg VARCHAR2(2000);
393 BEGIN
394 msg := getErrorMsg(20013) ;
395 RAISE_APPLICATION_ERROR(-20013, msg);
396 END;
397 END IF;
398
399 --
400 -- Copy the output
401 --
402 col_val := col_val || SUBSTR(buffer, buffer_ptr_old, buffer_ptr_new - buffer_ptr_old);
403
404 buffer_ptr_new := buffer_ptr_new + LENGTH('\'); -- move past the \
405
406 --
407 -- Test for a \\
408 --
409 IF (buffer_ptr_new < buffer_size - 1) AND
410 SUBSTR(buffer, buffer_ptr_new, 1) = '\'
411 THEN
412 col_val := col_val || '\';
413 buffer_ptr_new := buffer_ptr_new + LENGTH('\');
414 ELSE
415 EXIT; -- LOOP;
416 END IF;
417 END LOOP;
418
422 col_val := NULL ;
419 -- Check for encoded NA and empty measure string values
420
421 IF col_val = '__NULL' THEN
423 ELSIF col_val = '__EMPTY_STR' THEN
424 col_val := '' ;
425 END IF ;
426
427 RETURN buffer_ptr_new;
428 END;
429
430 END EPS;
[Home] [Help]