ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Purpose
The OWA_UTIL package contains three types of utility subprograms.
Dynamic SQL Utilities enable you to produce pages with dynamically generated SQL code.
HTML utilities enable you to retrieve the values of CGI environment variables and perform URL redirects.
Date utilities enable correct date-handling. Date values are simple strings in HTML, but are treated as a datatype by the Oracle database.
AUTHID
CURRENT_USER
Constants
Name
Data Type
Value
HTML_TABLE
INTEGER
1
PRE_TABLE
INTEGER
2
Data Types
TYPE dateArray IS TABLE OF DATE INDEX BY BINARY_INTEGER;
TYPE vcArray IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
TYPE ncArray IS TABLE OF NVARCHAR2(2000) INDEX BY BINARY_INTEGER;
TYPE object_names_owners IS TABLE OF NUMBER INDEX BY VARCHAR2(500);
Undocumented procedure private to the package body
owa_util.calc_col_sizes(
ctable IN VARCHAR2,
ntable_type IN INTEGER,
ccolumns IN VARCHAR2,
col_names IN ident_arr,
col_dtypes IN ident_arr,
nnum_cols IN INTEGER,
col_aliases_len IN num_arr,
num_aliases IN INTEGER DEFAULT 0,
cclauses IN VARCHAR2 DEFAULT NULL,
nrow_min IN INTEGER DEFAULT NULL,
nrow_max IN INTEGER DEFAULT NULL,
col_sizes IN OUT num_arr,
table_empty OUT BOOLEAN);
owa_util.cellsprint(
p_colCnt IN INTEGER,
p_resultTbl IN vc_arr,
p_format_numbers IN VARCHAR2 DEFAULT NULL);
Source code in \rdbms\admin\privutil.sql
Overload 2
owa_util.cellsprint(
p_theQuery IN VARCHAR2,
p_max_rows IN NUMBER DEFAULT 100,
p_format_numbers IN VARCHAR2 DEFAULT NULL );
Source code in \rdbms\admin\privutil.sql
Overload 3
owa_util.cellsprint(
p_theCursor IN INTEGER,
p_max_rows IN NUMBER DEFAULT 100,
p_format_numbers IN VARCHAR2 DEFAULT NULL );
Source code in \rdbms\admin\privutil.sql
Overload 4
owa_util.cellsprint(
p_theQuery IN VARCHAR2,
p_max_rows IN NUMBER DEFAULT 100,
p_format_numbers IN VARCHAR2 DEFAULT NULL,
p_skip_rec IN NUMBER DEFAULT 0,
p_more_data OUT BOOLEAN);
Source code in \rdbms\admin\privutil.sql
Overload 5
owa_util.cellsprint(
p_theCursor IN INTEGER,
p_max_rows IN NUMBER DEFAULT 100,
p_format_numbers IN VARCHAR2 DEFAULT NULL,
p_skip_rec IN NUMBER DEFAULT 0,
p_more_data OUT BOOLEAN);
Source code in \rdbms\admin\privutil.sql
Overload 6
owa_util.cellsprint(
p_theQuery IN VARCHAR2,
p_max_rows IN NUMBER DEFAULT 100,
p_format_numbers IN VARCHAR2 DEFAULT NULL,
p_reccnt OUT NUMBER);
Source code in \rdbms\admin\privutil.sql
Overload 7
owa_util.cellsprint(
p_theCursor IN INTEGER,
p_max_rows IN NUMBER DEFAULT 100,
p_format_numbers IN VARCHAR2 DEFAULT NULL,
p_reccnt OUT NUMBER);
Source code in \rdbms\admin\privutil.sql
Overload 8
owa_util.cellsprint(
p_theQuery IN VARCHAR2,
p_max_rows IN NUMBER DEFAULT 100,
p_format_numbers IN VARCHAR2 DEFAULT NULL,
p_skip_rec IN NUMBER DEFAULT 0,
p_more_data OUT BOOLEAN,
p_reccnt OUT NUMBER);
Source code in \rdbms\admin\privutil.sql
Overload 9
owa_util.cellsprint(
p_theCursor IN INTEGER,
p_max_rows IN NUMBER DEFAULT 100,
p_format_numbers IN VARCHAR2 DEFAULT NULL,
p_skip_rec IN NUMBER DEFAULT 0,
p_more_data OUT BOOLEAN,
p_reccnt OUT NUMBER);
Source code in \rdbms\admin\privutil.sql
Overload 10: This overload is private to the package body
owa_util.cellsprint(
p_colCnt IN INTEGER,
p_resultTbl IN nc_arr,
p_format_numbers IN VARCHAR2 DEFAULT NULL);
owa_util.cellsprint_fn(
p_theCursor IN INTEGER,
p_max_rows IN NUMBER DEFAULT 100,
p_format_numbers IN VARCHAR2 DEFAULT NULL,
p_skip_rec IN NUMBER DEFAULT 0,
p_reccnt OUT NUMBER)
RETURN BOOLEAN;
Undocumented procedure private to the package body
owa_util.eliminate_longs(
col_names IN OUT ident_arr,
col_aliases IN OUT ident_arr,
col_dtypes IN OUT ident_arr,
num_cols IN OUT INTEGER,
num_aliases IN OUT INTEGER);
Returns the value of the specified CGI environment variable.
Both the code and the horrible grammar were produced by Oracle ... not the
Library.
owa_util.get_cgi_env(param_name IN VARCHAR2)
RETURN VARCHAR2;
DECLARE
charset VARCHAR2(40) := NULL;
ccharset VARCHAR2(100) DEFAULT 'MaGiC_KeY';
ccontent_type VARCHAR2(20) DEFAULT 'text/html';
nl_char CONSTANT VARCHAR2(10) := owa_cx.nl_char;
BEGIN
-- check if ccharset is passed in
IF (ccharset = 'MaGiC_KeY') THEN
-- check the ccontent_type is of type 'text'
IF (upper(ccontent_type) like 'TEXT%') THEN
charset := owa_util.get_cgi_env('REQUEST_IANA_CHARSET');
ELSE
htp.prn('Content-type: ' || ccontent_type || NL_CHAR);
END IF;
htp.setHTTPCharset(charset,
owa_util.get_cgi_env('REQUEST_CHARSET'));
ELSE
-- just output what was passed in without check for type 'text'
IF (ccharset is null) THEN
htp.prn('Content-type: ' || ccontent_type || NL_CHAR);
owa_util.get_next_col(
col_list IN VARCHAR2 CHARACTER SET any_cs,
inDB IN BOOLEAN,
loc_in IN INTEGER,
loc_out OUT NUMBER,
isExpr OUT BOOLEAN)
RETURN VARCHAR2 CHARACTER SET col_list%charset;
owa_util.get_synonym_defn(
csynonym IN VARCHAR2,
cschema IN VARCHAR2,
o_name OUT all_synonyms.table_name%type,
o_owner OUT all_synonyms.table_owner%type,
o_db_link OUT all_synonyms.db_link%type)
RETURN BOOLEAN;
Undocumented procedure private to the package body
FUNCTION is_weekend(d IN DATE) RETURN BOOLEAN IS
BEGIN
IF(TO_CHAR(d,'DY','NLS_DATE_LANGUAGE=AMERICAN') IN ('SAT','SUN')) THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END is_weekend;
/
INSERT INTO listtab VALUES (1, 'Alaska', NULL);
INSERT INTO listtab VALUES (2, 'California', 'X');
INSERT INTO listtab VALUES (3, 'Deleware', NULL);
INSERT INTO listtab VALUES (4, 'Florida', NULL);
INSERT INTO listtab VALUES (5, 'Idaho', NULL);
INSERT INTO listtab VALUES (6, 'Montana', NULL);
INSERT INTO listtab VALUES (7, 'New Jersey', NULL);
INSERT INTO listtab VALUES (8, 'Oregon', NULL);
INSERT INTO listtab VALUES (9, 'Washington', NULL);
COMMIT;
set serveroutput on
spool c:\temp\listtab.html
DECLARE
qstr VARCHAR2(200) := 'SELECT * FROM listtab ORDER BY 1';
BEGIN
owa_util.listprint(qstr, 'MyFormElement', 3, TRUE);
-- owa_util.showpage;
END;
/
FUNCTION path_to_me RETURN VARCHAR2 IS
o VARCHAR2(50);
n VARCHAR2(50);
l NUMBER;
t VARCHAR2(50);
BEGIN
who_called_me(o, n, l, t);
RETURN owa_util.get_cgi_env('SCRIPT_NAME') || '/' || n;
END path_to_me;
owa_util.print_headings(
ccol_aliases IN ident_narr,
num_aliases IN INTEGER,
ccol_names IN ident_arr,
ccol_sizes IN num_arr,
nnum_cols IN INTEGER,
ntable_width IN OUT INTEGER,
ntable_type IN INTEGER);
Source code in \rdbms\admin\privutil.sql
Overload 2
owa_util.print_headings(
ccol_aliases IN ident_arr,
num_aliases IN INTEGER,
ccol_names IN ident_arr,
ccol_sizes IN num_arr,
nnum_cols IN INTEGER,
ntable_width IN OUT INTEGER,
ntable_type IN INTEGER);
Undocumented function private to the package. Prints the rows and columns from the table in the specified format. Returns TRUE if there are more rows (beyond nrow_max) to print. False otherwise.
owa_util.print_rows(
ctable IN VARCHAR2,
ntable_type IN INTEGER DEFAULT HTML_TABLE,
ccolumns IN VARCHAR2 DEFAULT '*',
cclauses IN VARCHAR2 DEFAULT NULL,
col_dtypes IN ident_arr,
col_sizes IN num_arr,
nnum_cols IN INTEGER,
ntable_width IN INTEGER,
nrow_min IN INTEGER DEFAULT 0,
nrow_max IN INTEGER DEFAULT NULL)
RETURN BOOLEAN;
Undocumented procedure private to the package body
owa_util.resolve_synonym(
csynonym IN VARCHAR2,
cschema IN VARCHAR2,
resolved_name OUT VARCHAR2,
resolved_owner OUT VARCHAR2,
resolved_db_link OUT VARCHAR2);
Undocumented procedure private to the package body
owa_util.resolve_table(
cobject IN VARCHAR2,
cschema IN VARCHAR2,
resolved_name OUT VARCHAR2,
resolved_owner OUT VARCHAR2,
resolved_db_link OUT VARCHAR2);
Undocumented procedure private to the package body
owa_util.show_internal(
p_mf_only IN VARCHAR2,
p_start IN DATE,
p_dates IN dateArray,
p_text IN vcArray,
p_link IN vcArray,
p_cnt IN NUMBER,
p_ntext IN ncArray,
p_nlink IN ncArray,
nchar_path IN BOOLEAN);
set serveroutput on
spool c:\temp\demopage.html
exec feds_pkg.get_data;
exec owa_util.showpage;
BEGIN owa_util.showpage; END;
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 356
ORA-06512: at "SYS.HTP", line 665
ORA-06512: at "SYS.HTP", line 759
ORA-06512: at "SYS.HTP", line 1115
ORA-06512: at "SYS.OWA_UTIL", line 347
ORA-06512: at line 1
Undocumented procedure private to the package body
owa_util.tableData(
cdata IN VARCHAR2 CHARACTER SET any_cs,
ncolumn_size IN INTEGER,
calign IN VARCHAR2 DEFAULT 'LEFT',
crowstring IN OUT VARCHAR2,
ntable_type IN INTEGER DEFAULT HTML_TABLE);
Undocumented procedure private to the package body
Overload 1
owa_util.tableHeader(
ccolumn_name IN VARCHAR2 CHARACTER SET any_cs,
ncolumn_size IN INTEGER,
calign IN VARCHAR2 DEFAULT 'CENTER',
crowstring IN OUT VARCHAR2,
ntable_type IN INTEGER DEFAULT HTML_TABLE);
Source code in \rdbms\admin\privutil.sql
Overload 2
owa_util.tableHeader(
ccolumn_name IN VARCHAR2 CHARACTER SET any_cs,
ncolumn_size IN INTEGER,
calign IN VARCHAR2 DEFAULT 'CENTER',
crowstring IN OUT VARCHAR2,
ntable_width IN OUT INTEGER,
ntable_type IN INTEGER DEFAULT HTML_TABLE);
Undocumented procedure private to the package body
owa_util.procedure tableNoData(
calign IN VARCHAR2 DEFAULT 'LEFT',
crowstring OUT VARCHAR2,
nnum_cols IN INTEGER,
ntable_width IN INTEGER,
ntable_type IN INTEGER DEFAULT HTML_TABLE);
Prints the data from a table in the database as an HTML table
owa_util.tablePrint(
ctable IN VARCHAR2,
cattributes IN VARCHAR2 DEFAULT NULL,
ntable_type IN INTEGER DEFAULT HTML_TABLE,
ccolumns IN VARCHAR2 DEFAULT '*',
cclauses IN VARCHAR2 DEFAULT NULL,
ccol_aliases IN VARCHAR2 CHARACTER SET any_cs DEFAULT NULL,
nrow_min IN NUMBER DEFAULT 0,
nrow_max IN NUMBER DEFAULT 500)
RETURN BOOLEAN;
Source code in \rdbms\admin\privutil.sql
set serveroutput on
spool c:\temp\tabprint.html
DECLARE
ctab VARCHAR2(30) := 'SERVERS';
cols VARCHAR2(100) := 'srvr_id,latitude,longitude';
obcl VARCHAR2(100) := 'WHERE rownum < 11 ORDER BY 1';
cola VARCHAR2(100) := 'srvrid,lat,lon';
b BOOLEAN;
BEGIN
b := owa_util.tableprint(ctab, NULL, 1, cols, obcl, cola);
owa_util.showpage;
END;
/
