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