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