Oracle DBMS_EPG
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 12.1.0.1 to 12.1.0.2. 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.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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_SYS_ERROR DBMS_XMLDOM PLITBLM
DBMS_XDB DUAL XMLTYPE
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

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