Oracle OWA_UTIL
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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
CH_CHAR VARCHAR2(10) CHR(13)
HTML_TABLE INTEGER 1
NL_CHAR VARCHAR2(10) owa_cx.nl_char
OWA_VERSION VARCHAR2(64) '10.1.2.0.9'
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);

checked_synonyms object_names_owners;
colTblSz     BINARY_INTEGER;
colTbl       dbms_utility.uncl_array;
table_border CHAR(1);
Dependencies
ALL_SOURCE WWV_FLOW_AUTHENTICATION_NATIVE WWV_FLOW_PAGE_CACHE_API
ALL_SYNONYMS WWV_FLOW_CLOUD_IDM WWV_FLOW_PLUGIN_UTIL
DBMS_SQL WWV_FLOW_CUSTOMIZE WWV_FLOW_QUERY_BUILDER
DBMS_STANDARD WWV_FLOW_CUSTOM_AUTH_STD WWV_FLOW_REGION_NATIVE
DBMS_SYS_SQL WWV_FLOW_DATALOAD_XML WWV_FLOW_RENDER_QUERY
DBMS_UTILITY WWV_FLOW_DISP_PAGE_PLUGS WWV_FLOW_REST
HTF WWV_FLOW_DOWNLOAD WWV_FLOW_SESSION_STATE
HTMLDB_UTIL WWV_FLOW_ERROR WWV_FLOW_SVG
HTP WWV_FLOW_F4000_P4150 WWV_FLOW_SW_OBJECT_FEED
ORDPLSGWYUTIL WWV_FLOW_F4000_PLUGINS WWV_FLOW_TEAM_GEN_API
OWA WWV_FLOW_F4000_UTIL WWV_FLOW_TREE_REGION
OWA_CACHE WWV_FLOW_FILE_MGR WWV_FLOW_UTILITIES
OWA_COOKIE WWV_FLOW_FLASH_CHART WWV_FLOW_WORKSHEET
OWA_CX WWV_FLOW_FLASH_CHART2 WWV_FLOW_WS_EXPORT
OWA_MATCH WWV_FLOW_FLASH_CHART5 WWV_FLOW_WS_FLASH_CHART
OWA_SEC WWV_FLOW_FND_USER_API WWV_FLOW_WS_WEBPAGE
PLITBLM WWV_FLOW_GENERATE_TABLE_API WWV_FLOW_XLIFF
WPG_DOCLOAD WWV_FLOW_GEN_API2 WWV_MIG_FRMMENU_LOAD_XML
WWV_FLOW WWV_FLOW_GEN_HINT WWV_MIG_FRM_LOAD_XML
WWV_FLOW_4000_UI WWV_FLOW_IMAGE_GENERATOR WWV_MIG_FRM_OLB_LOAD_XML
WWV_FLOW_AJAX WWV_FLOW_LANG WWV_MIG_RPT_LOAD_XML
WWV_FLOW_API WWV_FLOW_LISTENER WWV_RENDER_REPORT3
WWV_FLOW_AUTHENTICATION WWV_FLOW_LOAD_DATA Z
WWV_FLOW_AUTHENTICATION_API WWV_FLOW_PAGE  
Documented Yes
First Available 8.1.7
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/pubutil.sql
{ORACLE_HOME}/rdbms/admin/privutil.sql
Subprograms
 
ALIGN
Undocumented function private to the package body owa_util.align(
cdata        IN VARCHAR2,
ncolumn_size IN INTEGER,
calign       IN VARCHAR2 DEFAULT 'LEFT')
RETURN VARCHAR2;
Source code in \rdbms\admin\privutil.sql
 
BIND_OUTPUTS
Undocumented function private to the package body owa_util.bind_outputs(
p_theCursor IN INTEGER,
colCnt      IN NUMBER,
rec_tab     IN dbms_sql.desc_tab2)
RETURN NUMBER;
Source code in \rdbms\admin\privutil.sql
 
