1 package OWA_UTIL is
2
3 type ident_arr is table of varchar2(30) index by binary_integer;
4 type ident_narr is table of nvarchar2(30) index by binary_integer;
5 type num_arr is table of number index by binary_integer;
6 type ip_address is table of integer index by binary_integer;
7
8 type vc_arr is table of varchar2(32000) index by binary_integer;
9 type nc_arr is table of nvarchar2(16000) index by binary_integer;
10 type raw_arr is table of raw(32000) index by binary_integer;
11
12 -- Functions/Procedures
13 procedure name_resolve(
14 cname in varchar2,
15 o_procowner out varchar2,
16 o_procname out varchar2
17 );
18
19 /******************************************************************/
20 /* Procedure to link back to the PL/SQL source for your procedure */
21 /******************************************************************/
22 /* SHOWSOURCE can take as an argument a procedure, function, */
23 /* package, package.procedure or package.function. */
24 /* SHOWSOURCE prints the source for the specified stored PL/SQL. */
25 /* If package.procedure or package.function are passed, it will */
26 /* print the entire package. */
27 procedure showsource(cname in varchar2);
28
29 /**************************************************/
30 /* Procedures for printing out an OWA "signature" */
31 /**************************************************/
32 /* SIGNATURE prints an HTML line followed by a line like: */
33 /* This page was produced by the Oracle Web Agent on 09/07/95 09:39 */
34 procedure signature;
35
36 /* SIGNATURE (cname) prints an HTML line followed by 2 lines like: */
37 /* This page was produced by the Oracle Web Agent on August 9, 1995
38 9:39 AM */
39 /* View PL/SQL source code (hypertext-link) */
40 /* SIGNATURE can take as an argument a procedure, function, or package, */
41 /* but not package.procedure or package.function. See SHOWSOURCE. */
42 procedure signature(cname in varchar2 character set any_cs);
43
44 /******************************************************/
45 /* Procedure for printing a page generated by htp/htf */
46 /* in SQL*Plus or SQL*DBA */
47 /******************************************************/
48 /* SHOWPAGE can be called to print out the results, */
49 /* in SQL*Plus or SQL*DBA, of an htp/htf generated */
50 /* page. This is done using dbms_output, and thus */
51 /* is limited to 255 characters per line and an */
52 /* overall buffer size of 1,000,000 bytes. */
53 procedure showpage;
54
55 /**************************************************************/
56 /* Procedure/function for accessing CGI environment variables */
57 /**************************************************************/
58 /* GET_CGI_ENV will return the value of the requested CGI */
59 /* environment variable, or NULL if that value is not set. */
60 function get_cgi_env(param_name in varchar2) return varchar2;
61
62 /* PRINT_CGI_ENV will print all of the CGI environment */
63 /* variables which OWA has made available to PL/SQL. */
64 procedure print_cgi_env;
65
66 /* MIME_HEADER will output "Content-type: <ccontent_type>\n\n" */
67 /* This allows changing the default MIME header which the Web */
68 /* Agent returns. This MUST be come before any htp.print or */
69 /* htp.prn calls in order to signal the Web Agent not to use */
70 /* the default. */
71 procedure mime_header(ccontent_type in varchar2 DEFAULT 'text/html',
72 bclose_header in boolean DEFAULT TRUE,
73 ccharset in varchar2 DEFAULT 'MaGiC_KeY');
74
75 /* REDIRECT_URL will output "Location: <curl>\n\n" */
76 /* This allows the PL/SQL program to tell the HTTP server to */
77 /* visit the specified URL instead of returning output from the */
78 /* current URL. By "visit" it is meant that if the specified */
79 /* URL is an HTML page, then it will be returned, but if the URL */
80 /* specifies another CGI program, or call to the Web Agent, then */
81 /* the Web Server will make that call. */
82 /* The call to REDIRECT_URL MUST be come before any htp calls in */
83 /* order to signal the HTTP server to do the redirect. */
84 /* This functionality is only available with OWA 1.0.2 or above. */
85 procedure redirect_url(curl in varchar2 character set any_cs,
86 bclose_header in boolean DEFAULT TRUE);
87
88 /* STATUS_LINE will output "Status: <nstatus> <creason>\n\n" */
89 /* This allows the PL/SQL program to tell the HTTP server to */
90 /* return a standard HTTP status code to the client. */
91 /* The call to STATUS_LINE MUST be come before any htp calls in */
92 /* order to signal the HTTP server to return the status as part */
93 /* of the HTTP header instead of as "content data". */
94 /* This functionality is only available with OWA 1.5 or above. */
95 procedure status_line(nstatus in integer,
96 creason in varchar2 character set any_cs DEFAULT NULL,
97 bclose_header in boolean DEFAULT TRUE);
98
99 /* HTTP_HEADER_CLOSE should be called after calls to either */
100 /* MIME_HEADER, REDIRECT_URL, or STATUS_LINE, where bclose_header */
101 /* is set to FALSE. HTTP_HEADER_CLOSE will close the HTTP header */
102 procedure http_header_close;
103
104 /* GET_OWA_SERVICE_PATH returns the name of the currently */
105 /* with its full virtual path, plus the currently active service */
106 /* For example, a call to get_owa_service_path could return: */
107 /* /ows-bin/myservice/owa/ */
108 function get_owa_service_path return varchar2;
109
110 /******************************************************************/
111 /* Procedures and functions for building HTML and non-HTML tables */
112 /******************************************************************/
113
114 /* TABLE_TYPE constants */
115 HTML_TABLE constant integer := 1;
116 PRE_TABLE constant integer := 2;
117
118 procedure show_query_columns(ctable in varchar2);
119
120 /* TABLEPRINT will print out an entire Oracle table either as */
121 /* an HTML table, or as a "pre-formatted" table. The table */
122 /* alignment follows the HTML 3.0 current standards for default */
123 /* alignment - column headings are CENTERED while table data is */
124 /* LEFT justified. */
125 /* */
126 /* TABLEPRINT takes the following parameters: */
127 /* */
128 /* ctable - the table, view, or synonym name */
129 /* cattributes - allows you to pass any of the attributes that */
130 /* can be passed to the HTML <TABLE> tag. */
131 /* ntable_type - HTML_TABLE or PRE_TABLE */
132 /* ccolumns - a comma-delimited list of columns from ctable */
133 /* cclauses - any SQL "where" or "order by clauses", */
134 /* for example : "where deptno = 10" */
135 /* "where deptno = 10 order by ename" */
136 /* "order by deptno" */
137 /* ccol_aliases - a comma-delimited list of column headings */
138 /* nrow_min - the first row, of those fetched, to print */
139 /* nrow_max - the last row, of those fetched, to print */
140 /* */
141 /* Note that RAW COLUMNS are supported, however LONG RAW */
142 /* are not. References to LONG RAW columns will print the */
143 /* result 'Not Printable'. */
144 function tablePrint(ctable in varchar2,
145 cattributes in varchar2 DEFAULT NULL,
146 ntable_type in integer DEFAULT HTML_TABLE,
147 ccolumns in varchar2 DEFAULT '*',
148 cclauses in varchar2 DEFAULT NULL,
149 ccol_aliases in varchar2 character set any_cs DEFAULT NULL,
150 nrow_min in number DEFAULT 0,
151 nrow_max in number DEFAULT 500) return boolean;
152
153 /* Lower-level routines for printing out the table */
154 procedure comma_to_ident_arr(list in varchar2 character set any_cs,
155 arr out ident_arr,
156 lenarr out num_arr,
157 arrlen out integer);
158
159 procedure comma_to_ident_arr(list in varchar2 character set any_cs,
160 arr out ident_narr,
161 lenarr out num_arr,
162 arrlen out integer);
163
164 procedure tableOpen(cattributes in varchar2 DEFAULT NULL,
165 ntable_type in integer DEFAULT HTML_TABLE);
166
167 procedure tableCaption(ccaption in varchar2 character set any_cs,
168 calign in varchar2 DEFAULT 'center',
169 ntable_type in integer DEFAULT HTML_TABLE);
170
171 procedure tableHeaderRowOpen(crowstring in out varchar2,
172 ntable_type in integer DEFAULT HTML_TABLE);
173
174 procedure tableHeaderRowOpen(crowstring in out varchar2,
175 ntable_width out integer,
176 ntable_type in integer DEFAULT HTML_TABLE);
177
178 procedure tableHeader(ccolumn_name in varchar2 character set any_cs,
179 ncolumn_size in integer,
180 calign in varchar2 DEFAULT 'center',
181 crowstring in out varchar2,
182 ntable_type in integer DEFAULT HTML_TABLE);
183
184 procedure tableHeader(ccolumn_name in varchar2 character set any_cs,
185 ncolumn_size in integer,
186 calign in varchar2 DEFAULT 'center',
187 crowstring in out varchar2,
188 ntable_width in out integer,
189 ntable_type in integer DEFAULT HTML_TABLE);
190
191 procedure tableHeaderRowClose(crowstring in out varchar2,
192 ntable_type in integer DEFAULT HTML_TABLE);
193
194 procedure tableHeaderRowClose(crowstring in out varchar2,
195 ntable_width in integer,
196 ntable_type in integer DEFAULT HTML_TABLE);
197
198 procedure tableRowOpen(crowstring in out varchar2,
199 ntable_type in integer DEFAULT HTML_TABLE);
200
201 procedure tableData(cdata in varchar2 character set any_cs,
202 ncolumn_size in integer,
203 calign in varchar2 DEFAULT 'left',
204 crowstring in out varchar2,
205 ntable_type in integer DEFAULT HTML_TABLE);
206
207 procedure tableNoData(calign in varchar2 DEFAULT 'left',
208 crowstring in out varchar2,
209 nnum_cols in integer,
210 ntable_width in integer,
211 ntable_type in integer DEFAULT HTML_TABLE);
212
213 procedure tableRowClose(crowstring in out varchar2,
214 ntable_type in integer DEFAULT HTML_TABLE);
215
216 procedure tableClose(ntable_type in integer DEFAULT HTML_TABLE);
217
218 procedure tableClose(ntable_width in integer,
219 ntable_type in integer DEFAULT HTML_TABLE);
220
221
222 procedure resolve_table(cobject in varchar2,
223 cschema in varchar2,
224 resolved_name out varchar2,
225 resolved_owner out varchar2,
226 resolved_db_link out varchar2);
227
228 /* DESCRIBE_COLS returns the column_names and datatypes as */
229 /* arrays for passing to calc_col_sizes */
230 procedure describe_cols(
231 ctable in varchar2,
232 ccolumns in varchar2,
233 col_names out ident_arr,
234 col_dtypes out ident_arr,
235 nnum_cols out integer);
236
237
238 /**********************************************************************/
239 /* Function to obtain the procedure being invoked by the PL/SQL Agent */
240 /**********************************************************************/
241 function get_procedure return varchar2;
242
243 PRAGMA RESTRICT_REFERENCES(get_cgi_env, WNDS, WNPS, RNDS);
244
245
246 /******************************************************************/
247 /* Procedures and functions for new utilities for 2.1 */
248 /******************************************************************/
249
250 /* Exception raised when a query fails to be parsed of when a */
251 /* non SELECT statement is passed down */
252 INVALID_QUERY exception;
253
254
255 /* Utility routine used to figure out who called you. Can be */
256 /* used in standard footer routine. */
257 procedure who_called_me( owner out varchar2,
258 name out varchar2,
259 lineno out number,
260 caller_t out varchar2 );
261
262
263 /* Ite = Macro for If then Else */
264 /* only for internal usage. Not exposed */
265 function ite( tf in boolean, yes in varchar2, no in varchar2 )
266 return varchar2;
267
268 /* shorthand for owa_util.get_cgi_env( 'SCRIPT_NAME' ); */
269 /* only for internal usage. Not exposed */
270 function path_to_me return varchar2;
271
272 /* This prepares a sql query and binds variables to it */
273 function bind_variables
274 ( theQuery in varchar2,
275 bv1Name in varchar2 default NULL,
276 bv1Value in varchar2 character set any_cs default NULL,
277 bv2Name in varchar2 default NULL,
278 bv2Value in varchar2 character set any_cs default NULL,
279 bv3Name in varchar2 default NULL,
280 bv3Value in varchar2 character set any_cs default NULL,
281 bv4Name in varchar2 default NULL,
282 bv4Value in varchar2 character set any_cs default NULL,
283 bv5Name in varchar2 default NULL,
284 bv5Value in varchar2 character set any_cs default NULL,
285 bv6Name in varchar2 default NULL,
286 bv6Value in varchar2 character set any_cs default NULL,
287 bv7Name in varchar2 default NULL,
288 bv7Value in varchar2 character set any_cs default NULL,
289 bv8Name in varchar2 default NULL,
290 bv8Value in varchar2 character set any_cs default NULL,
291 bv9Name in varchar2 default NULL,
292 bv9Value in varchar2 character set any_cs default NULL,
293 bv10Name in varchar2 default NULL,
294 bv10Value in varchar2 character set any_cs default NULL,
295 bv11Name in varchar2 default NULL,
296 bv11Value in varchar2 character set any_cs default NULL,
297 bv12Name in varchar2 default NULL,
298 bv12Value in varchar2 character set any_cs default NULL,
299 bv13Name in varchar2 default NULL,
300 bv13Value in varchar2 character set any_cs default NULL,
301 bv14Name in varchar2 default NULL,
302 bv14Value in varchar2 character set any_cs default NULL,
303 bv15Name in varchar2 default NULL,
304 bv15Value in varchar2 character set any_cs default NULL,
305 bv16Name in varchar2 default NULL,
306 bv16Value in varchar2 character set any_cs default NULL,
307 bv17Name in varchar2 default NULL,
311 bv19Name in varchar2 default NULL,
308 bv17Value in varchar2 character set any_cs default NULL,
309 bv18Name in varchar2 default NULL,
310 bv18Value in varchar2 character set any_cs default NULL,
312 bv19Value in varchar2 character set any_cs default NULL,
313 bv20Name in varchar2 default NULL,
314 bv20Value in varchar2 character set any_cs default NULL,
315 bv21Name in varchar2 default NULL,
316 bv21Value in varchar2 character set any_cs default NULL,
317 bv22Name in varchar2 default NULL,
318 bv22Value in varchar2 character set any_cs default NULL,
319 bv23Name in varchar2 default NULL,
320 bv23Value in varchar2 character set any_cs default NULL,
321 bv24Name in varchar2 default NULL,
322 bv24Value in varchar2 character set any_cs default NULL,
323 bv25Name in varchar2 default NULL,
324 bv25Value in varchar2 character set any_cs default NULL )
325 return integer;
326
327
328 /* Many forms of cellsprint. First parm is always a query or */
329 /* an open cursor (from owa_util.bind_variables above). */
330 /* use max_rows to limit the number of rows displayed (default 100) */
331 /* set p_format_numbers to any NON-NULL value to have any field */
332 /* that is an oracle number right justified with commas and rounded */
333 /* off to 2 decimal places (if it has decimals) */
334
335 procedure cellsprint( p_colCnt in integer,
336 p_resultTbl in vc_arr,
337 p_format_numbers in varchar2 default NULL);
338
339 procedure cellsprint( p_theQuery in varchar2,
340 p_max_rows in number default 100,
341 p_format_numbers in varchar2 default NULL );
342
343 procedure cellsprint( p_theCursor in integer,
344 p_max_rows in number default 100,
345 p_format_numbers in varchar2 default NULL );
346
347 /* More involved cellsprint allows you to slice and dice a */
348 /* result set. Can be used to page up and down thru queries. In */
349 /* addition to the above you can tell it what row to start printing */
350 /* at (eg: skip the first 25 records and then print the next 25 */
351 /* records) and it will tell you. if there are more rows to print. */
352 /* You would save the offset within the query in a hidden field to */
353 /* paginate. */
354 procedure cellsprint( p_theQuery in varchar2,
355 p_max_rows in number default 100,
356 p_format_numbers in varchar2 default NULL,
357 p_skip_rec in number default 0,
358 p_more_data out boolean );
359
360 procedure cellsprint( p_theCursor in integer,
361 p_max_rows in number default 100,
362 p_format_numbers in varchar2 default NULL,
363 p_skip_rec in number default 0,
364 p_more_data out boolean );
365
366 procedure cellsprint( p_theQuery in varchar2,
367 p_max_rows in number default 100,
368 p_format_numbers in varchar2 default NULL,
369 p_reccnt out number );
370
371 procedure cellsprint( p_theCursor in integer,
372 p_max_rows in number default 100,
373 p_format_numbers in varchar2 default NULL,
374 p_reccnt out number );
375
376 procedure cellsprint( p_theQuery in varchar2,
377 p_max_rows in number default 100,
378 p_format_numbers in varchar2 default NULL,
379 p_skip_rec in number default 0,
380 p_more_data out boolean,
381 p_reccnt out number );
382
383 procedure cellsprint( p_theCursor in integer,
384 p_max_rows in number default 100,
385 p_format_numbers in varchar2 default NULL,
386 p_skip_rec in number default 0,
387 p_more_data out boolean,
388 p_reccnt out number );
389
390 /* Create a multi select list, a drop down list or a single select */
391 /* list. */
392 /* You send it a query that selects out in ORDER: */
393 /* COLUMN 1 - What your procedure will get back */
394 /* COLUMN 2 - What your user will see in the list box */
395 /* COLUMN 3 - a null or non-null field. If the field is non-null, */
396 /* the current row will be flagged as SELECTED in the */
397 /* list box */
398
399
400 procedure listprint( p_theCursor in integer,
401 p_cname in varchar2,
402 p_nsize in number,
403 p_multiple in boolean default FALSE );
404
405 procedure listprint( p_theQuery in varchar2,
406 p_cname in varchar2,
407 p_nsize in number,
408 p_multiple in boolean default FALSE );
409
410
411 /* procedure for displaying a Date field in html and allowing */
412 /* the user to pick an arbritrary date. This procedure uses */
413 /* 3 html input fields to get the DAY, MONTH, and YEAR fields. */
414 /* The procedure you write that recieves the input should have */
415 /* an input variable of type owa_util.datetype. */
416
417 type dateType is table of varchar2(10) index by binary_integer;
418 procedure choose_date( p_name in varchar2, p_date in date default sysdate);
419 function todate( p_dateArray in dateType ) return date;
420
421 empty_date owa_util.datetype;
422
423 /* routines to print calendars in html format */
424 procedure calendarprint( p_query in varchar2,
425 p_mf_only in varchar2 default 'N' );
426 procedure calendarprint( p_cursor in integer,
427 p_mf_only in varchar2 default 'N' );
428
429 /**********************************************************************/
430 /* Function to obtain the version number */
431 /**********************************************************************/
432 function get_version return varchar2;
433 PRAGMA RESTRICT_REFERENCES(get_version, WNDS, WNPS, RNDS);
434
435 /**********************************************************************/
436 /* Procedure to print the version number */
437 /**********************************************************************/
438 procedure print_version;
439
440 /************************************************************************/
441 /* Function to validate strings that gets set in HTTP header,cookie etc */
442 /************************************************************************/
443 function validate_arg(param in varchar2 character set any_cs) return varchar2;
444
445 end;
[Home] [Help]