=====XML per Spool Out aus SQL*Plus schreiben - Ein Datenbank Konfigurationsdokument erstellen und auswerten====


====XML in SQL*Plus spoolen mit "spool <dateiname>"====

<fc #800000>**Das Problem**</fc>

Wenn in SQL*Plus ein XML Dokument einfach so gespoolt wird, kann es passieren das sich Umbrüche/Leerzeichen in den XML Elemente einschleichen und damit das XML "invalid" wird.

Der Effekt:
<code xml>
 <ROLE_T><VERS_
MAJOR>1</VERS_MA
JOR><VERS_MINOR>
</code>

Mit Linesize und Long Parametern lässt sich das zwar für kleine XML Dateien optimieren, spätestes ab 32xxx Zeichen ist dann aber Schluss.


<fc #008000><fc #800000>Lösung</fc></fc>

In PL/SQL Block wird das Clob XML Dokument so ausgeben, das sichergestellt ist, das ein XML Element nicht aufgetrennt wird indem immer  bis zu einem ">" ausgeben wird.

<code sql>
...
  v_loopcnt:=0; 
  v_last_pos:=1;
  v_bytes_write:=0;
  
  WHILE v_bytes_write <= v_length 
  LOOP
 
      -- try to find closing xml!
      v_end_char:=DBMS_LOB.SUBSTR (v_result, 1, v_last_pos+v_readsize+v_readpt );
     
      if  v_end_char =  '>'  
	 or v_readsize > v_length 
	 or v_last_pos+v_readsize+v_readpt > v_length then

	
	 dbms_output.put_line( DBMS_LOB.SUBSTR (v_result, v_readsize+v_readpt+1, v_last_pos ) );		
		    
	 v_last_pos:=v_last_pos+v_readsize+v_readpt+2;
	 					
	 v_bytes_write:=v_bytes_write+v_readsize+v_readpt;			   
		    
	 v_readpt:=1;
			
        else
	  v_readpt:=v_readpt+1;
    	end if;

    END LOOP;
...
</code>


----

----

==== Mit DBMS_METADATA  ein DB User Konfigurationsdokument erzeugen ====


Aufgabe:

Per Ansible sollen in einer DB Umgebung alle User/Rolen/Profile eingesammelt und in einem Repository analysiert werden.

Normalerweise wäre in der Ansible Welt JSON die richtige Wahl für so ein Dokument, mit DBMS_METADATA (siehe https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_METADATA.html) lässt sich so etwas ähnliches auch mit den Standards der DB als XML Datei erzeugen. 

Dieses lässt sich dann auch gut per Anisble erzeugen und abholen und dann später in der Datenbank auswerten.

Das SQL Script um die Daten zu ermittlen:

<code sql>

set long 2000000;
set pagesize 0
set linesize 2300
set heading off
set echo off
set feedback off

-- define the name of the spoolfile
set termout off
column SPOOL_NAME_COL new_val SPOOL_NAME
column SPOOL_NAME_COL format a60

select  SYS_CONTEXT('USERENV', 'HOST')||'_'||global_name||'.xml' 
        as SPOOL_NAME_COL
  from global_name
/
set termout on

--start to spool
spool &&SPOOL_NAME


set serveroutput on



DECLARE
  cursor c_orauser_schemas
  is 
  select username 
   from dba_users 
   where ORACLE_MAINTAINED ='Y'
  union
  select 'PUBLIC' from dual
  order by 1;
  
  v_handle       NUMBER;
  v_transform_handle  NUMBER;
  v_xml         SYS.XMLType;
  v_result      CLOB;
  v_temp        CLOB;
  -- 
  v_length   pls_INTEGER;
  v_readpt   pls_INTEGER := 0;
  v_readsize pls_INTEGER := 2000; 
  v_loopcnt  pls_INTEGER := 0;
  v_end_char varchar2(1);
  v_last_pos pls_integer;
  v_bytes_write pls_integer;
  v_user_list varchar2(32000);
  --
  v_ctx dbms_xmlgen.ctxHandle;
  v_sql varchar2(32767);
	
