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 DBMS_EPG

Version 11.1
 
General Information
Note: 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.
Source {ORACLE_HOME}/rdbms/admin/dbmsepg.sql
First Available 2004

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

Attribute Name

database-username
default-page
document-table-name
document-path-docs
document-procedure
nls-language
request-validation
Data Types TYPE varchar2_table IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
Dependencies

DBA_EPG_DAD_AUTHORIZATION

DBMS_XDB DUAL PLITBLM
DBMS_SYS_ERROR DBMS_XMLDOM EPG$_AUTH XMLTYPE
Exceptions
Number Name Constant
-24231 dad_not_found dad_not_found_num
-24232 unknown_attribute unknown_attribute_num
-24240 invalid_dad_name invalid_dad_name_num
Security Model Execute is granted to PUBLIC
 
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
 
DEAUTHORIZE_DAD

Revoke authorization of a DAD
dbms_epg.deauthorize_dad(
dad_name IN VARCHAR2,
user     IN VARCHAR2 DEFAULT NULL);
BEGIN
  dbms_epg.authorize_dad('UWDAD', 'UWCLASS');
END;
/
 
DELETE_DAD_ATTRIBUTE

Deletes a DAD attribute
dbms_epg.delete_dad_attribute(
dad_name  IN VARCHAR2,
attr_name IN VARCHAR2);
BEGIN
  dbms_epg.deauthorize_dad('UWDAD', 'nls-language');
END;
/
 
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
 
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
 
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
 
Related Topics
 
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [228 users online]    © 2010 psoug.org