Oracle DBMS_EPG
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Purpose EPG is an acronym for Embedded PL/SQL Gateway. DAD is an acronym for Data Access Descriptor.

The XDBADMIN role is required to modify the embedded gateway configuration through the configuration API. Modification of the configuration by a user without the role will result in an "access denied" exception.
AUTHID CURRENT_USER
Constants
Name Data Type Value
LOG_EMERG PLS_INTEGER 0
LOG_ALERT PLS_INTEGER 1
LOG_CRIT PLS_INTEGER 2
LOG_ERR PLS_INTEGER 3
LOG_WARNING PLS_INTEGER 4
LOG_NOTICE PLS_INTEGER 5
LOG_INFO PLS_INTEGER 6
LOG_DEBUG PLS_INTEGER 7
DAD Attributes
database-username document-path-docs nls-language
default-page document-procedure request-validation
document-table-name    
Data Types TYPE varchar2_table IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
Dependencies
DBMS_STANDARD DBMS_XMLDOM PLITBLM
DBMS_SYS_ERROR DUAL XMLTYPE
DBMS_XDB    
Documented Yes
Exceptions
Error Code Reason
ORA-24231 dad_not_found
ORA-24232 unknown_attribute
ORA-24240 invalid_dad_name
First Available 2004
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmsepg.sql
Subprograms
 
AUTHORIZE_DAD
Authorizes a DAD dbms_epg.authorize_dad(
dad_name IN VARCHAR2,
user     IN VARCHAR2 DEFAULT NULL);
SELECT * FROM dba_epg_dad_authorization;

set serveroutput on

DECLARE
 path_list dbms_epg.varchar2_table;
 dad_list  dbms_epg.varchar2_table;
BEGIN
  dbms_epg.create_dad('UWDAD', '/uwweb/*');
  dbms_epg.authorize_dad('UWDAD', 'UWCLASS');

  dbms_epg.get_all_dad_mappings('UWDAD', path_list);
  dbms_output.put_line('-' || path_list(1) || '-');

  dbms_epg.get_dad_list(dad_list);
  FOR i IN 1..dad_list.COUNT LOOP
    dbms_output.put_line('-' || dad_list(i) || '-');
  END LOOP;
END;
/

SELECT * FROM dba_epg_dad_authorization;
 
CREATE_DAD
Creates a DAD dbms_epg.create_dad(
dad_name IN VARCHAR2,
path     IN VARCHAR2 DEFAULT NULL);
See AUTHORIZE_DAD Demo Above
 
DEAUTHORIZE_DAD
Revoke authorization of a DAD dbms_epg.deauthorize_dad(
dad_name IN VARCHAR2,
user     IN VARCHAR2 DEFAULT NULL);
exec dbms_epg.authorize_dad('UWDAD', 'UWCLASS');
 
DELETE_DAD_ATTRIBUTE
Deletes a DAD attribute dbms_epg.delete_dad_attribute(
dad_name  IN VARCHAR2,
attr_name IN VARCHAR2);
exec dbms_epg.deauthorize_dad('UWDAD', 'nls-language');
 
DELETE_GLOBAL_ATTRIBUTE
Deletes a global attribute dbms_epg.delete_global_attribute(attr_name IN VARCHAR2);
BEGIN
  dbms_epg.delete_global_attribute('nls-language');
END;
/
 
DROP_DAD
Drops a DAD dbms_epg.drop_dad(dad_name IN VARCHAR2);
SELECT * FROM dba_epg_dad_authorization;

exec dbms_epg.drop_dad('UWDAD');

SELECT * FROM dba_epg_dad_authorization;
 
GET_ALL_DAD_ATTRIBUTES
Retrieves all the attributes of a DAD dbms_epg.get_all_dad_attributes(
dad_name    IN         VARCHAR2,
attr_names  OUT NOCOPY VARCHAR2_TABLE,
attr_values OUT NOCOPY VARCHAR2_TABLE);
set serveroutput on

