Oracle XML_SCHEMA_NAME

Oracle XML_SCHEMA_NAME_PRESENT Version 11.2.0.3

General Information

AUTHID

DEFINER

Dependencies

ALL_ANNOTATION_TEXT_METADATA	ALL_REPGENERATED	ALL_XDS_ATTRIBUTE_SECS
ALL_OBJECTS	ALL_REPGENOBJECTS	ALL_XDS_OBJECTS
ALL_OBJECTS_AE	ALL_REPGROUPED_COLUMN	USER_XDS_ATTRIBUTE_SECS
ALL_PROBE_OBJECTS	ALL_REPKEY_COLUMNS	_ALL_REPCOLUMN
ALL_PROCEDURES	ALL_REPOBJECT	_ALL_REPCOLUMN_GROUP
ALL_REPCATLOG	ALL_REPPARAMETER_COLUMN	_ALL_REPCONFLICT
ALL_REPCOLUMN	ALL_REPPROP	_ALL_REPFLAVOR_OBJECTS
ALL_REPCOLUMN_GROUP	ALL_SDO_GEOM_METADATA	_ALL_REPGROUPED_COLUMN
ALL_REPDDL	ALL_STREAMS_COLUMNS	_ALL_REPL_NESTED_TABLE_NAMES
ALL_REPFLAVOR_COLUMNS	ALL_STREAMS_NEWLY_SUPPORTED	_ALL_REPPARAMETER_COLUMN
ALL_REPFLAVOR_OBJECTS	ALL_STREAMS_UNSUPPORTED	_ALL_REPRESOLUTION

First Available

10.1

Security Model

Owned by SYS with no privileges granted

Source

{ORACLE_HOME}/rdbms/admin/catxdbdv.sql & catxdbv.sql

Subprograms

IS_SCHEMA_PRESENT

Undocumented - but here's the source code. Some of the slickest coding I've ever seen. <g>

This code is in catxdbdv.sql and not implemented in 11.1.0.6 through 11.2.0.3.

FUNCTION is_schema_present(objname IN VARCHAR2, userno IN NUMBER) RETURN NUMBER AS
BEGIN
RETURN 0;
END;

SELECT xml_schema_name_present.is_schema_present('Larry Ellison', 1)
FROM dual;

Undocumented - but here's the source code.

This code is in catxdbv.sql. It is this code that is implemented in 11.1.0.6.through 11.2.0.3.

function is_schema_present(objname IN VARCHAR2, userno IN NUMBER) RETURN NUMBER AS
sel_stmt VARCHAR2(4000);
tmp_num NUMBER;
BEGIN
sel_stmt := ' select count(*) ' ||
              ' from user$ u, xdb.xdb$schema s ' ||
              ' where u.user# = :1 ' ||
              ' and u.name = s.xmldata.schema_owner ' ||
              ' and (xdb.xdb$Extname2Intname(s.xmldata.schema_url,
              s.xmldata.schema_owner) = :2)';

EXECUTE IMMEDIATE sel_stmt INTO tmp_num USING userno, objname;

/* schema found */
IF (tmp_num > 0) THEN
    RETURN 1;
END IF;

sel_stmt := ' select count(*) '||
              ' from xdb.xdb$schema s ' ||
              ' where bitand(to_number(s.xmldata.flags, ''xxxxxxxx''), 16) = 16 ' ||
              ' and xdb.xdb$Extname2Intname( s.xmldata.schema_url,s.xmldata.schema_owner) = :1 ' ||
              ' and s.xmldata.schema_url ' ||
              ' not in (select s2.xmldata.schema_url ' ||
              ' from xdb.xdb$schema s2, user$ u2 ' ||
              ' where u2.user# = :2 ' ||
              ' and u2.name = s.xmldata.schema_owner) ';

EXECUTE IMMEDIATE sel_stmt INTO tmp_num USING objname, userno;

/* schema found */
IF (tmp_num > 0) THEN
    RETURN 1;
END IF;

RETURN 0;
END;

SELECT xml_schema_name_present.is_schema_present('Larry Ellison', 1)
FROM dual;