21 PL/SQL Access and Management of Data Using DBMS_XDB

This chapter describes the Oracle XML DB resource application program interface (API) for PL/SQL (DBMS_XDB) used for accessing and managing Oracle XML DB repository resources and data using PL/SQL. It includes methods for managing the resource security and Oracle XML DB configuration.

This chapter contains these topics:

Introducing Oracle XML DB Resource API for PL/SQL
Overview of DBMS_XDB
DBMS_XDB: Oracle XML DB Resource Management
DBMS_XDB: Oracle XML DB ACL-Based Security Management
DBMS_XDB: Oracle XML DB Configuration Management

Introducing Oracle XML DB Resource API for PL/SQL

This chapter describes the Oracle XML DB resource API for PL/SQL (PL/SQL package DBMS_XDB). This is also known as the PL/SQL foldering API.

Oracle XML DB repository is modeled on XML and provides a database file system for any data. Oracle XML DB repository maps path names (or URLs) onto database objects of XMLType and provides management facilities for these objects.

DBMS_XDB package provides functions and procedures for accessing and managing Oracle XML DB repository using PL/SQL.

See Also:

Overview of DBMS_XDB

The DBMS_XDB provides the PL/SQL application developer with an API that manages:

Oracle XML DB Resources
Oracle XML DB access control list (ACL) based Security. ACL is a list of access control entries that determines which principals have access to a given resource or resources.
Oracle XML DB Configuration
Oracle XML DB Hierarchical Index Rebuild

DBMS_XDB: Oracle XML DB Resource Management

Table 21-1 lists the DBMS_XDB Oracle XML DB resource management methods.

Table 21-1 DBMS_XDB Resource Management Methods

DBMS_XDB Method	Arguments, Return Values
`Link`	Argument: `(srcpath VARCHAR2, linkfolder VARCHAR2, linkname VARCHAR2)` Return value: N/A
LockResource	Argument: `(path IN VARCHAR2, depthzero IN BOOLEAN, shared IN boolean)` Return value: `TRUE` if successful.
`GetLockToken`	Argument: `(path IN VARCHAR2, locktoken OUT VARCHAR2)` Return value: N/A
`UnlockResource`	Argument: `(path IN VARCHAR2, deltoken IN VARCHAR2)` Return value: `TRUE` if successful.
`CreateResource`	`FUNCTION CreateResource (path IN VARCHAR2, data IN VARCHAR2) RETURN BOOLEAN;` Creates a new resource with the given string as its contents. `FUNCTION CreateResource (path IN VARCHAR2, data IN SYS.XMLTYPE) RETURN BOOLEAN;` Creates a new resource with the given XMLType data as its contents. `FUNCTION CreateResource (path IN VARCHAR2, datarow IN REF SYS.XMLTYPE) RETURN BOOLEAN;` Given a REF to an existing XMLType row, creates a resource whose contents point to that row. That row should not already exist inside another resource. `FUNCTION CreateResource (path IN VARCHAR2, data IN CLOB) RETURN BOOLEAN;` Creates a resource with the given CLOB as its contents. `FUNCTION CreateResource (abspath IN VARCHAR2, data IN BFILE, csid IN NUMBER := 0) RETURN BOOLEAN;` Creates an XML DB resource. For input data from `BFILE` or `BLOB`, an optional new parameter, `csid`, is added to the function to identify the character set of the input contents. `FUNCTION CreateResource (abspath IN VARCHAR2, data IN BLOB, csid IN NUMBER := 0) RETURN BOOLEAN;` For input data from `BFILE` or `BLOB`, an optional new parameter, `csid`, is added to the function to identify the character set of the input contents.
`CreateFolder`	Argument: `(path IN VARCHAR2)` Return value: `TRUE` if successful.
`DeleteResource`	Argument: `(path IN VARCHAR2)` Return value: N/A

The input character set id, if specified, must be a valid Oracle id; otherwise, an error is returned. This value if nonzero overrides any encoding specified in the source data. Otherwise, the character encoding of the resource is based on the MIME type which is derived from the abspath argument. If the MIME type is "*/xml" then the character encoding is determined by auto-detection as defined in Appendix F of the W3C XML Recommendation; otherwise, the character encoding of the input is defaulted to the database character set so that no conversion is applied. The character encoding of the data file is determined based on the following precedence:

