Skip to Content

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;
1 Comment
You must be Logged on to comment or reply to a post.
  • 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!