-- SQL Name: Datastore_Configuration_Parser
-- Author  : Jim Egan (MTGI)
-- This PL/SQL block will construct a list of Datastores and the configurations defined within each Datastore.
-- It will only report on the first alias defined within the Datastore.  Additional code is necessary to report
-- on all the aliases.
-- This is primarly oriented towards Oracle Datastores but you should at least see the configurations for all databases.
-- The output uses the vertical bar as a column separator.  Paste the output into Excel and use the Text to Columns
-- feature to make it readable.  Then sort on the first two columns and adjust all the column widths as needed.
-- To get useful output I run this from SQL Developer using the F5 key (Run script).  It will also work in SQLPlus.
set serveroutput on

DECLARE 
vDataStore_Full               NVARCHAR2(32000) := NULL;
vCurrentDatastoreName         NVARCHAR2(64);
vDSConfiguration_BeginPos     NUMBER(5);
vDSConfiguration_EndPos       NUMBER(5);
vDSConfiguration_Instance     NUMBER(2) := 1;
vDSConfiguration_InstanceText NVARCHAR2(4000);
vDSConfiguration_Name         NVARCHAR2(64);
vDSConfiguration_DatabaseType NVARCHAR2(30);
vDSConfiguration_AliasName    NVARCHAR2(30);
vDSConfiguration_AliasValue   NVARCHAR2(30);
vDSConfiguration_OracleHost   NVARCHAR2(30);
vDSConfiguration_User         NVARCHAR2(30);
vDSConfiguration_DefaultFlag  NVARCHAR2(5);
vDSConfiguration_AliasCounter NUMBER(2);


-- Reverse sort on the seqnum so we always end on the first line
CURSOR DataStore_Cursor
IS
SELECT al_lang.name, al_langtext.* 
FROM al_langtext 
     JOIN al_lang 
       ON (al_lang.object_type = 5 and
           al_lang.object_key = al_langtext.parent_objid) 
WHERE version = (select MAX(version) 
                 FROM al_lang subq 
                 WHERE subq.normname = al_lang.normname 
                   AND subq.object_type = 5)           
ORDER BY al_lang.name, al_langtext.seqnum DESC;

-- Inline function so I don't have to create an object
FUNCTION GetTagString(pString IN NVARCHAR2, pTagStart IN NVARCHAR2, pTagEnd IN NVARCHAR2)
RETURN NVARCHAR2
IS
vTagBeginPos NUMBER(4);
vTagEndPos   NUMBER(4);
vTagContents NVARCHAR2(4000) := '';

BEGIN
	 -- Get the string value between the two tags
   vTagBeginPos := INSTR(pString, pTagStart, 1, 1);
   
   -- If the begin tag wasn't found then don't try to find the string within the tags
   IF (vTagBeginPos > 0) THEN
      vTagEndPos   := INSTR(pString, pTagEnd, vTagBeginPos + LENGTH(pTagStart), 1);
   
      vTagContents := SUBSTR(pString, 
                             vTagBeginPos + LENGTH(pTagStart), 
                             vTagEndPos - (vTagBeginPos + LENGTH(pTagStart)));
   END IF;

   RETURN vTagContents;
	
END GetTagString;

BEGIN
PRAGMA INLINE(GetTagString, 'YES');

-- Write out the headings
DBMS_OUTPUT.PUT_LINE('DataStore_Name|' ||
                     'Configuration_Name|' ||
                     'Default|' ||
                     'Database_Type|' ||
                     'Alias_Name|' ||
                     'Alias_Value|' ||
                     'Oracle_Host|' ||
                     'Oracle_User'); 

FOR DataStore_Rec IN DataStore_Cursor
LOOP

   vDataStore_Full := REPLACE(REPLACE(DataStore_Rec.TEXT_VALUE,CHR(13),''), CHR(10),'') || vDataStore_Full ;
   
   -- If the SEQNUM = 1 then this is the first line of the Datastore and we need to process it
   IF (DataStore_Rec.SEQNUM = 1) THEN
