--
connect &DBA/&DBAPASSWORD@&TNSALIAS
--

/
--
-- Delete the any Global XML Schema that defines IMAGE_METADATA_TABLE
--
-- May need to add code to remove all refs to the rows in this table.
--
set serveroutput on
--
declare
  table_does_not_exist exception;
  PRAGMA EXCEPTION_INIT( table_does_not_exist , -942 );
  
  cursor getXMLSchemas
  is
  select SCHEMA_URL, OWNER
    from ALL_XML_SCHEMAS
   where XMLExists
         (
            'declare namespace xsd = "http://www.w3.org/2001/XMLSchema"; (: :)
             declare namespace xdb = "http://xmlns.oracle.com/xdb"; (: :)
             $s/xsd:schema/xsd:element[@xdb:defaultTable="IMAGE_METADATA_TABLE"]'
             passing schema as "s"
         )
     and LOCAL = 'NO';   
begin
  for s in getXMLSchemas loop
    dbms_output.put_line('Deleting Global XML Schema ' || s.schema_url || '. Owner = ' || s.OWNER);
    execute immediate 'alter session set current_schema = ' || s.OWNER;
    dbms_xmlschema.deleteSchema
    (
       s.schema_url,
       dbms_xmlschema.DELETE_CASCADE_FORCE
    );
    begin
      execute immediate 'drop table "' || s.OWNER || '".IMAGE_METADATA_ERROR_TABLE';
      dbms_output.put_line('Dropped table "' || s.OWNER || '".IMAGE_METADATA_ERROR_TABLE');
    exception
      when table_does_not_exist then
          null;
  end;    
  end loop;
end;
/
set serveroutput off
--
disconnect
--