The csid value if nonzero.
The MIME type based on the file extension specified in the abspath argument. If the MIME type is "*/xml" then the character encoding is determined by auto-detection, as defined in Appendix F of the W3C XML Recommendation. Otherwise, the character set of the BFILE or BLOB data is defaulted to the database character set.

Note:

the determination of the character set based on the MIME type is only done when creating a resource. Afterward, changing the MIME type of a resource, either through the RESOURCE_VIEW or XDBResource Java class, will not affect the character set property of the resource.

Use IANA character name for XML documents if possible. The character set id can be derived from an IANA name using: UTL_GDK.CHARSET_MAP and NLS_CHARSET_ID functions. For example:

Example 21-1 Deriving the Character Set ID From an IANA Name Using UTL_GDK.CHARSET_MAP

DECLARE
   oracs  VARCHAR2(255); 
   csid   NUMBER; 
BEGIN 
   oracs := UTL_GDK.CHARSET_MAP('EUC-JP', utl_gdk.IANA_TO_ORACLE); 
   csid := NLS_CHARSET_ID(oracs); 
END;

Using DBMS_XDB to Manage Resources, Calling Sequence

Figure 21-1 describes the calling sequence when using DBMS_XDB to manage repository resources:

When managing repository resources the calling sequence diagram assumes that the resources and folders already exist. If not, then you must create the resources using createResource() or create folders using createFolder()
- createResource() takes resource data and the resource path as parameters.
- createFolder() takes the resource path as a parameter.
If the resource or folder does not need additional processing or managing, then they are simply output.
If the resource or folder need additional processing or managing, then you can apply any or all of the following methods as listed in Table 21-1:
- Link()
- LockResource()
- GetLockToken()
- UnlockResource()
- DeleteResource()

See Example 21-2 for an examples of using DBMS_XDB to manage repository resources.

Figure 21-1 Using DBMS_XDB to Manage Resources: Calling Sequence

Description of the illustration adxdb040.gif

Example 21-2 Using DBMS_XDB to Manage Resources

DECLARE
   retb boolean;
BEGIN
   retb := dbms_xdb.createfolder('/public/mydocs');
   commit;
END;
/

declare
  bret boolean;
begin
  bret :=
dbms_xdb.createresource('/public/mydocs/emp_scott.xml','<emp_name>scott</emp_name>');
  commit;
end;
/

declare
  bret boolean;
begin
  bret :=
dbms_xdb.createresource('/public/mydocs/emp_david.xml','<emp_name>david</emp_name>');
  commit;
end;
/

call dbms_xdb.link('/public/mydocs/emp_scott.xml','/public/mydocs',
'person_scott.xml');
call dbms_xdb.link('/public/mydocs/emp_david.xml','/public/mydocs',
'person_david.xml');
commit;

call dbms_xdb.deleteresource('/public/mydocs/emp_scott.xml');
call dbms_xdb.deleteresource('/public/mydocs/person_scott.xml');
call dbms_xdb.deleteresource('/public/mydocs/emp_david.xml');
call dbms_xdb.deleteresource('/public/mydocs/person_david.xml');
call dbms_xdb.deleteresource('/public/mydocs');
commit;

DBMS_XDB: Oracle XML DB ACL-Based Security Management

Table 21-2 lists the DBMS_XDB Oracle XML DB ACL- based security management methods. Because the arguments and return values for the methods are self-explanatory, only a brief description of the methods is provided here.

Table 21-2 DBMS_XDB: Security Management Methods

DBMS_XDB Method	Arguments, Return Values
`getAclDocument`	Argument: `(abspath VARCHAR2)` Return value: `XMLType` for the ACL document
`ACLCheckPrivileges`	Argument: `(acl_path IN VARCHAR2, owner IN VARCHAR2, privs IN XMLType)` Return value: Positive integer if privileges are granted.
`checkPrivileges`	Argument: `(res_path IN VARCHAR2, privs IN XMLType)` Return value: Positive integer if privileges are granted.
`getprivileges`	Argument: `(res_path IN VARCHAR2)` Return value: `XMLType` instance of the `<privilege>` element.
`changePrivileges`	Argument: `(res_path IN VARCHAR2, ace IN XMLType)` Return value: Positive integer if ACL was successfully modified.
`setAcl`	Argument: `(res_path IN VARCHAR2, acl_path IN VARCHAR2)`. This sets the ACL of the resource at `res_path` to the ACL located at `acl_path`. Return value: N/A

