Accessing to MDS files from PL/SQL code (SOA 11G)

Hi All,

Accessing to MDS files sometimes can be very time consuming. 

First option; you can use IDE but let's be honest It's not very user friendly and not very fast when we talk about accessing MDS files.

Second option; we can also append mds full path to any composite's wsdl address. This is easier for me because I'm an administrator and not running the JDEV all the time :)

for example: If your composite's wsdl is @ http://www.yourserver.com/soa-infra/services/BlaBlaComposite/client?wsdl 

then you can change it to 

http://www.yourserver.com/soa-infra/services/BlaBlaComposite/apps/WSDL/3rdParty/abc.xsd

and put the address to any browser in order to access abc.xsd.


But still for me it's not easy. So I investigated a little and found that engine uses exportDocumentById method while accessing the files. But I had to debug a little more because there is a 32000 byte limit in a single call. So I have to make a loop and also find correct input for the exportDocumentById  function in order to get consecutive chunks. 

With this function you can get your wsdls, xsds or your bpel, mediator code from MDS.


create or replace function get_mds_data(fullpath in varchar2)
return clob is
  vchunk varchar (32000);
  vexport number(1);
  pd number;
  pc number;
  pv number;
  retClob clob;
begin
select path_docid,path_contentid,path_version  into pd,pc,pv 
       from mds_paths p 
        where
       p.path_fullname=fullpath and path_high_cn is null;    
 mds_internal_shredded.exportDocumentById(vchunk,vexport,1,pd,pc,pv,1);
 retClob:=retClob||vchunk;
 while (vexport=0)
  loop
    mds_internal_shredded.exportDocumentById(vchunk,vexport,1,null,pc,null,null);
    retClob:=retClob||vchunk;
   end loop;
 return retClob;
end;

So this is the easiest and fastest way for me to access MDS files;

select get_mds_data('/apps/WSDL/3rdParty/abc.xsd') from dual

ps: compile this function at your mds schema or if you want to compile it at another schema then add schema names to tables and functions and also give grants . 


You can use this function in your posts or website but please give credit to my blog when doing this.