Rem Rem $Header: rdbms/admin/xdbs111.sql /st_rdbms_11.2.0.1.0_gen/1 2010/02/25 14:51:53 badeoti Exp $ Rem Rem xdbs111.sql Rem Rem Copyright (c) 2007, 2010, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem xdbs111.sql - XDB Upgrade from 11.1.0 Rem Rem DESCRIPTION Rem This script performs XDB schema upgrade actions to upgrade from Rem 11.1.0 to the current release Rem Rem NOTES Rem It is invoked by xdbdbmig.sql and by xdbs102.sql Rem Rem MODIFIED (MM/DD/YY) Rem badeoti 02/24/10 - XbranchMerge badeoti_bug-9304342 from main Rem badeoti 10/28/09 - Include sidicula_bug-7596530 fixes for 9201NT Rem schema-for-schemas Rem badeoti 07/23/09 - drop utility functions Rem mkandarp 06/16/09 - 8571751: increase maxval for xdb$namesuff_seq Rem spetride 05/08/09 - upgrade XMLIndexMethods Rem spetride 02/16/09 - upgrade for Expire headers in xdbconfig Rem badeoti 02/26/09 - add respond-with-server-info to httpcfg Rem atabar 02/09/09 - xdbconfig upgrade: default-type-mappings Rem spetride 02/02/09 - upgrade for realm under httpconfig Rem spetride 06/11/08 - upgrade ACL and CONFIG schemas 11.1 to 11.2 Rem badeoti 10/28/08 - manual upgrade for resconfig schema, no Rem inplaceEvolve Rem bhammers 04/22/08 - renamed col in XDB.XDB$XIDX_IMP_T Rem badeoti 04/29/08 - ipv6 support: ipaddress type upgrade Rem attran 04/15/08 - Add support for new partitioning M of XMLIndex Rem bhammers 03/18/08 - add upgrade for XML Index Rem yifeng 11/07/07 - use xmldiff before call to InPlaceEvolve Rem jwwarner 10/12/07 - add upgrade for ResConfig schema Rem mrafiq 09/26/07 - add upgrade for acl and ace any element Rem rburns 08/22/07 - add 11g XDB up/down scripts Rem rburns 08/22/07 - Created Rem -- utility functions may not have been dropped during prior upgrade from 10.2 -- drop them here @@xdbuud2 Rem Load XDB upgrade downgrade utilities (dbms_xdbmig_util) @@prvtxudu.plb Rem Fix schema-for-schemas in DBs upgraded from 9201 execute dbms_xdbmig_util.checkSchSchCfgKids; execute dbms_xdbmig_util.fixSchSchCfgKids; execute dbms_xdbmig_util.checkSchSchCfgKids; commit; Rem Fix corrupted complex type rows execute dbms_xdbmig_util.fixCfgPDs; execute dbms_xdbmig_util.checkCfgPDs; execute dbms_xdbmig_util.checkSchSchCfgKids; commit; -- increase maxval for namesuff_seq alter sequence xdb.xdb$namesuff_seq maxvalue 99999; -- ipv6 support: modify simpletype ipaddress in xdbconfig declare config_schema_ref REF XMLTYPE; config_schema_url VARCHAR2(100); begin config_schema_url := 'http://xmlns.oracle.com/xdb/xdbconfig.xsd'; select ref(s) into config_schema_ref from xdb.xdb$schema s where s.xmldata.schema_url = config_schema_url; update xdb.xdb$simple_type e set e.xmldata.restriction.sys_xdbpd$ = XDB.XDB$RAW_LIST_T('3310000200001104'), e.xmldata.restriction.maxlength = XDB.XDB$NUMFACET_T(XDB.XDB$RAW_LIST_T('130200000102'), NULL, 40, '00', NULL), e.xmldata.restriction.pattern = NULL where e.xmldata.parent_schema = config_schema_ref and e.xmldata.name = 'ipaddress'; commit; end; / -- end modify simpletype ipaddress in xdbconfig -- This changes the processContents attribute for the any element in the acl and ace elements -- to lax so that user defined data can be added to these any elements. declare seq_ref REF XMLTYPE; any_ref REF XMLTYPE; anylist XDB.XDB$XMLTYPE_REF_LIST_T; acl_schema_url VARCHAR2(100); acl_schema_ref REF XMLTYPE; begin acl_schema_url := 'http://xmlns.oracle.com/xdb/acl.xsd'; -- update the any element of the ace element to make -- processContents attibute equals lax select ref(s) into acl_schema_ref from xdb.xdb$schema s where s.xmldata.schema_url = acl_schema_url; select c.xmldata.sequence_kid into seq_ref from xdb.xdb$complex_type c where ref(c) = (select e.xmldata.cplx_type_decl from xdb.xdb$element e where e.xmldata.property.name like 'ace' and e.xmldata.property.parent_schema = acl_schema_ref); select m.xmldata.anys into anylist from xdb.xdb$sequence_model m where ref(m) = seq_ref; any_ref := anylist(1); update xdb.xdb$any a set a.xmldata.process_contents = XDB.XDB$PROCESSCHOICE('01'), a.xmldata.property.sys_xdbpd$ = XDB.XDB$RAW_LIST_T('43881800F000001E1F1C1D0809181B030B0C07') where ref(a) = any_ref; -- update the any element of the acl element to make -- processContents attibute equals lax select c.xmldata.sequence_kid into seq_ref from xdb.xdb$complex_type c where c.xmldata.name like 'aclType'; select m.xmldata.anys into anylist from xdb.xdb$sequence_model m where ref(m) = seq_ref; any_ref := anylist(1); update xdb.xdb$any a set a.xmldata.process_contents = XDB.XDB$PROCESSCHOICE('01'), a.xmldata.property.sys_xdbpd$ = XDB.XDB$RAW_LIST_T('43881800F000001E1F1C1D0809181B030B0C07') where ref(a) = any_ref; commit; end; / -- upgrade to 11.2 ACL schema: add '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('330008020000118B8005'), 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), XDB.XDB$FACET_T(XDB.XDB$RAW_LIST_T('130200000102'), NULL, 'ApplicationName', '00', NULL)), NULL, NULL) where ref(s) = (select a.xmldata.smpl_type_decl from xdb.xdb$attribute a where ref(a)=aceattrs(i)); -- exit the loop exit; end if; end loop; commit; end; / create or replace procedure createCustomAuthTrustType(refs IN REF SYS.XMLTYPE) as trustnmnum NUMBER(38); reftrustnm REF SYS.XMLTYPE; reqparsenum NUMBER(38); refreqparse REF SYS.XMLTYPE; allowregnum NUMBER(38); refallowreg REF SYS.XMLTYPE; trustdescnum NUMBER(38); reftrustdesc REF SYS.XMLTYPE; trustusernum NUMBER(38); reftrustuser REF SYS.XMLTYPE; trustparsenum NUMBER(38); reftrustparse REF SYS.XMLTYPE; grnum NUMBER(38); refgr REF SYS.XMLTYPE; skidtrustelems XDB.XDB$XMLTYPE_REF_LIST_T; refskidtrustelems REF SYS.XMLTYPE; anypart VARCHAR2(4000); i NUMBER(38); reftrustschtyp REF SYS.XMLTYPE; trustschnum NUMBER(38); reftrustsch REF SYS.XMLTYPE; skidtrustschs XDB.XDB$XMLTYPE_REF_LIST_T; refskidtrustschs REF SYS.XMLTYPE; begin -- create trust-scheme-name element trustnmnum := xdb.xdb$propnum_seq.nextval; insert into xdb.xdb$element e (e.xmlextra, e.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T('83B810200080030000000004050809181B23262A32343503150C07292728'), refs, trustnmnum, 'trust-scheme-name', XDB.XDB$QNAME('00','string'), NULL, '01', '00', '00', NULL, NULL, 'string', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'), NULL, NULL, '00', NULL, NULL, '00', '01', '01', '01', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(e) into reftrustnm; -- create the requirePrasingSchema element reqparsenum := xdb.xdb$propnum_seq.nextval; insert into xdb.xdb$element e (e.xmlextra, e.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T('83B890200080030400000004050F320809181B23262A343503150C07292728'), refs, reqparsenum, 'requireParsingSchema', XDB.XDB$QNAME('00', 'boolean'), NULL, 'FC', '00', '00', NULL, NULL, 'boolean', NULL, NULL, 'true', NULL, NULL, NULL, NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'), NULL, NULL, '00', NULL, NULL, '00', '01', '01', '01', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(e) into refreqparse; -- create the allowRegistration element allowregnum := xdb.xdb$propnum_seq.nextval; insert into xdb.xdb$element e (e.xmlextra, e.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T('83B890200080030400000004050F320809181B23262A343503150C07292728'), refs, allowregnum, 'allowRegistration', XDB.XDB$QNAME('00', 'boolean'), NULL, 'FC', '00', '00', NULL, NULL, 'boolean', NULL, NULL, 'true', NULL, NULL, NULL, NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'), NULL, NULL, '00', NULL, NULL, '00', '01', '01', '01', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(e) into refallowreg; -- create the trust-scheme-description element trustdescnum := xdb.xdb$propnum_seq.nextval; insert into xdb.xdb$element e (e.xmlextra, e.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T('83B81020008003040000000405320809181B23262A343503150C07292728'), refs, trustdescnum, 'trust-scheme-description', XDB.XDB$QNAME('00', 'string'), NULL, '01', '00', '00', NULL, NULL, 'string', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'), NULL, NULL, '00', NULL, NULL, '00', '01', '01', '01', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(e) into reftrustdesc; -- create the trusted-user-session element trustusernum := xdb.xdb$propnum_seq.nextval; insert into xdb.xdb$element e (e.xmlextra, e.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T('83B810200080030C000000040532330809181B23262A343503150C07292728'), refs, trustusernum, 'trusted-session-user', XDB.XDB$QNAME('00', 'string'), NULL, '01', '00', '00', NULL, NULL, 'string', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'), NULL, NULL, '00', NULL, NULL, '00', '01', '01', '01', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, 'unbounded', '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(e) into reftrustuser; -- create the trusted-parsing-schema element trustparsenum := xdb.xdb$propnum_seq.nextval; insert into xdb.xdb$element e (e.xmlextra, e.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T('83B810200080030C000000040532330809181B23262A343503150C07292728'), refs, trustparsenum, 'trusted-parsing-schema', XDB.XDB$QNAME('00', 'string'), NULL, '01', '00', '00', NULL, NULL, 'string', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'), NULL, NULL, '00', NULL, NULL, '00', '01', '01', '01', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 'unbounded', '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(e) into reftrustparse; -- create the workgroup element grnum := xdb.xdb$propnum_seq.nextval; insert into xdb.xdb$element e (e.xmlextra, e.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T('83B810200080030C000000040532330809181B23262A343503150C07292728'), refs, grnum, 'workgroup', XDB.XDB$QNAME('00', 'string'), NULL, '01', '00', '00', NULL, NULL, 'string', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'), NULL, NULL, '00', NULL, NULL, '00', '01', '01', '01', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 'unbounded', '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(e) into refgr; -- create seq(above trust elements) skidtrustelems := XDB.XDB$XMLTYPE_REF_LIST_T(); skidtrustelems.extend(7); skidtrustelems(1):= reftrustnm; skidtrustelems(2):= refreqparse; skidtrustelems(3):= refallowreg; skidtrustelems(4):= reftrustdesc; skidtrustelems(5):= reftrustuser; skidtrustelems(6):= reftrustparse; skidtrustelems(7):= refgr; insert into xdb.xdb$sequence_model m (m.xmlextra, m.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$MODEL_T(XDB.XDB$RAW_LIST_T('230200000081800707'), refs, 0, NULL, skidtrustelems, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(m) into refskidtrustelems; -- create annotation for trust-scheme i := 1; anypart := ''; anypart := anypart || chr(10) || ' '; i := 2; anypart := anypart || chr(10) || ' '; i := 3; anypart := anypart || chr(10) || ' '; i := 4; anypart := anypart || chr(10) || ' '; i := 5; anypart := anypart || chr(10) || ' '; i := 6; anypart := anypart || chr(10) || ' '; i := 7; anypart := anypart || chr(10) || ' '; anypart := anypart || chr(10) || ''; -- create complex type declaration for trust-scheme insert into xdb.xdb$complex_type c (c.xmlextra, c.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$COMPLEX_T(XDB.XDB$RAW_LIST_T('33090000000000030D0E13'), refs, NULL, NULL, '00', '00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, refskidtrustelems, NULL, NULL, 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), NULL, NULL, '01', NULL, NULL, NULL, NULL)) returning ref(c) into reftrustschtyp; -- create complex element trust-scheme trustschnum := xdb.xdb$propnum_seq.nextval; insert into xdb.xdb$element e (e.xmlextra, e.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T('839800201080030C0000000432331C0809181B23262A3435031507292728'), refs, trustschnum, 'trust-scheme', NULL, NULL, '0102', '00', '00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, reftrustschtyp, NULL, NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'), NULL, NULL, '00', NULL, NULL, '00', '00', '01', '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, reftrustschtyp, NULL, NULL, 0, 'unbounded', '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(e) into reftrustsch; -- create seq(trust-scheme) skidtrustschs := XDB.XDB$XMLTYPE_REF_LIST_T(); skidtrustschs.extend(1); skidtrustschs(1) := reftrustsch; insert into xdb.xdb$sequence_model m (m.xmlextra, m.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$MODEL_T(XDB.XDB$RAW_LIST_T('23020000000107'), refs, 0, NULL, skidtrustschs, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(m) into refskidtrustschs; -- create annotation for complex type custom-authentication-trust-type i := 1; anypart := ''; anypart := anypart || chr(10) || ' '; anypart := anypart || chr(10) || ''; -- create complex type custom-authentication-trust-type insert into xdb.xdb$complex_type c (c.xmlextra, c.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$COMPLEX_T(XDB.XDB$RAW_LIST_T('3309104000000C00030D0E1316'), refs, NULL, 'custom-authentication-trust-type', '00', '00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, refskidtrustschs, NULL, NULL, 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), NULL, NULL, '01', NULL, NULL, NULL, 215)); --returning ref(c) into refcauthtrusttyp; commit; end; / show errors; create or replace procedure updateSysConfigForTrust(refs IN REF SYS.XMLTYPE, refcauthtrusttyp IN REF SYS.XMLTYPE) as allowtrustnum NUMBER(38); refallowtrust REF SYS.XMLTYPE; systrustnum NUMBER(38); refsystrust REF SYS.XMLTYPE; refsystype REF SYS.XMLTYPE; refskidsys REF SYS.XMLTYPE; skidsyselems XDB.XDB$XMLTYPE_REF_LIST_T; anypart VARCHAR2(4000); i NUMBER(38); elem_propno NUMBER(38); begin -- create allow-authentication-trust element allowtrustnum := xdb.xdb$propnum_seq.nextval; insert into xdb.xdb$element e (e.xmlextra, e.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T('83B890200080030400000004050F320809181B23262A343503150C07292728'), refs, allowtrustnum, 'allow-authentication-trust', XDB.XDB$QNAME('00', 'boolean'), NULL, 'FC', '00', '00', NULL, NULL, 'boolean', NULL, NULL, 'false', NULL, NULL, NULL, NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'), NULL, NULL, '00', NULL, NULL, '00', '01', '01', '01', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(e) into refallowtrust; -- create custom-authentication-trust element under sysconfig systrustnum := xdb.xdb$propnum_seq.nextval; insert into xdb.xdb$element e (e.xmlextra, e.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T('83B80020008003040000000405320809181B23262A3435031507292728'), refs, systrustnum, 'custom-authentication-trust', XDB.XDB$QNAME('01', 'custom-authentication-trust-type'), NULL, '0102', '00', '00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, refcauthtrusttyp, NULL, NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'), NULL, NULL, '00', NULL, NULL, '00', '00', '01', '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(e) into refsystrust; commit; -- update elements and PD for seq kid of sysconfig select e.xmldata.cplx_type_decl into refsystype from xdb.xdb$element e where e.xmldata.property.name ='sysconfig' and e.xmldata.property.parent_schema = refs; select c.xmldata.sequence_kid into refskidsys from xdb.xdb$complex_type c where ref(c) = refsystype; select m.xmldata.elements into skidsyselems from xdb.xdb$sequence_model m where ref(m)= refskidsys; skidsyselems.extend(2); skidsyselems(skidsyselems.last-1) := refallowtrust; skidsyselems(skidsyselems.last) := refsystrust; update xdb.xdb$sequence_model m set m.xmldata.elements = skidsyselems, m.xmldata.sys_xdbpd$ = XDB.XDB$RAW_LIST_T('23020002000200182067656E65726963205844422070726F7065727469657320020E1E2070726F746F636F6C2073706563696669632070726F706572746965732081801A07') where ref(m)= refskidsys; commit; -- update annotations for the complex type declaration for sysconfig anypart := ''; 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); anypart := anypart || chr(10) || ' '; end loop; anypart := anypart || chr(10) || ''; 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; -- needed in the 11.1.0.7 upgrade to main update xdb.xdb$complex_type c set c.xmldata.annotation.sys_xdbpd$=XDB.XDB$RAW_LIST_T('1301000000') where c.xmldata.parent_schema = refs and ref(c)=refsystype; end; / show errors; -- this procedure upgrades xdbconfig.xsd for custom authentication and trust -- we also add the 'realm' element under httpconfig create or replace procedure upgradeConfig as schema_url VARCHAR2(700) := 'http://xmlns.oracle.com/xdb/xdbconfig.xsd'; refs REF SYS.XMLTYPE; idem NUMBER := 0; CONFIG_PRPONUMS CONSTANT INTEGER := 198; numprops NUMBER(38); refauthpat REF SYS.XMLTYPE; authpatnum NUMBER(38); refauthname1 REF SYS.XMLTYPE; authnamenum1 NUMBER(38); authtrustnmnum NUMBER(38); refauthtrustnm REF SYS.XMLTYPE; authprefnum NUMBER(38); refauthpref REF SYS.XMLTYPE; skidmapelems XDB.XDB$XMLTYPE_REF_LIST_T; refskidmap REF SYS.XMLTYPE; anypart VARCHAR2(4000); i NUMBER(38); j NUMBER(38); refmaptype REF SYS.XMLTYPE; refmap REF SYS.XMLTYPE; mapnum NUMBER(38); skidmapselems XDB.XDB$XMLTYPE_REF_LIST_T; 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; skidauthelems XDB.XDB$XMLTYPE_REF_LIST_T; refskidauth REF SYS.XMLTYPE; refauthtype REF SYS.XMLTYPE; authnum NUMBER(38); refauth REF SYS.XMLTYPE; skidauthlistelems XDB.XDB$XMLTYPE_REF_LIST_T; refskidauthlist REF SYS.XMLTYPE; refauthlisttype REF SYS.XMLTYPE; authlistnum NUMBER(38); refauthlist REF SYS.XMLTYPE; skidcauthtypeelems XDB.XDB$XMLTYPE_REF_LIST_T; refskidcauthtype REF SYS.XMLTYPE; refcauthtype REF SYS.XMLTYPE; clistinsch XDB.XDB$XMLTYPE_REF_LIST_T; cauthnum NUMBER(38); refcauth REF SYS.XMLTYPE; skidcauthelems XDB.XDB$XMLTYPE_REF_LIST_T; refskidcauth REF SYS.XMLTYPE; refhttptype REF SYS.XMLTYPE; refskidhttp REF SYS.XMLTYPE; skidhttpelems XDB.XDB$XMLTYPE_REF_LIST_T; elem_propno NUMBER(38); refcauthtrusttyp REF SYS.XMLTYPE; httptrustnum NUMBER(38); refhttptrust REF SYS.XMLTYPE; refrealm REF SYS.XMLTYPE; realmnum NUMBER(38); refsrvinfo REF SYS.XMLTYPE; srvinfonum NUMBER(38); elem_ref REF SYS.XMLTYPE; elem_ref2 REF SYS.XMLTYPE; elem_name VARCHAR2(4000); 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 any of the custom-authentication types or elements -- already 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 > 0) then dbms_output.put_line('xdbconfig schema already upgrated'); return; end if; dbms_output.put_line('upgrading xdbconfig schema, numprops was ' || numprops); -- create authentication-pattern element authpatnum := xdb.xdb$propnum_seq.nextval; insert into xdb.xdb$element e (e.xmlextra, e.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T('83B810200080030000000004050809181B23262A32343503150C07292728'), refs, authpatnum, 'authentication-pattern', XDB.XDB$QNAME('00', 'string'), NULL, '01', '00', '00', NULL, NULL, 'string', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'), NULL, NULL, '00', NULL, NULL, '00', '01', '01', '01', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(e) into refauthpat; -- create authentication-name element authnamenum1 := xdb.xdb$propnum_seq.nextval; insert into xdb.xdb$element e (e.xmlextra, e.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T('83B810200080030000000004050809181B23262A32343503150C07292728'), refs, authnamenum1, 'authentication-name', XDB.XDB$QNAME('00', 'string'), NULL, '01', '00', '00', NULL, NULL, 'string', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'), NULL, NULL, '00', NULL, NULL, '00', '01', '01', '01', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(e) into refauthname1; -- create authentication-trust-name element authtrustnmnum := xdb.xdb$propnum_seq.nextval; insert into xdb.xdb$element e (e.xmlextra, e.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '50000478646263002A687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E666967322E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T('83B81020008003040000000405320809181B23262A343503150C07292728'), refs, authtrustnmnum, 'authentication-trust-name', XDB.XDB$QNAME('00', 'string'), NULL, '01', '00', '00', NULL, NULL, 'string', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'), NULL, NULL, '00', NULL, NULL, '00', '01', '01', '01', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(e) into refauthtrustnm; -- create user-prefix element authprefnum := xdb.xdb$propnum_seq.nextval; insert into xdb.xdb$element e (e.xmlextra, e.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '50000478646263002A687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E666967322E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T('83B81020008003040000000405320809181B23262A343503150C07292728'), refs, authprefnum, 'user-prefix', XDB.XDB$QNAME('00', 'string'), NULL, '01', '00', '00', NULL, NULL, 'string', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'), NULL, NULL, '00', NULL, NULL, '00', '01', '01', '01', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(e) into refauthpref; -- create seq(authentication-pattern, authentication-name, authentication-trust-name,user-prefix) skidmapelems := XDB.XDB$XMLTYPE_REF_LIST_T(); skidmapelems.extend(4); skidmapelems(1) := refauthpat; skidmapelems(2) := refauthname1; skidmapelems(3) := refauthtrustnm; skidmapelems(4) := refauthpref; insert into xdb.xdb$sequence_model m (m.xmlextra, m.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$MODEL_T(XDB.XDB$RAW_LIST_T('230200000081800407'), refs, 0, NULL, skidmapelems, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(m) into refskidmap; -- create annotation for custom-authentication-mapping i := 1; anypart := ''; anypart := anypart || chr(10) || ' '; i := 2; anypart := anypart || chr(10) || ' '; i := 3; anypart := anypart || chr(10) || ' '; i := 4; anypart := anypart || chr(10) || ' '; anypart := anypart || chr(10) || ''; -- create complex type declaration for custom-authentication-mapping insert into xdb.xdb$complex_type c (c.xmlextra, c.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$COMPLEX_T(XDB.XDB$RAW_LIST_T('33090000000000030D0E13'), refs, NULL, NULL, '00', '00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, refskidmap, NULL, NULL, 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), NULL, NULL, '01', NULL, NULL, NULL, NULL)) returning ref(c) into refmaptype; -- create custom-authentication-mapping element mapnum := xdb.xdb$propnum_seq.nextval; insert into xdb.xdb$element e (e.xmlextra, e.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T('839800201080030C0000000432331C0809181B23262A3435031507292728'), refs, mapnum, 'custom-authentication-mapping', NULL, NULL, '0102', '00', '00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, refmaptype, -- smpl_type_decl NULL, NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'), NULL, NULL, '00', NULL, NULL, '00', '00', '01', '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, refmaptype, -- cplx_type_decl NULL, NULL, 0, 'unbounded', '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(e) into refmap; -- create seq(custom-authentication-mapping) skidmapselems := XDB.XDB$XMLTYPE_REF_LIST_T(); skidmapselems.extend(1); skidmapselems(1) := refmap; insert into xdb.xdb$sequence_model m (m.xmlextra, m.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$MODEL_T(XDB.XDB$RAW_LIST_T('23020000000107'), refs, 0, NULL, skidmapselems, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(m) into refskidmaps; -- create annotation for complex type declaration for custom-authentication-mappings i := 1; anypart := ''; anypart := anypart || chr(10) || ' '; anypart := anypart || chr(10) || ''; -- create complex type declaration for custom-authentication-mappings insert into xdb.xdb$complex_type c (c.xmlextra, c.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$COMPLEX_T(XDB.XDB$RAW_LIST_T('33090000000000030D0E13'), refs, NULL, NULL, '00', '00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, refskidmaps, NULL, NULL, 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), NULL, NULL, '01', NULL, NULL, NULL, NULL)) returning ref(c) into refmapstype; -- create custom-authentication-mappings element mapsnum := xdb.xdb$propnum_seq.nextval; insert into xdb.xdb$element e (e.xmlextra, e.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T('8398002010800300000000041C0809181B23262A323435031507292728'), refs, mapsnum, 'custom-authentication-mappings', NULL, NULL, '0102', '00', '00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, refmapstype, NULL, NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'), NULL, NULL, '00', NULL, NULL, '00', '00', '01', '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, refmapstype, NULL, NULL, 0, NULL, '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(e) into refmaps; -- create authentication-name element authnamenum2 := xdb.xdb$propnum_seq.nextval; insert into xdb.xdb$element e (e.xmlextra, e.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T('83B810200080030000000004050809181B23262A32343503150C07292728'), refs, authnamenum2, 'authentication-name', XDB.XDB$QNAME('00', 'string'), NULL, '01', '00', '00', NULL, NULL, 'string', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'), NULL, NULL, '00', NULL, NULL, '00', '01', '01', '01', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(e) into refauthname2; -- create authentication-description element descrnum := xdb.xdb$propnum_seq.nextval; insert into xdb.xdb$element e (e.xmlextra, e.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T('83B81020008003040000000405320809181B23262A343503150C07292728'), refs, descrnum, 'authentication-description', XDB.XDB$QNAME('00', 'string'), NULL, '01', '00', '00', NULL, NULL, 'string', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'), NULL, NULL, '00', NULL, NULL, '00', '01', '01', '01', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(e) into refdescr; -- create authentication-implement-schema element implnum := xdb.xdb$propnum_seq.nextval; insert into xdb.xdb$element e (e.xmlextra, e.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T('83B810200080030000000004050809181B23262A32343503150C07292728'), refs, implnum, 'authentication-implement-schema', XDB.XDB$QNAME('00', 'string'), NULL, '01', '00', '00', NULL, NULL, 'string', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'), NULL, NULL, '00', NULL, NULL, '00', '01', '01', '01', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(e) into refimpl; -- create authentication-implement-method element methodnum := xdb.xdb$propnum_seq.nextval; insert into xdb.xdb$element e (e.xmlextra, e.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T('83B810200080030000000004050809181B23262A32343503150C07292728'), refs, methodnum, 'authentication-implement-method', XDB.XDB$QNAME('00', 'string'), NULL, '01', '00', '00', NULL, NULL, 'string', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'), NULL, NULL, '00', NULL, NULL, '00', '01', '01', '01', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(e) into refmethod; -- create annotation for simple type for authentication-implement-language (not needed) -- create simple type for authentication-implement-language insert into xdb.xdb$simple_type c (c.xmlextra, c.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$SIMPLE_T(XDB.XDB$RAW_LIST_T('23020000000106'), refs, NULL, '00', XDB.XDB$SIMPLE_DERIVATION_T(XDB.XDB$RAW_LIST_T('330008020000110B'), 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, 'PL/SQL', '00', NULL)), NULL, NULL), NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(c) into reflangtype; -- create authentication-implement-language element langnum := xdb.xdb$propnum_seq.nextval; insert into xdb.xdb$element e (e.xmlextra, e.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T('839A10200080030000000004010809181B23262A32343503150C07292728'), refs, langnum, 'authentication-implement-language', NULL, NULL, '0103', '00', '00', NULL, NULL, 'string', NULL, NULL, NULL, reflangtype, --smpl_type_decl reflangtype, --type_ref NULL, NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'), NULL, NULL, '00', NULL, NULL, '00', '01', '01', '01', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(e) into reflang; -- create seq(authentication-name, authentication-description, authentication-implement-schema, -- authentication-implement-method, authentication-implement-language) skidauthelems := XDB.XDB$XMLTYPE_REF_LIST_T(); skidauthelems.extend(5); skidauthelems(1) := refauthname2; skidauthelems(2) := refdescr; skidauthelems(3) := refimpl; skidauthelems(4) := refmethod; skidauthelems(5) := reflang; insert into xdb.xdb$sequence_model m (m.xmlextra, m.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$MODEL_T(XDB.XDB$RAW_LIST_T('230200000081800507'), refs, 0, NULL, skidauthelems, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(m) into refskidauth; -- create annotation for complex type declaration for authentication i := 1; anypart := ''; anypart := anypart || chr(10) || ' '; i := 2; anypart := anypart || chr(10) || ' '; i := 3; anypart := anypart || chr(10) || ' '; i := 4; anypart := anypart || chr(10) || ' '; i := 5; anypart := anypart || chr(10) || ' '; anypart := anypart || chr(10) || ''; -- create complex type declaration for authentication insert into xdb.xdb$complex_type c (c.xmlextra, c.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$COMPLEX_T(XDB.XDB$RAW_LIST_T('33090000000000030D0E13'), refs, NULL, NULL, '00', '00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, refskidauth, NULL, NULL, 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), NULL, NULL, '01', NULL, NULL, NULL, NULL)) returning ref(c) into refauthtype; -- create authentication element authnum := xdb.xdb$propnum_seq.nextval; insert into xdb.xdb$element e (e.xmlextra, e.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T('839800201080030C0000000432331C0809181B23262A3435031507292728'), refs, authnum, 'authentication', NULL, NULL, '0102', '00', '00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, refauthtype, NULL, NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'), NULL, NULL, '00', NULL, NULL, '00', '00', '01', '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, refauthtype, NULL, NULL, 0, 'unbounded', '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(e) into refauth; -- create seq(authentication) skidauthlistelems := XDB.XDB$XMLTYPE_REF_LIST_T(); skidauthlistelems.extend(1); skidauthlistelems(1) := refauth; insert into xdb.xdb$sequence_model m (m.xmlextra, m.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$MODEL_T(XDB.XDB$RAW_LIST_T('23020000000107'), refs, 0, NULL, skidauthlistelems, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(m) into refskidauthlist; -- create annotation for complex type declaration for custom-authentication-list i := 1; anypart := ''; anypart := anypart || chr(10) || ' '; anypart := anypart || chr(10) || ''; -- create complex type declaration for custom-authentication-list insert into xdb.xdb$complex_type c (c.xmlextra, c.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$COMPLEX_T(XDB.XDB$RAW_LIST_T('33090000000000030D0E13'), refs, NULL, NULL, '00', '00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, refskidauthlist, NULL, NULL, 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), NULL, NULL, '01', NULL, NULL, NULL, NULL)) returning ref(c) into refauthlisttype; -- create custom-authentication-list element authlistnum := xdb.xdb$propnum_seq.nextval; insert into xdb.xdb$element e (e.xmlextra, e.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T('8398002010800300000000041C0809181B23262A323435031507292728'), refs, authlistnum, 'custom-authentication-list', NULL, NULL, '0102', '00', '00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, refauthlisttype, NULL, NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'), NULL, NULL, '00', NULL, NULL, '00', '00', '01', '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, refauthlisttype, NULL, NULL, 0, NULL, '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(e) into refauthlist; -- create custom-authentication-trust-type complex type createCustomAuthTrustType(refs); select ref(c) into refcauthtrusttyp from xdb.xdb$complex_type c where c.xmldata.name='custom-authentication-trust-type' and c.xmldata.parent_schema =refs; httptrustnum := xdb.xdb$propnum_seq.nextval; insert into xdb.xdb$element e (e.xmlextra, e.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T('83B80020008003040000000405320809181B23262A3435031507292728'), refs, httptrustnum, 'custom-authentication-trust', XDB.XDB$QNAME('01', 'custom-authentication-trust-type'), NULL, '0102', '00', '00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, refcauthtrusttyp, NULL, NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'), NULL, NULL, '00', NULL, NULL, '00', '00', '01', '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(e) into refhttptrust; -- create seq(custom-authentication-mappings,custom-authentication-list) skidcauthtypeelems := XDB.XDB$XMLTYPE_REF_LIST_T(); skidcauthtypeelems.extend(3); skidcauthtypeelems(1) := refmaps; skidcauthtypeelems(2) := refauthlist; skidcauthtypeelems(3) := refhttptrust; insert into xdb.xdb$sequence_model m (m.xmlextra, m.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$MODEL_T(XDB.XDB$RAW_LIST_T('230200000081800307'), refs, 0, NULL, skidcauthtypeelems, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(m) into refskidcauthtype; -- create annotation for custom-authentication-type i := 1; anypart := ''; anypart := anypart || chr(10) || ' '; i := 2; anypart := anypart || chr(10) || ' '; i := 3; anypart := anypart || chr(10) || ' '; anypart := anypart || chr(10) || ''; -- create complex type custom-authentication-type insert into xdb.xdb$complex_type c (c.xmlextra, c.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$COMPLEX_T(XDB.XDB$RAW_LIST_T('3309104000000C00030D0E1316'), refs, NULL, 'custom-authentication-type', '00', '00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, refskidcauthtype, NULL, NULL, 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), NULL, NULL, '01', NULL, NULL, NULL, 141)) returning ref(c) into refcauthtype; -- create custom-authentication element of type custom-authentication-type cauthnum := xdb.xdb$propnum_seq.nextval; insert into xdb.xdb$element e (e.xmlextra, e.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T('83B80020008003040000000405320809181B23262A3435031507292728'), refs, cauthnum, 'custom-authentication', XDB.XDB$QNAME('01', 'custom-authentication-type'), NULL, '0102', '00', '00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, refcauthtype, NULL, NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'), NULL, NULL, '00', NULL, NULL, '00', '00', '01', '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(e) into refcauth; commit; -- create the realm element realmnum := xdb.xdb$propnum_seq.nextval; insert into xdb.xdb$element e (e.xmlextra, e.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T('83B81020008003040000000405320809181B23262A343503150C07292728'), refs, realmnum, 'realm', XDB.XDB$QNAME('00', 'string'), NULL, '01', '00', '00', NULL, NULL, 'string', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'), NULL, NULL, '00', NULL, NULL, '00', '01', '01', '01', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(e) into refrealm; commit; -- create the respond-with-server-info element srvinfonum := xdb.xdb$propnum_seq.nextval; insert into xdb.xdb$element e (e.xmlextra, e.xmldata) values (SYS.XMLTYPEEXTRA( SYS.XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), SYS.XMLTYPEPI('523030')), XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T('83B890200080030400000004050F320809181B23262A343503150C07292728'), refs, srvinfonum, 'respond-with-server-info', XDB.XDB$QNAME('00', 'boolean'), NULL, 'FC', '00', '00', NULL, NULL, 'boolean', NULL, NULL, 'true', NULL, NULL, NULL, NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'), NULL, NULL, '00', NULL, NULL, '00', '01', '01', '01', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(e) into refsrvinfo; commit; -- update elements and PD for seq kid of httpconfig 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; skidhttpelems.extend(3); -- add the cauth, realm and srvinfo elements at end of array -- TODO : try splicing in srvinfo element after max-header-size skidhttpelems(skidhttpelems.last - 2) := refcauth; skidhttpelems(skidhttpelems.last - 1) := refrealm; skidhttpelems(skidhttpelems.last) := refsrvinfo; update xdb.xdb$sequence_model m set m.xmldata.elements = skidhttpelems, m.xmldata.sys_xdbpd$ = XDB.XDB$RAW_LIST_T('230200000081801707') where ref(m)= refskidhttp; commit; -- update annotations for the complex type declaration for httpconfig anypart := ''; for i in 1..skidhttpelems.last loop select e.xmldata.property.prop_number, e.xmldata.property.name into elem_propno, elem_name from xdb.xdb$element e where ref(e) = skidhttpelems(i); -- dbms_output.put_line('httpconfig kid ' || (i-1) || ' ' || elem_propno || ' ' || elem_name); anypart := anypart || chr(10) || ' '; end loop; anypart := anypart || chr(10) || ''; -- needed in the 11.1.0.7 upgrade to main 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)), c.xmldata.annotation.sys_xdbpd$ = XDB.XDB$RAW_LIST_T('1301000000') where c.xmldata.parent_schema = refs and ref(c)=refhttptype; -- add '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('330008020000118B8003'), 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), XDB.XDB$FACET_T(XDB.XDB$RAW_LIST_T('130200000102'), NULL, 'custom', '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 sysconfig element for trust updateSysConfigForTrust(refs, refcauthtrusttyp); -- update complex_types list in schema to include the new custom-authentication and trust types -- update num_props in schema select s.xmldata.complex_types into clistinsch from xdb.xdb$schema s where s.xmldata.schema_url = schema_url; clistinsch.extend(2); clistinsch(clistinsch.last-1) := refcauthtype; clistinsch(clistinsch.last) := refcauthtrusttyp; 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 where s.xmldata.schema_url = schema_url; commit; end; / show errors; -- now upgrade XDB$CONFIG exec upgradeConfig; -- clean up drop procedure upgradeConfig; drop procedure createCustomAuthTrustType; drop procedure updateSysConfigForTrust; -------------------------------------------------- -- start upgrading xdbconfig default-type-mappings -------------------------------------------------- -- adds element: /sysconfig/default-type-mappings -- Note: Upgrading 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 upgradeConfigDTM as i NUMBER(38); pnum NUMBER(38); numprops NUMBER(38); elem_propnum NUMBER(38); cplx_ref REF SYS.XMLTYPE; seq_ref REF SYS.XMLTYPE; confsch_ref REF SYS.XMLTYPE; element_ref REF SYS.XMLTYPE; simpletype_ref REF SYS.XMLTYPE; seq_elems XDB.XDB$XMLTYPE_REF_LIST_T; NUM_PROPS CONSTANT INTEGER := 199; --num_props after upgrade anypart VARCHAR2(4000); confsch_url VARCHAR2(700) := 'http://xmlns.oracle.com/xdb/xdbconfig.xsd'; begin -- ref to xdbconfig schema select ref(s) into confsch_ref from xdb.xdb$schema s where s.xmldata.schema_url = confsch_url; -- ref to complex type (sysconfig anonymous complex 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 sequence (sysconfig complex type sequence) select c.xmldata.sequence_kid into seq_ref from xdb.xdb$complex_type c where ref(c) = cplx_ref; -- sequence elements select m.xmldata.elements into seq_elems from xdb.xdb$sequence_model m where ref(m) = seq_ref; -- num_props before doing upgrade (used for checking only) select s.xmldata.num_props into numprops from xdb.xdb$schema s where s.xmldata.schema_url = confsch_url; -- already upgraded? if (numprops >= NUM_PROPS) then dbms_output.put_line ('xdbconfig schema already upgraded'); return; end if; -- create simple type declaration for default-type-mappings insert into xdb.xdb$simple_type st(st.xmlextra, st.xmldata) values (SYS.XMLTYPEEXTRA(SYS.XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), SYS.XMLTYPEPI('523030')), XDB.XDB$SIMPLE_T(XDB.XDB$RAW_LIST_T('23020000000106'), confsch_ref, NULL, '00', 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, 'pre-11.2', '00', NULL), XDB.XDB$FACET_T(XDB.XDB$RAW_LIST_T('130200000102'), NULL, 'post-11.2', '00', NULL)), NULL, NULL), NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(st) into simpletype_ref; -- create default-type-mappings element pnum := xdb.xdb$propnum_seq.nextval; insert into xdb.xdb$element e (e.xmlextra, e.xmldata) values (SYS.XMLTYPEEXTRA(SYS.XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), SYS.XMLTYPEPI('523030')), XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T('839A1020008003040000000432010809181B23262A343503150C07292728'), confsch_ref, pnum, 'default-type-mappings', NULL, NULL, '0103', '00', '00', NULL, NULL, 'string', NULL, NULL, NULL, simpletype_ref,--smpl_type_decl simpletype_ref,--type_ref NULL, NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'), NULL, NULL, '00', NULL, NULL, '00', '01', '01', '01', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL) ) returning ref(e) into element_ref; -- add one element to the end of varray seq_elems.extend(1); seq_elems(seq_elems.last) := element_ref; -- update element 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('23020002000200182067656E65726963205844422070726F7065727469657320020E1E2070726F746F636F6C2073706563696669632070726F706572746965732081801B07') where ref(m) = seq_ref; -- update annotations for the complex type declaration for sysconfig anypart := ''; for i in 1..seq_elems.last loop select e.xmldata.property.prop_number into elem_propnum from xdb.xdb$element e where ref(e) = seq_elems(i); anypart := anypart || chr(10) || ' '; end loop; anypart := anypart || chr(10) || ''; 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 num_props for schema update xdb.xdb$schema s set s.xmldata.num_props = NUM_PROPS where s.xmldata.schema_url = confsch_url; commit; end; / show errors; exec upgradeConfigDTM; -- clean up drop procedure upgradeConfigDTM; ------------------------------------------------ -- end upgrading xdbconfig default-type-mappings ------------------------------------------------ ------------------------------------------------ -- upgrading xdbconfig for Expire Headers ------------------------------------------------ create or replace procedure upgradeConfigForExpire as schema_url VARCHAR2(700) := 'http://xmlns.oracle.com/xdb/xdbconfig.xsd'; refs REF SYS.XMLTYPE; idem NUMBER := 0; numprops NUMBER(38); CONFIG_PRPONUMS_EXP CONSTANT INTEGER := 203; 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; 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 any of the expire types or elements already 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 > 0) then dbms_output.put_line('xdbconfig schema already upgrated for expire headers'); return; end if; -- create the expire-pattern element exppatnum := xdb.xdb$propnum_seq.nextval; insert into xdb.xdb$element e (e.xmlextra, e.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T('83B810200080030000000004050809181B23262A32343503150C07292728'), refs, exppatnum,'expire-pattern', XDB.XDB$QNAME('00', 'string'), NULL, '01', '00', '00', NULL, NULL, 'string', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'), NULL, NULL, '00', NULL, NULL, '00', '01', '01', '01', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(e) into exppatelem; dbms_output.put_line('1. expire-pattern element created'); -- create simple type fro expire-default insert into xdb.xdb$simple_type st (st.xmlextra, st.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$SIMPLE_T(XDB.XDB$RAW_LIST_T('23020000000106'), refs, NULL, '00', XDB.XDB$SIMPLE_DERIVATION_T(XDB.XDB$RAW_LIST_T('330004020000110A'), NULL, XDB.XDB$QNAME('00', 'string'),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, '(now|modification)(\s(plus))?(\s(([1]\s(year))|([0-9]*\s(years))))?(\s(([1]\s(month))|([0-9]*\s(months))))?(\s(([1]\s(week))|([0-9]*\s(weeks))))?(\s(([1]\s(day))|([0-9]*\s(days))))?(\s(([1]\s(hour))|([0-9]*\s(hours))))?(\s(([1]\s(minute))|([0-9]*\s(minutes))))?(\s(([1]\s(second))|([0-9]*\s(seconds))))?', '00', NULL)), NULL, NULL, NULL), NULL, NULL, NULL, NULL, NULL, NULL, NULL)) return ref(st) into expdeftype; dbms_output.put_line('2. simple type for expire-default created'); -- create expire-default element expdefnum := xdb.xdb$propnum_seq.nextval; insert into xdb.xdb$element e (e.xmlextra, e.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T('839A10200080030000000004010809181B23262A32343503150C07292728'), refs, expdefnum,'expire-default', NULL, NULL, '01', '00', '00', NULL, NULL, 'string', NULL, NULL, NULL, expdeftype, expdeftype, NULL, NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'), NULL, NULL, '00', NULL, NULL, '00', '01', '01', '01', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(e) into expdefelem; dbms_output.put_line('3. expire-default element created'); skidexpmap := XDB.XDB$XMLTYPE_REF_LIST_T(); skidexpmap.extend(2); skidexpmap(1) := exppatelem; skidexpmap(2) := expdefelem; -- seq(expire-pattern, expire-default) insert into xdb.xdb$sequence_model m (m.xmlextra, m.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$MODEL_T(XDB.XDB$RAW_LIST_T('230200000081800207'), refs, 0, NULL, skidexpmap, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(m) into refskidmap; dbms_output.put_line('4. seq(expire-pattern, expire-default) created'); -- create annotation for the type of expire-mapping i := 1; anypart := ''; anypart := anypart || chr(10) || ' '; i := 2; anypart := anypart || chr(10) || ' '; anypart := anypart || chr(10) || ''; -- create complex type declaration for expire-mapping insert into xdb.xdb$complex_type c (c.xmlextra, c.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$COMPLEX_T(XDB.XDB$RAW_LIST_T('33090000000000030D0E13'), refs, NULL, NULL, '00', '00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, refskidmap, NULL, NULL, 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), NULL, NULL, '01', NULL, NULL, NULL, NULL)) returning ref(c) into expmaptype; dbms_output.put_line('5. complex type for expire-mapping created'); -- create expire-mapping element expmapnum := xdb.xdb$propnum_seq.nextval; insert into xdb.xdb$element e (e.xmlextra, e.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T('839800201080030C0000000432331C0809181B23262A3435031507292728'), refs, expmapnum, 'expire-mapping', NULL, NULL, '0102', '00', '00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, expmaptype, NULL, NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'), NULL, NULL, '00', NULL, NULL, '00', '00', '01', '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, expmaptype, NULL, NULL, 0, 'unbounded', '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(e) into expmapelem; dbms_output.put_line('6. expire-mapping element created'); -- seq(expire-mapping) skidexp := XDB.XDB$XMLTYPE_REF_LIST_T(); skidexp.extend(1); skidexp(1) := expmapelem; insert into xdb.xdb$sequence_model m (m.xmlextra, m.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$MODEL_T(XDB.XDB$RAW_LIST_T('23020000000107'), refs, 0, NULL, skidexp, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(m) into refskidexp; dbms_output.put_line('7. seq(expire-mapping) created'); -- create annotation for expire-type i := 1; anypart := ''; anypart := anypart || chr(10) || ' '; anypart := anypart || chr(10) || ''; -- create expire-type insert into xdb.xdb$complex_type c (c.xmlextra, c.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$COMPLEX_T(XDB.XDB$RAW_LIST_T('3309104000000C00030D0E1316'), refs, NULL, 'expire-type', '00', '00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, refskidexp, NULL, NULL, 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), NULL, NULL, '01', NULL, NULL, NULL, 143)) returning ref(c) into exptype; dbms_output.put_line('8. expire-type created'); -- create the expire element expnum := xdb.xdb$propnum_seq.nextval; insert into xdb.xdb$element e (e.xmlextra, e.xmldata) values(XMLTYPEEXTRA(XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500004786462630029687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F786462636F6E6669672E787364', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462'), XMLTYPEPI('523030')), XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T('83B80020008003040000000405320809181B23262A3435031507292728'), refs, expnum, 'expire', XDB.XDB$QNAME('01', 'expire-type'), NULL, '0102', '00', '00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, exptype, NULL, NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'), NULL, NULL, '00', NULL, NULL, '00', '00', '01', '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(e) into expelem; dbms_output.put_line('9. expire element created'); commit; -- update elements and PD for seq kid of httpconfig 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; skidhttpelems.extend(1); skidhttpelems(skidhttpelems.last) := expelem; update xdb.xdb$sequence_model m set m.xmldata.elements = skidhttpelems, m.xmldata.sys_xdbpd$ = XDB.XDB$RAW_LIST_T('230200000081801807') where ref(m)= refskidhttp; dbms_output.put_line('10. httpconfig: updated sequence kid and PD'); commit; -- update annotations for the complex type declaration for httpconfig anypart := ''; 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); anypart := anypart || chr(10) || ' '; end loop; anypart := anypart || chr(10) || ''; 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('11. httpconfig: updated annotation'); -- update complex_types list in schema to include the new expire-type types -- update num_props in schema select s.xmldata.complex_types into clistinsch from xdb.xdb$schema s where s.xmldata.schema_url = schema_url; clistinsch.extend(1); clistinsch(clistinsch.last) := exptype; update xdb.xdb$schema s set s.xmldata.complex_types = clistinsch, s.xmldata.sys_xdbpd$ = XDB.XDB$RAW_LIST_T('43163C8600050084010084020184030202081820637573746F6D697A6564206572726F7220706167657320020A3E20706172616D6574657220666F72206120736572766C65743A206E616D652C2076616C7565207061697220616E642061206465736372697074696F6E20200B0C110482800B818002828004131416120A170D'), s.xmldata.num_props = CONFIG_PRPONUMS_EXP where s.xmldata.schema_url = schema_url; dbms_output.put_line('12. schema: updated complex type list, num_props and PD'); commit; end; / show errors; exec upgradeConfigForExpire; -- clean up drop procedure upgradeConfigForExpire; ------------------------------------------------ -- end upgrading xdbconfig for Expire Headers ------------------------------------------------ Rem Manually add xlink-config/pre-condition property to XDB resource config schema declare seq_ref REF SYS.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 SYS.XMLTYPE; elem_ref_precond REF SYS.XMLTYPE; elem_typeref_precond REF SYS.XMLTYPE; elem_propno number(38); anypart varchar2(4000); begin config_schema_url := 'http://xmlns.oracle.com/xdb/XDBResConfig.xsd'; if not dbms_xdbmig_util.element_exists_complextype(config_schema_url, 'xlink-config', 'pre-condition') then 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; -- create pre-condition element select ref(c) into elem_typeref_precond from xdb.xdb$complex_type c where c.xmldata.name = 'condition' and c.xmldata.parent_schema = config_schema_ref; insert into xdb.xdb$element e (e.xmlextra, e.xmldata) values (SYS.XMLTYPEEXTRA(SYS.XMLTYPEPI('4E0020687474703A2F2F7777772E77332E6F72672F323030312F584D4C536368656D61', '500003786462001B687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F786462', '500006726573636667002C687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F7864622F584442526573436F6E6669672E787364'), SYS.XMLTYPEPI('523030')), XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T('83B800200080030C000000040532330809181B23262A3435031507292728'), config_schema_ref, xdb.xdb$propnum_seq.nextval, 'pre-condition', XDB.XDB$QNAME('02', 'condition'), NULL, '0102', '00', '00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, elem_typeref_precond, NULL, NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'), NULL, NULL, '00', NULL, NULL, '00', '00', '01', '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, '1', '00', '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) returning ref(e) into elem_ref_precond; -- extend elem_arr and add new element elem_arr.extend(1); elem_arr(elem_arr.last) := elem_ref_precond; -- 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('230200000081800407') where ref(m) = seq_ref; -- edit annotation kidlist -- construct kidlist from element and attribute lists anypart := ''; 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) || ' '; 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) || ' '; end loop; anypart := anypart || chr(10) || ''; 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; / Rem clean up updown utilities @@dbmsxuducu.sql -- BEGIN: upgrade for XML Index drop table XDB.XDB$XIDX_IMP_T; create table XDB.XDB$XIDX_IMP_T (index_name VARCHAR2(40), schema_name VARCHAR2(40), id VARCHAR2(40), data CLOB); grant insert, select, delete on XDB.XDB$XIDX_IMP_T to public; -- create ximetadata_pkg package CREATE OR REPLACE PACKAGE XDB.ximetadata_pkg AS FUNCTION getIndexMetadata (idxinfo IN sys.ODCIIndexInfo, expver IN VARCHAR2, newblock OUT number, idxenv IN sys.ODCIEnv) return VARCHAR2; FUNCTION getIndexMetadataCallback (idxinfo IN sys.ODCIIndexInfo, expver IN VARCHAR2, newblock OUT number, idxenv IN sys.ODCIEnv) return VARCHAR2; FUNCTION utlgettablenames(idxinfo IN sys.ODCIIndexInfo) return BOOLEAN; END ximetadata_pkg; / show errors; CREATE OR REPLACE PACKAGE BODY XDB.ximetadata_pkg AS -- 'iterate' is a package level variable used to maintain state across calls -- by export in this session. iterate NUMBER := 0; FUNCTION getIndexMetadata (idxinfo IN sys.ODCIIndexInfo, expver IN VARCHAR2, newblock OUT number, idxenv IN sys.ODCIEnv) return VARCHAR2 IS BEGIN -- We are generating only one PL/SQL block consisting of one line of code. newblock := 1; IF iterate = 0 THEN -- Increment iterate so we'll know we're done next time we're called. iterate := iterate + 1; RETURN getIndexMetadataCallback (idxinfo, expver, newblock, idxenv); ELSE -- reset iterate for next index iterate := 0; -- Return a 0-length string; we won't be called again for this index. RETURN ''; END IF; END getIndexMetadata; function getIndexMetadataCallback (idxinfo IN sys.ODCIIndexInfo, expver IN VARCHAR2, 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, expver, expver INDICATOR, newblock,newblock INDICATOR, idxenv, idxenv INDICATOR struct, RETURN OCIString); FUNCTION utlgettablenames(idxinfo IN sys.ODCIIndexInfo) return BOOLEAN is language C name "QMIX_TABLEUTILS" library XDB.XMLINDEX_LIB with context parameters ( context, idxinfo, idxinfo INDICATOR struct, RETURN INDICATOR sb4, return); END ximetadata_pkg; / show errors; grant execute on XDB.ximetadata_pkg to public; / create or replace public synonym ximetadata_pkg for XDB.ximetadata_pkg; / -- alter type in xdb.XMLIndexMethods -- first drop the old definition ALTER TYPE xdb.XMLIndexMethods DROP static function ODCIIndexGetMetadata(idxinfo IN sys.ODCIIndexInfo, expver IN VARCHAR2, len_newblock OUT number, idxenv IN sys.ODCIEnv) return VARCHAR2; -- create new definition ALTER TYPE xdb.XMLIndexMethods ADD 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 ODCIIndexUtilGetTableNames(ia IN sys.ODCIIndexInfo, read_only IN PLS_INTEGER, version IN varchar2, context OUT PLS_INTEGER) return BOOLEAN; ALTER TYPE xdb.XMLIndexMethods ADD static procedure ODCIIndexUtilCleanup (context IN PLS_INTEGER); -- create body 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; static function ODCIIndexGetMetadata(idxinfo IN sys.ODCIIndexInfo, expver IN VARCHAR2, newblock OUT number, idxenv IN sys.ODCIEnv) return VARCHAR2 is begin return XDB.ximetadata_pkg.getIndexMetadata(idxinfo, expver, newblock, idxenv); end ODCIIndexGetMetadata; -- path table and secondary indexes on it are already exported in schema-mode -- this routine should only expose them for Transportable Tablespaces, -- via DataPump static function ODCIIndexUtilGetTableNames(ia IN sys.ODCIIndexInfo, read_only IN PLS_INTEGER, version IN varchar2, context OUT PLS_INTEGER) return BOOLEAN is begin return XDB.ximetadata_pkg.utlgettablenames(ia); end ODCIIndexUtilGetTableNames; static procedure ODCIIndexUtilCleanup (context PLS_INTEGER) is begin -- dummy routine return; end ODCIIndexUtilCleanup; end; / show errors; ---------------------------------------------------------------------------- -- Support new partitioning methods alter indextype XDB.xmlindex using XDB.XMLIndexMethods with local partition with system managed storage tables; -- END: upgrade for XML Index -- 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; alter system flush shared_pool;