BIND_VARIABLES
Initialize Shared Dynamic SQL owa_util.bind_variables(
theQuery  VARCHAR2,
bv1Name   VARCHAR2 DEFAULT NULL,
bv1Value  VARCHAR2 CHARACTER SET any_cs DEFAULT NULL,
bv2Name   VARCHAR2 DEFAULT NULL,
bv2Value  VARCHAR2 CHARACTER SET any_cs DEFAULT NULL,
bv3Name   VARCHAR2 DEFAULT NULL,
bv3Value  VARCHAR2 CHARACTER SET any_cs DEFAULT NULL,
bv4Name   VARCHAR2 DEFAULT NULL,
bv4Value  VARCHAR2 CHARACTER SET any_cs DEFAULT NULL,
bv5Name   VARCHAR2 DEFAULT NULL,
bv5Value  VARCHAR2 CHARACTER SET any_cs DEFAULT NULL,
bv6Name   VARCHAR2 DEFAULT NULL,
bv6Value  VARCHAR2 CHARACTER SET any_cs DEFAULT NULL,
bv7Name   VARCHAR2 DEFAULT NULL,
bv7Value  VARCHAR2 CHARACTER SET any_cs DEFAULT NULL,
bv8Name   VARCHAR2 DEFAULT NULL,
bv8Value  VARCHAR2 CHARACTER SET any_cs DEFAULT NULL,
bv9Name   VARCHAR2 DEFAULT NULL,
bv9Value  VARCHAR2 CHARACTER SET any_cs DEFAULT NULL,
bv10Name  VARCHAR2 DEFAULT NULL,
bv10Value VARCHAR2 CHARACTER SET any_cs DEFAULT NULL,
bv11Name  VARCHAR2 DEFAULT NULL,
bv11Value VARCHAR2 CHARACTER SET any_cs DEFAULT NULL,
bv12Name  VARCHAR2 DEFAULT NULL,
bv12Value VARCHAR2 CHARACTER SET any_cs DEFAULT NULL,
bv13Name  VARCHAR2 DEFAULT NULL,
bv13Value VARCHAR2 CHARACTER SET any_cs DEFAULT NULL,
bv14Name  VARCHAR2 DEFAULT NULL,
bv14Value VARCHAR2 CHARACTER SET any_cs DEFAULT NULL,
bv15Name  VARCHAR2 DEFAULT NULL,
bv15Value VARCHAR2 CHARACTER SET any_cs DEFAULT NULL,
bv16Name  VARCHAR2 DEFAULT NULL,
bv16Value VARCHAR2 CHARACTER SET any_cs DEFAULT NULL,
bv17Name  VARCHAR2 DEFAULT NULL,
bv17Value VARCHAR2 CHARACTER SET any_cs DEFAULT NULL,
bv18Name  VARCHAR2 DEFAULT NULL,
bv18Value VARCHAR2 CHARACTER SET any_cs DEFAULT NULL,
bv19Name  VARCHAR2 DEFAULT NULL,
bv19Value VARCHAR2 CHARACTER SET any_cs DEFAULT NULL,
bv20Name  VARCHAR2 DEFAULT NULL,
bv20Value VARCHAR2 CHARACTER SET any_cs DEFAULT NULL,
bv21Name  VARCHAR2 DEFAULT NULL,
bv21Value VARCHAR2 CHARACTER SET any_cs DEFAULT NULL,
bv22Name  VARCHAR2 DEFAULT NULL,
bv22Value VARCHAR2 CHARACTER SET any_cs DEFAULT NULL,
bv23Name  VARCHAR2 DEFAULT NULL,
bv23Value VARCHAR2 CHARACTER SET any_cs DEFAULT NULL,
bv24Name  VARCHAR2 DEFAULT NULL,
bv24Value VARCHAR2 CHARACTER SET any_cs DEFAULT NULL,
bv25Name  VARCHAR2 DEFAULT NULL,
bv25Value VARCHAR2 CHARACTER SET any_cs DEFAULT NULL)
RETURN INTEGER;
Source code in \rdbms\admin\privutil.sql
 