DECLARE
 name_list dbms_epg.varchar2_table;
 vals_list dbms_epg.varchar2_table;
BEGIN
  dbms_epg.get_all_dad_attributes('APEX', name_list, vals_list);

  FOR i IN 1..name_list.COUNT LOOP
    dbms_output.put_line(name_list(i) || '-' || vals_list(i));
  END LOOP;
END;
/
 
GET_ALL_DAD_MAPPINGS
Gets all virtual paths a DAD is mapped to dbms_epg.get_all_dad_mappings(
dad_name IN         VARCHAR2,
paths    OUT NOCOPY VARCHAR2_TABLE);
See AUTHORIZE_DAD Demo Above
 
GET_ALL_GLOBAL_ATTRIBUTES
Returns all global attributes and values dbms_epg.get_all_global_attributes(
attr_names  OUT NOCOPY VARCHAR2_TABLE,
attr_values OUT NOCOPY VARCHAR2_TABLE);
set serveroutput on

DECLARE
 name_list dbms_epg.varchar2_table;
 vals_list dbms_epg.varchar2_table;
BEGIN
  dbms_epg.get_all_global_attributes(name_list, vals_list);

  FOR i IN 1..name_list.COUNT LOOP
    dbms_output.put_line('-' || name_list(1) || '-');
    dbms_output.put_line('-' || vals_list(1) || '-');
  END LOOP;
END;
/
 
GET_DAD_ATTRIBUTE
Retrieves the value of a DAD attribute dbms_epg.get_dad_attribute(
dad_name  IN VARCHAR2,
attr_name IN VARCHAR2)
RETURN VARCHAR2;
SELECT dbms_epg.get_dad_attribute('UWDAD', 'database-username')
FROM dual;
 
GET_DAD_LIST
Returns a list of all DADs dbms_epg.get_dad_list(dad_names OUT NOCOPY VARCHAR2_TABLE);
See AUTHORIZE_DAD Demo Above
 
GET_GLOBAL_ATTRIBUTE
Gets a global attribute dbms_epg.get_global_attribute(attr_name IN VARCHAR2)
RETURN VARCHAR2;
SELECT dbms_epg.get_global_attribute('default-page')
FROM dual;
 
MAP_DAD
Maps a DAD to a virtual path. If the virtual path exists already, the virtual path will be mapped to the new DAD dbms_epg.map_dad(
dad_name IN VARCHAR2,
path     IN VARCHAR2);
BEGIN
  dbms_epg.unmap_dad('UWDAD', '/uwweb/*');

  dbms_epg.map_dad('UWDAD', '/uwweb/*');
END;
/
 
SET_DAD_ATTRIBUTE
Sets a DAD attribute dbms_epg.set_global_attribute(
attr_name  IN VARCHAR2,
attr_value IN VARCHAR2);
BEGIN
  dbms_epg.set_dad_attribute('UWDAD', 'database-username', 'SCOTT');
  execute immediate 'GRANT execute ON dbms_epg TO SCOTT';
END;
/
 
SET_GLOBAL_ATTRIBUTE
Sets a global attribute dbms_epg.set_global_attribute(
attr_name  IN VARCHAR2,
attr_value IN VARCHAR2);
BEGIN
  dbms_epg.set_global_attribute('database-username', 'SYSTEM');
END;
/
 
UNMAP_DAD
Unmaps a DAD from a virtual path. If the virtual path is NULL, unmap the DAD from all virtual paths. dbms_epg.unmap_dad(
dad_name IN VARCHAR2,
path     IN VARCHAR2 DEFAULT NULL);
See MAP_DAD Demo Above

Related Topics
DBMS_XDB
{ORACLE_HOME}/rdbms/admin/epgstat.sql
Packages
What's New In 12cR1
What's New In 12cR2

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-2017 Daniel A. Morgan All Rights Reserved