Rem Rem $Header: rdbms/admin/catmetx.sql /main/24 2009/01/23 12:36:03 htseng Exp $ Rem Rem catmetx.sql Rem Rem Copyright (c) 2001, 2009, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem catmetx.sql - Metadata API: Real definitions for XDB object views. Rem Rem DESCRIPTION Rem Metadata API object views for XDB objects Rem Rem NOTES Rem For reasons having to do with compatibility, the XDB objects Rem cannot be created by catproc.sql; they must instead be created Rem by a separate script catqm.sql. Since catmeta.sql is run Rem by catproc.sql, it contains fake object views for XDB objects. Rem The real object views are defined in this file which is Rem invoked by catxdbv.sql (which is invoked by catqm.sql). Rem Rem MODIFIED (MM/DD/YY) Rem htseng 01/21/09 - fix lrg 3756606 for recompile view Rem lbarton 12/05/08 - bug 5976472: sql inj Rem lbarton 07/01/08 - lrg 3465660 Rem lbarton 06/09/08 - move xmlschema registration to catmetx2.sql Rem lbarton 04/07/08 - add kuscomp.xsd Rem rapayne 02/08/08 - bug 6088114 - xmlschema for TYPE_SPECs Rem spetride 05/07/07 - bug 5950173 - xmlschema dependencies Rem sdavidso 04/17/07 - bug 5950173 - xmlschema dependencies Rem bmccarth 04/06/07 - validate args to ku$ view check procedure Rem before executing (bug 597642) Rem sdavidso 03/22/07 - 5903231 - maintain binary storage for xmlschemas Rem lbarton 01/04/07 - conditional compilation of differ code Rem lbarton 03/09/06 - bug 4918185: base xmlschema_view on catalog view Rem abagrawa 03/11/06 - Move dbms_metadata_hack to catxdbh Rem htseng 10/20/05 - register schemas Rem lbarton 09/06/05 - register schemas Rem vkapoor 05/18/05 - LRG 1798757. Freeing some memory Rem ataracha 10/21/04 - ku$_xmlschema_elmt_view - use the base element Rem - name for ref elements Rem spannala 05/20/04 - workaround for disabled xdbhi_idx Rem bmccarth 01/17/03 - add procedure to revalidate ku$ views if needed Rem bmccarth 11/15/02 - enable stripusername for url Rem amanikut 10/25/02 - specify namespace correctly in extractvalue() Rem bmccarth 08/22/02 - XDB 92->main merge - disable (for now) Rem call to stripschema in xdb utility package (not Rem available yet) Rem bmccarth 03/01/02 - add schemaoid to xmlschema Rem lbarton 01/16/02 - Merged lbarton_mdapi_xdb Rem lbarton 01/15/02 - fix comment Rem lbarton 12/03/01 - debug Rem lbarton 11/21/01 - Created Rem -- indexes get disabled on compilation of function. Workaround. alter package xdb.xdb_funcimpl compile; alter index xdb.xdbhi_idx rebuild; -- view for xmlschemas create or replace force view sys.ku$_xmlschema_view of sys.ku$_xmlschema_t with object identifier (schemaoid) as select '1','0', u.user#, u.name, x.schema_url, x.schema_id, (case when x.local='YES' then 1 else 0 end + case when x.binary='YES' then 2 else 0 end), xdb.dbms_xdbutil_int.XMLSchemaDependencyLevel(x.schema_id, u.name), x.schema.getClobVal(), xdb.dbms_xdbutil_int.XMLSchemaStripUsername(XMLTYPE( x.schema.getClobVal()), u.name) -- stripped from sys.user$ u, sys.dba_xml_schemas x where x.owner=u.name and (SYS_CONTEXT('USERENV','CURRENT_USERID') IN (u.user#, 0) OR EXISTS ( SELECT * FROM session_roles WHERE role='SELECT_CATALOG_ROLE' )) / grant select on sys.ku$_xmlschema_view to public / create or replace force view sys.ku$_xmlschema_elmt_view of sys.ku$_xmlschema_elmt_t with object identifier(schemaoid, elemnum) as select schm.sys_nc_oid$, extractValue(value(schm), '/schema/@x:schemaURL', 'xmlns="http://www.w3.org/2001/XMLSchema" xmlns:x="http://xmlns.oracle.com/xdb"'), extractValue(value(xel), '/element/@x:propNumber', 'xmlns="http://www.w3.org/2001/XMLSchema" xmlns:x="http://xmlns.oracle.com/xdb"'), case WHEN (extractValue(value(xel) , '/element/@name') is NULL)then xel.xmldata.property.propref_name.name else extractValue(value(xel) , '/element/@name') end from xdb.xdb$element xel, xdb.xdb$schema schm where ref(schm) = extractValue(value(xel), '/element/@x:parentSchema', 'xmlns="http://www.w3.org/2001/XMLSchema" xmlns:x="http://xmlns.oracle.com/xdb"') / grant select on sys.ku$_xmlschema_elmt_view to select_catalog_role / -- recompile dbms_metadata_int to enable the diffing code alter package dbms_metadata_int compile plsql_ccflags = 'ku$xml_enabled:true'; -- recompile dbms_metadata_util to enable the xmlschema load code alter package dbms_metadata_util compile plsql_ccflags = 'ku$xml_enabled:true'; Rem Rem During the XDB setup, several KU$_ views go invalid, recompile Rem them as needed. Rem (In particular, recompiling dbms_metadata_util causes views that Rem reference it to go invalid. - lrg 3465660) Rem CREATE OR REPLACE PROCEDURE recomp_catmeta_views AS TYPE cur_type is REF CURSOR; data_cursor cur_type; invalid_view VARCHAR2(100); sql_stmt_1 VARCHAR2(1000); sql_stmt_2 VARCHAR2(1000); my_status number; BEGIN -- -- Only get SYS-owned KU$_ views with invalid states -- OPEN data_cursor FOR 'SELECT name FROM SYS.OBJ$ WHERE STATUS > 1 ' || 'AND TYPE# = 4 AND owner#=0 AND name like ''KU$_%'''; sql_stmt_2 := 'select status from sys.obj$ where name = :1 and owner# = 0'; LOOP FETCH data_cursor INTO invalid_view; EXIT WHEN data_cursor%NOTFOUND; -- The compile of a view will automatically compile any other view which it -- references. If we compile a view that already has a success status it -- will invalidate any calling view. Therefore, check if the current view -- still needs to be compiled. execute immediate sql_stmt_2 into my_status using invalid_view; -- -- the 2 dbms_assert functions are to prevent sql injection -- if my_status > 1 THEN sql_stmt_1 := 'ALTER VIEW ' || dbms_assert.enquote_name(dbms_assert.qualified_sql_name(invalid_view)) || ' COMPILE'; EXECUTE IMMEDIATE sql_stmt_1; -- BEGIN -- EXECUTE IMMEDIATE sql_stmt_1; -- kupf$file.trace('GOOD', 'View complilation succeeded on ' || -- invalid_view); -- EXCEPTION -- WHEN OTHERS THEN -- IF SQLCODE = -24344 THEN -- NULL; -- kupf$file.trace('WARN', 'View complilation warning on ' || -- invalid_view); -- ELSE -- kupf$file.trace('FAIL', 'View failed to compile ' || -- invalid_view || ' ' || sqlerrm); -- RAISE; -- END IF; -- END; -- else -- kupf$file.trace('SKIP', 'skipping View ' || -- invalid_view); end if; END LOOP; CLOSE data_cursor; end recomp_catmeta_views; / alter system flush shared_pool; alter system flush shared_pool; alter system flush shared_pool; Rem Rem Execute then drop the procedure. Rem BEGIN recomp_catmeta_views; end; / drop procedure recomp_catmeta_views;