BV
Undocumented procedure private to the package body owa_util.bv(c IN INTEGER, n IN VARCHAR2, v IN VARCHAR2 CHARACTER SET any_cs);
Source code in \rdbms\admin\privutil.sql
 
CALC_COL_SIZES
Calculates column sizes

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);
Source code in \rdbms\admin\privutil.sql
 
CALENDARPRINT
Print HTML calendar

Overload 1

The showpage proc is commented out due to a bug that appears to have broken it. An SR has been opened 08-JUL-2014.
owa_util.calendarprint(
p_query   IN VARCHAR2,
p_mf_only IN VARCHAR2 DEFAULT 'N'); -- if 'Y' only prints Mon-Fri
conn uwclass/uwclass@pdbdev

CREATE TABLE caltab (
caldate DATE,
caljob  VARCHAR2(10),
hyprlnk VARCHAR2(30));

INSERT INTO caltab VALUES (SYSDATE, 'DB SIG', 'www.morganslibrary.org');
INSERT INTO caltab VALUES (SYSDATE+14, 'DB SIG', 'www.morganslibrary.org');
COMMIT;

set serveroutput on
spool c:\temp\caldemo.html

DECLARE
 qstr VARCHAR2(200) := 'SELECT * FROM caltab ORDER BY 1';
BEGIN
  owa_util.calendarprint(qstr, 'N');
--  owa_util.showpage;
END;
/

spool off
Overload 2 owa_util.calendarprint(
p_cursor  IN INTEGER,
p_mf_only IN VARCHAR2 DEFAULT 'N');
Source code in \rdbms\admin\privutil.sql
 
CELLSPRINT
Prints the contents of a query in an HTML table

Overload 1
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);
Source code in \rdbms\admin\privutil.sql
 
CELLSPRINT_FN
Prints the contents of a query in an HTML table

Undocumented function private to the package body
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;
Source code in \rdbms\admin\privutil.sql
 
CHOOSE_DATE
Generates HTML form elements that allow the user to select a date owa_util.choose_date(
p_name IN VARCHAR2,
p_date IN DATE DEFAULT SYSDATE);
Source code in \rdbms\admin\privutil.sql
 
CLOSE_SOURCE_CURSOR
Undocumented procedure private to the package body by making a cal to DBMS_SQL PROCEDURE close_source_cursor(stmt_cursor IN OUT NUMBER) IS
BEGIN
  dbms_sql.close_cursor(stmt_cursor);
END close_source_cursor;
Source code above
 
COMMA_TO_IDENT_ARR
Lower-level routine for table printing

Overload 1
owa_util.comma_to_ident_arr(
list   IN  VARCHAR2 CHARACTER SET any_cs,
arr    OUT ident_arr,
lenarr OUT num_arr,
arrlen OUT INTEGER);
Source code in \rdbms\admin\privutil.sql
Overload 2 for NCHAR owa_util.comma_to_ident_arr(
list   IN  VARCHAR2 CHARACTER SET any_cs,
arr    OUT ident_narr,
lenarr OUT num_arr,
arrlen OUT INTEGER);
Source code in \rdbms\admin\privutil.sql
 
DESCRIBE_COLS
Returns the column_names and datatypes as arrays for passing to calc_col_sizes owa_util.describe_cols(
ctable     IN  VARCHAR2,
ccolumns   IN  VARCHAR2,
col_names  OUT ident_arr,
col_dtypes OUT ident_arr,
nnum_cols  OUT INTEGER);
Source code in \rdbms\admin\privutil.sql
 
ELIMINATE_LONGS
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);
Source code in \rdbms\admin\privutil.sql
 
FETCH_SOURCE_CURSOR
Fetch cursor for all_source

Undocumented function private to the package body
owa_util.fetch_source_cursor(
stmt_cursor IN  NUMBER,
line        OUT NUMBER,
text        OUT VARCHAR2) RETURN NUMBER;
Source code in \rdbms\admin\privutil.sql
 
FORMAT_CELL
Undocumented procedure private to the package body owa_util.format_cell(
columnValue     IN VARCHAR2 CHARACTER SET any_cs,
format_numbers  IN VARCHAR2);
Source code in \rdbms\admin\privutil.sql
 
