[Home] [Help]
PACKAGE BODY: OWAPUB.OWA_UTIL
Source
1 package body OWA_UTIL is
2
3 table_border char(1);
4
5 /* datatypes for procedure calendarprint */
6 type dateArray is table of date index by binary_integer;
7 type vcArray is table of varchar2(2000) index by binary_integer;
8
9 /* The broken line below is intentional */
10 NL_CHAR constant char(1) := '
11 ';
12 colTblSz binary_integer;
13 colTbl dbms_utility.uncl_array;
14
15 /*********************************************************************/
16 /* Function to check if the given object is a table in user's schema */
17 /*********************************************************************/
18 function is_table(
19 item_owner in varchar2,
20 item_name in varchar2
21 ) return boolean is
22 stmt_cursor number; -- stmt cursor
23 rc number; -- return code
24 found number;
25 begin
26 found := 0;
27 stmt_cursor := dbms_sql.open_cursor;
28 sys.dbms_sys_sql.parse_as_user(stmt_cursor,
29 'begin
30 select 1
31 into :found
32 from all_tables
33 where table_name = upper(:item_name)
34 and owner = upper(:item_owner);
35 exception
36 when others then
37 :found := 0;
38 end;', dbms_sql.v7);
39 dbms_sql.bind_variable(stmt_cursor, ':item_owner', item_owner);
40 dbms_sql.bind_variable(stmt_cursor, ':item_name', item_name);
41 dbms_sql.bind_variable(stmt_cursor, ':found', found);
42 rc := dbms_sql.execute(stmt_cursor);
43 dbms_sql.variable_value(stmt_cursor, ':found', found);
44 dbms_sql.close_cursor(stmt_cursor);
45 return(found <> 0);
46 end is_table;
47
48 /********************************************************************/
49 /* Function to check if the given object is a view in user's schema */
50 /********************************************************************/
51 function is_view(
52 item_owner in varchar2,
53 item_name in varchar2
54 ) return boolean is
55 stmt_cursor number; -- stmt cursor
56 rc number; -- return code
57 found number;
58 begin
59 found := 0;
60 stmt_cursor := dbms_sql.open_cursor;
61 sys.dbms_sys_sql.parse_as_user(stmt_cursor,
62 'begin
63 select 1
64 into :found
65 from all_views
66 where view_name = upper(:item_name)
67 and owner = upper(:item_owner);
68 exception
69 when others then
70 :found := 0;
71 end;', dbms_sql.v7);
72 dbms_sql.bind_variable(stmt_cursor, ':item_owner', item_owner);
73 dbms_sql.bind_variable(stmt_cursor, ':item_name', item_name);
74 dbms_sql.bind_variable(stmt_cursor, ':found', found);
75 rc := dbms_sql.execute(stmt_cursor);
76 dbms_sql.variable_value(stmt_cursor, ':found', found);
77 dbms_sql.close_cursor(stmt_cursor);
78 return(found <> 0);
79 end is_view;
80
81 /**************************************/
82 /* Function to get synonym definition */
83 /**************************************/
84 function get_synonym_defn(
85 csynonym in varchar2,
86 cschema in varchar2,
87 o_name out all_synonyms.table_name%type,
88 o_owner out all_synonyms.table_owner%type,
89 o_db_link out all_synonyms.db_link%type
90 ) return boolean is
91 stmt_cursor number; -- stmt cursor
92 rc number; -- return code
93 name all_synonyms.table_name%type;
94 owner all_synonyms.table_owner%type;
95 db_link all_synonyms.db_link%type;
96 begin
97 stmt_cursor := dbms_sql.open_cursor;
98 sys.dbms_sys_sql.parse_as_user(stmt_cursor,
99 'begin
100 select table_name, table_owner, db_link
101 into :name, :owner, :db_link
102 from all_synonyms
103 where synonym_name = upper(:csynonym)
104 and owner = upper(:cschema);
105 exception
106 when others then
107 :name := NULL;
108 :owner := NULL;
109 :db_link := NULL;
110 end;', dbms_sql.v7);
111 dbms_sql.bind_variable(stmt_cursor, ':csynonym', csynonym);
112 dbms_sql.bind_variable(stmt_cursor, ':cschema', cschema);
113 dbms_sql.bind_variable(stmt_cursor, ':name', name, 2000);
114 dbms_sql.bind_variable(stmt_cursor, ':owner', owner, 2000);
115 dbms_sql.bind_variable(stmt_cursor, ':db_link', db_link, 2000);
116 rc := dbms_sql.execute(stmt_cursor);
117 dbms_sql.variable_value(stmt_cursor, ':name', name);
118 dbms_sql.variable_value(stmt_cursor, ':owner', owner);
119 dbms_sql.variable_value(stmt_cursor, ':db_link', db_link);
120 o_name := name;
121 o_owner := owner;
122 o_db_link := db_link;
123 return(name is NOT NULL or owner is NOT NULL or db_link is NOT NULL);
124 end get_synonym_defn;
125
126 /************************************/
127 /* Procedure to resolve object name */
128 /************************************/
129 procedure name_resolve(
130 cname in varchar2,
131 o_procowner out varchar2,
132 o_procname out varchar2
133 ) is
134 stmt_cursor number; -- stmt cursor
135 rc number; -- return code
136 procownerl constant number := 255;
137 procowner varchar2(255);
138 procnamel constant number := 255;
139 procname varchar2(255);
140 begin
141 stmt_cursor := dbms_sql.open_cursor;
142 /* resolve name and compose the real package.procedure */
143 sys.dbms_sys_sql.parse_as_user(stmt_cursor,
144 'declare
145 part1 varchar2(255);
146 part2 varchar2(255);
147 dblink varchar2(255);
148 part1_type number;
149 object_number number;
150 begin
151 dbms_utility.name_resolve(:cname, 1,
152 :procowner, part1, part2, dblink, part1_type, object_number);
153 if part1_type in (7, 8)
154 then
155 :procname := part2;
156 else
157 if (part2 is null)
158 then
159 :procname := part1;
160 else
161 :procname := part1 || ''.'' || part2;
162 end if;
163 end if;
164 exception
165 when others then
166 :procowner := NULL;
167 :procname := NULL;
168 end;', dbms_sql.v7
169 );
170 dbms_sql.bind_variable(stmt_cursor, ':cname', cname);
171 dbms_sql.bind_variable(stmt_cursor, ':procowner', procowner, procownerl);
172 dbms_sql.bind_variable(stmt_cursor, ':procname', procname, procnamel);
173 rc := dbms_sql.execute(stmt_cursor);
174 dbms_sql.variable_value(stmt_cursor, ':procowner', procowner);
175 dbms_sql.variable_value(stmt_cursor, ':procname', procname);
176 dbms_sql.close_cursor(stmt_cursor);
177 o_procowner := procowner;
178 o_procname := procname;
179 end name_resolve;
180
181 /***********************************************************************/
182 /* Function to open cursor for all_source to get definition of proc/fn */
183 /***********************************************************************/
184 function open_source_cursor(
185 o in varchar2, -- procowner
186 n in varchar2 -- procname
187 ) return number is
188 stmt_cursor number; -- stmt cursor
189 rc number; -- return code
190 nm varchar2(255);
191 line all_source.line%type;
192 text all_source.text%type;
193 begin
194 rc := instr(n, '.');
195 if (rc > 0)
196 then
197 nm := substr(n, 1, (rc - 1));
198 else
199 nm := n;
200 end if;
201 stmt_cursor := dbms_sql.open_cursor;
202 sys.dbms_sys_sql.parse_as_user(stmt_cursor,
203 'select line, text
204 from all_source
205 where name = upper(:n)
206 and owner = upper(:o)
207 order by type, line', dbms_sql.v7);
208 dbms_sql.bind_variable(stmt_cursor, ':o', o);
209 dbms_sql.bind_variable(stmt_cursor, ':n', nm);
210 dbms_sql.define_column(stmt_cursor, 1, line);
211 dbms_sql.define_column(stmt_cursor, 2, text, 2000);
212 rc := dbms_sql.execute(stmt_cursor);
213 return(stmt_cursor);
214 end open_source_cursor;
215
216 /************************************************************************/
217 /* Function to fetch cursor for all_source to get definition of proc/fn */
218 /************************************************************************/
219 function fetch_source_cursor(
220 stmt_cursor in number,
221 line out number,
222 text out varchar2
223 ) return number is
224 begin
225 if (stmt_cursor >= 0 and dbms_sql.fetch_rows(stmt_cursor) > 0)
226 then
227 dbms_sql.column_value(stmt_cursor, 1, line);
228 dbms_sql.column_value(stmt_cursor, 2, text);
229 return(0);
230 else
231 return(-1);
232 end if;
233 end fetch_source_cursor;
234
235 /*******************************************/
236 /* Function to close cursor for all_source */
237 /*******************************************/
238 procedure close_source_cursor(stmt_cursor in out number) is
239 begin
240 dbms_sql.close_cursor(stmt_cursor);
241 end close_source_cursor;
242
243 /******************************************************************/
244 /* Procedure to link back to the PL/SQL source for your procedure */
245 /******************************************************************/
246 procedure showsource(cname in varchar2) is
247 procname varchar2(255);
248 procowner varchar2(255);
249
250 stmt_cursor number; -- stmt cursor
251
252 line1s integer := 0;
253 line all_source.line%type;
254 text all_source.text%type;
255 begin
256
257 name_resolve(cname, procowner, procname);
258
259 htp.header(1,'Source code for ' || procname);
260 htp.preOpen;
261
262 stmt_cursor := open_source_cursor(procowner, procname);
263 while (fetch_source_cursor(stmt_cursor, line, text) >= 0)
264 loop
265 if (line = 1)
266 then
267 line1s := line1s + 1;
268 if (line1s = 2)
269 then
270 htp.print;
271 end if;
272 end if;
273 htp.prints(translate(text,NL_CHAR,' '));
274 end loop;
275 close_source_cursor(stmt_cursor);
276
277 htp.preClose;
278 signature;
279 end;
280
281 /**************************************************/
282 /* Procedures for printing out an OWA "signature" */
283 /**************************************************/
284 procedure signature is
285 begin
286 htp.line;
287 htp.p('This page was produced by the ');
288 htp.p(htf.bold('PL/SQL Cartridge')||' on '||
289 to_char(sysdate,'Month DD, YYYY HH12:MI PM')||htf.nl);
290 end;
291
292 procedure signature(cname in varchar2) is
293 begin
294 signature;
295 htp.anchor(owa_util.get_owa_service_path||
296 'owa_util.showsource?cname='||cname,
297 'View PL/SQL source code');
298 end;
299
300 /******************************************************/
301 /* Procedure for printing a page generated by htp/htf */
302 /* in SQL*Plus or SQL*DBA */
303 /******************************************************/
304 procedure showpage is
305 begin
306 htp.showpage;
307 end;
308
309 /**************************************************************/
310 /* Procedure/function for accessing CGI environment variables */
311 /**************************************************************/
312 function get_cgi_env(param_name in varchar2) return varchar2 is
313 upper_param_name varchar2(2000) := upper(param_name);
314 begin
315 for i in 1..owa.num_cgi_vars
316 loop
317 if (owa.cgi_var_name(i) = upper_param_name)
318 then return(owa.cgi_var_val(i));
319 end if;
320 end loop;
321
322 return NULL;
323 end;
324
325 procedure print_cgi_env is
326 begin
327 for i in 1..owa.num_cgi_vars
328 loop
329 htp.print(owa.cgi_var_name(i)||' = '||owa.cgi_var_val(i)||htf.nl);
330 end loop;
331 end;
332
333 function get_owa_service_path return varchar2 is
334 script_name varchar2(2000) := get_cgi_env('SCRIPT_NAME');
335 begin
336 if (substr(script_name,length(script_name)-1) = '/')
337 then
338 return script_name;
339 else
340 return script_name||'/';
341 end if;
342 end;
343
344 procedure mime_header(ccontent_type in varchar2 DEFAULT 'text/html',
345 bclose_header in boolean DEFAULT TRUE,
346 ccharset in varchar2 DEFAULT
347 owa_util.get_cgi_env('REQUEST_IANA_CHARSET'))
348 is
349 begin
350 if (ccharset is NULL)
351 then
352 htp.prn('Content-type: '||ccontent_type||NL_CHAR);
353 else
354 htp.prn('Content-type: '||ccontent_type
355 ||'; charset='||ccharset||' '||NL_CHAR);
356 end if;
357
358 if (bclose_header)
359 then http_header_close;
360 end if;
361 end;
362
363 procedure redirect_url(curl in varchar2,
364 bclose_header in boolean DEFAULT TRUE)
365 is
366 begin
367 htp.prn('Location: '||curl||NL_CHAR);
368
369 if (bclose_header)
370 then http_header_close;
371 end if;
372 end;
373
374 procedure status_line(nstatus in integer,
375 creason in varchar2 DEFAULT NULL,
376 bclose_header in boolean DEFAULT TRUE)
377 is
378 begin
379 htp.prn('Status: '||nstatus||' '||creason||NL_CHAR);
380
381 if (bclose_header)
382 then http_header_close;
383 end if;
384 end;
385
386 procedure http_header_close is
387 begin
388 htp.prn(NL_CHAR);
389 end;
390
391 /**********************************************/
392 /* A couple of handy routines used internally */
393 /**********************************************/
394 function get_next_col(
395 col_list in varchar2,
396 inDB in boolean,
397 loc_in in integer,
398 loc_out out number,
399 isExpr out boolean
400 ) return varchar2 is
401 ix number;
402 len number := length(col_list);
403 parenCnt number;
404 inQuote boolean;
405 nxt_ch varchar2(1);
406 begin
407 if (inDB)
408 then
409 if (loc_in = 1)
410 then
411 colTbl.delete;
412 dbms_utility.comma_to_table(col_list, colTblSz, colTbl);
413 end if;
414 if (loc_in <= colTblSz)
415 then
416 if (loc_in < colTblSz)
417 then
418 loc_out := loc_in;
419 else
420 loc_out := -1;
421 end if;
422 isExpr := (instr(colTbl(loc_in), '(') > 0);
423 return(colTbl(loc_in));
424 end if;
425 loc_out := -1;
426 isExpr := false;
427 return(NULL);
428 end if;
429
430 isExpr := false;
431 parenCnt := 0;
432 inQuote := false;
433 ix := loc_in;
434 while (ix <= len)
435 loop
436 nxt_ch := substr(col_list, ix, 1);
437 if (nxt_ch = ',')
438 then
439 if (parenCnt = 0 and (not inQuote))
440 then
441 exit;
442 end if;
443 elsif (nxt_ch = '(')
444 then
445 isExpr := true;
446 if (not inQuote)
447 then
448 parenCnt := parenCnt + 1;
449 end if;
450 elsif (nxt_ch = ')')
451 then
452 isExpr := true;
453 if (not inQuote)
454 then
455 parenCnt := parenCnt - 1;
456 end if;
457 elsif (nxt_ch = '''')
458 then
459 isExpr := true;
460 inQuote := (not inQuote);
461 end if;
462 ix := ix + 1;
463 end loop;
464 if (ix > len)
465 then
466 loc_out := -1;
467 return(ltrim(rtrim(substr(col_list, loc_in))));
468 else
469 loc_out := ix;
470 return(ltrim(rtrim(substr(col_list, loc_in, ix - loc_in))));
471 end if;
472 end get_next_col;
473
474 procedure comma_to_ident_arr(list in varchar2,
475 arr out ident_arr,
476 arrlen out integer) is
477 tok_counter number;
478 tok_loc_out number;
479 isExpr boolean;
480 begin
481 if (list is null)
482 then
483 arrlen := 0;
484 return;
485 end if;
486 tok_counter := 0;
487 tok_loc_out := 0;
488 while (tok_loc_out >= 0) loop
489 tok_counter := tok_counter + 1;
490 arr(tok_counter) :=
491 substr(get_next_col(list, FALSE,
492 (tok_loc_out + 1), tok_loc_out, isExpr),
493 1, 30);
494 end loop;
495 arrlen := tok_counter;
496 end;
497
498 function align(cdata in varchar2,
499 ncolumn_size in integer,
500 calign in varchar2 DEFAULT 'LEFT') return varchar2
501 is
502 lalign integer;
503 align_type char(1);
504 begin
505 align_type := upper(substr(calign,1,1));
506 if (align_type = 'L')
507 then
508 lalign := 1 + nvl(lengthb(cdata),0);
509 else
510 if (align_type = 'R')
511 then
512 lalign := ncolumn_size+1;
513 else /* align_type = 'C' */
514 lalign := 1 + ceil((ncolumn_size - nvl(lengthb(cdata),0))/2)
515 + nvl(lengthb(cdata),0);
516 end if;
517 end if;
518
519 return (rpad(lpad(nvl(cdata,' '), lalign), ncolumn_size+2)||table_border);
520 end;
521
522 /******************************************************************/
523 /* Procedures and functions for building HTML and non-HTML tables */
524 /******************************************************************/
525 /* This is just a function prototype */
526 procedure resolve_synonym(csynonym in varchar2,
527 cschema in varchar2,
528 resolved_name out varchar2,
529 resolved_owner out varchar2,
530 resolved_db_link out varchar2);
531
532 procedure resolve_table(
533 cobject in varchar2,
534 cschema in varchar2,
535 resolved_name out varchar2,
536 resolved_owner out varchar2,
537 resolved_db_link out varchar2
538 ) is
539 stmt_cursor number; -- stmt cursor
540 rc number; -- return code
541 al constant number := 255;
542 a varchar2(255);
543 bl constant number := 255;
544 b varchar2(255);
545 cl constant number := 255;
546 c varchar2(255);
547 dblinkl constant number := 255;
548 dblink varchar2(255);
549 next_pos binary_integer;
550
551 item_name varchar2(255);
552 item_owner varchar2(255);
553
554 dummy char(1);
555 begin
556 stmt_cursor := dbms_sql.open_cursor;
557 sys.dbms_sys_sql.parse_as_user(stmt_cursor,
558 'begin
559 dbms_utility.name_tokenize(:cobject,
560 :a, :b, :c, :dblink, :next_pos);
561 end;', dbms_sql.v7);
562 dbms_sql.bind_variable(stmt_cursor, ':cobject', cobject);
563 dbms_sql.bind_variable(stmt_cursor, ':a', a, al);
564 dbms_sql.bind_variable(stmt_cursor, ':b', b, bl);
565 dbms_sql.bind_variable(stmt_cursor, ':c', c, cl);
566 dbms_sql.bind_variable(stmt_cursor, ':dblink', dblink, dblinkl);
567 dbms_sql.bind_variable(stmt_cursor, ':next_pos', next_pos);
568 rc := dbms_sql.execute(stmt_cursor);
569 dbms_sql.variable_value(stmt_cursor, ':a', a);
570 dbms_sql.variable_value(stmt_cursor, ':b', b);
571 dbms_sql.variable_value(stmt_cursor, ':c', c);
572 dbms_sql.variable_value(stmt_cursor, ':dblink', dblink);
573 dbms_sql.variable_value(stmt_cursor, ':next_pos', next_pos);
574 dbms_sql.close_cursor(stmt_cursor);
575
576 if (c is not null)
577 then
578 /* For a table, we should see AT MOST owner.table */
579 /* If c has a value, we've got owner.table.column */
580 /* or owner.package.procedure */
581 raise_application_error(-20000,
582 'Value '||cobject||' passed to resolve_table is invalid');
583 end if;
584
585 if (b is not null)
586 then
587 item_owner := a;
588 item_name := b;
589 else
590 item_owner := cschema;
591 item_name := a;
592 end if;
593
594 if (is_table(item_owner, item_name) or is_view(item_owner, item_name))
595 then
596 resolved_name := item_name;
597 resolved_owner := item_owner;
598 resolved_db_link := dblink;
599 else
600 resolve_synonym(item_name, item_owner,
601 resolved_name, resolved_owner, resolved_db_link);
602 end if;
603 end;
604
605 procedure resolve_synonym(csynonym in varchar2,
606 cschema in varchar2,
607 resolved_name out varchar2,
608 resolved_owner out varchar2,
609 resolved_db_link out varchar2) is
610 name varchar2(255);
611 owner varchar2(255);
612 db_link varchar2(128);
613 is_also_syn number;
614 begin
615 if (get_synonym_defn(csynonym, cschema, name, owner, db_link)
616 or get_synonym_defn(csynonym, 'PUBLIC', name, owner, db_link))
617 then
618 if (db_link is null)
619 then
620 resolve_table(name, owner,
621 resolved_name, resolved_owner, resolved_db_link);
622 else
623 raise_application_error(-20002,
624 'Cannot resolve remote object ' || csynonym);
625 end if;
626 else
627 raise_application_error(-20001, 'Cannot resolve object ' || csynonym);
628 end if;
629 end;
630
631 /* DESCRIBE_COLS returns the column_names and datatypes as */
632 /* arrays for passing to calc_col_sizes */
633 procedure describe_cols(
634 ctable in varchar2,
635 ccolumns in varchar2,
636 col_names out ident_arr,
637 col_dtypes out ident_arr,
638 nnum_cols out integer
639 )
640 is
641 col_cursor integer;
642 col_name varchar2(255);
643 col_dtype varchar2(9);
644 col_counter number;
645 new_row boolean;
646 col_num number;
647
648 col_loc_out number;
649 next_col varchar2(255);
650 col_decode varchar2(2000);
651 col_in_clause varchar2(2000);
652
653 table_resolved varchar2(255);
654 owner_resolved varchar2(255);
655 db_link_resolved varchar2(255);
656
657 ignore integer;
658 isExpr boolean;
659 begin
660 /* There's no dynamic describe unfortunately. */
661 /* We will need to parse out the owner, etc. */
662 resolve_table(ctable,USER,
663 table_resolved,owner_resolved,db_link_resolved);
664
665 col_counter := 0;
666 if (ccolumns = '*')
667 then
668 col_cursor := dbms_sql.open_cursor;
669 sys.dbms_sys_sql.parse_as_user(col_cursor,
670 'select column_name, data_type '||
671 'from all_tab_columns '||
672 'where table_name = '''||upper(table_resolved)||''' '||
673 ' and owner = '''||upper(owner_resolved)||''' '||
674 'order by column_id',
675 dbms_sql.v7);
676
677 dbms_sql.define_column(col_cursor, 1, col_name, 255);
678 dbms_sql.define_column(col_cursor, 2, col_dtype, 9);
679
680 ignore := dbms_sql.execute(col_cursor);
681
682 loop
683 if (dbms_sql.fetch_rows(col_cursor) > 0)
684 then
685 dbms_sql.column_value(col_cursor, 2, col_dtype);
686 dbms_sql.column_value(col_cursor, 1, col_name);
687
688 col_counter := col_counter + 1;
689 col_dtypes(col_counter) := col_dtype;
690 col_names(col_counter) := col_name;
691 else
692 exit;
693 end if;
694 end loop;
695 dbms_sql.close_cursor(col_cursor);
696 else
697 col_decode := '';
698 col_in_clause := '';
699 col_loc_out := 0;
700 while (col_loc_out >= 0) loop
701 next_col :=
702 upper(get_next_col(ccolumns, TRUE,
703 (col_loc_out + 1), col_loc_out, isExpr));
704 col_counter := col_counter + 1;
705 col_names(col_counter) := next_col;
706 if (not isExpr)
707 then
708 col_decode := col_decode || ',''' || next_col
709 || ''',' || col_counter;
710 col_in_clause := col_in_clause || '''' || next_col || ''',';
711 end if;
712 end loop;
713 if (col_in_clause = '')
714 then
715 for i in 1..col_counter
716 loop
717 col_dtypes(i) := 'VARCHAR2';
718 end loop;
719 else
720 -- remove trailing ',' from col_in_clause
721 col_in_clause := substr(col_in_clause, 1, length(col_in_clause)-1);
722
723 col_cursor := dbms_sql.open_cursor;
724 sys.dbms_sys_sql.parse_as_user(col_cursor,
725 'select column_name, data_type, '||
726 'decode(column_name'||col_decode||') '||
727 'from all_tab_columns '||
728 'where table_name = '''||upper(table_resolved)||''' '||
729 ' and owner = '''||upper(owner_resolved)||''' '||
730 ' and column_name in ('||col_in_clause||') '||
731 'order by 3',
732 dbms_sql.v7);
733
734 dbms_sql.define_column(col_cursor, 1, col_name, 255);
735 dbms_sql.define_column(col_cursor, 2, col_dtype, 9);
736 dbms_sql.define_column(col_cursor, 3, col_num, 9);
737
738 ignore := dbms_sql.execute(col_cursor);
739
740 new_row := (dbms_sql.fetch_rows(col_cursor) > 0);
741 for i in 1..col_counter
742 loop
743 if (new_row) AND (i = col_num)
744 then
745 dbms_sql.column_value(col_cursor, 2, col_dtype);
746 col_dtypes(i) := col_dtype;
747 new_row := (dbms_sql.fetch_rows(col_cursor) > 0);
748 else
749 col_dtypes(i) := 'VARCHAR2';
750 end if;
751 end loop;
752 dbms_sql.close_cursor(col_cursor);
753 end if;
754 end if;
755
756 nnum_cols := col_counter;
757
758 end;
759
760 procedure eliminate_longs(
761 col_names in out ident_arr,
762 col_aliases in out ident_arr,
763 col_dtypes in out ident_arr,
764 num_cols in out integer,
765 num_aliases in out integer
766 ) is
767 col_dtype varchar2(2000);
768 begin
769
770 for i in 1..num_cols
771 loop
772 col_dtype := col_dtypes(i);
773 if (col_dtype = 'LONG' OR col_dtype = 'LONG RAW')
774 then
775 num_cols := num_cols - 1;
776
777 if (i < num_aliases)
778 then
779 num_aliases := num_aliases - 1;
780 end if;
781
782 for j in i..num_cols
783 loop
784 col_dtypes(j) := col_dtypes(j + 1);
785 col_names(j) := col_names(j + 1);
786
787 if (j <= num_aliases)
788 then
789 col_aliases(j) := col_aliases(j + 1);
790 end if;
791 end loop;
792
793 /* To be totally clean, let's null the last values */
794 col_names(num_cols+1) := NULL;
795 col_dtypes(num_cols+1) := NULL;
796 col_aliases(num_aliases+1) := NULL;
797
798 /* Since there is only one LONG allowed in a table, exit */
799 exit;
800 end if;
801 end loop;
802
803 end;
804
805 /* CALC_COL_SIZES will calculate the necessary column sizes */
806 /* for a table. If an ntable_type = HTML_TABLE, then it */
807 /* merely builds an array of NULLs, one entry for each column */
808 /* This is necessary for calls to print_headings. */
809 /* For PRE_TABLEs, CALC_COL_SIZES must scan the table up to */
810 /* the nrow_max-th to determine the widest values. If */
811 /* nrow_max is NULL, then the entire table is scanned. */
812 procedure calc_col_sizes(ctable in varchar2,
813 ntable_type in integer,
814 ccolumns in varchar2,
815 col_names in ident_arr,
816 col_dtypes in ident_arr,
817 nnum_cols in integer,
818 col_aliases in ident_arr,
819 num_aliases in integer DEFAULT 0,
820 cclauses in varchar2 DEFAULT NULL,
821 nrow_min in integer DEFAULT NULL,
822 nrow_max in integer DEFAULT NULL,
823 col_sizes in out num_arr,
824 table_empty out boolean) is
825 crsr integer;
826 ignore integer;
827
828 col_counter integer;
829 col_dtype varchar2(2000);
830
831 vc_var varchar2(2000);
832 number_var number;
833 date_var date;
834 long_var varchar2(32767);
835 raw_var raw(255);
836
837 col_size integer;
838
839 row_count number;
840 begin
841 if ntable_type = HTML_TABLE
842 then
843 for i in 1..nnum_cols
844 loop
845 col_sizes(i) := NULL;
846 end loop;
847 else
848 crsr := dbms_sql.open_cursor;
849 sys.dbms_sys_sql.parse_as_user(crsr,
850 'select '||ccolumns||' from '||ctable||' '||cclauses,
851 dbms_sql.v7);
852
853 for col_counter in 1..nnum_cols
854 loop
855 if (col_counter <= num_aliases)
856 then
857 col_sizes(col_counter):= nvl(lengthb(col_aliases(col_counter)),0);
858 else
859 col_sizes(col_counter):= nvl(lengthb(col_names(col_counter)),0);
860 end if;
861
862 col_dtype := col_dtypes(col_counter);
863 if (col_dtype = 'VARCHAR2' OR col_dtype = 'CHAR')
864 then
865 dbms_sql.define_column(crsr, col_counter, vc_var, 2000);
866 else if (col_dtype = 'NUMBER')
867 then
868 dbms_sql.define_column(crsr,
869 col_counter, number_var);
870 else if (col_dtype = 'DATE')
871 then
872 dbms_sql.define_column(crsr,
873 col_counter, date_var);
874 else if (col_dtype = 'LONG')
875 then
876 dbms_sql.define_column(crsr, col_counter,
877 long_var, 32767);
878 else if (col_dtype = 'RAW')
879 then
880 dbms_sql.define_column_raw(crsr, col_counter,
881 raw_var, 255);
882 end if;
883 end if;
884 end if;
885 end if;
886 end if;
887 end loop;
888
889 ignore := dbms_sql.execute(crsr);
890
891 row_count := 0;
892 if (nrow_min is NOT NULL)
893 then
894 while (row_count < nrow_min - 1)
895 loop
896 if (dbms_sql.fetch_rows(crsr) > 0)
897 then row_count := row_count+1;
898 else exit;
899 end if;
900 end loop;
901 end if;
902
903 while (nrow_max is NULL) or (row_count < nrow_max)
904 loop
905
906 if dbms_sql.fetch_rows(crsr) > 0
907 then
908 row_count := row_count+1;
909
910 for col_counter in 1..nnum_cols
911 loop
912 col_dtype := col_dtypes(col_counter);
913 if (col_dtype = 'VARCHAR2' OR col_dtype = 'CHAR')
914 then
915 dbms_sql.column_value(crsr, col_counter, vc_var);
916 col_size := nvl(lengthb(vc_var),0);
917 else if (col_dtype = 'NUMBER')
918 then
919 dbms_sql.column_value(crsr, col_counter,
920 number_var);
921 col_size := nvl(lengthb(number_var),0);
922 else if (col_dtype = 'DATE')
923 then
924 dbms_sql.column_value(crsr, col_counter,
925 date_var);
926 col_size := nvl(lengthb(date_var),0);
927 else if (col_dtype = 'LONG')
928 then
929 dbms_sql.column_value(crsr, col_counter,
930 long_var);
931 col_size := nvl(lengthb(long_var),0);
932 else if (col_dtype = 'RAW')
933 then
934 dbms_sql.column_value_raw(crsr,
935 col_counter,
936 raw_var);
937 col_size := nvl(lengthb(raw_var),0);
938 else
939 col_size := length('Not Printable');
940 end if;
941 end if;
942 end if;
943 end if;
944 end if;
945
946 if (col_size > col_sizes(col_counter))
947 then
948 col_sizes(col_counter) := col_size;
949 end if;
950
951 end loop;
952
953 else
954 if row_count = 0
955 then
956 table_empty := true;
957 else
958 table_empty := false;
959 end if;
960 exit;
961 end if;
962 end loop;
963
964 dbms_sql.close_cursor(crsr);
965 end if;
966 end;
967
968 /* PRINT_HEADINGS will print the column headings for a table. */
969 /* If ccol_aliases is populated, it will use them, else it */
970 /* will use ccol_names. */
971 procedure print_headings(
972 ccol_aliases in ident_arr,
973 num_aliases in integer,
974 ccol_names in ident_arr,
975 ccol_sizes in num_arr,
976 nnum_cols in integer,
977 ntable_width in out integer,
978 ntable_type in integer
979 ) is
980 row_string varchar2(32000);
981 begin
982 tableHeaderRowOpen(row_string, ntable_width, ntable_type);
983
984 for i in 1..nnum_cols
985 loop
986 if (i <= num_aliases)
987 then
988 tableHeader(ccol_aliases(i), ccol_sizes(i), 'CENTER',
989 row_string, ntable_width, ntable_type);
990 else
991 tableHeader(ccol_names(i), ccol_sizes(i), 'CENTER',
992 row_string, ntable_width, ntable_type);
993 end if;
994 end loop;
995
996 tableHeaderRowClose(row_string, ntable_width, ntable_type);
997 end;
998
999 /* PRINT_ROWS will print the requested rows (nrow_min, nrow_max, */
1000 /* cclauses) and columns (ccolumns) from the table (ctable) */
1001 /* in the specified format (ntable_type). */
1002 /* DESCRIBE_COLS (or a functional equivalent) must be called */
1003 /* before calling PRINT_ROWS to populate col_dtypes, col_sizes. */
1004 /* PRINT_ROWS returns TRUE if there are more rows (beyond */
1005 /* nrow_max) to print. False otherwise. */
1006 function print_rows(
1007 ctable in varchar2,
1008 ntable_type in integer DEFAULT HTML_TABLE,
1009 ccolumns in varchar2 DEFAULT '*',
1010 cclauses in varchar2 DEFAULT NULL,
1011 col_dtypes in ident_arr,
1012 col_sizes in num_arr,
1013 nnum_cols in integer,
1014 ntable_width in integer,
1015 nrow_min in integer DEFAULT 0,
1016 nrow_max in integer DEFAULT NULL
1017 ) return boolean
1018 is
1019 table_cursor integer;
1020
1021 col_counter integer;
1022 col_dtype varchar2(2000);
1023
1024 vc_var varchar2(2000);
1025 number_var number;
1026 date_var date;
1027 long_var varchar2(32767);
1028 raw_var raw(255);
1029
1030 ignore integer;
1031
1032 row_string varchar2(32000);
1033
1034 row_count number;
1035 more_rows boolean := TRUE;
1036 begin
1037 table_cursor := dbms_sql.open_cursor;
1038 sys.dbms_sys_sql.parse_as_user(table_cursor,
1039 'select '||ccolumns||' from '||ctable||' '||cclauses,
1040 dbms_sql.v7);
1041
1042 for col_counter in 1..nnum_cols
1043 loop
1044 col_dtype := col_dtypes(col_counter);
1045 if (col_dtype = 'VARCHAR2' OR col_dtype = 'CHAR')
1046 then
1047 dbms_sql.define_column(table_cursor, col_counter, vc_var, 2000);
1048 elsif (col_dtype = 'NUMBER') then
1049 dbms_sql.define_column(table_cursor, col_counter, number_var);
1050 elsif (col_dtype = 'DATE') then
1051 dbms_sql.define_column(table_cursor, col_counter, date_var);
1052 elsif (col_dtype = 'LONG') then
1053 dbms_sql.define_column(table_cursor, col_counter, long_var, 32767); /* Kelly: not sure over 2000 is valid */
1054 elsif (col_dtype = 'RAW') then
1055 dbms_sql.define_column_raw(table_cursor, col_counter, raw_var, 32767);
1056 end if;
1057 end loop;
1058
1059 ignore := dbms_sql.execute(table_cursor);
1060
1061 row_count := 0;
1062 if (nrow_min is NOT NULL)
1063 then
1064 while (row_count < nrow_min - 1)
1065 loop
1066 if (dbms_sql.fetch_rows(table_cursor) > 0)
1067 then row_count := row_count+1;
1068 else exit;
1069 end if;
1070 end loop;
1071 end if;
1072
1073 while (nrow_max is NULL) or (row_count < nrow_max)
1074 loop
1075
1076 if dbms_sql.fetch_rows(table_cursor) > 0
1077 then
1078 row_count := row_count+1;
1079
1080 tableRowOpen(row_string, ntable_type);
1081
1082 for col_counter in 1..nnum_cols
1083 loop
1084 col_dtype := col_dtypes(col_counter);
1085 if (col_dtype = 'VARCHAR2' OR col_dtype = 'CHAR')
1086 then
1087 dbms_sql.column_value(table_cursor, col_counter, vc_var);
1088 tableData(vc_var, col_sizes(col_counter), 'LEFT', row_string, ntable_type);
1089 elsif (col_dtype = 'NUMBER') then
1090 dbms_sql.column_value(table_cursor, col_counter,number_var);
1091 tableData(number_var, col_sizes(col_counter), 'LEFT', row_string, ntable_type);
1092 elsif (col_dtype = 'DATE') then
1093 dbms_sql.column_value(table_cursor, col_counter, date_var);
1094 tableData(date_var, col_sizes(col_counter), 'LEFT', row_string, ntable_type);
1095 elsif (col_dtype = 'LONG') then
1096 dbms_sql.column_value(table_cursor, col_counter,long_var);
1097 tableData(long_var, col_sizes(col_counter),'LEFT', row_string, ntable_type);
1098 elsif (col_dtype = 'RAW') then
1099 dbms_sql.column_value_raw(table_cursor, col_counter, raw_var);
1100 tableData(raw_var, col_sizes(col_counter), 'LEFT', row_string, ntable_type);
1101 else
1102 tableData('Not Printable', col_sizes(col_counter),'LEFT', row_string, ntable_type);
1103 end if;
1104
1105 end loop;
1106
1107 tableRowClose(row_string, ntable_type);
1108 else
1109 more_rows := FALSE;
1110 exit;
1111 end if;
1112 end loop;
1113
1114 if (row_count < nrow_min)
1115 then
1116 tableRowOpen(row_string, ntable_type);
1117 tableNoData('LEFT', row_string, nnum_cols, ntable_width, ntable_type);
1118 tableRowClose(row_string, ntable_type);
1119 else
1120 if (more_rows)
1121 then more_rows := dbms_sql.fetch_rows(table_cursor) > 0;
1122 end if;
1123 end if;
1124
1125 dbms_sql.close_cursor(table_cursor);
1126
1127 return more_rows;
1128 end;
1129
1130 procedure show_query_columns(ctable in varchar2) is
1131 ignore integer;
1132 cols_cursor integer;
1133 table_resolved varchar2(255);
1134 owner_resolved varchar2(255);
1135 db_link_resolved varchar2(255);
1136 col_name varchar2(2000);
1137 begin
1138 /* There's no dynamic describe unfortunately. */
1139 /* We will need to parse out the owner, etc. */
1140 resolve_table(ctable,USER,
1141 table_resolved,owner_resolved,db_link_resolved);
1142
1143 htp.formHidden('ctable', ctable);
1144 htp.formHidden('COLS', 'DUMMY');
1145
1146 cols_cursor := dbms_sql.open_cursor;
1147 sys.dbms_sys_sql.parse_as_user(cols_cursor,
1148 'select column_name from all_tab_columns where table_name = upper(:t)
1149 and owner = upper(:o)',
1150 dbms_sql.v7);
1151 dbms_sql.bind_variable(cols_cursor, ':t', table_resolved);
1152 dbms_sql.bind_variable(cols_cursor, ':o', owner_resolved);
1153 dbms_sql.define_column(cols_cursor, 1, col_name, 2000);
1154 ignore := dbms_sql.execute(cols_cursor);
1155 loop
1156 if (dbms_sql.fetch_rows(cols_cursor) > 0)
1157 then
1158 dbms_sql.column_value(cols_cursor, 1, col_name);
1159 htp.formCheckbox('COLS', col_name);
1160 htp.print(col_name);
1161 htp.nl;
1162 else
1163 exit;
1164 end if;
1165 end loop;
1166 dbms_sql.close_cursor(cols_cursor);
1167 htp.formSubmit(NULL,'Execute Query');
1168 end;
1169
1170 function tablePrint(ctable in varchar2,
1171 cattributes in varchar2 DEFAULT NULL,
1172 ntable_type in integer DEFAULT HTML_TABLE,
1173 ccolumns in varchar2 DEFAULT '*',
1174 cclauses in varchar2 DEFAULT NULL,
1175 ccol_aliases in varchar2 DEFAULT NULL,
1176 nrow_min in number DEFAULT 0,
1177 nrow_max in number DEFAULT 500) return boolean
1178 is
1179 col_names ident_arr;
1180 col_aliases ident_arr;
1181 num_aliases integer;
1182 col_dtypes ident_arr;
1183 col_sizes num_arr;
1184
1185 nnum_cols integer;
1186 ntable_width integer;
1187
1188 no_data_len integer;
1189 inc_len integer;
1190 amt_left integer;
1191
1192 table_empty boolean;
1193 more_rows boolean;
1194 begin
1195 describe_cols(ctable, ccolumns, col_names, col_dtypes, nnum_cols);
1196
1197 if (ccol_aliases is not null)
1198 then
1199 comma_to_ident_arr(ccol_aliases, col_aliases, num_aliases);
1200 else
1201 num_aliases := 0;
1202 end if;
1203
1204 calc_col_sizes(ctable, ntable_type, ccolumns, col_names, col_dtypes,
1205 nnum_cols, col_aliases, num_aliases, cclauses,
1206 nrow_min, nrow_max, col_sizes, table_empty);
1207
1208 if (table_empty)
1209 then
1210 ntable_width := 1;
1211 for i in 1..nnum_cols
1212 loop
1213 ntable_width := ntable_width + col_sizes(i) + 3;
1214 end loop;
1215
1216 no_data_len := length(' No Data Found ');
1217
1218 if (ntable_width < no_data_len)
1219 then
1220 amt_left := no_data_len - ntable_width;
1221 inc_len := ceil(amt_left/nnum_cols);
1222
1223 for i in 1..nnum_cols
1224 loop
1225 if amt_left > inc_len
1226 then
1227 col_sizes(i) := col_sizes(i) + inc_len;
1228 amt_left := amt_left - inc_len;
1229 else
1230 col_sizes(i) := col_sizes(i) + amt_left;
1231 amt_left := 0;
1232 end if;
1233 end loop;
1234 end if;
1235 end if;
1236
1237 tableOpen(cattributes, ntable_type);
1238
1239 print_headings(col_aliases, num_aliases, col_names, col_sizes,
1240 nnum_cols, ntable_width, ntable_type);
1241
1242 more_rows := print_rows(ctable, ntable_type, ccolumns, cclauses,
1243 col_dtypes, col_sizes, nnum_cols, ntable_width,
1244 nrow_min, nrow_max);
1245
1246 tableClose(ntable_width, ntable_type);
1247
1248 return(more_rows);
1249 end;
1250
1251 procedure tableOpen(cattributes in varchar2 DEFAULT NULL,
1252 ntable_type in integer DEFAULT HTML_TABLE)
1253 is
1254 begin
1255 if (ntable_type = HTML_TABLE)
1256 then
1257 htp.tableOpen(NULL, NULL, NULL, NULL, cattributes);
1258 else
1259 if (cattributes is not null)
1260 then
1261 table_border := '|';
1262 else
1263 table_border := ' ';
1264 end if;
1265 htp.print('<PRE>');
1266 end if;
1267 end;
1268
1269 procedure tableCaption(ccaption in varchar2,
1270 calign in varchar2 DEFAULT 'CENTER',
1271 ntable_type in integer DEFAULT HTML_TABLE) is
1272 begin
1273 if (ntable_type = HTML_TABLE)
1274 then
1275 htp.tableCaption(ccaption, calign);
1276 else
1277 htp.print(ccaption);
1278 end if;
1279 end;
1280
1281 procedure tableHeaderRowOpen(crowstring in out varchar2,
1282 ntable_type in integer DEFAULT HTML_TABLE)
1283 is
1284 begin
1285 if (ntable_type = HTML_TABLE)
1286 then
1287 htp.tableRowOpen;
1288 else
1289 crowstring := table_border;
1290 end if;
1291 end;
1292
1293 procedure tableHeaderRowOpen(crowstring in out varchar2,
1294 ntable_width out integer,
1295 ntable_type in integer DEFAULT HTML_TABLE)
1296 is
1297 begin
1298 if (ntable_type = HTML_TABLE)
1299 then
1300 htp.tableRowOpen;
1301 else
1302 ntable_width := 1;
1303 crowstring := table_border;
1304 end if;
1305 end;
1306
1307 procedure tableHeader(ccolumn_name in varchar2,
1308 ncolumn_size in integer,
1309 calign in varchar2 DEFAULT 'CENTER',
1310 crowstring in out varchar2,
1311 ntable_type in integer DEFAULT HTML_TABLE)
1312 is
1313 begin
1314 if (ntable_type = HTML_TABLE)
1315 then
1316 htp.tableHeader(ccolumn_name);
1317 else
1318 crowstring := crowstring||align(ccolumn_name,ncolumn_size,calign);
1319 end if;
1320 end;
1321
1322 procedure tableHeader(ccolumn_name in varchar2,
1323 ncolumn_size in integer,
1324 calign in varchar2 DEFAULT 'CENTER',
1325 crowstring in out varchar2,
1326 ntable_width in out integer,
1327 ntable_type in integer DEFAULT HTML_TABLE)
1328 is
1329 begin
1330 if (ntable_type = HTML_TABLE)
1331 then
1332 htp.tableHeader(ccolumn_name);
1333 else
1334 ntable_width := ntable_width+ncolumn_size+3;
1335 crowstring := crowstring||align(ccolumn_name,ncolumn_size,calign);
1336 end if;
1337 end;
1338
1339 procedure tableHeaderRowClose(crowstring in out varchar2,
1340 ntable_type in integer DEFAULT HTML_TABLE)
1341 is
1342 begin
1343 if (ntable_type = HTML_TABLE)
1344 then
1345 htp.tableRowClose;
1346 else
1347 htp.print(crowstring);
1348 end if;
1349 end;
1350
1351 procedure tableHeaderRowClose(crowstring in out varchar2,
1352 ntable_width in integer,
1353 ntable_type in integer DEFAULT HTML_TABLE)
1354 is
1355 begin
1356 if (ntable_type = HTML_TABLE)
1357 then
1358 htp.tableRowClose;
1359 else
1360 if (table_border = '|')
1361 then
1362 htp.print(rpad('-',ntable_width,'-'));
1363 htp.print(crowstring);
1364 htp.print(rpad('-',ntable_width,'-'));
1365 else
1366 htp.print(' ');
1367 htp.print(crowstring);
1368 htp.print(' ');
1369 end if;
1370 end if;
1371 end;
1372
1373 procedure tableRowOpen(crowstring in out varchar2,
1374 ntable_type in integer DEFAULT HTML_TABLE)
1375 is
1376 begin
1377 if (ntable_type = HTML_TABLE)
1378 then
1379 htp.tableRowOpen;
1380 else
1381 crowstring := table_border;
1382 end if;
1383 end;
1384
1385 procedure tableData(cdata in varchar2,
1386 ncolumn_size in integer,
1387 calign in varchar2 DEFAULT 'LEFT',
1388 crowstring in out varchar2,
1389 ntable_type in integer DEFAULT HTML_TABLE)
1390 is
1391 begin
1392 if (ntable_type = HTML_TABLE)
1393 then
1394 htp.tableData(cdata, calign);
1395 else
1396 crowstring := crowstring||align(translate(cdata,NL_CHAR,' '),
1397 ncolumn_size, calign);
1398 end if;
1399 end;
1400
1401 procedure tableNoData(calign in varchar2 DEFAULT 'LEFT',
1402 crowstring in out varchar2,
1403 nnum_cols in integer,
1404 ntable_width in integer,
1405 ntable_type in integer DEFAULT HTML_TABLE)
1406 is
1407 begin
1408 if (ntable_type = HTML_TABLE)
1409 then
1410 htp.tableData('No data found', ccolspan=>nnum_cols);
1411 else
1412 crowstring := crowstring||align('No data found',ntable_width-4,calign);
1413 end if;
1414 end;
1415
1416 procedure tableRowClose(crowstring in out varchar2,
1417 ntable_type in integer DEFAULT HTML_TABLE)
1418 is
1419 begin
1420 if (ntable_type = HTML_TABLE)
1421 then
1422 htp.tableRowClose;
1423 else
1424 htp.print(crowstring);
1425 end if;
1426 end;
1427
1428 procedure tableClose(ntable_type in integer DEFAULT HTML_TABLE)
1429 is
1430 begin
1431 if (ntable_type = HTML_TABLE)
1432 then
1433 htp.tableClose;
1434 else
1435 htp.print('</PRE>');
1436 end if;
1437 end;
1438
1439 procedure tableClose(ntable_width in integer,
1440 ntable_type in integer DEFAULT HTML_TABLE)
1441 is
1442 begin
1443 if (ntable_type = HTML_TABLE)
1444 then
1445 htp.tableClose;
1446 else
1447 if (table_border = '|')
1448 then
1449 htp.print(rpad('-',ntable_width,'-'));
1450 else
1451 htp.print(' ');
1452 end if;
1453
1454 htp.print('</PRE>');
1455 end if;
1456 end;
1457
1458 /******************************************************************/
1459 /* Procedures and functions for new utilities for 2.1 */
1460 /******************************************************************/
1461
1462 /******************************************************************/
1463 /* Function macro for if then else -- ite */
1464 /******************************************************************/
1465
1466 function ite( tf in boolean, yes in varchar2, no in varchar2 )
1467 return varchar2
1468 is
1469 begin
1470 if ( tf ) then
1471 return yes;
1472 else
1473 return no;
1474 end if;
1475 end ite;
1476
1477 /******************************************************************/
1478 /* Internal procedures for new utilities */
1479 /******************************************************************/
1480
1481 procedure bv( c in integer, n in varchar2, v in varchar2 )
1482 is
1483 begin
1484 if ( n is NOT NULL ) then
1485 dbms_sql.bind_variable( c, n, v );
1486 end if;
1487 end bv;
1488
1489
1490 function tochar( d in number, f in varchar2 ) return varchar2
1491 is
1492 begin
1493 return nvl(ltrim(to_char(d,f)), '(null)');
1494 end tochar;
1495
1496
1497
1498 procedure format_cell(
1499 columnValue in varchar2, format_numbers in varchar2
1500 ) is
1501 begin
1502 htp.p(htf.format_cell(columnValue, format_numbers));
1503 end format_cell;
1504
1505 function bind_outputs( p_theCursor in integer ) return number
1506 is
1507 columnValue varchar2(1);
1508 colCnt number default 0;
1509 status integer;
1510 begin
1511 for i in 1 .. 255 loop
1512 begin
1513 dbms_sql.define_column( p_theCursor, i, columnValue, 2000 );
1514 colCnt := colCnt + 1;
1515 exception
1516 when others then
1517 if ( sqlcode = -1007 ) then
1518 exit;
1519 else
1520 raise;
1521 end if;
1522 end;
1523 end loop;
1524 status := dbms_sql.execute(p_theCursor);
1525 return colCnt;
1526 end bind_outputs;
1527
1528
1529 function path_to_me return varchar2
1530 is
1531 o varchar2(50);
1532 n varchar2(50);
1533 l number;
1534 t varchar2(50);
1535 begin
1536 who_called_me( o, n, l, t );
1537
1538 return owa_util.get_cgi_env( 'SCRIPT_NAME' ) || '/' || n;
1539 end path_to_me;
1540
1541
1542 /******************************************************************/
1543 /* Procedure to get owner and name of the PL/SQL procedure */
1544 /******************************************************************/
1545
1546 procedure who_called_me( owner out varchar2,
1547 name out varchar2,
1548 lineno out number,
1549 caller_t out varchar2 )
1550 as
1551 call_stack varchar2(4096) default dbms_utility.format_call_stack;
1552 n number;
1553 found_stack BOOLEAN default FALSE;
1554 line varchar2(255);
1555 t varchar2(255);
1556 cnt number := 0;
1557 begin
1558 --
1559 loop
1560 n := instr( call_stack, chr(10) );
1561 exit when ( cnt = 3 or n is NULL or n = 0 );
1562 --
1563 line := substr( call_stack, 1, n-1 );
1564 call_stack := substr( call_stack, n+1 );
1565 --
1566 if ( NOT found_stack ) then
1567 if ( line like '%handle%number%name%' ) then
1568 found_stack := TRUE;
1569 end if;
1570 else
1571 cnt := cnt + 1;
1572 -- cnt = 1 is ME
1573 -- cnt = 2 is MY Caller
1574 -- cnt = 3 is Their Caller
1575 if ( cnt = 3 ) then
1576 -- Fix 718865
1577 --lineno := to_number(substr( line, 13, 6 ));
1578 --line := substr( line, 21 );
1579 n := instr(line, ' ');
1580 if (n > 0)
1581 then
1582 t := ltrim(substr(line, n));
1583 n := instr(t, ' ');
1584 end if;
1585 if (n > 0)
1586 then
1587 lineno := to_number(substr(t, 1, n - 1));
1588 line := ltrim(substr(t, n));
1589 else
1590 lineno := 0;
1591 end if;
1592 if ( line like 'pr%' ) then
1593 n := length( 'procedure ' );
1594 elsif ( line like 'fun%' ) then
1595 n := length( 'function ' );
1596 elsif ( line like 'package body%' ) then
1597 n := length( 'package body ' );
1598 elsif ( line like 'pack%' ) then
1599 n := length( 'package ' );
1600 else
1601 n := length( 'anonymous block ' );
1602 end if;
1603 caller_t := ltrim(rtrim(upper(substr( line, 1, n-1 ))));
1604 line := substr( line, n );
1605 n := instr( line, '.' );
1606 owner := ltrim(rtrim(substr( line, 1, n-1 )));
1607 name := ltrim(rtrim(substr( line, n+1 )));
1608 end if;
1609 end if;
1610 end loop;
1611 end;
1612
1613
1614 /******************************************************************/
1615 /* Function to initialize the shared dynamic SQL */
1616 /******************************************************************/
1617
1618 function bind_variables
1619 ( theQuery in varchar2,
1620 bv1Name in varchar2 default NULL, bv1Value in varchar2 default NULL,
1621 bv2Name in varchar2 default NULL, bv2Value in varchar2 default NULL,
1622 bv3Name in varchar2 default NULL, bv3Value in varchar2 default NULL,
1623 bv4Name in varchar2 default NULL, bv4Value in varchar2 default NULL,
1624 bv5Name in varchar2 default NULL, bv5Value in varchar2 default NULL,
1625 bv6Name in varchar2 default NULL, bv6Value in varchar2 default NULL,
1626 bv7Name in varchar2 default NULL, bv7Value in varchar2 default NULL,
1627 bv8Name in varchar2 default NULL, bv8Value in varchar2 default NULL,
1628 bv9Name in varchar2 default NULL, bv9Value in varchar2 default NULL,
1629 bv10Name in varchar2 default NULL, bv10Value in varchar2 default NULL,
1630 bv11Name in varchar2 default NULL, bv11Value in varchar2 default NULL,
1631 bv12Name in varchar2 default NULL, bv12Value in varchar2 default NULL,
1632 bv13Name in varchar2 default NULL, bv13Value in varchar2 default NULL,
1633 bv14Name in varchar2 default NULL, bv14Value in varchar2 default NULL,
1634 bv15Name in varchar2 default NULL, bv15Value in varchar2 default NULL,
1635 bv16Name in varchar2 default NULL, bv16Value in varchar2 default NULL,
1636 bv17Name in varchar2 default NULL, bv17Value in varchar2 default NULL,
1637 bv18Name in varchar2 default NULL, bv18Value in varchar2 default NULL,
1638 bv19Name in varchar2 default NULL, bv19Value in varchar2 default NULL,
1639 bv20Name in varchar2 default NULL, bv20Value in varchar2 default NULL,
1640 bv21Name in varchar2 default NULL, bv21Value in varchar2 default NULL,
1641 bv22Name in varchar2 default NULL, bv22Value in varchar2 default NULL,
1642 bv23Name in varchar2 default NULL, bv23Value in varchar2 default NULL,
1643 bv24Name in varchar2 default NULL, bv24Value in varchar2 default NULL,
1644 bv25Name in varchar2 default NULL, bv25Value in varchar2 default NULL )
1645 return integer
1646 is
1647 theCursor integer;
1648 begin
1649 if ( upper( substr( ltrim( theQuery ), 1, 6 ) ) <> 'SELECT' ) then
1650 raise INVALID_QUERY;
1651 end if;
1652 --
1653 theCursor := dbms_sql.open_cursor;
1654 sys.dbms_sys_sql.parse_as_user( theCursor, theQuery, dbms_sql.native );
1655 --
1656 bv( theCursor, bv1Name, bv1Value );
1657 bv( theCursor, bv2Name, bv2Value );
1658 bv( theCursor, bv3Name, bv3Value );
1659 bv( theCursor, bv4Name, bv4Value );
1660 bv( theCursor, bv5Name, bv5Value );
1661 bv( theCursor, bv6Name, bv6Value );
1662 bv( theCursor, bv7Name, bv7Value );
1663 bv( theCursor, bv8Name, bv8Value );
1664 bv( theCursor, bv9Name, bv9Value );
1665 bv( theCursor, bv10name, bv10Value );
1666 bv( theCursor, bv11name, bv11Value );
1667 bv( theCursor, bv12name, bv12Value );
1668 bv( theCursor, bv13name, bv13Value );
1669 bv( theCursor, bv14name, bv14Value );
1670 bv( theCursor, bv15name, bv15Value );
1671 bv( theCursor, bv16name, bv16Value );
1672 bv( theCursor, bv17name, bv17Value );
1673 bv( theCursor, bv18name, bv18Value );
1674 bv( theCursor, bv19name, bv19Value );
1675 bv( theCursor, bv20name, bv20Value );
1676 bv( theCursor, bv21name, bv21Value );
1677 bv( theCursor, bv22name, bv22Value );
1678 bv( theCursor, bv23name, bv23Value );
1679 bv( theCursor, bv24name, bv24Value );
1680 bv( theCursor, bv25name, bv25Value );
1681 --
1682 return theCursor;
1683 end bind_variables;
1684
1685
1686 /******************************************************************/
1687 /* Procedure to print cells from a table */
1688 /******************************************************************/
1689
1690 function cellsprint_fn( p_theCursor in integer,
1691 p_max_rows in number default 100,
1692 p_format_numbers in varchar2 default NULL,
1693 p_skip_rec in number default 0,
1694 p_reccnt out number)
1695 return boolean is
1696 columnValue varchar2(2000);
1697 colCnt number default 0;
1698 tmpcursor number default p_theCursor;
1699 recIx number default 0;
1700 recCnt number default 0;
1701 begin
1702 --
1703 colCnt := bind_outputs(p_theCursor);
1704 --
1705 while (recCnt < p_max_rows)
1706 loop
1707 exit when (dbms_sql.fetch_rows(p_theCursor) <= 0);
1708 recIx := recIx + 1;
1709 if (recIx > p_skip_rec)
1710 then
1711 recCnt := recCnt + 1;
1712 htp.tableRowOpen;
1713 for i in 1..colCnt
1714 loop
1715 dbms_sql.column_value(p_theCursor, i, columnValue);
1716 format_cell(columnValue, p_format_numbers);
1717 end loop;
1718 htp.tableRowClose;
1719 end if;
1720 end loop;
1721 dbms_sql.close_cursor(tmpCursor);
1722 p_reccnt := recCnt;
1723 return(recCnt >= p_max_rows);
1724 exception
1725 when others then
1726 if dbms_sql.is_open(p_theCursor) then
1727 dbms_sql.close_cursor(tmpCursor);
1728 end if;
1729 raise;
1730 end cellsprint_fn;
1731
1732 procedure cellsprint(p_colCnt in integer,
1733 p_resultTbl in vc_arr,
1734 p_format_numbers in varchar2 default NULL)
1735 is
1736 recMax number;
1737 colRec number;
1738 begin
1739 if (p_colCnt < 1)
1740 then
1741 return;
1742 end if;
1743 recMax := p_resultTbl.count / p_colCnt;
1744 colRec := 0;
1745 for recIx in 1..recMax
1746 loop
1747 htp.tableRowOpen;
1748 for i in 1..p_colCnt
1749 loop
1750 colRec := colRec + 1;
1751 format_cell(p_resultTbl(colRec), p_format_numbers);
1752 end loop;
1753 htp.tableRowClose;
1754 end loop;
1755 end cellsprint;
1756
1757 procedure cellsprint( p_theQuery in varchar2,
1758 p_max_rows in number default 100,
1759 p_format_numbers in varchar2 default NULL )
1760 is
1761 l_theCursor integer default bind_variables(p_theQuery);
1762 l_more_data boolean;
1763 reccnt number;
1764 begin
1765 l_more_data := cellsprint_fn(
1766 l_theCursor, p_max_rows, p_format_numbers,
1767 0, reccnt);
1768 end;
1769
1770 procedure cellsprint( p_theCursor in integer,
1771 p_max_rows in number default 100,
1772 p_format_numbers in varchar2 default NULL )
1773 is
1774 l_more_data boolean;
1775 reccnt number;
1776 begin
1777 l_more_data := cellsprint_fn(
1778 p_theCursor, p_max_rows, p_format_numbers,
1779 0, reccnt);
1780 end;
1781
1782 procedure cellsprint( p_theQuery in varchar2,
1783 p_max_rows in number default 100,
1784 p_format_numbers in varchar2 default NULL,
1785 p_skip_rec in number default 0,
1786 p_more_data out boolean )
1787 is
1788 l_theCursor integer default bind_variables(p_theQuery);
1789 reccnt number;
1790 begin
1791 p_more_data := cellsprint_fn(
1792 l_theCursor, p_max_rows, p_format_numbers,
1793 p_skip_rec, reccnt);
1794 end cellsprint;
1795
1796 procedure cellsprint( p_theCursor in integer,
1797 p_max_rows in number default 100,
1798 p_format_numbers in varchar2 default NULL,
1799 p_skip_rec in number default 0,
1800 p_more_data out boolean)
1801 is
1802 reccnt number;
1803 begin
1804 p_more_data := cellsprint_fn(
1805 p_theCursor, p_max_rows, p_format_numbers,
1806 p_skip_rec, reccnt);
1807 end cellsprint;
1808
1809 procedure cellsprint( p_theQuery in varchar2,
1810 p_max_rows in number default 100,
1811 p_format_numbers in varchar2 default NULL,
1812 p_reccnt out number )
1813 is
1814 l_theCursor integer default bind_variables(p_theQuery);
1815 l_more_data boolean;
1816 begin
1817 l_more_data := cellsprint_fn(
1818 l_theCursor, p_max_rows, p_format_numbers,
1819 0, p_reccnt);
1820 end;
1821
1822 procedure cellsprint( p_theCursor in integer,
1823 p_max_rows in number default 100,
1824 p_format_numbers in varchar2 default NULL,
1825 p_reccnt out number )
1826 is
1827 l_more_data boolean;
1828 begin
1829 l_more_data := cellsprint_fn(
1830 p_theCursor, p_max_rows, p_format_numbers,
1831 0, p_reccnt);
1832 end;
1833
1834 procedure cellsprint( p_theQuery in varchar2,
1835 p_max_rows in number default 100,
1836 p_format_numbers in varchar2 default NULL,
1837 p_skip_rec in number default 0,
1838 p_more_data out boolean,
1839 p_reccnt out number )
1840 is
1841 l_theCursor integer default bind_variables(p_theQuery);
1842 begin
1843 p_more_data := cellsprint_fn(
1844 l_theCursor, p_max_rows, p_format_numbers,
1845 p_skip_rec, p_reccnt);
1846 end cellsprint;
1847
1848 procedure cellsprint( p_theCursor in integer,
1849 p_max_rows in number default 100,
1850 p_format_numbers in varchar2 default NULL,
1851 p_skip_rec in number default 0,
1852 p_more_data out boolean,
1853 p_reccnt out number )
1854 is
1855 begin
1856 p_more_data := cellsprint_fn(
1857 p_theCursor, p_max_rows, p_format_numbers,
1858 p_skip_rec, p_reccnt);
1859 end cellsprint;
1860
1861 /******************************************************************/
1862 /* Procedure to print a list from a query */
1863 /******************************************************************/
1864
1865 procedure listprint( p_theCursor in integer,
1866 p_cname in varchar2,
1867 p_nsize in number,
1868 p_multiple in boolean default FALSE )
1869 is
1870 colCnt number;
1871 value varchar2(2000);
1872 visible varchar2(2000);
1873 selected varchar2(2000);
1874 begin
1875 colCnt := bind_outputs( p_theCursor );
1876 --
1877 htp.formSelectOpen( cname => p_cname,
1878 nsize => p_nsize,
1879 cattributes => ite( p_multiple,'multiple',NULL) );
1880 loop
1881 exit when ( dbms_sql.fetch_rows(p_theCursor) <= 0 );
1882 dbms_sql.column_value( p_theCursor, 1, value );
1883 dbms_sql.column_value( p_theCursor, 2, visible );
1884 dbms_sql.column_value( p_theCursor, 3, selected );
1885 htp.formSelectOption( cvalue => visible,
1886 cselected => ite( selected IS NULL, NULL, 'SELECTED' ),
1887 cattributes => 'value="' || value || '"' );
1888 end loop;
1889 htp.formSelectClose;
1890 end listprint;
1891
1892 procedure listprint( p_theQuery in varchar2,
1893 p_cname in varchar2,
1894 p_nsize in number,
1895 p_multiple in boolean default FALSE )
1896 is
1897 theCursor integer default bind_variables( p_theQuery );
1898 begin
1899 listprint( theCursor, p_cname, p_nsize, p_multiple );
1900 end listprint;
1901
1902 /******************************************************************/
1903 /* Procedure to choose a date using HTML forms */
1904 /******************************************************************/
1905
1906 procedure choose_date( p_name in varchar2, p_date in date default sysdate)
1907 is
1908 l_day number default to_number(to_char(p_date,'DD'));
1909 l_mon number default to_number(to_char(p_date,'MM'));
1910 l_year number default to_number(to_char(p_date,'YYYY'));
1911 begin
1912 htp.formSelectOpen( cname => p_name, nsize => 1 );
1913 for i in 1 .. 31 loop
1914 htp.formSelectOption( cvalue => i,
1915 cselected => ite( i=l_day, 'SELECTED', NULL ),
1916 cattributes => 'value="' ||
1917 ltrim(to_char(i,'00')) || '"' );
1918 end loop;
1919 htp.formSelectClose;
1920 htp.p( '-' );
1921 htp.formSelectOpen( cname => p_name, nsize => 1 );
1922 for i in 1 .. 12 loop
1923 htp.formSelectOption( cvalue => to_char( to_date( i, 'MM' ), 'MON' ),
1924 cselected => ite( i=l_mon, 'SELECTED', NULL ),
1925 cattributes => 'value="' ||
1926 ltrim(to_char(i,'00')) || '"' );
1927 end loop;
1928 htp.formSelectClose;
1929 htp.p( '-' );
1930 htp.formSelectOpen( cname => p_name, nsize => 1 );
1931 for i in l_year-5 .. l_year+5 loop
1932 htp.formSelectOption( cvalue => i,
1933 cselected => ite( i=l_year, 'SELECTED', NULL ),
1934 cattributes => 'value="' ||
1935 ltrim(to_char(i,'0000')) || '"' );
1936 end loop;
1937 htp.formSelectClose;
1938 end;
1939
1940 function todate( p_dateArray in dateType ) return date
1941 is
1942 begin
1943 return to_date( p_dateArray(1) || '-' || p_dateArray(2) || '-' ||
1944 p_dateArray(3), 'DD-MM-YYYY' );
1945 exception
1946 when no_data_found then
1947 return NULL;
1948 when others then
1949 return last_day( to_date( p_dateArray(2) || '-' || p_dateArray(3),
1950 'MM-YYYY' ) );
1951 end todate;
1952
1953
1954 /******************************************************************/
1955 /* Procedure to print calender in HTML formats */
1956 /******************************************************************/
1957
1958 /******************************************************************/
1959 /* Internal procedures */
1960 /******************************************************************/
1961
1962
1963 function is_weekend( d in date ) return boolean
1964 is
1965 begin
1966 if ( to_char(d,'DY','NLS_DATE_LANGUAGE=AMERICAN') in ( 'SAT', 'SUN' ) ) then
1967 return true;
1968 else
1969 return false;
1970 end if;
1971 end is_weekend;
1972
1973
1974 procedure show_internal( p_mf_only in varchar2,
1975 p_start in date,
1976 p_dates in dateArray,
1977 p_text in vcArray,
1978 p_link in vcArray,
1979 p_cnt in number )
1980 as
1981 l_start date default trunc(p_start,'month');
1982 l_magic_date date default to_date('12111111','ddmmyyyy' );
1983 l_cnt number default 0;
1984 l_width varchar2(25) default 'width="15%"';
1985 l_loop_start number default 0;
1986 l_loop_stop number default 6;
1987 l_mf_only boolean default upper(p_mf_only) = 'Y';
1988 begin
1989 if ( l_mf_only ) then
1990 l_width := 'width="20%"';
1991 l_loop_start := 1;
1992 l_loop_stop := 5;
1993 end if;
1994
1995 htp.tableOpen( cborder=>'border', cattributes=>'width="100%"' );
1996 htp.tableCaption( to_char( l_start, 'Month YYYY' ) );
1997
1998 for i in l_loop_start .. l_loop_stop loop
1999 htp.tableHeader( cvalue => to_char( l_magic_date+i, 'Day' ),
2000 cattributes => l_width );
2001 end loop;
2002 htp.tableRowOpen;
2003 loop
2004 exit when to_char( l_magic_date, 'DY' ) = to_char(l_start,'DY');
2005 if ( not l_mf_only or not is_weekend(l_magic_date) ) then
2006 htp.tableData( htf.br );
2007 end if;
2008 l_magic_date := l_magic_date+1;
2009 end loop;
2010
2011 loop
2012 exit when ( to_char( p_start,'MON') <> to_char( l_start,'MON') );
2013
2014 if ( not l_mf_only or not is_weekend(l_start) ) then
2015 htp.p( '<td valign="TOP" ' || l_width || '>' );
2016 htp.p( htf.italic(htf.bold(to_char(l_start,'DD'))) || htf.br );
2017 end if;
2018
2019 while(l_cnt < p_cnt AND to_char(l_start) = to_char(p_dates(l_cnt)) )
2020 loop
2021 if ( not l_mf_only or not is_weekend(l_start) ) then
2022 htp.p( '»' );
2023 if ( p_link(l_cnt) is NULL ) then
2024 htp.p( p_text(l_cnt) );
2025 else
2026 htp.anchor( p_link(l_cnt), p_text(l_cnt) );
2027 end if;
2028 htp.br;
2029 end if;
2030 l_cnt := l_cnt+1;
2031 end loop;
2032 if ( not l_mf_only or not is_weekend( l_start ) ) then
2033 htp.p( '</td>' );
2034 end if;
2035
2036 if ( to_char(l_start,'DY','NLS_DATE_LANGUAGE=AMERICAN') = 'SAT' ) then
2037 htp.tableRowClose;
2038 if ( l_start <> last_day(l_start) ) then
2039 htp.tableRowOpen;
2040 end if;
2041 end if;
2042 l_start := l_start+1;
2043 end loop;
2044 if ( to_char(l_start ,'DY','NLS_DATE_LANGUAGE=AMERICAN') <> 'SUN' ) then
2045 loop
2046 if ( not l_mf_only or not is_weekend( l_start ) ) then
2047 htp.tableData( htf.br );
2048 end if;
2049 exit when ( to_char(l_start,'DY','NLS_DATE_LANGUAGE=AMERICAN') = 'SAT' );
2050 l_start := l_start+1;
2051 end loop;
2052 htp.tableRowClose;
2053 end if;
2054 htp.tableClose;
2055
2056 end show_internal;
2057
2058
2059 /******************************************************************/
2060 /* Procedure calendarprint */
2061 /******************************************************************/
2062
2063 procedure calendarprint( p_query in varchar2, p_mf_only in varchar2 default 'N' )
2064 is
2065 l_cursor integer default owa_util.bind_variables( p_query );
2066 begin
2067 calendarprint( l_cursor, p_mf_only );
2068 end calendarprint;
2069
2070
2071 procedure calendarprint( p_cursor in integer, p_mf_only in varchar2 default 'N' )
2072 is
2073 l_dates dateArray;
2074 l_text vcArray;
2075 l_link vcArray;
2076 l_cnt number;
2077 l_yyyymon varchar2(7) default NULL;
2078 l_curr_date date;
2079 begin
2080 l_dates(0) := NULL;
2081 l_text(0) := NULL;
2082 l_link(0) := NULL;
2083
2084 dbms_sql.define_column( p_cursor, 1, l_dates(0) );
2085 dbms_sql.define_column( p_cursor, 2, l_text(0), 2000 );
2086 dbms_sql.define_column( p_cursor, 3, l_link(0), 2000 );
2087
2088 l_cnt := dbms_sql.execute( p_cursor );
2089 l_cnt := 0;
2090
2091 loop
2092 exit when ( dbms_sql.fetch_rows( p_cursor ) <= 0 );
2093 dbms_sql.column_value( p_cursor, 1, l_curr_date );
2094 if (l_yyyymon is null)
2095 then
2096 l_yyyymon := to_char(l_curr_date, 'YYYYMON');
2097 end if;
2098
2099 if (to_char(l_curr_date, 'YYYYMON') <> l_yyyymon)
2100 then
2101 show_internal( p_mf_only, l_dates(0),
2102 l_dates, l_text, l_link, l_cnt );
2103 l_cnt := 0;
2104 l_yyyymon := to_char(l_curr_date, 'YYYYMON');
2105 end if;
2106 l_dates(l_cnt) := l_curr_date;
2107 dbms_sql.column_value( p_cursor, 2, l_text(l_cnt) );
2108 dbms_sql.column_value( p_cursor, 3, l_link(l_cnt) );
2109 l_cnt := l_cnt+1;
2110 end loop;
2111
2112 if (l_cnt > 0)
2113 then
2114 show_internal(p_mf_only,
2115 l_dates(0), l_dates, l_text, l_link, l_cnt);
2116 end if;
2117 end calendarprint;
2118
2119 /**********************************************************************/
2120 /* Function to obtain the procedure being invoked by the PL/SQL Agent */
2121 /**********************************************************************/
2122 function get_procedure return varchar2 is
2123 path_info varchar2(255);
2124 procname varchar2(255);
2125 procowner varchar2(255);
2126 begin
2127 /* get PATH_INFO without the first '/' */
2128 path_info := get_cgi_env('PATH_INFO');
2129 --if (path_info like '/%') then
2130 if (substr(path_info, 1, 1) = '/')
2131 then
2132 path_info := substr(path_info, 2);
2133 end if;
2134
2135 /* resolve name and compose the real package.procedure */
2136 name_resolve(path_info, procowner, procname);
2137 return(procname);
2138 end;
2139 end;