CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle OWA_UTIL
Version 11.1
 
General Information
Source {ORACLE_HOME}/rdbms/admin/pubutil.sql
{ORACLE_HOME}/rdbms/admin/privutil.sql
First Availability 8.1.7
Constants
Name Data Type Value

HTML_TABLE

INTEGER 1

NL_CHAR

VARCHAR2(10) owa_cx.nl_char

owa_version

VARCHAR2(64) '10.1.2.0.0'

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;
Dependencies
ALL_SOURCE HTF OWA_MATCH
ALL_SYNONYMS HTP OWA_SEC
CHILD OWA OWA_UTIL
DBMS_SQL OWA_CACHE PLITBLM
DBMS_SYS_SQL OWA_COOKIE SYS
DBMS_UTILITY OWA_CX WPG_DOCLOAD
Variables colTblSz      BINARY_INTEGER
colTbl        dbms_utility.uncl_array
table_border  CHAR(1);
 
ALIGN
Undocumented Function

Private to package
owa_util.function align(
cdata         VARCHAR2,
ncolumn_size  INTEGER,
calign        VARCHAR2 DEFAULT 'LEFT') RETURN VARCHAR2
Source code in dbms\admin\privutil.sql
 
BIND_OUTPUTS
Undocumented. Private to package owa_util.bind_outputs(
p_theCursor INTEGER, 
colCnt      NUMBER, 
rec_tab     dbms_sql.desc_tab2) RETURN NUMBER
Source code in dbms\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 dbms\admin\privutil.sql
 
BV
Undocumented. Private to package owa_util.bv(c INTEGER, n VARCHAR2, v VARCHAR2 CHARACTER SET any_cs)
Source code in dbms\admin\privutil.sql
 
CALC_COL_SIZES

Calculates column sizes

Private to package
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 dbms\admin\privutil.sql
 
CALENDARPRINT

Print HTML calendar

Overload 1
owa_util.calendarprint(
p_query   IN VARCHAR2
p_mf_only IN VARCHAR2 DEFAULT 'N') -- if 'Y' only prints Mon-Fri
CREATE TABLE caltab (
caldate DATE,
caljob  VARCHAR2(10),
hyprlnk VARCHAR2(30));

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

set serveroutput on
spool c: emp\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 dbms\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 dbms\admin\privutil.sql
Overload 2 owa_util.cellsprint(
p_colCnt         IN INTEGER,
p_resultTbl      IN nc_arr,
p_format_numbers IN VARCHAR2 DEFAULT NULL)
Source code in dbms\admin\privutil.sql
Overload 3 owa_util.cellsprint(
p_theQuery       IN VARCHAR2,
p_max_rows       IN NUMBER DEFAULT 100,
p_format_numbers IN VARCHAR2 DEFAULT NULL )
Source code in dbms\admin\privutil.sql
Overload 4 owa_util.cellsprint(
p_theCursor      IN INTEGER,
p_max_rows       IN NUMBER DEFAULT 100,
p_format_numbers IN VARCHAR2 DEFAULT NULL )
Source code in dbms\admin\privutil.sql
Overload 5 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 dbms\admin\privutil.sql
Overload 6 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 dbms\admin\privutil.sql
Overload 7 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 dbms\admin\privutil.sql
Overload 8 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 dbms\admin\privutil.sql
Overload 9 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 dbms\admin\privutil.sql
Overload 10 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 dbms\admin\privutil.sql
 
CELLSPRINT_FN
Prints the contents of a query in an HTML table 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 dbms\admin\privutil.sql
 
CHOOSE_DATE
Generates HTML form elements that allow the user to select a date owa_util.choose_date(p_name VARCHAR2, p_date DATE DEFAULT SYSDATE)
Source code in dbms\admin\privutil.sql
 
CLOSE_SOURCE_CURSOR
Undocumented. Private to package 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
Undocumented. Private to package

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 dbms\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 dbms\admin\privutil.sql
 
DESCRIBE_COLS
Undocumented. Private to package 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 dbms\admin\privutil.sql
 
ELIMINATE_LONGS
Undocumented. Private to Package 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 dbms\admin\privutil.sql
 
FETCH_SOURCE_CURSOR
Fetch cursor for all_source

Undocumented. Private to package
owa_util.fetch_source_cursor(
stmt_cursor IN  NUMBER,
line        OUT NUMBER,
text        OUT VARCHAR2 ) RETURN NUMBER
Source code in dbms\admin\privutil.sql
 
FORMAT_CELL
Undocumented. Private to package owa_util.format_cell(
columnValue     VARCHAR2 CHARACTER SET any_cs, 
format_numbers  VARCHAR2)
Source code in dbms\admin\privutil.sql
 
GET_CGI_ENV

