Demo: https://apex.oracle.com/pls/apex/f?p=80981:3
What should happen during submit?
- get the item values on the page and its data attributes and compose a JSON string
- pass the JSON string to the database
- in the database procedure, convert the JSON parameter and compose an URL for the Report Server. Validate values, for example when the Destination Type = FILE, the Destination Name should be present
- make a Report Server request to generate output
Things missing
Before we can submit, we add some more things to the page:
- a submit button 😉
- a hidden Page Item P3_REPORT_RDF which contains the actual report implementation name, needed as part of the parameter list
- a hidden Page Item P3_URL which is handy to check the response of the Report Server in a later stage
- a Before Header Process to populate the item P3_REPORT_PDF
Add submit button
We are missing a submit button on the page. We will add it now and attach a Dynamic Action to it (actually we have to because we didn’t render a form tag).
Create Region Button P3_SUBMIT in Section Parameters:
Identification
Button Name: P3_SUBMIT
Label: Run Report
Appearance
Button Template: Text with Icon
Hot: Yes
Template Options: Icon Position → Left
Icon: fa-thumbs-up
Behavior
Action: Defined by Dynamic Action
Advanced:
Static ID: P3_Submit
Add hidden page items P3_REPORT_RDF and P3_URL in Region Select Report
Identification
Name: P3_REPORT_RDF
Type: Hidden
Identification
Name: P3_URL
Type: Hidden
Add Pre-Rendering Before Header Process
Identification
Name: Populate RDF name into P3_REPORT
Type: PL/SQL Code
Source
PL/SQL Code:
begin
if :P3_REPORT_MODULE is not null
then
select implementation_name
into :P3_REPORT_RDF
from qms_modules
where id = :P3_REPORT_MODULE
;
end if;
end;
Ready to Submit?
So now we are ready to push the “Run Report” button. But we have to add some Javascript to make it work.
So lets add some Javascript to the Dynamic Action:
This JavaScript runReport(); is a call to the function which is defined at Page Level:
Purpose of the Javascript is to loop through all the input items, compose an array and parse it as a JSON string to an APEX.SERVER.PROCESS
Page, JavaScript
Function and Global Variable Declaration:
function runReport() {
var jsonArray = new Array;
var jsonObj = new Object;
apex.debug.log('Start composing array and object for module ', apex.item("P3_REPORT_RDF").getValue() );
apex.item("P3_Submit").disable();
apex.message.clearErrors();
apex.message.hidePageSuccess();
// add report module (implementation name) to empty array
jsonObj.key = 'report'
jsonObj.value = apex.item("P3_REPORT_RDF").getValue();
jsonObj.required = 'Y';
jsonObj.uppercase = 'N';
jsonArray.push(jsonObj);
$("input[name='f01'], select[name='f01']").each(function(){
var oParamField = new Object;
oParamField.key = $(this).data("name");
oParamField.value = $(this).val();
if (Array.isArray(oParamField.value)) {
oParamField.value = oParamField.value.join(",")
}
oParamField.required = $(this).data("required");
oParamField.uppercase = $(this).data("uppercase");
jsonArray.push(oParamField);
});
apex.debug.log('Stringify: ', JSON.stringify(jsonArray) );
apex.server.process(
"P3_RunReportJSON", {
p_clob_01: JSON.stringify(jsonArray),
pageItems: "P3_URL"
} ,
{ success: function(data) {
apex.debug.log('Success of server process RunReportJSON',data.url);
apex.item('P3_URL').setValue(data.url);
apex.item("P3_Submit").enable();
if (data.url) {
apex.debug.log('URL contains a value');
apex.message.showPageSuccess( "Report has been run successfully. Check new Window." );
window.open(data.url,"Report Output", "_blank");
}
else {
apex.message.showPageSuccess( "Report has been run successfully!" );
}
}
, error: function(request, status, error) {
apex.debug.log(status, error);
apex.debug.log(request.responseText);
apex.item("P3_Submit").enable();
apex.message.showErrors([
{ type: "error",
location: "page",
message: error,
unsafe: false
}
]);
}
, loadingIndicatorPosition: "page"
}
);
}
From line 13-17 the Report module (implementation name) is added to the jsonArray. In the loop 19-29 each input item or select item is added to the array.
An example of the JSON output is:
[
{
"key": "report",
"value": "hsd0500r",
"required": "Y",
"uppercase": "N"
},
{
"key": "P_DEP_ID",
"value": "10",
"required": "Y",
"uppercase": "N"
},
{
"key": "DESFORMAT",
"value": "PDF",
"required": "N",
"uppercase": "N"
},
{
"key": "DESNAME",
"value": "",
"required": "N",
"uppercase": "N"
},
{
"key": "DESTYPE",
"value": "CACHE",
"required": "Y",
"uppercase": "N"
},
{
"key": "ORIENTATION",
"value": "DEFAULT",
"required": "Y",
"uppercase": "N"
}
]
On line 35 the array is passed as a JSON string to the APEX.SERVER.PROCESS named P3_RunReportJSON.
We will add this process to Apex:
Processing, Ajax Callback:
Identification
Name: P3_RunReportJSON
Type: PL/SQL Code
Source
Location: Local Database
PL/SQL Code:
declare
l_json_clob clob ;
l_url varchar2(4000) ;
l_message varchar2(4000) ;
l_error boolean default false ;
l_debug boolean default (:DEBUG = 'YES') ;
begin
-- get the clob passed in from the Ajax process
l_json_clob := apex_application.g_clob_01;
apexdemo.run_report
( p_json_text => l_json_clob -- in
, p_debug => l_debug -- in
, p_url => l_url -- out
, p_error => l_error -- out
, p_error_message => l_message. -- out
) ;
--Write JSON out for the response
apex_json.open_object();
if not l_error
then
apex_json.write('success', true);
apex_json.write('url', l_url);
-- write session state variable
:P3_URL := l_url;
-- DONT write this line !!
-- apex_json.write('error', 'N');
else
apex_json.write('success', false);
apex_json.write('error', l_message);
end if;
apex_json.close_object();
end;
Most of the logic is sitting in the database procedure APEXDEMO.RUN_REPORT
pseudocode procedure run_report
( p_json_text in clob )
begin
compose_parameterstring
( p_json_text -- in
, p_paramlist -- out
) ;
run_report_soap
( p_paramlist -- in
, p_response_xml -- out
) ;
end run_report;
Compose parameterstring
In this procedure the JSON string is used in a cursor, values are checked and the output is a string which will be passed to the SOAP request for the Report Server. The output string has a format like this:
desformat=PDF destype=CACHE orientation=DEFAULT p_dep_id=10 report=hsd0500r userid=headstart/demo@orcl
Please note that the separator is a space ” ” in stead of an ampersand “&” we are used to. The latter is used in a get request directly in the RWSERVLET url, but in a SOAP message the parameters are separated by a space.
The most interesting part of this procedure is the JSON processing as an implicit cursor, via a XML table, see line 4:
for r in (select key,value,required,uppercase
from xmltable(
'/json/row'
passing apex_json.to_xmltype(p_json_text)
columns
key varchar2(240) path '/row/key',
value varchar2(240) path '/row/value',
required varchar2(240) path '/row/required',
uppercase varchar2(240) path '/row/uppercase'
)
)
loop
l_key := upper(r.key) ;
l_value := nvl(r.value,'%NULL%') ;
-- do some validations
if r.uppercase = 'Y'
then
a_params(l_key) := upper(l_value);
else
a_params(l_key) := l_value;
end if;
end loop;
Make a SOAP request to Report Server
The main reason for making a SOAP request is that we receive the JOBID back which we can use to open the report in a new window (see line 47 of the runReport() JavaScript here above). If we would use the RWSERVLET get request, this would result in a redirect of the Apex page, thereby loosing the Apex application focus and having the credentials displayed in the URL. I know there are other ways to circumvent this but the SOAP request is straightforward and fulfilling our needs.
For further details about the SOAP usage, see the official documentation
https://docs.oracle.com/cd/B14099_19/bi.1012/b14048/pbr_webservice.htm#CIAFICHE
I discovered that there are differences in the SOAP messages depending on the version of the Report server. To cope with these differences I added a SOAP_VERSION variable to enable different version usages in the procedure:
if gb_soap_version = 'SOAP10G'
then
l_soap_req := q'[
<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:urn="urn:oracle-reports-rwclient-RWWebService">
<soapenv:Header/>
<soapenv:Body>
<urn:runJob soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<param0 xsi:type="xsd:string">#PARAM#</param0>
<param1 xsi:type="xsd:boolean">true</param1>
</urn:runJob>
</soapenv:Body>
</soapenv:Envelope>
]' ;
else -- SOAP 12c
l_soap_req := q'[
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:rwc="http://oracle.reports/rwclient/">
<soapenv:Header/>
<soapenv:Body>
<rwc:runJob>
<!--Optional:-->
<arg0>#PARAM#</arg0>
<arg1>true</arg1>
</rwc:runJob>
</soapenv:Body>
</soapenv:Envelope>
]';
end if; -- g_soap_version
-- substitute param0 value with p_paramlist
l_soap_req := replace(l_soap_req, '#PARAM#', p_paramlist);
-- start http request
utl_http.set_response_error_check(true);
l_http_req := utl_http.begin_request ( url => gb_reports_server_soap
, method => 'POST'
, http_version => 'HTTP/1.1'
) ;
All variables which have to be modified according to the infrastructure (for example the GB_REPORTS_SERVER_SOAP on line 3 here above) are listed in the top of the APEXDEMO package body as global variables to the body:
gb_reports_server varchar2(240) := 'http://localhost:7778/reports/rwservlet?server=rep_www_asfr10g102' ;
gb_reports_server_soap varchar2(240) := 'http://localhost:7778/reports/rwwebservice' ;
gb_soap_version varchar2(240) := 'SOAP10G' ;
gb_sep varchar2(1) := ' ' ; -- RWSERVLET chr(38) ;
gb_login_string varchar2(240) := 'headstart/pwd@orcl' ;
gb_output_dir varchar2(240) := '/tmp' ;
So now we have posted the SOAP message from the database to the Report Server and we have to wait for the response. Let’s look at it on the next page.
<< Prev: Rendering generic Report Form >> Next: Open report page