Do you want to delete mds files easily?

Hi all,

You can delete file (document) or folder (package) by calling mds_internal_common package procedures;

declare
p1 number;
p2 number;
p3 number;
p4 number;
p5 varchar2(200):='/apps/dvm/NYS';  -- thing you want to delete
pathtype varchar2(20);
o1 number;
begin 
select p.path_partition_id,p.path_docid,p.path_low_cn,p.path_version,p.path_name,p.path_type into p1,p2,p3,p4,p5,pathtype from mds_paths p where p.path_fullname=p5 and p.path_high_cn is null;

if(pathtype='DOCUMENT')
then
mds_internal_common.deleteDocument(partitionID =>p1, docID =>p2 , lowCN => p3, version =>p4 , docName => p5, force => 1);
end if;

if(pathtype='PACKAGE')
then
mds_internal_common.deletePackage(result => o1, partitionID => p1, pathID => p2 );
dbms_output.put_line(o1);
end if;

end;

Accessing raw Audit Trail xml directly from database

Hi Everyone, 

If you need to access the audit trail directly from database then what you have to do is simple. First query the audit_trail table. You'll see several lines for a single cikey. Convert chunks to binary and concatenate all rows for a cikey but do this for each block separately. Finally uncompress it and what you'll get is a raw audit trail xml. You can convert it to table or just search a specific keyword or even you can code your own display engine if you don't like the oracle's em console. 

For me this function is important because for an archived instance I don't have to move the instance to production in order to see the audit trail. 


Function works for both 10g and 11g. I didn't have the chance to test 12c but if audit_trail is still there then I think it will work for 12c as well..

You will need blob_to_clob function. you can get it from here; http://www.dba-oracle.com/t_convert_blob_to_clob_script.htm



create or replace function get_audit_xml(vcikey in number) return clob is
  tmpblob blob;
  vblob blob;
  audit_trail clob:='<audit_trail>';
begin
for b in (select distinct block from audit_trail where block=1 and cikey=vcikey order by 1) 
  loop
  tmpblob:=to_blob('0');
  vblob:=to_blob('0'); 
  for x in (select row_number() over (order by count_id) row_num ,to_blob(log) chunk from audit_trail where cikey=vcikey  and block=b.block)
   loop 
      if(x.row_num=1) then
        tmpblob:= x.chunk;
      else
        dbms_lob.append(dest_lob => tmpblob,src_lob => x.chunk);
      end if;
   end loop;
 utl_compress.lz_uncompress(src => tmpblob, dst => vblob);
audit_trail:=audit_trail||blob_to_clob(vblob);
end loop;
return audit_trail||'</audit_trail>';
end get_audit_xml; 

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

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.

oraclesoatricks.blogspot.com

Hello everybody!

I will share some interesting and some life saving tricks in this blog which will be related to oracle bpel and soa suite (mostly administration tricks and some may be development).

First of all I'd like to talk a bit about myself; I'm an expert administrator working at Turkcell for more than 8 years and 6 years had passed with working on Oracle Middleware products. 

Nowadays I'm focused on Oracle SOA suite 11g but we also have legacy systems running bpel 10.1.2 and soa suite 10.1.3.

So; be ready to hear our first trick about "bpel 10.1.2" which will be a real life saving one in a few days..