Get datastore details form bods repository
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;
Hello!
for example i have 4 configurations for a datastore. is there a way for adding a column to query and know which configuration is true and which one is false? or which one is set as "Yes" in default config and which one as "No" ???
Thanks!