Home -> Application Builder -> Shared Components -> Web Service References -> Create: -> Do you want to search a UDDI registry to find the WSDL: No -> click on the next button -> WSDL Location:
http://<host>:<port>/xmlpserver/services/PublicReportService?wsdl
-> Click on Finish button
2) Shared Components -> Application Items -> Create
Name: NS
3) Create new page (page 24) ->Choose "Form" ->Choose "Form on Web Service" ->
Web Service Reference: PublicReportServiceService
Operation: getFolderContents
-> Next -> Next -> Rename new items (P24_USERNAME => P24_USERNAME_AUTH, and P24_PASSWORD => P24_PASSWORD_AUTH) -> Next -> Next -> Next -> Create Form
4) Create new report
SQL Query:
select extractValue(value(t),'/*/displayName',v('NS'))"Name"
, extractValue(value(t),'/*/creationDate',v('NS')) "creationDate"
, decode(extractValue(value(t),'/*/type',v('NS')),
'Folder',' ',
'<a href="f?p=&APP_ID.:25:&SESSION.::::P25_ABS_PATH,P25_FORMAT,P25_FILENAME:'||wwv_flow_utilities.url_encode2(extractValue(value(t),'/*/absolutePath',v('NS')))||',pdf,'||extractValue(value(t),'/*/displayName',v('NS'))||'.pdf">[PDF]</a> '
||' <a href="f?p=&APP_ID.:25:&SESSION.::::P25_ABS_PATH,P25_FORMAT,P25_FILENAME:'||wwv_flow_utilities.url_encode2(extractValue(value(t),'/*/absolutePath',v('NS')))||',html,'||extractValue(value(t),'/*/displayName',v('NS'))||'.html">[HTML]</a> '
||' <a href="f?p=&APP_ID.:25:&SESSION.::::P25_ABS_PATH,P25_FORMAT,P25_FILENAME:'||wwv_flow_utilities.url_encode2(extractValue(value(t),'/*/absolutePath',v('NS')))||',rtf,'||extractValue(value(t),'/*/displayName',v('NS'))||'.rtf">[Word]</a> '
||' <a href="f?p=&APP_ID.:25:&SESSION.::::P25_ABS_PATH,P25_FORMAT,P25_FILENAME:'||wwv_flow_utilities.url_encode2(extractValue(value(t),'/*/absolutePath',v('NS')))||',excel,'||extractValue(value(t),'/*/displayName',v('NS'))||'.xls">[Excel]</a> '
||' <a href="f?p=&APP_ID.:25:&SESSION.::::P25_ABS_PATH,P25_FORMAT,P25_FILENAME:'||wwv_flow_utilities.url_encode2(extractValue(value(t),'/*/absolutePath',v('NS')))||',ppt,'||extractValue(value(t),'/*/displayName',v('NS'))||'.ppt">[PPT]</a>') "Download"
from wwv_flow_collections c,
table(xmlsequence(extract(xmltype.createxml(c.clob001),'//getFolderContentsResponse/getFolderContentsReturn','xmlns="http://xmlns.oracle.com/oxp/service/PublicReportService"'))) t
5) Create new process on page 24 ->
Process Point: On Load- Before Header
Process:
begin
:NS:='xmlns="http://xmlns.oracle.com/oxp/service/PublicReportService';
end;
6) Create a new page(page 25)
-> Create a new region
-> Create new items:
P25_ABS_PATH Hidden and Protected
P25_FORMAT Hidden and Protected
P25_FILENAME Hidden and Protected
-> Create new computation ->
Item name: P25_ABS_PATH
Type: PL/SQL Function Body
Computation: return wwv_flow_utilities.url_decode2(:P25_ABS_PATH);
7) Create a new process:
Process point: On Load - After Region
Process:
declare
l_mime varchar2(48);
l_env clob;
l_blob blob;
v_clob clob;
l_size number;
l_ns varchar2(4000) :='xmlns="http://xmlns.oracle.com/oxp/service/PublicReportService';
l_url varchar2(4000);
v_start integer;
v_stop integer;
v_username varchar2(256) := :P24_USERNAME;
v_password varchar2(256) := :P24_PASSWORD_AUTH;
v_format varchar2(256):=:P25_FORMAT ;
v_abs_path varchar2(2000):= :P25_ABS_PATH;
v_filename varchar2(256):= :P25_FILENAME;
begin
l_env := '<soapenv:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:pub="http://xmlns.oracle.com/oxp/service/PublicReportService" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">
<soapenv:Header/>
<soapenv:Body>
<pub:runReport soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<reportRequest xsi:type="pub:ReportRequest">
<attributeFormat xsi:type="xsd:string">'||v_format||'</attributeFormat>
<attributeLocale xsi:type="xsd:string">en-US</attributeLocale>
<parameterNameValues xsi:type="pub:ArrayOfParamNameValue" soapenc:arrayType="pub:ParamNameValue[]"/>
<reportAbsolutePath xsi:type="xsd:string">'||v_abs_path||'</reportAbsolutePath>
<sizeOfDataChunkDownload xsi:type="xsd:int">10000000</sizeOfDataChunkDownload>
</reportRequest>
<userID xsi:type="xsd:string">'||v_username||'</userID>
<password xsi:type="xsd:string">'||v_password||'</password>
</pub:runReport>
</soapenv:Body>
</soapenv:Envelope>';
l_url := 'http://orasu.dyndns.info:9704/xmlpserver/services/PublicReportService';
getResponseXMLfromURL (l_url, l_env, v_clob);
--mime
SELECT DBMS_LOB.instr (v_clob,'<reportContentType>',1,1)
into v_start
from dual;
SELECT DBMS_LOB.instr (v_clob,'</reportContentType>',1,1)
into v_stop
from dual;
SELECT DBMS_LOB.SUBSTR (v_clob,v_stop-v_start-19,v_start+19)
into l_mime
from dual;
--clob
SELECT DBMS_LOB.instr (v_clob,'<reportBytes>',1,1)
into v_start
from dual;
SELECT DBMS_LOB.instr (v_clob,'</reportBytes>',1,1)
into v_stop
from dual;
l_blob := getblobfile(v_clob,v_start,v_stop);
l_size := dbms_lob.getlength(l_blob);
htp.init;
owa_util.mime_header( nvl(l_mime,'application/octet'), FALSE );
htp.p('Content-length: '||l_size);
htp.p('Content-Disposition: attachment; filename="'||replace(replace(v_filename,chr(10),null),chr(13),null)||'"');
owa_util.http_header_close;
wpg_docload.download_file( l_blob );
end;
8) Create a new function:
create or replace
function getblobfile (
p_clob in clob
, in_start in integer
, in_stop_width in integer) return blob
is
pos pls_integer := 1;
buffer blob;
res blob;
lob_len integer := dbms_lob.getlength (p_clob);
l_width pls_integer := (76 / 4 * 3)-9;
v_witdh integer;
begin
pos:= in_start+13;
lob_len:=in_stop_width;
v_witdh:=lob_len-pos;
dbms_lob.createtemporary (res, true);
dbms_lob.open (res, dbms_lob.lob_readwrite);
while (pos < lob_len) loop
IF v_witdh < l_width THEN
l_width:= v_witdh;
END IF;
buffer :=utl_encode.base64_decode(utl_raw.cast_to_raw(dbms_lob.substr (p_clob, l_width, pos)));
dbms_lob.writeappend (res, dbms_lob.getlength(buffer), buffer);
pos := pos + l_width;
v_witdh:=v_witdh- l_width;
end loop;
return res;
end getblobfile;
9) and here you can find how to create the getResponseXMLfromURL procedure
