Hello,
i am trying to call a BI publisher report using web services.
we alredy create the Envelope and the wallet folder.
now trying to call the report using Apex webservice API.
however got the below error.
please advise
Dataase : Oralce 11g
Apex Version : Application Express 4.0.0.00.46
OS : Windows 7
Error got the below
Error report -
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "APEX_040000.WWV_FLOW_WEBSERVICES_API", line 125
ORA-06512: at line 60
06503. 00000 - "PL/SQL: Function returned without value"
*Cause: A call to PL/SQL function completed, but no RETURN statement was
executed.
*Action: Rewrite PL/SQL function, making sure that it always returns
a value of a proper type.
Code tried to run to call the report
=========================
DECLARE
l_envelope CLOB;
l_response_msg CLOB;
l_blob BLOB;
l_xml XMLTYPE;
BEGIN
l_envelope := '<?xml version="1.0" encoding="UTF-8"?><soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:v2="http://xmlns.oracle.com/oxp/service/v2">
<soapenv:Header/>
<soapenv:Body>
<v2:runReport>
<v2:reportRequest>
<v2:attributeFormat>excel</v2:attributeFormat>
<v2:attributeLocale>en-US</v2:attributeLocale>
<v2:attributeTemplate>WPACorpDataExtractTemplate.xls</v2:attributeTemplate>
<v2:parameterNameValues>
<v2:listOfParamNameValues>
<!--Zero or more repetitions:-->
<v2:item>
<v2:name>P_LANGUAGE</v2:name>
<v2:values>
<v2:item>1</v2:item>
</v2:values>
</v2:item>
<v2:item>
<v2:name>P_DIVISION_ID</v2:name>
<v2:values>
<v2:item>1</v2:item>
</v2:values>
</v2:item>
<v2:item>
<v2:name>P_BRANCH_ID</v2:name>
<v2:values>
<v2:item>1</v2:item>
</v2:values>
</v2:item>
<v2:item>
<v2:name>P_FISCAL_YEAR</v2:name>
<v2:values>
<v2:item>2018-2019</v2:item>
</v2:values>
</v2:item>
<v2:item>
<v2:name>P_BRANCH_NAME</v2:name>
<v2:values>
<v2:item>Program Quality</v2:item>
</v2:values>
</v2:item>
</v2:listOfParamNameValues>
</v2:parameterNameValues>
<v2:reportAbsolutePath>/DEV/DPS/APEX/WPAPP/AnnualWPACorporateObjectivesExcelReport.xdo</v2:reportAbsolutePath>
<v2:sizeOfDataChunkDownload>-1</v2:sizeOfDataChunkDownload>
</v2:reportRequest>
<v2:userID>psapexrep</v2:userID>
<v2:password>42LatroMdiZqi</v2:password>
</v2:runReport>
</soapenv:Body>
</soapenv:Envelope>' ;
l_xml:= apex_web_service.make_request(
p_url => 'https://bidev.gnb.ca/xmlpserver/services/v2/ReportService',
p_action => ' ',
--p_version => '1.1',
--p_collection_name => 'yourcolectionname',
p_envelope => l_envelope,
p_wallet_path => '/u01/app/oracle/admin/pki/wallet');
--Step2 PARSE web service response
l_response_msg := apex_web_service.parse_response_clob(p_collection_name => 'your collection name',
p_xpath => '/soapenv:Envelope[@xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"]/soapenv:Body/runReportResponse@xmlns="http://xmlns.oracle.com/oxp/service/v2"]/runReportReturn/reportByte',
p_ns=>'xmlns="http://xmlns.oracle.com/oxp/service/v2"');
--Step 3 Decode element <reportBytes> (hold the report in base64 )
l_blob := apex_web_service.clobbase642blob(l_response_msg);
END;
Thanks Very much