--         DBMS_OUTPUT.PUT_LINE(vDataStore_Full);

         -- Find the first configuration
         vDSConfiguration_Instance := 1;
         vDSConfiguration_BeginPos := INSTR(vDataStore_Full,'<DSConfiguration ',1, vDSConfiguration_Instance);

         -- Enter the loop if a configuration was found
         WHILE (vDSConfiguration_BeginPos > 0)
         LOOP
            -- Find the end of this specific configuration
            vDSConfiguration_EndPos := INSTR(vDataStore_Full, '</DSConfiguration>', vDSConfiguration_BeginPos, 1);
          
            -- Capture the entire string for this specific configuration
            vDSConfiguration_InstanceText := SUBSTR(vDataStore_Full, 
                                                    vDSConfiguration_BeginPos, 
                                                    vDSConfiguration_EndPos - vDSConfiguration_BeginPos + LENGTH('</DSConfiguration>'));

            -- Get the name of the configuration
            vDSConfiguration_Name := GetTagString(vDSConfiguration_InstanceText, 'name="','"');
            vDSConfiguration_DefaultFlag := GetTagString(vDSConfiguration_InstanceText, 'default="','"');
            -- Convert to Yes/No
            select DECODE(vDSConfiguration_DefaultFlag,'true','Yes','No')
            INTO vDSConfiguration_DefaultFlag 
            FROM DUAL;
            
            -- Get the database type for the configuration
            vDSConfiguration_DatabaseType := GetTagString(vDSConfiguration_InstanceText, '<database_type>','</database_type>');
            vDSConfiguration_OracleHost :=  GetTagString(vDSConfiguration_InstanceText, '<oracle_host_string>', '</oracle_host_string>');
            vDSConfiguration_User :=  GetTagString(vDSConfiguration_InstanceText, '<user>', '</user>');

						vDSConfiguration_AliasCounter := 1;
            -- Get the name/value for each alias.  There may be more than one.
            vDSConfiguration_AliasName := GetTagString(vDSConfiguration_InstanceText, '<db_alias_name' || vDSConfiguration_AliasCounter || '>', '</db_alias_name' || vDSConfiguration_AliasCounter || '>');

            WHILE (LENGTH(vDSConfiguration_AliasName) > 0)
            LOOP
               vDSConfiguration_AliasValue := GetTagString(vDSConfiguration_InstanceText, '<db_alias_user' || vDSConfiguration_AliasCounter ||'>', '</db_alias_user' || vDSConfiguration_AliasCounter ||'>');
   
   
               DBMS_OUTPUT.PUT_LINE(DataStore_Rec.NAME            || '|' ||
                                    vDSConfiguration_Name         || '|' ||
                                    vDSConfiguration_DefaultFlag  || '|' ||
                                    vDSConfiguration_DatabaseType || '|' ||
                                    vDSConfiguration_AliasName    || '|' ||
                                    vDSConfiguration_AliasValue   || '|' ||
                                    vDSConfiguration_OracleHost   || '|' ||
                                    vDSConfiguration_User); 
            
            	 vDSConfiguration_AliasCounter := vDSConfiguration_AliasCounter + 1;
            	 vDSConfiguration_AliasName := GetTagString(vDSConfiguration_InstanceText, '<db_alias_name' || vDSConfiguration_AliasCounter || '>', '</db_alias_name' || vDSConfiguration_AliasCounter || '>');
            END LOOP;
            -- Find the start of the next configuration
            vDSConfiguration_Instance := vDSConfiguration_Instance + 1;
            vDSConfiguration_BeginPos := INSTR(vDataStore_Full, '<DSConfiguration ', 1, vDSConfiguration_Instance);
         END LOOP;
      vDataStore_Full := NULL;
      vDSConfiguration_Instance := 1;
   END IF;
END LOOP;

END;