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 XML_SCHEMA_NAME_PRESENT

Version 11.1
 
General Information
Source {ORACLE_HOME}/rdbms/admin/catxdbdv.sql & catxdbv.sql

Dependencies
ALL_OBJECTS ALL_REPOBJECT
ALL_OBJECTS_AE ALL_REPPARAMETER_COLUMN
ALL_PROBE_OBJECTS ALL_REPPROP
ALL_PROCEDURES ALL_STREAMS_COLUMNS
ALL_REPCATLOG ALL_STREAMS_NEWLY_SUPPORTED
ALL_REPCOLUMN XML_SCHEMA_NAME_PRESENT
ALL_REPCOLUMN_GROUP _ALL_REPCOLUMN
ALL_REPDDL _ALL_REPCOLUMN_GROUP
ALL_REPFLAVOR_COLUMNS _ALL_REPCONFLICT
ALL_REPFLAVOR_OBJECTS _ALL_REPFLAVOR_OBJECTS
ALL_REPGENERATED _ALL_REPGROUPED_COLUMN
ALL_REPGENOBJECTS _ALL_REPL_NESTED_TABLE_NAMES
ALL_REPGROUPED_COLUMN _ALL_REPPARAMETER_COLUMN
ALL_REPKEY_COLUMNS _ALL_REPRESOLUTION
Security Model No privs assigned.
 
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

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. Some of the slickest coding I've ever seen. <g>

This code is in catxdbv.sql

It is this code that is implemented in 11.1.0.6.

function is_schema_present(objname IN VARCHAR22, 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;
 
Related Topics
 
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [99 users online]    © 2010 psoug.org