Oracle XML_SCHEMA_NAME_PRESENT
Version 11.2.0.3
 
General Information
Source {ORACLE_HOME}/rdbms/admin/catxdbdv.sql & catxdbv.sql
First Available 10.1
Dependencies
ALL_ANNOTATION_TEXT_METADATA ALL_REPPROP
ALL_OBJECTS ALL_SDO_GEOM_METADATA
ALL_OBJECTS_AE ALL_STREAMS_COLUMNS
ALL_PROBE_OBJECTS ALL_STREAMS_NEWLY_SUPPORTED
ALL_PROCEDURES ALL_STREAMS_UNSUPPORTED
ALL_REPCATLOG ALL_XDS_ATTRIBUTE_SECS
ALL_REPCOLUMN ALL_XDS_OBJECTS
ALL_REPCOLUMN_GROUP USER_XDS_ATTRIBUTE_SECS
ALL_REPDDL XML_SCHEMA_NAME_PRESENT
ALL_REPFLAVOR_COLUMNS _ALL_REPCOLUMN
ALL_REPFLAVOR_OBJECTS _ALL_REPCOLUMN_GROUP
ALL_REPGENERATED _ALL_REPCONFLICT
ALL_REPGENOBJECTS _ALL_REPFLAVOR_OBJECTS
ALL_REPGROUPED_COLUMN _ALL_REPGROUPED_COLUMN
ALL_REPKEY_COLUMNS _ALL_REPL_NESTED_TABLE_NAMES
ALL_REPOBJECT _ALL_REPPARAMETER_COLUMN
ALL_REPPARAMETER_COLUMN _ALL_REPRESOLUTION
Security Model Owned by SYS with no granted privileges
 
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.1.
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.2.
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;
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: © 2012 Daniel A. Morgan All Rights Reserved