Get datastore details form bods repository

[kiran2509]

Below select query displays all datastore details in target repository up to 10 configurations, to run for more just change the number 10 do desired number.

Run the query in target repository metadata database

select datastore_name,configuration,db_name,user_name from

(

with level_select as (select level as seq_num

from dual

connect by level <= 10) 

select * from 

(

SELECT distinct datastore_name,

cast (substr(list,INSTR(LIST,'<DSConfigurationdefault=',1,seq_num)+37,instr(list,'">',INSTR(LIST,'<DSConfigurationdefault=',1,seq_num))-INSTR(LIST,'<DSConfigurationdefault=',1,seq_num)-37) as varchar2(4000)) as configuration,

cast (substr(list,INSTR(LIST,'<oracle_host_string>',1,seq_num)+20,instr(list,'</oracle_host_string>',INSTR(LIST,'<oracle_host_string>',1,seq_num))-INSTR(LIST,'<oracle_host_string>',1,seq_num)-20) as varchar2(4000))  as DB_NAME,

cast (substr(list,INSTR(LIST,'<user>',1,seq_num)+6,instr(list,'</user>',INSTR(LIST,'<user>',1,seq_num)+1)-INSTR(LIST,'<user>',1,seq_num)-6) as varchar2(4000))  as user_name

FROM 

(

select di.datastore_name, replace(REPLACE(REPLACE(replace(REPLACE(replace(DBMS_XMLGEN.CONVERT(RTRIM(XMLAGG(XMLELEMENT(P, TEXT_VALUE,',') ORDER BY seqnum).GetClobVal(),','),1),chr(10),''),',',' '),'<P>',''),'</P>',''),' ',''),'<DSConfigurationdefault="true"','<DSConfigurationdefault="true" ') AS LIST

       from al_langtext lt,alvw_datastoreinfo di

     where di.datastore_id = lt.parent_objid

     group by di.datastore_name

     ORDER BY datastore_name

),level_select)where configuration is not null and db_name is not null and user_name is not null

) order by datastore_name;