Returns the value of the specified CGI environment variable
owa_util.get_cgi_env(param_name 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 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 dbms\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 dbms\admin\privutil.sql
 
GET_PROCEDURE
Returns the name of the procedure that is invoked by the PL/SQL Gateway owa_util.get_procedure RETURN VARCHAR2
Source code in dbms\admin\privutil.sql
 
GET_SYNONYM_DEFN
Undocumented. Private to package 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 dbms\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 PROCEDURE http_header_close IS
BEGIN
  htp.prn(NL_CHAR);
END;
Source code above
 
IS_TABLE
Undocumented. Private to package owa_util.is_table(item_owner VARCHAR2, item_name VARCHAR2)
RETURN BOOLEAN
Source code in dbms\admin\privutil.sql
 
IS_VIEW
Undocumented. Private to package owa_util.is_view(item_owner VARCHAR2, item_name VARCHAR2) RETURN BOOLEAN
Source code in dbms\admin\privutil.sql
 
IS_WEEKEND
Undocumented. Private to package FUNCTION is_weekend(d  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;
Source code above
 
ITE
Macro for If Then Else owa_util.ite(tf BOOLEAN, yes VARCHAR2, no VARCHAR2 ) RETURN VACHAR2
Source code in dbms\admin
 
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 dbms\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 dbms\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: emp\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;
/
 
NAME_RESOLVE
Resolve object names owa_util.name_resolve(
cname       IN  VARCHAR2,
o_procowner OUT VARCHAR2,
o_procname  OUT VARCHAR2)
Source code in dbms\admin\privutil.sql
 
OPEN_SOURCE_CURSOR
Undocumented. Private to package open_source_cursor(
o IN VARCHAR2,  -- procowner
n IN VARCHAR2)  -- procname
RETURN NUMBER
Source code in dbms\admin\privutil.sql
 
PATH_TO_ME
Undocumented. Private to package 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 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 dbms\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 dbms\admin\privutil.sql
 
PRINT_ROWS
Private to package. Prints the rows (nrow_min, nrow_max, cclauses) and columns (ccolumns) from the table (ctable) in the specified format (ntable_type). DESCRIBE_COLS (or a functional equivalent) must be called before calling PRINT_ROWS to populate col_dtypes, col_sizes. PRINT_ROWS 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 dbms\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 dbms\admin\privutil.sql

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

-- returns: <www.psoug.org>
-- an example of its use:

BEGIN
  owa_util.status_line(301, FALSE);
  owa_util.redirect_url('http://www.psoug.org', TRUE);
END;
/
 
RESOLVE_SYNONYM
Undocumented. Private to package

Overload 1
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 dbms\admin\privutil.sql
Overload 2 resolve_synonym(csynonym in VARCHAR2,
cschema          IN  VARCHAR2,
resolved_name    OUT VARCHAR2,
resolved_owner   OUT VARCHAR2,
resolved_db_link OUT VARCHAR2)
Source code in dbms\admin\privutil.sql
 
RESOLVE_TABLE
Undocumented. Private to package 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 dbms\admin\privutil.sql
 
SHOW_INTERNAL
Undocumented. Private to package 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 dbms\admin\privutil.sql
 
SHOW_QUERY_COLUMNS
Undocumented. Private to package owa_util.show_query_columns(ctable in VARCHAR2)
Source code in dbms\admin\privutil.sql
 
SHOWPAGE

Prints a page generated by the HTP and HTF packages in SQL*Plus

Same as HTP.SHOWPAGE
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 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: emp\demopage.html
exec feds_pkg.get_data;
exec owa_util.showpage;
spool off
 
SHOWSOURCE
Prints the source for the specified subprogram owa_util.showsource(cname IN VARCHAR2)
Source code in dbms\admin\privutil.sql
 
SHOW_QUERY_COLUMNS
Undocumented. Private to package show_query_columns(ctable IN VARCHAR2)
Source code in dbms\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 dbms\admin\privutil.sql
Overload 2 owa_util.signature(cname IN VARCHAR2 CHARACTER SET any_cs)
Source code in dbms\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 dbms\admin\privutil.sql

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

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 dbms\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 dbms\admin\privutil.sql
 
TABLEHEADERROWCLOSE
Undocumented. Private to package.

Overload 1
owa_util.tableHeaderRowClose(
crowstring  IN OUT VARCHAR2,
ntable_type  IN     INTEGER DEFAULT HTML_TABLE)
Source code in dbms\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 dbms\admin\privutil.sql
 
TABLEHEADERROWOPEN
Undocumented. Private to package.

Overload 1
owa_util.tableHeaderRowOpen(
crowstring  IN OUT VARCHAR2,
ntable_type  IN     INTEGER DEFAULT HTML_TABLE)
Source code in dbms\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 dbms\admin\privutil.sql
 
TABLENODATA
Undocumented. Private to package. 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 dbms\admin\privutil.sql
 
TABLEOPEN
Undocumented. Private to package. owa_util.tableOpen(
cattributes IN VARCHAR2 DEFAULT NULL,
ntable_type IN INTEGER DEFAULT HTML_TABLE)
Source code in dbms\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 dbms\admin\privutil.sql

set serveroutput on
spool c: emp abprint.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. Private to package owa_util.tableRowClose(
crowstring  IN OUT VARCHAR2,
ntable_type IN     INTEGER DEFAULT HTML_TABLE)
Source code in dbms\admin\privutil.sql
 
TABLEROWOPEN
Undocumented. Private to package owa_util.tableRowOpen(
crowstring  IN OUT VARCHAR2,
ntable_type IN INTEGER DEFAULT HTML_TABLE)
Source code in dbms\admin\privutil.sql
 
TOCHAR
Undocumented. Private to package FUNCTION tochar(d in NUMBER, f in VARCHAR2) RETURN VARCHAR2 IS
BEGIN
  RETURN NVL(LTRIM(TO_CHAR(d,f)), '(NULL)');
END tochar;
Source code above
 
TODATE
Converts dateType data to the standard PL/SQL date type function todate(p_dateArray  dateType) RETURN DATE
Source code in dbms\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)
CREATE OR REPLACE PROCEDURE child IS
 oname VARCHAR2(100);
 pname VARCHAR2(100);
 lnumb VARCHAR2(100);
 callr VARCHAR2(100);
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 IS
BEGIN
  child;
END parent_p;
/

exec parent_p

-- test with function
CREATE OR REPLACE FUNCTION parent_f RETURN INTEGER 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
UTL_URL
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [78 users online]    © 2010 psoug.org