Skip to Content
Author's profile photo Kiran Bodla

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;

Assigned Tags

      1 Comment
      You must be Logged on to comment or reply to a post.
      Author's profile photo Gajen Bungaroo
      Gajen Bungaroo

      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!