Morgan's Library Oracle DBMS_METADATA_HACK Example Sample Code SQL PL/SQL

Oracle DBMS_METADATA_HACK
Version 11.2.0.3

General Information

Note

This package is undocumented and is not built during the default installation. Here's what it looks like when it is built.

SQL> @?/rdbms/admin/catxdbh.sql

SP2-0808: Package created with compilation warnings

Errors for PACKAGE DBMS_METADATA_HACK:

LINE/COL ERROR
-------- ------------------------------------------------------------------------------
    7/26 PLW-06010: keyword "NAME" used as a defined name
Type created.
SP2-0810: Package Body created with compilation warnings
    66/3 PLW-06006: uncalled procedure "GET_SCHEMA_DIR" is removed.
152/26 PLW-06010: keyword "NAME" used as a defined name
156/18 PLW-06009: procedure "DELETESCHEMA" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR
   174/3 PLW-06006: uncalled procedure "GET_XML_DIR" is removed.

Source

{ORACLE_HOME}/rdbms/admin/catxdbh.sql

First Available

10.2.0.1

Constants

Name	Data Type	Value
kolfuseslf	VARCHAR2(4000)	'FALSE'
XML_DIR	BINARY_INTEGER	1
SCHEMA_DIR	BINARY_INTEGER	2
RDBMS_DIR	DIRNAMESMH	DIRNAMESMH(NULL, 'schema')
LOGIC_DIR	DIRNAMESMH	DIRNAMESMH('XMLDIR', 'XSDDIR')
PLATFORM_WINDOWS32	BINARY_INTEGER	7
PLATFORM_WINDOWS64	BINARY_INTEGER	8
PLATFORM_OPENVMS	BINARY_INTEGER	15

Dependencies

DBMS_SYSTEM

DBMS_XMLSCHEMA

DIRNAMESMH

V$DATABASE

Security Model

Owned by SYS with no privileges granted when manually created.

Subprograms

CRE_DIR	DROP_DIR	GET_XML_BFILE
CRE_XML_DIR	DROP_XML_DIR	GET_XML_DIRNAME
DELETESCHEMA	GET_BFILE	LOAD_XSD

CRE_DIR

Undocumented
Assumes directory is rdbms/xml/schema

dbms_metadata_hack.cre_dir;

exec dbms_metadata_hack.cre_dir;

CRE_XML_DIR

Undocumented
needed for rdbms/xml access

dbms_metadata_hack.procedure cre_xml_dir;

exec dbms_metadata_hack.procedure cre_xml_dir;

DELETESCHEMA

Undocumented
Assumes directory is rdbms/xml/schema

dbms_metadata_hack.deleteSchema(name IN VARCHAR2);

TBD

DROP_DIR

Undocumented
Assumes directory is rdbms/xml/schema

dbms_metadata_hack.drop_dir;

exec dbms_metadata_hack.drop_dir;

DROP_XML_DIR

Undocumented
needed for rdbms/xml access

dbms_metadata_hack.drop_dir;

exec dbms_metadata_hack.drop_xml_dir;

GET_BFILE

Undocumented
Assumes directory is rdbms/xml/schema

dbms_metadata_hack.get_bfile(filename IN VARCHAR2) RETURN BFILE;

TBD

GET_XML_BFILE

Undocumented
Assumes directory is rdbms/xml/schema

dbms_metadata_hack.get_xml_bfile(filename IN VARCHAR2) RETURN BFILE;

TBD

GET_XML_DIRNAME

Undocumented
Assumes directory is rdbms/xml/schema

dbms_metadata_hack.get_xml_dirname RETURN VARCHAR2;

SELECT dbms_metadata_hack.get_xml_dirname
FROM dual;

LOAD_XSD

Undocumented
Assumes directory is rdbms/xml/schema

dbms_metadata_hack.load_xsd(filename IN VARCHAR2, gentypes1 IN BOOLEAN := FALSE);

TBD

Package Source Code

The only changes made to Oracle's source code is to format it better to improve readability.

