-- Modify and create select that will get filename and content from a table.
-- set serverouput on
-- make sure flex_ws_api package is installed in same schema that you run this.
declare
 l_filename varchar2(100);
 l_login varchar2(20);
 l_ddocname varchar2(20);
 l_BLOB BLOB;
 l_CLOB CLOB;
 l_envelope CLOB;
 l_response_msg varchar2(32767);
begin
  select name, content, ddocname
  into l_filename, l_BLOB, l_ddocname
  from pdfs
  where id = 1;

  l_login := 'test';
  l_CLOB := flex_ws_api.blob2clobbase64(l_BLOB);

  l_envelope := '<?xml version=''''1.0'''' encoding=''''UTF-8''''?>';
  l_envelope := l_envelope || '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:chec="http://www.stellent.com/CheckIn/">';
  l_envelope := l_envelope || '<soapenv:Header/>';
  l_envelope := l_envelope || '  <soapenv:Body>';
  l_envelope := l_envelope || '     <chec:CheckInUniversal>';

  if length(ltrim(rtrim(l_ddocname))) > 0 then
    l_envelope := l_envelope || '        <chec:dDocName>'||l_ddocname||'</chec:dDocName>';
  end if;

  l_envelope := l_envelope || '        <chec:dDocTitle>'||l_filename||'</chec:dDocTitle>';
  l_envelope := l_envelope || '        <chec:dDocType>WebContent</chec:dDocType>';
  l_envelope := l_envelope || '        <chec:dDocAuthor>'||l_login||'</chec:dDocAuthor>';
  l_envelope := l_envelope || '        <chec:dSecurityGroup>Public</chec:dSecurityGroup>';
  l_envelope := l_envelope || '        <chec:dDocAccount>WebContent</chec:dDocAccount>';
  l_envelope := l_envelope || '        <chec:primaryFile>';
  l_envelope := l_envelope || '           <chec:fileName>'||l_filename||'</chec:fileName>';
  l_envelope := l_envelope || '           <chec:fileContent>'||l_CLOB||'</chec:fileContent>';
  l_envelope := l_envelope || '        </chec:primaryFile>';
  l_envelope := l_envelope || '     </chec:CheckInUniversal>';
  l_envelope := l_envelope || '  </soapenv:Body>';
  l_envelope := l_envelope || '</soapenv:Envelope>';

  l_xmltype := flex_ws_api.make_request(
    p_url               => 'https://server.domain.com/_dav/cs/idcplg',
    p_action            => 'http://www.stellent.com/Search/',
    p_envelope          => l_envelope,
    p_username          => 'adminuser',
    p_password          => 'password', 
    p_wallet_path       => 'file:/u01/app/oracle/admin/SID/wallet',
    p_wallet_pwd        => 'walletpassword'
  );

  l_response_msg := flex_ws_api.parse_response(p_collection_name=>'STELLENT_CHECKIN',p_xpath=>'//idc:CheckInUniversalResponse/idc:CheckInUniversalResult/idc:StatusInfo/idc:statusMessage/text()',p_ns=>'xmlns:idc="http://www.stellent.com/CheckIn/"');

  dbms_output.put_line(l_response_msg);
exception
  when others then
    dbms_output.put_line(l_response_msg);
    raise;
end;
/