Home » SQL & PL/SQL » SQL & PL/SQL » BI Publisher Apex web Services (Oracle 11g)
BI Publisher Apex web Services [message #677279] Mon, 09 September 2019 12:58 Go to next message
akarra
Messages: 17
Registered: August 2011
Location: Housston
Junior Member
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

My Code
=========


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 => ' http://swa78webld01:9704/xmlpserver/services/v2/ReportService',
p_action => ' ' ,
p_envelope => l_envelope);

END;


Error
=====

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.





Thanks
BI Publisher Apex web Services [message #677280 is a reply to message #677279] Mon, 09 September 2019 12:59 Go to previous message
akarra
Messages: 17
Registered: August 2011
Location: Housston
Junior Member
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

My Code
=========


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 => ' http://swa78webld01:9704/xmlpserver/services/v2/ReportService',
p_action => ' ' ,
p_envelope => l_envelope);

END;


Error
=====

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.





Thanks
Previous Topic: IIF and Oracle
Next Topic: Procedure SendMail Gmail
Goto Forum:
  


Current Time: Mon Oct 14 04:28:33 CDT 2019