Using DBMS_XDB to Manage Security, Calling Sequence

Figure 21-2 describes the calling sequence when using DBMS_XDB to manage security.

Each DBMS_XDB security management method take in a path (resource_path, abspath, or acl_path).
You can then use any or all of the DBMS_XDB methods listed in Table 21-2 to perform security management tasks:
- getAclDocument()
- ACLCheckPrivileges()
- checkPrivileges()
- getPrivileges()
- changePrivileges()
- setACL()

See Example 21-3 for an examples of using DBMS_XDB to manage repository resource security.

Figure 21-2 Using DBMS_XDB to Manage Security: Calling Sequence

Description of the illustration adxdb041.gif

Example 21-3 Using DBMS_XDB to Manage ACL-Based Security

DECLARE
   retb boolean;
BEGIN
   retb := dbms_xdb.createfolder('/public/mydocs');
   commit;
END;
/

declare
  bret boolean;
begin
  bret :=
dbms_xdb.createresource('/public/mydocs/emp_scott.xml','<emp_name>scott</emp_name>');
  commit;
end;
/

call dbms_xdb.setacl('/public/mydocs/emp_scott.xml',
'/sys/acls/all_owner_acl.xml');
commit;

select dbms_xdb.getacldocument('/public/mydocs/emp_scott.xml') from
dual;
declare
  r          pls_integer;
  ace        XMLType;
  ace_data   varchar2(2000);
begin
   ace_data := 
'<ace
      xmlns="http://xmlns.oracle.com/xdb/acl.xsd" 
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
      xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd  
                          http://xmlns.oracle.com/xdb/acl.xsd 
                          DAV:http://xmlns.oracle.com/xdb/dav.xsd"> 
    <principal>SCOTT</principal>
    <grant>true</grant>
    <privilege>
      <all/>
    </privilege>
 </ace>';
  ace := xmltype.createxml(ace_data);
  r := dbms_xdb.changeprivileges('/public/mydocs/emp_scott.xml', ace);
  dbms_output.put_line('retval = ' || r);
  commit;
end;
/

select dbms_xdb.getacldocument('/public/mydocs/emp_scott.xml') from
dual;
select dbms_xdb.getprivileges('/public/mydocs/emp_scott.xml') from dual;
call dbms_xdb.deleteresource('/public/mydocs/emp_scott.xml');
call dbms_xdb.deleteresource('/public/mydocs');
commit;

DBMS_XDB: Oracle XML DB Configuration Management

Table 21-3 lists the DBMS_XDB Oracle XML DB Configuration Management Methods. Because the arguments and return values for the methods are self-explanatory, only a brief description of the methods is provided here.

Table 21-3 DBMS_XDB: Configuration Management Methods

DBMS_XDB Method	Arguments, Return Value
`CFG_get`	Argument: None Return value: `XMLType` for session configuration information
`CFG_refresh`	Argument: None Return value: N/A
`CFG_update`	Argument: `(xdbconfig IN XMLType)` Return value: N/A

Using DBMS_XDB for Configuration Management, Calling Sequence

Figure 21-3 shows the calling sequence when using DBMS_XDB for configuration management.

The diagram shows the following sequence:

To manage the Oracle XML DB configuration, first retrieve the configuration instance using cfg_get.
You can then optionally modify the Oracle XML DB configuration XMLType instance to update it, or simply produce the Oracle XML DB configuration.
To update the Oracle XML DB configuration resource use cfg_update. You must either input a new Oracle XML DB configuration XMLType instance or use a modified version of the current configuration.
To refresh the Oracle XML DB configuration resource use cfg_refresh. You are not required to input a configuration XMLType instance.

See Example 21-4 for an example of using DBMS_XDB for configuration management of repository resources.

Figure 21-3 Using DBMS_XDB for Configuration Management: Calling Sequence

Description of the illustration adxdb043.gif

Example 21-4 Using DBMS_XDB for Configuration Management of Oracle XML DB

connect system/manager
select dbms_xdb.cfg_get() from dual;
declare
  config   XMLType;
begin
  config := dbms_xdb.cfg_get();
  -- Modify the xdb configuration using updatexml ...
  dbms_xdb.cfg_update(config);
end;
/

-- To pick up the latest Oracle XML DB Configuration
--   In this example it is not needed as cfg_update() 
--   automatically does a cfg_refresh().
call dbms_xdb.cfg_refresh();