I'm trying to display a PDF document returned from a SOAP v1.1 web service. I'm able to return the XML into a collection using a process of type 'web service', but am struggling to display the document.
This is the code I'm using to (attempt to) display the document, which doesn't return anything. If I exclude the clobbase642blob conversion an Adobe Reader pop-up appears, but I get an error message stating it is not a supported file type or the file has been damaged, which I would expect since it's a clob rather than a blob.
DECLARE
l_length NUMBER;
l_file_name VARCHAR2 (4000);
l_file CLOB;
l_blobfile BLOB;
l_ext VARCHAR2 (4000);
BEGIN
SELECT xtab."fileContent", xtab."fileName"
INTO l_file, l_file_name
FROM apex_collections c,
XMLTable(XMLNAMESPACES(DEFAULT 'http://www.stellent.com/Payslip_Services/'),'//Payslip_Get_FileResponse/Payslip_Get_FileResult/downloadFile' passing xmltype001
COLUMNS "fileContent" clob PATH 'fileContent'
, "fileName" PATH 'fileName'
) xtab
where c.collection_name = 'P15_PAYSLIP_GET_FILE_RESULTS';
l_blobfile := apex_web_service.clobbase642blob(l_file);
l_length := DBMS_LOB.getlength(l_file);
If INSTR (l_file_name, '.', -1, 1) > 0 then
l_ext := SUBSTR (l_file_name, INSTR (l_file_name, '.', -1, 1) + 1);
End if;
IF (UPPER (l_ext) = 'PDF') THEN
OWA_UTIL.mime_header ('application/pdf', FALSE);
ELSIF (UPPER (l_ext) = 'DOC') THEN
OWA_UTIL.mime_header ('application/msword', FALSE);
ELSIF (UPPER (l_ext) = 'TXT') THEN
OWA_UTIL.mime_header ('text/plain', FALSE);
ELSIF (UPPER (l_ext) = 'HTML') THEN
OWA_UTIL.mime_header ('text/html', FALSE);
ELSE
owa_util.mime_header('application/octet', FALSE );
END IF;
HTP.p ('Content-length: ' || l_length);
HTP.p ( 'Content-Disposition: attachment; filename="' || l_file_name || '"' );
OWA_UTIL.http_header_close;
WPG_DOCLOAD.download_file (l_file);
END;
Apex 4.2.2
Oracle 11g 11.2.0.1.0
Any suggestions would be appreciated.
Thanks,
Graham