CREATE OR REPLACE PACKAGE BODY dbms_metadata_hack AS
--------------------------------------------------------------------
-- PACKAGE STATE
--
kolfuseslf          VARCHAR2(4000) := 'FALSE';
XML_DIR    CONSTANT BINARY_INTEGER := 1;
SCHEMA_DIR CONSTANT BINARY_INTEGER := 2;

RDBMS_DIR CONSTANT DIRNAMESMH := DIRNAMESMH(NULL, 'schema');
LOGIC_DIR CONSTANT DIRNAMESMH := DIRNAMESMH('XMLDIR', 'XSDDIR');
-- XSDDIR: schema directory name
-- XMLDIR: xml doc directory name

-- Constants defined in rdbms/include/splatform3.h
PLATFORM_WINDOWS32 CONSTANT BINARY_INTEGER := 7;
PLATFORM_WINDOWS64 CONSTANT BINARY_INTEGER := 8;
PLATFORM_OPENVMS CONSTANT BINARY_INTEGER := 15;

---------------------------------------------------------------------
-- GET_DIR_INT: Helper function. Return the platform-
-- specific pathname for the rdbms/xml/`subdir` directory.
-- RETURNS:
-- - directory containing XML data/schemas

FUNCTION get_dir_int(subdir BINARY_INTEGER) RETURN VARCHAR2 IS
-- local variables
pfid    NUMBER;
root VARCHAR2(2000);
oraroot VARCHAR2(2000);
BEGIN
-- get the platform id
SELECT platform_id INTO pfid FROM v$database;

IF pfid = PLATFORM_OPENVMS THEN
    -- ORA_ROOT is a VMS logical name
   IF (subdir = XML_DIR) THEN
     oraroot := 'ORA_ROOT:[RDBMS.XML]';
   ELSE
     oraroot := 'ORA_ROOT:[RDBMS.XML.' || RDBMS_DIR(subdir) || ']';
   END IF;
   RETURN oraroot;
ELSE
   -- Get ORACLE_HOME
   DBMS_SYSTEM.GET_ENV('ORACLE_HOME', root);
   -- Return platform-specific string
   IF pfid = PLATFORM_WINDOWS32 OR pfid = PLATFORM_WINDOWS64 THEN
     IF (subdir = XML_DIR) THEN
        oraroot := root || '\rdbms\xml';
     ELSE
       oraroot := root || '\rdbms\xml\' || RDBMS_DIR(subdir);
     END IF;
     RETURN oraroot;
   ELSE
     IF (subdir = XML_DIR) THEN
       oraroot := root || '/rdbms/xml';
     ELSE
       oraroot := root || '/rdbms/xml/' || RDBMS_DIR(subdir);
     END IF;
     RETURN oraroot;
   END IF;
END IF;
END;

FUNCTION get_schema_dir RETURN VARCHAR2 IS
oraroot VARCHAR2(2000);
BEGIN
oraroot := get_dir_int(SCHEMA_DIR);
RETURN oraroot;
END;

procedure drop_dir_int(subdir BINARY_INTEGER) IS
stmt VARCHAR2(2000);
BEGIN
stmt := 'DROP DIRECTORY ' || LOGIC_DIR(subdir);
EXECUTE IMMEDIATE stmt;

-- alter session: disable use of symbolic links
-- (restore the variable to its prior value)
stmt := 'ALTER SESSION SET "_kolfuseslf" = ' || kolfuseslf;
EXECUTE IMMEDIATE stmt;
END;

procedure cre_dir_int(subdir BINARY_INTEGER) IS
-- local variables
kolfuseslf_cnt NUMBER := 0;
dirpath        VARCHAR2(2000);
stmt           VARCHAR2(2000);
BEGIN
-- alter session: enable use of symbolic links
-- first get the current value of _kolfuseslf (default FALSE)
stmt := 'SELECT COUNT(*) FROM V$PARAMETER WHERE NAME=''_kolfuseslf''';
EXECUTE IMMEDIATE stmt INTO kolfuseslf_cnt;