GET_CGI_ENV
Returns the value of the specified CGI environment variable 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);

      htp.setHTTPCharset(ccharset,
      owa_util.get_cgi_env('REQUEST_CHARSET'));
    ELSE
      htp.prn('Content-type: ' || ccontent_type || '; charset=' ||
      ccharset || NL_CHAR);

      htp.setHTTPCharset(ccharset);
    END IF;
  END IF;
END;
/
 
GET_NEXT_COL
Undocumented function private to the package body 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;
Source code in \rdbms\admin\privutil.sql
 
GET_OWA_SERVICE_PATH
Returns the full virtual path for the PL/SQL Gateway owa_util.get_owa_service_path RETURN VARCHAR2;
Source code in \rdbms\admin\privutil.sql
 
GET_PROCEDURE
Returns the procedure being invoked by the PL/SQL Agent owa_util.get_procedure RETURN VARCHAR2;
Source code in \rdbms\admin\privutil.sql
 
GET_SYNONYM_DEFN
Undocumented function private to the package body 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;
Source code in \rdbms\admin\privutil.sql
 
GET_VERSION
Get Current OWA Toolkit Version owa_util.get_version RETURN VARCHAR2;
SELECT owa_util.get_version
FROM dual;
 
HTTP_HEADER_CLOSE
Closes the HTTP header by making a cal to the HTP package PROCEDURE http_header_close IS
BEGIN
  htp.prn(NL_CHAR);
END http_header_close;
Source code above
 
IS_TABLE
Undocumented function private to the package body owa_util.is_table(item_owner IN VARCHAR2, item_name IN VARCHAR2)
RETURN BOOLEAN;
Source code in \rdbms\admin\privutil.sql
 
IS_VIEW
Undocumented function private to the package body owa_util.is_view(item_owner IN VARCHAR2, item_name IN VARCHAR2)
RETURN BOOLEAN;
Source code in \rdbms\admin\privutil.sql
 
IS_WEEKEND
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;
 
 
ITE
Macro for If Then Else owa_util.ite(tf IN BOOLEAN, yes IN VARCHAR2, no IN VARCHAR2)
RETURN VACHAR2;
Source code in \rdbms\admin\privutil.sql
 
LISTPRINT
Generates a HTML form element that contains data from a query

Overload 1
owa_util.listprint(
p_theCursor IN INTEGER,
p_cname     IN VARCHAR2,
p_nsize     IN NUMBER,
p_multiple  IN BOOLEAN DEFAULT FALSE );
Source code in \rdbms\admin\privutil.sql
Overload 2 owa_util.listprint(
p_theQuery IN VARCHAR2,
p_cname    IN VARCHAR2,
p_nsize    IN NUMBER,
p_multiple IN BOOLEAN DEFAULT FALSE)
Source code in \rdbms\admin\privutil.sql

CREATE TABLE listtab (
listval  NUMBER,
listtxt  VARCHAR2(15),
selflag  VARCHAR2(1));

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;
/

spool off
 
MIME_HEADER
Generates the Content-type line in the HTTP header owa_util.mime_header(
ccontent_type IN VARCHAR2 DEFAULT 'text/html',
bclose_header IN BOOLEAN  DEFAULT TRUE,
ccharset      IN VARCHAR2 DEFAULT 'MaGiC_KeY');
BEGIN
  owa_util.mime_header('text/html', FALSE);
END;
/
Print out the mime header for a document type BEGIN
  owa_util.mime_header( 'image/gif' );
END;
/
 
NAME_RESOLVE
Resolve object names owa_util.name_resolve(
cname       IN  VARCHAR2,
o_procowner OUT VARCHAR2,
o_procname  OUT VARCHAR2);
Source code in \rdbms\admin\privutil.sql
 
OPEN_SOURCE_CURSOR
Undocumented: Private to the package body open_source_cursor(
o IN VARCHAR2,  -- procowner
n IN VARCHAR2)  -- procname
RETURN NUMBER;
Source code in \rdbms\admin\privutil.sql
 
PATH_TO_ME
Undocumented function private to the package body 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;
Source code above
 
PRINT_CGI_ENV
Print CGI Environment Variables
Undocumented procedure private to package body
PROCEDURE print_cgi_env IS
BEGIN
  FOR i IN 1..owa.num_cgi_vars LOOP
    htp.print(owa.cgi_var_name(i)||' = '||owa.cgi_var_val(i)||htf.nl);
  END LOOP;
END;
Source code above
 
PRINT_HEADINGS
Private to package

Overload 1
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);
Source code in \rdbms\admin\privutil.sql
 
PRINT_ROWS
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;
Source code in \rdbms\admin\privutil.sql
 
PRINT_VERSION
Prints Current OWA Toolkit Version owa_util.print_version;
set serveroutput on
exec owa_util.print_version;
-- exec owa_util.showpage;
 
REDIRECT_URL
Generates the Location line in the HTTP header owa_util.redirect_url(
curl          IN VARCHAR2 CHARACER SET any_cs,
bclose_header IN BOOLEAN DEFAULT TRUE);
Source code in \rdbms\admin\privutil.sql

BEGIN
  owa_util.redirect_url('www.morganslibrary.org', TRUE);
END;
/

-- returns: <www.mlib.org>\n\n
-- an example of its use:


BEGIN
  owa_util.status_line(301, FALSE);
  owa_util.redirect_url('http://www.morganslibrary.org', TRUE);
END;
/
 
RESOLVE_SYNONYM
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);
Source code in \rdbms\admin\privutil.sql
 
RESOLVE_TABLE
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);
Source code in \rdbms\admin\privutil.sql
 
SHOW_INTERNAL
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);
Source code in \rdbms\admin\privutil.sql
 
SHOW_QUERY_COLUMNS
Undocumented procedure private to the package body owa_util.show_query_columns(ctable IN VARCHAR2);
Source code in \rdbms\admin\privutil.sql
 
SHOWPAGE
Prints a page generated by the HTP and HTF packages in SQL*Plus

The same as HTP.SHOWPAGE

This procedure appears to be broken in 12cR1 as shown. I will be opening an SR 08-JUL-2014.
owa_utl.showpage;
CREATE TABLE feds_data (
tid       NUMBER(9),
serial_no VARCHAR2(30),
mfg_abbr  VARCHAR2(8),
type_code VARCHAR2(5),
mod_dt    DATE,
mod_by    VARCHAR2(30));

INSERT INTO feds_data
(tid, serial_no, mfg_abbr, type_code, mod_dt, mod_by)
VALUES
(1, '42', 'BCAG', 'ABC', SYSDATE, USER);

INSERT INTO feds_data
(tid, serial_no, mfg_abbr, type_code, mod_dt, mod_by)
VALUES
(1, '666', 'MSC', 'XXX', SYSDATE, USER);

CREATE OR REPLACE PACKAGE feds_pkg AUTHID DEFINER IS
 PROCEDURE get_data;
END feds_pkg;
/

CREATE OR REPLACE PACKAGE BODY feds_pkg IS

PROCEDURE get_data IS
CURSOR gmi_cur IS
SELECT tid, serial_no, mfg_abbr,
type_code, mod_dt, mod_by
FROM feds_data
ORDER BY tid;

ProcName VARCHAR2(61) := 'feds_pkg.get_data';
HeadText VARCHAR2(2000) := 'Demo Page Header';
FootText VARCHAR2(500) := 'Demo Page Footer';

BEGIN
  htp.p(HeadText);
  htp.p('<form method="POST"><center>');
  htp.p('<table border="1" width="30%">');
  FOR i IN gmi_cur LOOP
    htp.p('<tr>');
    --
    htp.p('<td align="center">');
    htp.p('<font face="Arial" color=#000000">');
    htp.p('<input type=text size=3 maxlength=3 name="tid"');
    htp.p('value = ' || i.tid || '></font>');
    htp.p('</td>');
    --
    htp.p('<td align="center">');
    htp.p('<font face="Arial" color=#000000">');
    htp.p('<input type=text size=8 maxlength=8 name="serial_no"');
    htp.p('value = ' || i.serial_no || '></font>');
    htp.p('</td>');
    --
    htp.p('<td align="center">');
    htp.p('<font face="Arial" color=#000000">');
    htp.p('<input type=text size=6 maxlength=6 name="serial_no"');
    htp.p('value = ' || i.mfg_abbr || '></font>');
    htp.p('</td>');
    --
    htp.p('<td align="center">');
    htp.p('<font face="Arial" color=#000000">');
    htp.p('<input type=text size=1 maxlength=1 name="serial_no"');
    htp.p('value = ' || i.type_code || '></font>');
    htp.p('</td>');
    --
    htp.p('<td align="center">');
    htp.p('<font face="Arial" color=#000000">');
    htp.p('<input type=text size=7 maxlength=7 name="serial_no"');
    htp.p('value = ' || TO_CHAR(i.mod_dt, 'MM-DD-YYYY') ||'></font>');
    htp.p('</td>');
    --
    htp.p('<td align="center">');
    htp.p('<font face="Arial" color=#000000">');
    htp.p('<input type=text size=6 maxlength=6 name="serial_no"');
    htp.p('value = ' || i.mod_by || '></font>');
    htp.p('</td>');
    --
    htp.p('</tr>');
  END LOOP;
  htp.p('<p align=center>');
  htp.p('<input type=submit name="Submit" value="Save"></p>');
  htp.p('</form></center>');
  htp.p('</table>');
  htp.p(FootText);
END get_data;

END feds_pkg;
/

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


spool off
 
SHOWSOURCE
Prints the source for the specified subprogram owa_util.showsource(cname IN VARCHAR2);
Source code in \rdbms\admin\privutil.sql
 
SHOW_QUERY_COLUMNS
Undocumented procedure private to the package body show_query_columns(ctable IN VARCHAR2);
Source code in \rdbms\admin\privutil.sql
 
SIGNATURE
Prints a line that says that the page is generated by the PL/SQL Agent
Overload 1
owa_util.signature;
Source code in \rdbms\admin\privutil.sql
Overload 2 owa_util.signature(cname IN VARCHAR2 CHARACTER SET any_cs);
Source code in \rdbms\admin\privutil.sql
 
STATUS_LINE
Generates the Status line in the HTTP header owa_util.status_line(
nstatus       IN INTEGER,
creason       IN VARCHAR2 CHARACTER SET any_cs DEFAULT NULL,
bclose_header IN BOOLEAN DEFAULT TRUE);
Source code in \rdbms\admin\privutil.sql

For an example see the REDIRECT_URL procedure
 
TABLECAPTION
Undocumented procedure private to the package body owa_util.tableCaption(
ccaption    IN VARCHAR2 CHARACTER SET any_cs,
calign      IN VARCHAR2 DEFAULT 'CENTER',
ntable_type IN INTEGER  DEFAULT HTML_TABLE);
Source code in \rdbms\admin\privutil.sql
 
TABLECLOSE
Undocumented procedure private to the package body
Overload 1
owa_util.tableClose(ntable_type INTEGER DEFAULT HTML_TABLE);
Source code in \rdbms\admin\privutil.sql
Overload 2 owa_util.tableClose(
ntable_width IN INTEGER,
ntable_type  IN INTEGER DEFAULT HTML_TABLE);
Source code in \rdbms\admin\privutil.sql
 
TABLEDATA
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);
Source code in \rdbms\admin\privutil.sql
 
TABLEHEADER
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);
Source code in \rdbms\admin\privutil.sql
 
TABLEHEADERROWCLOSE
Undocumented procedure private to the package body

Overload 1
owa_util.tableHeaderRowClose(
crowstring  IN OUT VARCHAR2,
ntable_type IN     INTEGER DEFAULT HTML_TABLE);
Source code in \rdbms\admin\privutil.sql
Overload 2 owa_util.tableHeaderRowClose(
crowstring   IN OUT VARCHAR2,
ntable_width IN     INTEGER,
ntable_type  IN     INTEGER DEFAULT HTML_TABLE);
Source code in \rdbms\admin\privutil.sql
 