BEGIN
  
  v_result:='<DBUSER_SHEET>';
  
   -- ==================================================
   -- get DB Meta Data
   -- Use dbms_xmlgen to get result of select in XML Format
   --
   v_result:=v_result||'<DBDBINFO>';	
   v_sql := 'select * from gv$database';
   v_ctx := dbms_xmlgen.newContext(v_sql);
   v_temp:=dbms_xmlgen.getXml(v_ctx);
   v_result:=v_result||v_temp;
   v_result:=v_result||'</DBDBINFO>';

   v_result:=v_result||'<DBDBINSTANCE>';	
   v_sql := 'select * from gv$instance';
   v_ctx := dbms_xmlgen.newContext(v_sql);
   v_temp:=dbms_xmlgen.getXml(v_ctx);
   v_result:=v_result||v_temp;
   v_result:=v_result||'</DBDBINSTANCE>';	   
   
   v_result:=v_result||'<DBDBCONTAINER>';	
   v_sql := 'select * from v$pdbs';
   v_ctx := dbms_xmlgen.newContext(v_sql);
   v_temp:=dbms_xmlgen.getXml(v_ctx);
   v_result:=v_result||v_temp;
   v_result:=v_result||'</DBDBCONTAINER>';	
   
   v_result:=v_result||'<DBREGISTRY>';	
   v_sql := 'select * from dba_registry';
   v_ctx := dbms_xmlgen.newContext(v_sql);
   v_temp:= dbms_xmlgen.getXml(v_ctx);
   v_result:=v_result||v_temp;
   v_result:=v_result||'</DBREGISTRY>';	  
   --
   v_result:=v_result||'<DBPATHLEVEL>';	
   v_sql := 'select * from DBA_REGISTRY_SQLPATCH';
   v_ctx := dbms_xmlgen.newContext(v_sql);
   v_temp:= dbms_xmlgen.getXml(v_ctx);
   v_result:=v_result||v_temp;
   v_result:=v_result||'</DBPATHLEVEL>';	  
   --
   v_result:=v_result||'<DBTABLESPACE>';	
   v_sql := 'select * from CDB_TABLESPACES';
   v_ctx := dbms_xmlgen.newContext(v_sql);
   v_temp:= dbms_xmlgen.getXml(v_ctx);
   v_result:=v_result||v_temp;
   v_result:=v_result||'</DBTABLESPACE>';	 
   --
   v_result:=v_result||'<DBDBFILES>';	
   v_sql := 'select * from cdb_data_files';
   v_ctx := dbms_xmlgen.newContext(v_sql);
   v_temp:= dbms_xmlgen.getXml(v_ctx);
   v_result:=v_result||v_temp;
   v_result:=v_result||'</DBDBFILES>';	
   
   -- ===================================
   -- use DBMS_METADATA to get XML Results
   
   v_handle := DBMS_METADATA.OPEN('USER');
  
   v_transform_handle := DBMS_METADATA.ADD_TRANSFORM(v_handle,'SXML');
  
   dbms_metadata.set_transform_param( v_transform_handle,'PHYSICAL_PROPERTIES',FALSE) ;
   dbms_metadata.set_transform_param( v_transform_handle, 'PRETTY',             true );  
  
  -- to filter something use the name filter element!
  -- DBMS_METADATA.SET_FILTER(v_handle,'NAME','GPI');
  
  v_result:=v_result||'<DBUSERS>';	
  
  loop
   v_xml := DBMS_METADATA.FETCH_XML(v_handle);
    
	exit WHEN v_xml IS NULL;
    
	v_result :=  v_result|| v_xml.getClobVal;
   
  END LOOP;
  
  v_result:=v_result||'</DBUSERS>'	;
   
  
  v_handle := DBMS_METADATA.OPEN('PROFILE');
  
  v_result:=v_result||'<DBPROFILES>';	
  
  loop
   v_xml := DBMS_METADATA.FETCH_XML(v_handle);
    
	exit WHEN v_xml IS NULL;
    
	v_result :=  v_result|| v_xml.getClobVal;
   
  END LOOP;
  
  v_result:=v_result||'</DBPROFILES>';
  
  v_handle := DBMS_METADATA.OPEN('ROLE');
  
  v_result:=v_result||'<DBROLES>';	
  
  loop
   v_xml := DBMS_METADATA.FETCH_XML(v_handle);
    
	exit WHEN v_xml IS NULL;
    
	v_result :=  v_result|| v_xml.getClobVal;
   
  END LOOP;
  
  v_result:=v_result||'</DBROLES>';
  
  
  
  v_handle := DBMS_METADATA.OPEN('OBJECT_GRANT');
  
  v_result:=v_result||'<DBOBJECT_GRANTS>';	
  
  v_loopcnt:=0; 
  v_user_list:=' in (';
  for rec in c_orauser_schemas
   loop
	if v_loopcnt > 0 then
		v_user_list:= v_user_list ||',';
	end if;
   
    v_user_list:= v_user_list || ''''|| rec.username|| ''''; 
       
    v_loopcnt:=v_loopcnt+1;
  
  end loop;
  v_user_list:=v_user_list ||') ';
  
  --debug dbms_output.put_line( '-- User List => '||v_user_list);

  DBMS_METADATA.SET_FILTER(v_handle,'EXCLUDE_GRANTEE_EXPR',v_user_list);
  
  loop
   v_xml := DBMS_METADATA.FETCH_XML(v_handle);
    
	exit WHEN v_xml IS NULL;
    
	v_result :=  v_result|| v_xml.getClobVal;
   
  END LOOP;
  
  v_result:=v_result||'</DBOBJECT_GRANTS>';
 
    
  DBMS_METADATA.CLOSE(v_handle);
  
  -- remove all xml defs
  v_result:=replace(v_result,'<?xml version="1.0"?>','');
  v_result:=replace(v_result,'xmlns="http://xmlns.oracle.com/ku" version="1.0"','');
     
  v_result:='<?xml version="1.0"?>'||v_result;	
  v_result:=v_result||'</DBUSER_SHEET>';	
  
  -- loop through result
  v_length := DBMS_LOB.getlength (v_result);
  
  --debug dbms_output.put_line( '-----');--
  --debug dbms_output.put_line( '-- Lenght '||to_char(v_length) );
  --debug dbms_output.put_line( DBMS_LOB.SUBSTR (v_result,32000,1));
  --debug dbms_output.put_line( '-----');
  
  v_loopcnt:=0; 
  v_last_pos:=1;
  v_bytes_write:=0;
  
  WHILE v_bytes_write <= v_length 
  LOOP
		  -- try to find closing xml!
	     v_end_char:=DBMS_LOB.SUBSTR (v_result, 1, v_last_pos+v_readsize+v_readpt );
			
		 if  v_end_char =  '>'  
		     or v_readsize > v_length 
			 or v_last_pos+v_readsize+v_readpt > v_length then
		 		 
			--debug dbms_output.put_line( '-- ' || 'found end char at '|| to_char(v_last_pos+v_readsize+v_readpt ));
	        --debug dbms_output.put_line( '-- ' ||v_end_char) ;
		    --debug dbms_output.put_line( '--  read this data length ' || to_char(v_readsize+v_readpt));
			--debug dbms_output.put_line( '--  from this poistion => '  || to_char(v_last_pos) || ' - read chars '|| to_char(v_readsize +v_readpt+1) );			
			--debug dbms_output.put_line( '-----' );
			
			dbms_output.put_line( DBMS_LOB.SUBSTR (v_result, v_readsize+v_readpt+1, v_last_pos ) );		
		    
			v_last_pos:=v_last_pos+v_readsize+v_readpt+1;
						
		    v_bytes_write:=v_bytes_write+v_readsize+v_readpt;			   
		    
		    v_loopcnt := v_loopcnt + 1;
		   
		    v_readpt:=1;
			
        else
			v_readpt:=v_readpt+1;
     	end if;
  END LOOP;