IF kolfuseslf_cnt != 0 THEN
   stmt := 'SELECT VALUE FROM V$PARAMETER WHERE NAME=''_kolfuseslf''';
   EXECUTE IMMEDIATE stmt INTO kolfuseslf;
END IF;

stmt := 'ALTER SESSION SET "_kolfuseslf" = TRUE';
EXECUTE IMMEDIATE stmt;

-- get directory path
dirpath := get_dir_int(subdir);

-- create a directory object
stmt := 'CREATE OR REPLACE DIRECTORY ' || LOGIC_DIR(subdir) || ' AS ''' || dirpath || '''';
EXECUTE IMMEDIATE stmt;
EXCEPTION WHEN OTHERS THEN
BEGIN
   drop_dir_int(subdir);
   RAISE;
END;
END;

procedure cre_dir IS
BEGIN
cre_dir_int(SCHEMA_DIR);
END;

procedure drop_dir IS
BEGIN
drop_dir_int(SCHEMA_DIR);
END;

FUNCTION get_bfile(filename IN VARCHAR2) RETURN BFILE IS
BEGIN
RETURN BFILENAME(LOGIC_DIR(SCHEMA_DIR), filename);
END;

PROCEDURE load_xsd(filename IN VARCHAR2, gentypes1 IN BOOLEAN := FALSE) IS
ssfile BFILE;
BEGIN
ssfile := BFILENAME(LOGIC_DIR(SCHEMA_DIR), filename);
dbms_xmlschema.registerSchema(filename, ssfile,TRUE,gentypes1,FALSE, FALSE);
EXCEPTION
WHEN OTHERS THEN
    BEGIN
      ROLLBACK;
      drop_dir;
      RAISE;
    END;
END;

PROCEDURE deleteSchema(name IN VARCHAR2) IS
err_num NUMBER;
BEGIN
dbms_xmlschema.deleteSchema(name, dbms_xmlschema.DELETE_CASCADE_FORCE);
EXCEPTION
WHEN OTHERS THEN
    BEGIN
      -- suppress expected exception
      -- ORA-31000: Resource '<name>' is not an XDB schema document
      err_num := SQLCODE;

      IF err_num != -31000 THEN
         RAISE;
      END IF;
    END;
END;

---------------------------------------------------------------------
/* GET_XML_DIR: Helper function. Return the platform specific pathname for the rdbms/xml directory.
RETURNS:
directory containing XML docs */

FUNCTION get_xml_dir RETURN VARCHAR2 IS
oraroot VARCHAR2(2000);
BEGIN
oraroot := get_dir_int(XML_DIR);
RETURN oraroot;
END;

PROCEDURE drop_xml_dir IS
BEGIN
drop_dir_int(XML_DIR);
END;

PROCEDURE cre_xml_dir IS
BEGIN
cre_dir_int(XML_DIR);
END;

FUNCTION get_xml_bfile(filename IN VARCHAR2) RETURN BFILE IS
BEGIN
RETURN bfilename(LOGIC_DIR(XML_DIR), filename);
END;

FUNCTION get_xml_dirname RETURN VARCHAR2 IS
BEGIN
RETURN LOGIC_DIR(XML_DIR);
END;
END dbms_metadata_hack;
/

Package creation generates the following PL/SQL warnings

Not a particularly brilliant bit of code

SQL> @?/rdbms/admin/catxdbh

SP2-0808: Package created with compilation warnings

Errors for PACKAGE DBMS_METADATA_HACK:

LINE/COL ERROR
-------- --------------------------------------------------
7/26      PLW-06010: keyword "NAME" used as a defined name
Type created.
SP2-0810: Package Body created with compilation warnings
Errors for PACKAGE BODY DBMS_METADATA_HACK:
66/3      PLW-06006: uncalled procedure "GET_SCHEMA_DIR" is removed.
152/26    PLW-06010: keyword "NAME" used as a defined name
156/18    PLW-06009: procedure "DELETESCHEMA" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR
174/3     PLW-06006: uncalled procedure "GET_XML_DIR" is removed.

Morgan's Library Page Footer

This site is maintained by Dan Morgan. Last Updated: