Rem
Rem $Header: rdbms/admin/xdbes111.sql /st_rdbms_11.2.0/1 2011/07/31 10:32:40 juding Exp $
Rem
Rem xdbes111.sql
Rem
Rem Copyright (c) 2007, 2011, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem xdbes111.sql - XDB Schema Downgrade
Rem
Rem DESCRIPTION
Rem This script downgrades XDB schemas to 11.1
Rem
Rem NOTES
Rem It is invoked from the top-level XDB downgrade script (xdbe111.sql)
Rem and from the 10.2 schema downgrade script (xdbes102.sql)
Rem
Rem MODIFIED (MM/DD/YY)
Rem vhosur 03/04/10 - Downgrade changes for Conatiner mutable
Rem spetride 07/30/09 - downgrade config for localApplicationGroupStore
Rem samane 05/28/09 - drop xdb.XDB_PITRIG_PKG_01 (security fixes)
Rem spetride 05/08/09 - downgrade XMLIndexMethods
Rem spetride 02/16/09 - downgrade for Expire headers in xdbconfig
Rem badeoti 03/02/09 - downgrade respond-with-server-info in httpcfg
Rem atabar 02/03/09 - xdbconfig downgrade: default-type-mappings
Rem spetride 02/02/09 - downgrade for realm under httpconfig
Rem spetride 06/11/08 - downgrade XDBCONFIG schema 11.2 to 11.1
Rem spetride 06/11/08 - downgrade ACL schema 11.2 to 11.1
Rem badeoti 05/07/08 - manual downgrade of xdbrescfg schema,
Rem rescfg SGA cache unable to build with copyEvolve
Rem attran 04/15/08 - De-support partitioning of XMLIndex
Rem bhammers 03/19/08 - add downgrade for XML Index
Rem yifeng 01/29/08 - lrg 3272185: delete the old resconfig schema from
Rem xdb$schema after copyevolve
Rem yifeng 12/10/07 - downgrade xdbrescfg schema
Rem rburns 11/06/07 - add XDB schema 11.1 downgrade
Rem rburns 11/06/07 - Created
Rem
Rem ================================================================
Rem BEGIN XDB Schema downgrade to 11.2.0
Rem ================================================================
-- uncomment for next release
--@@xdbes121.sql
@@xdbes112.sql
Rem ================================================================
Rem END XDB Schema downgrade to 11.2.0
Rem ================================================================
Rem ================================================================
Rem BEGIN XDB Schema downgrade to 11.1.0
Rem ================================================================
Rem Load upgrade utilities functions
@@xdbuuc4.sql
Rem Drop xlink-config/pre-condition property from XDB resource config schema
declare
seq_ref REF XMLTYPE;
elem_arr XDB.XDB$XMLTYPE_REF_LIST_T;
attr_arr XDB.XDB$XMLTYPE_REF_LIST_T;
config_schema_url VARCHAR2(100);
config_schema_ref REF XMLTYPE;
numb number(38) := 0;
elem_propno number(38);
last_elem_name varchar2(100);
anypart varchar2(4000);
appinf XDB.XDB$APPINFO_LIST_T;
currAppinf XDB.XDB$APPINFO_T;
begin
config_schema_url := 'http://xmlns.oracle.com/xdb/XDBResConfig.xsd';
select ref(s) into config_schema_ref
from xdb.xdb$schema s
where s.xmldata.schema_url = config_schema_url;
select c.xmldata.sequence_kid into seq_ref
from xdb.xdb$complex_type c
where c.xmldata.name = 'xlink-config'
and c.xmldata.parent_schema = config_schema_ref;
-- Get a list of all elements in this sequence
select m.xmldata.elements into elem_arr
from xdb.xdb$sequence_model m
where ref(m) = seq_ref;
-- determine position of pre-condition element
numb := elem_arr.last;
while numb <> 0 loop
select e.xmldata.property.name into last_elem_name from xdb.xdb$element e
where ref(e) = elem_arr(numb);
if last_elem_name = 'pre-condition' then
dbms_output.put_line('downgrading rescfg:xlink-config');
-- save prop number
select e.xmldata.property.prop_number into elem_propno from xdb.xdb$element e
where ref(e) = elem_arr(numb);
delete_elem_by_ref(elem_arr(numb));
EXIT;
end if;
numb := numb - 1;
end loop;
-- only update if pre-condition element was found
if numb > 0 then
-- splice off pre-condition element
while numb < elem_arr.last loop
elem_arr(numb) := elem_arr(numb + 1);
numb := numb + 1;
end loop;
elem_arr.trim(1);
-- update child element sequence for the xlink-config complex type
update xdb.xdb$sequence_model m
set m.xmldata.elements = elem_arr,
m.xmldata.sys_xdbpd$ = XDB.XDB$RAW_LIST_T('230200000081800307')
where ref(m) = seq_ref;
-- edit annotation kidlist, we assume only 1 kidlist in 11.1.0.6, might change in 11.1.0.7+
-- construct kidlist from element and attribute lists
anypart := '<xdb:kidList xmlns:xdb="http://xmlns.oracle.com/xdb" sequential="true">';
for i in 1..elem_arr.last loop
select e.xmldata.property.prop_number into elem_propno from xdb.xdb$element e
where ref(e) = elem_arr(i);
anypart := anypart || chr(10) || ' <xdb:kid propNum="' || elem_propno || '" kidNum="' || (i-1) || '"/>';
end loop;
select c.xmldata.attributes into attr_arr
from xdb.xdb$complex_type c
where c.xmldata.name = 'xlink-config'
and c.xmldata.parent_schema = config_schema_ref;
for i in 1..attr_arr.last loop
select e.xmldata.prop_number into elem_propno from xdb.xdb$attribute e
where ref(e) = attr_arr(i);
anypart := anypart || chr(10) || ' <xdb:kid propNum="' || elem_propno || '" kidNum="' || (elem_arr.last + i-1) || '"/>';
end loop;
anypart := anypart || chr(10) || '</xdb:kidList>';
update xdb.xdb$complex_type c
set c.xmldata.annotation.appinfo = XDB.XDB$APPINFO_LIST_T(XDB.XDB$APPINFO_T(XDB.XDB$RAW_LIST_T('1301000000'), anypart, NULL))
where c.xmldata.parent_schema = config_schema_ref
and c.xmldata.name = 'xlink-config';
-- no need to alter type since this is not an object type
end if;
commit;
end;
/
-- downgrade to 11.1 ACL schema: remove 'ApplicationName' principal format
declare
schema_url VARCHAR2(700) := 'http://xmlns.oracle.com/xdb/acl.xsd';
refs REF SYS.XMLTYPE;
aceattrs XDB.XDB$XMLTYPE_REF_LIST_T;
i NUMBER;
nm VARCHAR2(256);
aceattr REF SYS.XMLTYPE;
begin
select ref(s) into refs from xdb.xdb$schema s where s.xmldata.schema_url = schema_url;
-- find the list of attributes for the ace's complex type
select c.xmldata.attributes into aceattrs from xdb.xdb$complex_type c, xdb.xdb$element e, xdb.xdb$schema s
where ref(s) = refs and e.xmldata.property.parent_schema = refs and
e.xmldata.property.name ='ace' and e.xmldata.cplx_type_decl = ref(c);
for i in 1..aceattrs.last loop
select a.xmldata.name into nm from xdb.xdb$attribute a where ref(a)=aceattrs(i);
if (nm = 'principalFormat') then
-- update the simple type for principalFormat
update xdb.xdb$simple_type s
set s.xmldata.sys_xdbpd$ = XDB.XDB$RAW_LIST_T('23020000000106'),
s.xmldata.restriction = XDB.XDB$SIMPLE_DERIVATION_T(XDB.XDB$RAW_LIST_T('330008020000118B8004'), NULL,
XDB.XDB$QNAME('00', 'string'), NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
XDB.XDB$FACET_LIST_T(XDB.XDB$FACET_T(XDB.XDB$RAW_LIST_T('130200000102'),
NULL, 'ShortName', '00', NULL),
XDB.XDB$FACET_T(XDB.XDB$RAW_LIST_T('130200000102'),
NULL, 'DistinguishedName', '00', NULL),
XDB.XDB$FACET_T(XDB.XDB$RAW_LIST_T('130200000102'),
NULL, 'GUID', '00', NULL),
XDB.XDB$FACET_T(XDB.XDB$RAW_LIST_T('130200000102'),
NULL, 'XSName', '00', NULL)),
NULL, NULL)
where ref(s) = (select a.xmldata.smpl_type_decl from xdb.xdb$attribute a where ref(a)=aceattrs(i));
exit;
end if;
end loop;
commit;
end;
/
-------------------------------------------------------------
-- start downgrading xdbconfig for localApplicationGroupStore
-------------------------------------------------------------
create or replace procedure downgradeConfigGroupStore as
isfound BOOLEAN;
confsch_ref REF SYS.XMLTYPE;
simpletype_ref REF SYS.XMLTYPE;
elem_ref REF SYS.XMLTYPE;
cplx_ref REF SYS.XMLTYPE;
seq_ref REF SYS.XMLTYPE;
seq_elems XDB.XDB$XMLTYPE_REF_LIST_T;
elem_propnum NUMBER(38);
propnum NUMBER(38);
NUM_PROPS CONSTANT INTEGER := 203; --prop_num after downgrade
confsch_url VARCHAR2(700) := 'http://xmlns.oracle.com/xdb/xdbconfig.xsd';
anypart VARCHAR2(4000);
i NUMBER(38);
numprops NUMBER(38);
begin
-- ref for xdbconfig schema
select ref(s) into confsch_ref from xdb.xdb$schema s
where s.xmldata.schema_url = confsch_url;
-- num_props
select s.xmldata.num_props into numprops from xdb.xdb$schema s
where s.xmldata.schema_url = confsch_url;
-- already downgraded?
if (numprops <= NUM_PROPS) then
dbms_output.put_line('xdbconfig schema already downgraded');
return;
end if;
-- ref and prop num for the default-type-mappings element
select ref(e),
e.xmldata.property.prop_number
into elem_ref, elem_propnum
from xdb.xdb$element e
where e.xmldata.property.name='localApplicationGroupStore' and
e.xmldata.property.parent_schema = confsch_ref;
-- ref to the sysconfig element and its type
select e.xmldata.cplx_type_decl into cplx_ref
from xdb.xdb$element e
where e.xmldata.property.name='sysconfig' and
e.xmldata.property.parent_schema = confsch_ref;
-- ref to the sequence kid in the complex type for sysconfig
select c.xmldata.sequence_kid into seq_ref from xdb.xdb$complex_type c
where ref(c) = cplx_ref;
-- elements in the sequence
select m.xmldata.elements into seq_elems from xdb.xdb$sequence_model m
where ref(m)= seq_ref;
-- update annotation for the complex type declaration for sysconfig
-- (remove reference to default-type-mappings)
isfound := FALSE;
anypart := '<xdb:kidList xmlns:xdb="http://xmlns.oracle.com/xdb" sequential="true">';
for i in 1..seq_elems.last loop
select e.xmldata.property.prop_number into propnum
from xdb.xdb$element e
where ref(e) = seq_elems(i);
if (not (isfound)) then
if (propnum != elem_propnum) then
anypart := anypart || chr(10) || ' <xdb:kid propNum="' || propnum || '" kidNum="' || (i-1) || '"/>';
else
isfound := TRUE;
end if;
else
-- shift left
anypart := anypart || chr(10) || ' <xdb:kid propNum="' || propnum || '" kidNum="' || (i-2) || '"/>';
seq_elems(i-1) := seq_elems(i);
end if;
end loop;
anypart := anypart || chr(10) || '</xdb:kidList>';
seq_elems.trim(1);
update xdb.xdb$complex_type c
set c.xmldata.annotation.appinfo = XDB.XDB$APPINFO_LIST_T(XDB.XDB$APPINFO_T(XDB.XDB$RAW_LIST_T('1301000000'), anypart, NULL))
where c.xmldata.parent_schema = confsch_ref and ref(c) = cplx_ref;
-- update elements and PD for seq kid of sysconfig
update xdb.xdb$sequence_model m set m.xmldata.elements = seq_elems,
m.xmldata.sys_xdbpd$ = XDB.XDB$RAW_LIST_T('23020002000200182067656E65726963205844422070726F7065727469657320020E1E2070726F746F636F6C2073706563696669632070726F706572746965732081801B0')
where ref(m)= seq_ref;
-- update num_props for schema
update xdb.xdb$schema s set s.xmldata.num_props = NUM_PROPS
where s.xmldata.schema_url = confsch_url;
-- remove the default-type-mappings element
delete from xdb.xdb$element e where ref(e) = elem_ref;
commit;
end;
/
show errors;
exec downgradeConfigGroupStore;
-- clean up
drop procedure downgradeConfigGroupStore;
--------------------------------------------------
-- end downgrading xdbconfig localApplicationGroupStore
--------------------------------------------------
----------------------------------------------------
-- start downgrading xdbconfig for Expire Headers
----------------------------------------------------
create or replace procedure downgradeConfigForExpire as
schema_url VARCHAR2(700) := 'http://xmlns.oracle.com/xdb/xdbconfig.xsd';
refs REF SYS.XMLTYPE;
idem NUMBER := 0;
numprops NUMBER(38);
CONFIG_PRPONUMS_NOEXP CONSTANT INTEGER := 199;
exppatnum NUMBER(38);
exppatelem REF SYS.XMLTYPE;
expdeftype REF SYS.XMLTYPE;
expdefnum NUMBER(38);
expdefelem REF SYS.XMLTYPE;
skidexpmap XDB.XDB$XMLTYPE_REF_LIST_T;
refskidmap REF SYS.XMLTYPE;
anypart VARCHAR2(4000);
i NUMBER;
expmaptype REF SYS.XMLTYPE;
expmapnum NUMBER(38);
expmapelem REF SYS.XMLTYPE;
skidexp XDB.XDB$XMLTYPE_REF_LIST_T;
refskidexp REF SYS.XMLTYPE;
exptype REF SYS.XMLTYPE;
expnum NUMBER(38);
expelem REF SYS.XMLTYPE;
refhttptype REF SYS.XMLTYPE;
refskidhttp REF SYS.XMLTYPE;
skidhttpelems XDB.XDB$XMLTYPE_REF_LIST_T;
elem_propno NUMBER(38);
clistinsch XDB.XDB$XMLTYPE_REF_LIST_T;
isfound BOOLEAN := FALSE;
typenm VARCHAR2(256);
begin
select ref(s) into refs from xdb.xdb$schema s
where s.xmldata.schema_url = schema_url;
select s.xmldata.num_props into numprops from xdb.xdb$schema s
where s.xmldata.schema_url = schema_url;
-- if at least one of the expire elements or types do not exist,
-- then this is a rerun, so return
select count(*) into idem from xdb.xdb$complex_type c
where c.xmldata.name='expire-type' and c.xmldata.parent_schema=refs;
if (idem < 1) then
dbms_output.put_line('xdbconfig schema already downgraded for expire headers');
return;
end if;
-- get a ref to the expire-pattern element
select ref(e) into exppatelem from xdb.xdb$element e
where e.xmldata.property.name='expire-pattern' and e.xmldata.property.parent_schema=refs;
dbms_output.put_line('1. got ref to expire-pattern');
-- get refs to the expire-default element and its simple type
select ref(e), e.xmldata.property.smpl_type_decl into expdefelem, expdeftype
from xdb.xdb$element e
where e.xmldata.property.name='expire-default' and e.xmldata.property.parent_schema=refs;
dbms_output.put_line('2. got refs for expire-default and its type');
-- get refs to expire-mapping element, its type and the sequence kid
select ref(e), e.xmldata.cplx_type_decl, c.xmldata.sequence_kid
into expmapelem, expmaptype, refskidmap
from xdb.xdb$element e, xdb.xdb$complex_type c
where e.xmldata.property.name='expire-mapping' and e.xmldata.property.parent_schema=refs
and e.xmldata.cplx_type_decl = ref(c);
dbms_output.put_line('3. got refs to expire-mapping, its type and sequence kid');
-- get refs to expire element, expire-type and its sequence kid, and propnum for expire
select ref(e), e.xmldata.property.prop_number into expelem, expnum
from xdb.xdb$element e
where e.xmldata.property.name='expire' and e.xmldata.property.parent_schema=refs;
select ref(c), c.xmldata.sequence_kid into exptype, refskidexp
from xdb.xdb$complex_type c
where c.xmldata.name='expire-type' and c.xmldata.parent_schema = refs;
dbms_output.put_line('4. gor refs to expire element, its type and sequence-kid');
-- update elements and PD for seq kid of httpconfig, update annotation
select e.xmldata.cplx_type_decl into refhttptype from xdb.xdb$element e
where e.xmldata.property.name ='httpconfig' and e.xmldata.property.parent_schema = refs;
select c.xmldata.sequence_kid into refskidhttp from xdb.xdb$complex_type c
where ref(c) = refhttptype;
select m.xmldata.elements into skidhttpelems from xdb.xdb$sequence_model m where ref(m)= refskidhttp;
anypart := '<xdb:kidList xmlns:xdb="http://xmlns.oracle.com/xdb" sequential="true">';
for i in 1..skidhttpelems.last loop
select e.xmldata.property.prop_number into elem_propno from xdb.xdb$element e
where ref(e) = skidhttpelems(i);
if (not (isfound)) then
if (elem_propno != expnum) then
anypart := anypart || chr(10) || ' <xdb:kid propNum="' || elem_propno || '" kidNum="' || (i-1) || '"/>';
else
isfound := TRUE;
end if;
else
-- shift left
anypart := anypart || chr(10) || ' <xdb:kid propNum="' || elem_propno || '" kidNum="' || (i-2) || '"/>';
skidhttpelems(i-1) := skidhttpelems(i);
end if;
end loop;
anypart := anypart || chr(10) || '</xdb:kidList>';
skidhttpelems.trim(1);
update xdb.xdb$complex_type c
set c.xmldata.annotation = XDB.XDB$ANNOTATION_T(XDB.XDB$RAW_LIST_T('1301000000'),
XDB.XDB$APPINFO_LIST_T(XDB.XDB$APPINFO_T(XDB.XDB$RAW_LIST_T('1301000000'),
anypart, NULL)
),
NULL)
where c.xmldata.parent_schema = refs and ref(c)=refhttptype;
dbms_output.put_line('5. updated annotation for httpconfig type');
update xdb.xdb$sequence_model m set m.xmldata.elements = skidhttpelems,
m.xmldata.sys_xdbpd$ = XDB.XDB$RAW_LIST_T('230200000081801607')
where ref(m)= refskidhttp;
dbms_output.put_line('6. updated sequence kid and pd for httpconfig');
commit;
-- remove expire-type from complex_types list in schema
select s.xmldata.complex_types into clistinsch from xdb.xdb$schema s where s.xmldata.schema_url = schema_url;
isfound := FALSE;
for i in 1..clistinsch.last loop
select c.xmldata.name into typenm from xdb.xdb$complex_type c where ref(c) = clistinsch(i);
if (not (isfound)) then
if (typenm = 'expire-type') then
isfound := TRUE;
end if;
else
-- shift left
clistinsch(i-1) := clistinsch(i);
end if;
end loop;
clistinsch.trim(1);
dbms_output.put_line('7. removed expire-type from schema type list');
-- update PD and num props for schema
update xdb.xdb$schema s set s.xmldata.complex_types = clistinsch,
s.xmldata.sys_xdbpd$ = XDB.XDB$RAW_LIST_T('43163C8600050084010084020184030202081820637573746F6D697A6564206572726F7220706167657320020A3E20706172616D6574657220666F72206120736572766C65743A206E616D652C2076616C7565207061697220616E642061206465736372697074696F6E20200B0C110482800B818002828003131416120A170D'),
s.xmldata.num_props = CONFIG_PRPONUMS_NOEXP
where s.xmldata.schema_url = schema_url;
dbms_output.put_line('8. updated pd$ and num props fro schema');
commit;
-- now to the cleanup
delete from xdb.xdb$element e where ref(e)=expelem;
delete from xdb.xdb$complex_type c where ref(c)=exptype;
delete from xdb.xdb$sequence_model m where ref(m)=refskidexp;
dbms_output.put_line('9. cleanup for expire element');
delete from xdb.xdb$element e where ref(e)=expmapelem;
delete from xdb.xdb$complex_type c where ref(c)=expmaptype;
delete from xdb.xdb$sequence_model m where ref(m)=refskidmap;
dbms_output.put_line('10. cleanup for expire-mapping element');
delete from xdb.xdb$element e where ref(e)=expdefelem;
delete from xdb.xdb$simple_type st where ref(st)=expdeftype;
dbms_output.put_line('11. cleanup for expire-default element');
delete from xdb.xdb$element e where ref(e)=exppatelem;
dbms_output.put_line('12. cleanup for expire-pattern element');
commit;
end;
/
show errors;
exec downgradeConfigForExpire;
-- clean-up
drop procedure downgradeConfigForExpire;
----------------------------------------------------
-- start downgrading xdbconfig default-type-mappings
----------------------------------------------------
-- removing element /sysconfig/default-type-mappings
-- Note: Downgrading more sysconfig elements should come after this
-- procedure.
-- Please be careful about num_props and PD changes after
-- this procedure execution.
create or replace procedure downgradeConfigDTM as
isfound BOOLEAN;
confsch_ref REF SYS.XMLTYPE;
simpletype_ref REF SYS.XMLTYPE;
elem_ref REF SYS.XMLTYPE;
cplx_ref REF SYS.XMLTYPE;
seq_ref REF SYS.XMLTYPE;
seq_elems XDB.XDB$XMLTYPE_REF_LIST_T;
elem_propnum NUMBER(38);
propnum NUMBER(38);
NUM_PROPS CONSTANT INTEGER := 198; --prop_num after downgrade
confsch_url VARCHAR2(700) := 'http://xmlns.oracle.com/xdb/xdbconfig.xsd';
anypart VARCHAR2(4000);
i NUMBER(38);
numprops NUMBER(38);
begin
-- ref for xdbconfig schema
select ref(s) into confsch_ref from xdb.xdb$schema s
where s.xmldata.schema_url = confsch_url;
-- num_props
select s.xmldata.num_props into numprops from xdb.xdb$schema s
where s.xmldata.schema_url = confsch_url;
-- already downgraded?
if (numprops <= NUM_PROPS) then
dbms_output.put_line('xdbconfig schema already downgraded');
return;
end if;
-- ref and prop num for the default-type-mappings element
select ref(e),
e.xmldata.property.prop_number,
e.xmldata.property.smpl_type_decl
into elem_ref, elem_propnum, simpletype_ref
from xdb.xdb$element e
where e.xmldata.property.name='default-type-mappings' and
e.xmldata.property.parent_schema = confsch_ref;
-- ref to the sysconfig element and its type
select e.xmldata.cplx_type_decl into cplx_ref
from xdb.xdb$element e
where e.xmldata.property.name='sysconfig' and
e.xmldata.property.parent_schema = confsch_ref;
-- ref to the sequence kid in the complex type for sysconfig
select c.xmldata.sequence_kid into seq_ref from xdb.xdb$complex_type c
where ref(c) = cplx_ref;
-- elements in the sequence
select m.xmldata.elements into seq_elems from xdb.xdb$sequence_model m
where ref(m)= seq_ref;
-- update annotation for the complex type declaration for sysconfig
-- (remove reference to default-type-mappings)
isfound := FALSE;
anypart := '<xdb:kidList xmlns:xdb="http://xmlns.oracle.com/xdb" sequential="true">';
for i in 1..seq_elems.last loop
select e.xmldata.property.prop_number into propnum
from xdb.xdb$element e
where ref(e) = seq_elems(i);
if (not (isfound)) then
if (propnum != elem_propnum) then
anypart := anypart || chr(10) || ' <xdb:kid propNum="' || propnum || '" kidNum="' || (i-1) || '"/>';
else
isfound := TRUE;
end if;
else
-- shift left
anypart := anypart || chr(10) || ' <xdb:kid propNum="' || propnum || '" kidNum="' || (i-2) || '"/>';
seq_elems(i-1) := seq_elems(i);
end if;
end loop;
anypart := anypart || chr(10) || '</xdb:kidList>';
seq_elems.trim(1);
update xdb.xdb$complex_type c
set c.xmldata.annotation.appinfo = XDB.XDB$APPINFO_LIST_T(XDB.XDB$APPINFO_T(XDB.XDB$RAW_LIST_T('1301000000'), anypart, NULL))
where c.xmldata.parent_schema = confsch_ref and ref(c) = cplx_ref;
-- update elements and PD for seq kid of sysconfig
update xdb.xdb$sequence_model m set m.xmldata.elements = seq_elems,
m.xmldata.sys_xdbpd$ = XDB.XDB$RAW_LIST_T('23020002000200182067656E65726963205844422070726F7065727469657320020E1E2070726F746F636F6C2073706563696669632070726F706572746965732081801A07')
where ref(m)= seq_ref;
-- update num_props for schema
update xdb.xdb$schema s set s.xmldata.num_props = NUM_PROPS
where s.xmldata.schema_url = confsch_url;
-- remove the default-type-mappings element
delete from xdb.xdb$element e where ref(e) = elem_ref;
-- remove annonymous simple type
delete from xdb.xdb$simple_type st where ref(st) = simpletype_ref;
commit;
end;
/
show errors;
exec downgradeConfigDTM;
-- clean up
drop procedure downgradeConfigDTM;
--------------------------------------------------
-- end downgrading xdbconfig default-type-mappings
--------------------------------------------------
-- downgrade to 11.1 CONFIG schema
-- this is the downgrade for custom authentication and trust,
-- as well as the 'realm' element under httpconfig
create or replace procedure downgradeConfig as
schema_url VARCHAR2(700) := 'http://xmlns.oracle.com/xdb/xdbconfig.xsd';
refs REF SYS.XMLTYPE;
idem NUMBER := 0;
CONFIG_PRPONUMS_DOWN CONSTANT INTEGER := 171;
numprops NUMBER(38);
refauthpat REF SYS.XMLTYPE;
authpatnum NUMBER(38);
refauthname1 REF SYS.XMLTYPE;
authnamenum1 NUMBER(38);
refskidmap REF SYS.XMLTYPE;
anypart VARCHAR2(4000);
i NUMBER(38);
refmaptype REF SYS.XMLTYPE;
refmap REF SYS.XMLTYPE;
mapnum NUMBER(38);
refskidmaps REF SYS.XMLTYPE;
refmapstype REF SYS.XMLTYPE;
refmaps REF SYS.XMLTYPE;
mapsnum NUMBER(38);
authnamenum2 NUMBER(38);
refauthname2 REF SYS.XMLTYPE;
descrnum NUMBER(38);
refdescr REF SYS.XMLTYPE;
implnum NUMBER(38);
refimpl REF SYS.XMLTYPE;
methodnum NUMBER(38);
refmethod REF SYS.XMLTYPE;
langnum NUMBER(38);
reflang REF SYS.XMLTYPE;
reflangtype REF SYS.XMLTYPE;
refskidauth REF SYS.XMLTYPE;
refauthtype REF SYS.XMLTYPE;
authnum NUMBER(38);
refauth REF SYS.XMLTYPE;
refskidauthlist REF SYS.XMLTYPE;
refauthlisttype REF SYS.XMLTYPE;
authlistnum NUMBER(38);
refauthlist REF SYS.XMLTYPE;
refskidcauthtype REF SYS.XMLTYPE;
refcauthtype REF SYS.XMLTYPE;
cauthnum NUMBER(38);
refcauth REF SYS.XMLTYPE;
refskidcauth REF SYS.XMLTYPE;
refhttp REF SYS.XMLTYPE;
refhttptype REF SYS.XMLTYPE;
refskidhttp REF SYS.XMLTYPE;
elem_propno NUMBER(38);
isfound BOOLEAN := FALSE;
typenm VARCHAR2(256);
refctrusttype REF SYS.XMLTYPE;
refallowtrust REF SYS.XMLTYPE;
allowtrustnum NUMBER(38);
refsys REF SYS.XMLTYPE;
refsystype REF SYS.XMLTYPE;
refskidsys REF SYS.XMLTYPE;
propname VARCHAR2(256);
refrealm REF SYS.XMLTYPE;
realmnum NUMBER(38);
refsrvinfo REF SYS.XMLTYPE;
srvinfonum NUMBER(38);
skidhttpelems XDB.XDB$XMLTYPE_REF_LIST_T;
skidsyselems XDB.XDB$XMLTYPE_REF_LIST_T;
clistinsch XDB.XDB$XMLTYPE_REF_LIST_T;
reftrustschtyp REF SYS.XMLTYPE;
refskidtrustelems REF SYS.XMLTYPE;
refcauthtrusttyp REF SYS.XMLTYPE;
refskidtrustschs REF SYS.XMLTYPE;
refskidclistelems XDB.XDB$XMLTYPE_REF_LIST_T;
ref_ondeny_typ REF SYS.XMLTYPE;
ref_ondeny REF SYS.XMLTYPE;
begin
select ref(s) into refs from xdb.xdb$schema s
where s.xmldata.schema_url = schema_url;
select s.xmldata.num_props into numprops from xdb.xdb$schema s
where s.xmldata.schema_url = schema_url;
-- if at least one of the custom-auth elements or types do not exist,
-- then this is a rerun, so return
select count(*) into idem from xdb.xdb$complex_type c
where c.xmldata.name='custom-authentication-trust-type'
and c.xmldata.parent_schema=refs;
if (idem < 1) then
dbms_output.put_line('xdbconfig schema already downgrated');
return;
end if;
-- save type and seq kid for custom-authentication-mapping
select e.xmldata.cplx_type_decl, c.xmldata.sequence_kid
into refmaptype, refskidmap
from xdb.xdb$element e, xdb.xdb$complex_type c
where e.xmldata.property.name='custom-authentication-mapping'
and e.xmldata.property.parent_schema=refs
and e.xmldata.cplx_type_decl = ref(c);
-- save type and seq kid for custom-authentication-mappings
select e.xmldata.cplx_type_decl, c.xmldata.sequence_kid
into refmapstype, refskidmaps
from xdb.xdb$element e, xdb.xdb$complex_type c
where e.xmldata.property.name='custom-authentication-mappings'
and e.xmldata.property.parent_schema=refs
and e.xmldata.cplx_type_decl = ref(c) ;
-- save type for authentication-implement-language
select e.xmldata.property.type_ref into reflangtype
from xdb.xdb$element e
where e.xmldata.property.name='authentication-implement-language'
and e.xmldata.property.parent_schema=refs;
-- save complex type declaration and seq kid for custom-authentication-list
select e.xmldata.cplx_type_decl, c.xmldata.sequence_kid, m.xmldata.elements
into refauthlisttype, refskidauthlist, refskidclistelems
from xdb.xdb$element e, xdb.xdb$complex_type c, xdb.xdb$sequence_model m
where e.xmldata.property.name='custom-authentication-list'
and e.xmldata.property.parent_schema=refs
and e.xmldata.cplx_type_decl = ref(c)
and c.xmldata.sequence_kid = ref(m);
refcauth := refskidclistelems(1);
select e.xmldata.cplx_type_decl, c.xmldata.sequence_kid
into refauthtype, refskidauth
from xdb.xdb$element e, xdb.xdb$complex_type c
where ref(e)=refcauth and e.xmldata.cplx_type_decl = ref(c);
-- save ref, prop num, complex type declaration and seq kid for custom-authentication
select ref(e), e.xmldata.property.prop_number
into refcauth, cauthnum
from xdb.xdb$element e
where e.xmldata.property.name='custom-authentication'
and e.xmldata.property.parent_schema=refs;
select ref(c), c.xmldata.sequence_kid
into refcauthtype, refskidcauthtype
from xdb.xdb$complex_type c
where c.xmldata.name='custom-authentication-type'
and c.xmldata.parent_schema = refs;
-- save complex_type declaration and seq kid for trust-scheme
select e.xmldata.cplx_type_decl, c.xmldata.sequence_kid
into reftrustschtyp, refskidtrustelems
from xdb.xdb$element e, xdb.xdb$complex_type c
where e.xmldata.property.name='trust-scheme'
and e.xmldata.property.parent_schema=refs
and e.xmldata.cplx_type_decl = ref(c);
-- save complex_type declaration and seq kid for
-- custom-authentication-trust
select ref(c), c.xmldata.sequence_kid
into refcauthtrusttyp, refskidtrustschs
from xdb.xdb$complex_type c
where c.xmldata.name='custom-authentication-trust-type'
and c.xmldata.parent_schema=refs;
-- ref to the httpconfig element and its type
select ref(e), e.xmldata.cplx_type_decl into refhttp, refhttptype from xdb.xdb$element e
where e.xmldata.property.name='httpconfig' and e.xmldata.property.parent_schema = refs;
-- ref to the sequence kid in the complex type for httpconfig
select c.xmldata.sequence_kid into refskidhttp from xdb.xdb$complex_type c
where ref(c) = refhttptype;
-- elements in the sequence
select m.xmldata.elements into skidhttpelems from xdb.xdb$sequence_model m where ref(m)= refskidhttp;
-- ref and prop num for the allow-authentication-trust element
select ref(e), e.xmldata.property.prop_number into refallowtrust, allowtrustnum from xdb.xdb$element e
where e.xmldata.property.name='allow-authentication-trust' and e.xmldata.property.parent_schema = refs;
-- ref to the sysconfig element and its type
select ref(e), e.xmldata.cplx_type_decl into refsys, refsystype from xdb.xdb$element e
where e.xmldata.property.name='sysconfig' and e.xmldata.property.parent_schema = refs;
-- ref to the sequence kid in the complex type for sysconfig
select c.xmldata.sequence_kid into refskidsys from xdb.xdb$complex_type c
where ref(c) = refsystype;
-- elements in the sequence
select m.xmldata.elements into skidsyselems from xdb.xdb$sequence_model m where ref(m)= refskidsys;
-- ref and prop num for the realm element
select ref(e), e.xmldata.property.prop_number into refrealm, realmnum from xdb.xdb$element e
where e.xmldata.property.name='realm' and e.xmldata.property.parent_schema = refs;
-- ref and prop num for the respond-with-server-info element
select ref(e), e.xmldata.property.prop_number into refsrvinfo, srvinfonum from xdb.xdb$element e
where e.xmldata.property.name='respond-with-server-info'
and e.xmldata.property.parent_schema = refs;
-- ref to the on-deny element and its simple type
select ref(e), e.xmldata.property.smpl_type_decl into ref_ondeny, ref_ondeny_typ
from xdb.xdb$element e
where e.xmldata.property.name='on-deny' and e.xmldata.property.parent_schema = refs;
-- remove 'custom' for 'allow-mechanism'
-- Note: if more than one 'allow-mechanism' subelemnts will ever be added to the CONFIG schema,
-- change this code to go through the kids of httpconfig, find 'authentication', and pick
-- the 'allow-mechanism' in the authentication kids
update xdb.xdb$simple_type t
set t.xmldata.sys_xdbpd$ = XDB.XDB$RAW_LIST_T('23020000000106'),
t.xmldata.restriction = XDB.XDB$SIMPLE_DERIVATION_T(XDB.XDB$RAW_LIST_T('330008020000118B8002'), NULL,
XDB.XDB$QNAME('00', 'string'), NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
XDB.XDB$FACET_LIST_T(XDB.XDB$FACET_T(XDB.XDB$RAW_LIST_T('130200000102'),
NULL, 'digest', '00', NULL),
XDB.XDB$FACET_T(XDB.XDB$RAW_LIST_T('130200000102'),
NULL, 'basic', '00', NULL)),
NULL, NULL)
where ref(t) = (select e.xmldata.property.smpl_type_decl from xdb.xdb$element e
where e.xmldata.property.name ='allow-mechanism' and e.xmldata.property.parent_schema = refs);
-- update annotation for the complex type declaration for httpconfig
-- This step and the two below can be much simplified
-- if we are guaranteed custom-auth, realm and srvinfo are
-- the last 3 kids in the sequence, until then we follow a very safe approach
-- remove reference to srvinfo
isfound := FALSE;
anypart := '<xdb:kidList xmlns:xdb="http://xmlns.oracle.com/xdb" sequential="true">';
for i in 1..skidhttpelems.last loop
select e.xmldata.property.prop_number into elem_propno from xdb.xdb$element e
where ref(e) = skidhttpelems(i);
if (not (isfound)) then
if (elem_propno != srvinfonum) then
anypart := anypart || chr(10) || ' <xdb:kid propNum="' || elem_propno || '" kidNum="' || (i-1) || '"/>';
else
isfound := TRUE;
end if;
else
-- shift left
anypart := anypart || chr(10) || ' <xdb:kid propNum="' || elem_propno || '" kidNum="' || (i-2) || '"/>';
skidhttpelems(i-1) := skidhttpelems(i);
end if;
end loop;
anypart := anypart || chr(10) || '</xdb:kidList>';
skidhttpelems.trim(1);
-- remove reference to realm
isfound := FALSE;
anypart := '<xdb:kidList xmlns:xdb="http://xmlns.oracle.com/xdb" sequential="true">';
for i in 1..skidhttpelems.last loop
select e.xmldata.property.prop_number into elem_propno from xdb.xdb$element e
where ref(e) = skidhttpelems(i);
if (not (isfound)) then
if (elem_propno != realmnum) then
anypart := anypart || chr(10) || ' <xdb:kid propNum="' || elem_propno || '" kidNum="' || (i-1) || '"/>';
else
isfound := TRUE;
end if;
else
-- shift left
anypart := anypart || chr(10) || ' <xdb:kid propNum="' || elem_propno || '" kidNum="' || (i-2) || '"/>';
skidhttpelems(i-1) := skidhttpelems(i);
end if;
end loop;
anypart := anypart || chr(10) || '</xdb:kidList>';
skidhttpelems.trim(1);
-- remove reference to custom-authentication
isfound := FALSE;
anypart := '<xdb:kidList xmlns:xdb="http://xmlns.oracle.com/xdb" sequential="true">';
for i in 1..skidhttpelems.last loop
select e.xmldata.property.prop_number into elem_propno from xdb.xdb$element e
where ref(e) = skidhttpelems(i);
if (not (isfound)) then
if (elem_propno != cauthnum) then
anypart := anypart || chr(10) || ' <xdb:kid propNum="' || elem_propno || '" kidNum="' || (i-1) || '"/>';
else
isfound := TRUE;
end if;
else
-- shift left
anypart := anypart || chr(10) || ' <xdb:kid propNum="' || elem_propno || '" kidNum="' || (i-2) || '"/>';
skidhttpelems(i-1) := skidhttpelems(i);
end if;
end loop;
anypart := anypart || chr(10) || '</xdb:kidList>';
skidhttpelems.trim(1);
update xdb.xdb$complex_type c
set c.xmldata.annotation.appinfo = XDB.XDB$APPINFO_LIST_T(XDB.XDB$APPINFO_T(XDB.XDB$RAW_LIST_T('1301000000'), anypart, NULL))
where c.xmldata.parent_schema = refs and ref(c)=refhttptype;
-- update elements and PD for seq kid of httpconfig (remove custom-authentication)
update xdb.xdb$sequence_model m set m.xmldata.elements = skidhttpelems,
m.xmldata.sys_xdbpd$ = XDB.XDB$RAW_LIST_T('230200000081801407')
where ref(m)= refskidhttp;
-- update annotation for the complex type declaration for sysconfig
-- (remove reference to allow-authentication-trust and custom-authentication-trust)
isfound := FALSE;
anypart := '<xdb:kidList xmlns:xdb="http://xmlns.oracle.com/xdb" sequential="true">';
for i in 1..skidsyselems.last loop
select e.xmldata.property.prop_number into elem_propno from xdb.xdb$element e
where ref(e) = skidsyselems(i);
if (not (isfound)) then
if (elem_propno != allowtrustnum) then
anypart := anypart || chr(10) || ' <xdb:kid propNum="' || elem_propno || '" kidNum="' || (i-1) || '"/>';
else
isfound := TRUE;
end if;
else
-- shift left
anypart := anypart || chr(10) || ' <xdb:kid propNum="' || elem_propno || '" kidNum="' || (i-2) || '"/>';
skidsyselems(i-1) := skidsyselems(i);
end if;
end loop;
anypart := anypart || chr(10) || '</xdb:kidList>';
skidsyselems.trim(1);
-- Note: there are 2 custom-authentication-trust elements in xdbconfig, so need a different approach than
-- with allow-authentication-trust
isfound := FALSE;
anypart := '<xdb:kidList xmlns:xdb="http://xmlns.oracle.com/xdb" sequential="true">';
for i in 1..skidsyselems.last loop
select distinct e.xmldata.property.name into propname from xdb.xdb$element e
where ref(e) = skidsyselems(i);
if (not (isfound)) then
if (propname != 'custom-authentication-trust') then
anypart := anypart || chr(10) || ' <xdb:kid propNum="' || elem_propno || '" kidNum="' || (i-1) || '"/>';
else
isfound := TRUE;
end if;
else
-- shift left
anypart := anypart || chr(10) || ' <xdb:kid propNum="' || elem_propno || '" kidNum="' || (i-2) || '"/>';
skidsyselems(i-1) := skidsyselems(i);
end if;
end loop;
anypart := anypart || chr(10) || '</xdb:kidList>';
skidsyselems.trim(1);
update xdb.xdb$complex_type c
set c.xmldata.annotation.appinfo = XDB.XDB$APPINFO_LIST_T(XDB.XDB$APPINFO_T(XDB.XDB$RAW_LIST_T('1301000000'), anypart, NULL))
where c.xmldata.parent_schema = refs and ref(c)=refsystype;
-- update elements and PD for seq kid of sysconfig
update xdb.xdb$sequence_model m set m.xmldata.elements = skidsyselems,
m.xmldata.sys_xdbpd$ = XDB.XDB$RAW_LIST_T('23020002000200182067656E65726963205844422070726F7065727469657320020E1E2070726F746F636F6C2073706563696669632070726F706572746965732081801807')
where ref(m)= refskidsys;
-- remove custom-authentication-type and custom-authentication-trust-type from complex_types list in schema
select s.xmldata.complex_types into clistinsch from xdb.xdb$schema s where s.xmldata.schema_url = schema_url;
isfound := FALSE;
for i in 1..clistinsch.last loop
select c.xmldata.name into typenm from xdb.xdb$complex_type c where ref(c) = clistinsch(i);
dbms_output.put_line(typenm);
if (not (isfound)) then
if (typenm = 'custom-authentication-type') then
isfound := TRUE;
end if;
else
-- shift left
clistinsch(i-1) := clistinsch(i);
end if;
end loop;
clistinsch.trim(1);
isfound := FALSE;
for i in 1..clistinsch.last loop
select c.xmldata.name into typenm from xdb.xdb$complex_type c where ref(c) = clistinsch(i);
if (not (isfound)) then
if (typenm = 'custom-authentication-trust-type') then
isfound := TRUE;
end if;
else
-- shift left
clistinsch(i-1) := clistinsch(i);
end if;
end loop;
clistinsch.trim(1);
-- update PD and num props for schema
update xdb.xdb$schema s set s.xmldata.complex_types = clistinsch,
s.xmldata.sys_xdbpd$ = XDB.XDB$RAW_LIST_T('43163C8600050084010084020184030202081820637573746F6D697A6564206572726F7220706167657320020A3E20706172616D6574657220666F72206120736572766C65743A206E616D652C2076616C7565207061697220616E642061206465736372697074696F6E20200B0C110482800B81800202131416120A170D'),
s.xmldata.num_props = CONFIG_PRPONUMS_DOWN
where s.xmldata.schema_url = schema_url;
------- now do the cleanup
delete from xdb.xdb$element e
where e.xmldata.property.name='realm'
and e.xmldata.property.parent_schema=refs;
delete from xdb.xdb$element e
where e.xmldata.property.name='respond-with-server-info'
and e.xmldata.property.parent_schema=refs;
delete from xdb.xdb$element e
where e.xmldata.property.name='custom-authentication'
and e.xmldata.property.parent_schema=refs;
delete from xdb.xdb$complex_type c where ref(c)=refcauthtype;
delete from xdb.xdb$sequence_model m where ref(m)= refskidcauthtype;
delete from xdb.xdb$element e
where e.xmldata.property.name='custom-authentication-trust'
and e.xmldata.property.parent_schema=refs;
delete from xdb.xdb$complex_type c where ref(c)= refcauthtrusttyp;
delete from xdb.xdb$sequence_model m where ref(m)=refskidtrustschs;
delete from xdb.xdb$element e
where e.xmldata.property.name='trust-scheme'
and e.xmldata.property.parent_schema=refs;
delete from xdb.xdb$complex_type c where ref(c)=reftrustschtyp;
delete from xdb.xdb$sequence_model m where ref(m)=refskidtrustelems;
delete from xdb.xdb$element e
where e.xmldata.property.name='trusted-parsing-schema'
and e.xmldata.property.parent_schema=refs;
delete from xdb.xdb$element e
where e.xmldata.property.name='trusted-session-user'
and e.xmldata.property.parent_schema=refs;
delete from xdb.xdb$element e
where e.xmldata.property.name='trust-scheme-description'
and e.xmldata.property.parent_schema=refs;
delete from xdb.xdb$element e
where e.xmldata.property.name='allowRegistration'
and e.xmldata.property.parent_schema=refs;
delete from xdb.xdb$element e
where e.xmldata.property.name='requireParsingSchema'
and e.xmldata.property.parent_schema=refs;
delete from xdb.xdb$element e
where e.xmldata.property.name='trust-scheme-name'
and e.xmldata.property.parent_schema=refs;
delete from xdb.xdb$element e
where e.xmldata.property.name='allow-authentication-trust'
and e.xmldata.property.parent_schema=refs;
delete from xdb.xdb$element e
where e.xmldata.property.name='custom-authentication-list'
and e.xmldata.property.parent_schema=refs;
delete from xdb.xdb$complex_type c where ref(c)= refauthlisttype;
delete from xdb.xdb$sequence_model m where ref(m)=refskidauthlist;
delete from xdb.xdb$element e
where e.xmldata.property.name='authentication-implement-language'
and e.xmldata.property.parent_schema=refs;
delete from xdb.xdb$simple_type c
where ref(c)=reflangtype;
delete from xdb.xdb$element e
where e.xmldata.property.name='authentication-implement-method'
and e.xmldata.property.parent_schema=refs;
delete from xdb.xdb$element e
where e.xmldata.property.name='authentication-implement-schema'
and e.xmldata.property.parent_schema=refs;
delete from xdb.xdb$element e
where e.xmldata.property.name='authentication-description'
and e.xmldata.property.parent_schema=refs;
delete from xdb.xdb$element e
where e.xmldata.property.name='authentication-name'
and e.xmldata.property.parent_schema=refs;
delete from xdb.xdb$element e
where e.xmldata.property.name='custom-authentication-mappings'
and e.xmldata.property.parent_schema=refs;
delete from xdb.xdb$complex_type c where ref(c) = refmapstype;
delete from xdb.xdb$sequence_model m where ref(m) = refskidmaps;
delete from xdb.xdb$element e
where e.xmldata.property.name='custom-authentication-mapping'
and e.xmldata.property.parent_schema=refs;
delete from xdb.xdb$complex_type c where ref(c) = refmaptype;
delete from xdb.xdb$sequence_model m where ref(m) = refskidmap;
delete from xdb.xdb$element e
where e.xmldata.property.name='user-prefix'
and e.xmldata.property.parent_schema=refs;
delete from xdb.xdb$element e
where e.xmldata.property.name='authentication-trust-name'
and e.xmldata.property.parent_schema=refs;
delete from xdb.xdb$element e where ref(e)=ref_ondeny;
delete from xdb.xdb$simple_type t where ref(t)=ref_ondeny_typ;
delete from xdb.xdb$element e
where e.xmldata.property.name='authentication-name'
and e.xmldata.property.parent_schema=refs;
delete from xdb.xdb$element e
where e.xmldata.property.name='authentication-pattern'
and e.xmldata.property.parent_schema=refs;
delete from xdb.xdb$element e where ref(e)=refcauth;
delete from xdb.xdb$complex_type c where ref(c)=refauthtype;
delete from xdb.xdb$sequence_model m where ref(m)=refskidauth;
commit;
end;
/
show errors;
exec downgradeConfig;
-- clean-up
drop procedure downgradeConfig;
Rem Drop upgrade utility functions
@@xdbuud.sql
-- BEGIN: downgrade for XML Index
drop table XDB.XDB$XIDX_IMP_T;
create global temporary table XDB.XDB$XIDX_IMP_T
(index_name VARCHAR2(40),
schema_name VARCHAR2(40),
id VARCHAR2(40),
sqlstr CLOB);
drop package XDB.ximetadata_pkg;
ALTER TYPE xdb.XMLIndexMethods DROP static function ODCIIndexGetMetadata(idxinfo IN sys.ODCIIndexInfo, expver IN VARCHAR2, newblock OUT number, idxenv IN sys.ODCIEnv) return VARCHAR2;
ALTER TYPE xdb.XMLIndexMethods ADD static function ODCIIndexGetMetadata(idxinfo IN sys.ODCIIndexInfo, expver IN VARCHAR2, len_newblock OUT number, idxenv IN sys.ODCIEnv) return VARCHAR2 is language C name "QMIX_XMETADATA" library XDB.XMLINDEX_LIB with context parameters (context, idxinfo, idxinfo INDICATOR struct, idxenv, idxenv INDICATOR struct, expver, expver INDICATOR, len_newblock, len_newblock INDICATOR sb4, RETURN LENGTH, RETURN);
ALTER TYPE xdb.XMLIndexMethods DROP static function ODCIIndexUtilGetTableNames(ia IN sys.ODCIIndexInfo, read_only IN PLS_INTEGER, version IN varchar2, context OUT PLS_INTEGER) return BOOLEAN;
ALTER TYPE xdb.XMLIndexMethods DROP static procedure ODCIIndexUtilCleanup (context IN PLS_INTEGER);
create or replace type body xdb.XMLIndexMethods
is
static function ODCIGetInterfaces(ilist OUT sys.ODCIObjectList)
return number is
begin
ilist := sys.ODCIObjectList(sys.ODCIObject('SYS','ODCIINDEX2'));
return ODCICONST.SUCCESS;
end ODCIGetInterfaces;
static function ODCIIndexUpdPartMetadata(ixdxinfo sys.ODCIIndexInfo,
palist sys.ODCIPartInfoList,
idxenv sys.ODCIEnv)
return NUMBER
is
BEGIN
RETURN ODCICONST.SUCCESS;
END;
end;
/
----------------------------------------------------------------------------
-- De-support partitioning of XMLIndex
alter indextype XDB.xmlindex
using XDB.XMLIndexMethods
without local partition
with system managed storage tables;
-- END: downgrade for XML Index
-- container - mark mutable
declare
res_schema_ref REF XMLTYPE;
res_schema_url VARCHAR2(100);
begin
res_schema_url := 'http://xmlns.oracle.com/xdb/XDBResource.xsd';
select ref(s) into res_schema_ref
from xdb.xdb$schema s
where s.xmldata.schema_url = res_schema_url;
update xdb.xdb$attribute a
set a.xmldata.MUTABLE = '00'
where a.xmldata.parent_schema = res_schema_ref
and a.xmldata.name = 'Container';
commit;
end;
/
drop package xdb.XDB_PITRIG_PKG_01;
-- Clean up session/shared state
exec xdb.dbms_xdbutil_int.flushsession;
alter system flush shared_pool;
alter system flush shared_pool;
alter system flush shared_pool;
Rem ================================================================
Rem END XDB Schema downgrade to 11.1.0
Rem ================================================================