END;
/

spool off

exit

</code>


----
==== XML prüfen ====

Vor dem Laden des Dokuments ist es praktischer diese auf gültiges XML zu prüfen ob alles auch geklappt hat.

Im Oracle Home liegt dazu das XML Test Programm "**schema**".


----

===== Auswerten =====

XML wird per SQL*LDR in die DB geladen und kann das über die XML Funktionen der DB ausgewertet werden.

Beispiel Abfrage mit Nested XML Verarbeitung
<code sql>

with
  function isXML( p_xml in clob)
     return varchar2
     is
      v_xml xmltype;
     begin
      v_xml:=xmltype(p_xml);
      return 'VALID';
     exception
      when others then
       return 'NOXML';
    end;
SELECT d.HOSTNAME, d.DBNAME, xd.PROFILE_NAME, xp.*
FROM   IGM_DATABASE_FACTS d,
        XMLTABLE( 
          '/DBUSER_SHEET/DBPROFILES/ROWSET/ROW/PROFILE_T'           
         PASSING xmlparse(document d.DBSHEET )
         COLUMNS 
               PROFILE_NAME         VARCHAR2(126)  PATH 'PROFILE_NAME'   
             , PROFILE_PARAMETER    xmltype        path 'PROFILE_LIST'
             , PROFILE_ID           VARCHAR2(126)  PATH 'PROFILE_ID' 
    ) xd    
    , XMLTABLE( 
     'PROFILE_LIST/PROFILE_LIST_ITEM'
      PASSING  xd.PROFILE_PARAMETER
     COLUMNS
       PROFILE_ID    VARCHAR2(126)  PATH 'PROFILE_ID'  
      ,RESOURCE_NUM  VARCHAR2(126)  PATH 'RESOURCE_NUM'  
      ,RESNAME       VARCHAR2(126)  PATH 'RESNAME'  
      ,TYPE_NUM      VARCHAR2(126)  PATH 'TYPE_NUM'  
      ,LIMIT_NUM     VARCHAR2(126)  PATH 'LIMIT_NUM'  
    ) xp
where  1=1
 and xd.PROFILE_ID=xp.PROFILE_ID
 and  isXML(d.DBSHEET)='VALID'   
</code>

Um "defekte" XML Fragmente herauszufiltern, wird immer zuerst mit der "integrieren" Funktion geprüft ob das XML auch valid ist!