TABLEHEADERROWOPEN
Undocumented: Private to the package body

Overload 1
owa_util.tableHeaderRowOpen(
crowstring  IN OUT VARCHAR2,
ntable_type IN     INTEGER DEFAULT HTML_TABLE);
Source code in \rdbms\admin\privutil.sql
Overload 2 owa_util.tableHeaderRowOpen(
crowstring   IN OUT VARCHAR2,
ntable_width    OUT INTEGER,
ntable_type  IN     INTEGER DEFAULT HTML_TABLE);
Source code in \rdbms\admin\privutil.sql
 
TABLENODATA
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);
Source code in \rdbms\admin\privutil.sql
 
TABLEOPEN
Undocumented procedure private to the package body owa_util.tableOpen(
cattributes IN VARCHAR2 DEFAULT NULL,
ntable_type IN INTEGER  DEFAULT HTML_TABLE);
Source code in \rdbms\admin\privutil.sql
 
TABLEPRINT
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;
/

spool off
 
TABLEROWCLOSE
Undocumented procedure private to the package body owa_util.tableRowClose(
crowstring  IN OUT VARCHAR2,
ntable_type IN     INTEGER DEFAULT HTML_TABLE);
Source code in \rdbms\admin\privutil.sql
 
TABLEROWOPEN
Undocumented procedure private to the package body owa_util.tableRowOpen(
crowstring  IN OUT VARCHAR2,
ntable_type IN     INTEGER DEFAULT HTML_TABLE);
Source code in \rdbms\admin\privutil.sql
 
TOCHAR
Undocumented function private to the package body FUNCTION tochar(d IN NUMBER, f IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
  RETURN NVL(LTRIM(TO_CHAR(d,f)), '(NULL)');
END tochar;
Source code in \rdbms\admin\privutil.sql
 
TODATE
Converts dateType data to the standard PL/SQL date type owa_util.todate(p_dateArray IN dateType) RETURN DATE;
Source code in \rdbms\admin\privutil.sql
 
WHO_CALLED_ME
Get owner and name of the PL/SQL procedure owa_util.who_called_me(
owner    OUT VARCHAR2,
name     OUT VARCHAR2,
lineno   OUT NUMBER,
caller_t OUT VARCHAR2);
conn uwclass/uwclass@pdbdev

CREATE OR REPLACE PROCEDURE child AUTHID DEFINER IS
 oname all_objects.owner%TYPE;
 pname all_objects.object_name%TYPE;
 lnumb all_source.line%TYPE;
 callr all_objects.object_type%TYPE;
 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  owa_util.who_called_me(oname, pname, lnumb, callr);
  dbms_output.put_line(oname);
  dbms_output.put_line(pname);
  dbms_output.put_line(lnumb);
  dbms_output.put_line(callr);
END;
/

set serveroutput on

-- test with anonymous block
BEGIN
  child;
END;
/

-- test with stored procedure
CREATE OR REPLACE PROCEDURE parent_p AUTHID DEFINER IS
BEGIN
  child;
END parent_p;
/

exec parent_p

-- test with function
CREATE OR REPLACE FUNCTION parent_f RETURN INTEGER AUTHID DEFINER IS
BEGIN
  child;
  RETURN 1;
END parent_f;
/

SELECT parent_f FROM dual;

-- test with trigger
CREATE TABLE t (
testcol NUMBER);

CREATE OR REPLACE TRIGGER mytrig
BEFORE INSERT
ON t
FOR EACH ROW

BEGIN
  child;
END mytrig;
/

INSERT INTO t (testcol) VALUES (1);

Related Topics
HTF
HTP
OWA
OWA_COOKIE
OWA_CUSTOM
OWA_CX
OWA_OPT_LOCK
OWA_SEC
OWA_TEXT
Packages
UTL_URL

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved