Calling Blitz Report over REST

How another application (Oracle APEX on a separate database, middleware, a script) runs Blitz Reports through the webservices the Excel add-in uses. The operations themselves are listed under Webservice operations.

Transports

The operations of package xxen_webservices are published twice. The instance uses the one set in profile Blitz Report Webservice Connection Type: blank means ORDS, ISG means the Integrated SOA Gateway. Call the transport the instance is configured for; the other may not be deployed or may be an older deployment that lacks recent operations.

xxen in the ORDS URL is the Blitz Report schema; profile Blitz Report Webservice ORDS URL holds the complete base URL. All operations are POST with Content-Type: application/json.

Authentication

ORDS

• The endpoints are protected by the ORDS privilege xxen_webservices, granted to the OAuth2 client of the same name (grant type client_credentials). The DBA reads its id and secret as the Blitz Report schema:

  select uoc.client_id, uoc.client_secret from user_ords_clients uoc where uoc.name='xxen_webservices';

• Request a token with them: POST /ords/xxen/oauth/token, Basic authentication with client id and secret, body grant_type=client_credentials. The token is valid for 3600 seconds.
• Log in as the EBS user: operation login with P_USER_NAME and P_PASSWORD, the password Base64 encoded. The answer "C" : "Y", "S" : "<cookie name>=<xsid>" carries a new ICX session in S. A wrong password answers HTTP 200 with "C" : "N", not 401.
• Every further call sends the token as Authorization: Bearer and the session string from S unchanged in P_COOKIE_STRING. A body without P_COOKIE_STRING, with an expired session, or with a P_USER_NAME other than the session's user is answered 401.
• The session is an ordinary ICX session: each call extends it, and it expires after ICX: Session Timeout without calls or at ICX: Limit Time. Keep it and log in again only when a call still answers 401 after a fresh token. Every login creates a new session.
• On a 401, first request a new token and resend the same body with the same P_COOKIE_STRING; only a 401 to that resend means the EBS session has ended.

ISG

• Send the EBS user and password as HTTP Basic authentication on every call. Alternatively send an EBS session cookie in the Cookie header; on 12.1.3 ISG accepts it only under the cookie name equal to the database SID.
• Always send the RESTHeader with the responsibility key, the application short name and STANDARD.
• 403 FND_SOA_AUTHORIZATION_FAILURE (User not authorized to execute service) means that operation's function has no grant: run sql/grant_ISG.sql as APPS. An operation added to Blitz Report after the service was deployed exists only after the service is redeployed.
• Do not send an Accept-Language header with an Oracle language name (PORTUGUESE): ISG rejects it with 400. The language is passed in P_NLS_LANGUAGE.

EBS context

Every operation that runs as a responsibility takes P_USER_NAME, P_RESPONSIBILITY_KEY and P_APPLICATION_NAME (the application short name, for example SQLGL), and initializes the session with them as the Forms run screen does: report access, operating unit and ledger security and profile values are those of that user and responsibility. The responsibility must be assigned to the user and active, otherwise the operation returns an error. user_responsibilities lists a user's responsibilities as name/key/application short name/security group, separated by |. P_NLS_LANGUAGE (an Oracle language name, blank for the user's language) sets the language of parameter names, column headings and messages.

Request format

ORDS body:

{"P_COOKIE_STRING":"<S from login>","P_USER_NAME":"<user>","P_RESPONSIBILITY_KEY":"<responsibility key>","P_APPLICATION_NAME":"<application short name>",
 "P_REPORT_ID":72,"P_PARAMETERS":[{"C":"<parameter name>","F":"Char","V":"<value>"}]}

ISG body of the same call, without P_COOKIE_STRING and with the bind list wrapped in its type name:

{"RESTHeader":{"Responsibility":"<responsibility key>","RespApplication":"<application short name>","SecurityGroup":"STANDARD"},
 "InputParameters":{"P_USER_NAME":"<user>","P_RESPONSIBILITY_KEY":"<responsibility key>","P_APPLICATION_NAME":"<application short name>",
  "P_REPORT_ID":72,"P_PARAMETERS":{"XXEN_WEBSERVICE_BINDS_TAB_TYPE":[{"C":"<parameter name>","F":"Char","V":"<value>"}]}}}

Parameter values (xxen_webservice_binds_tab_type)

Each element is one parameter, its fields in this order:

• List of values parameters take the display value, as typed on the run screen; the server converts it to the id. Prefix <id> to pass the id itself (<id>204).
• Dates are Excel serial numbers, days since 30-Dec-1899 (45658 is 1-Jan-2025); a DateTime carries the time as the fraction.
• Numbers use a period as decimal separator and no group separator.
• Multiple values: <multiple_values>A;B;C, only for parameters that report_metadata returns with "multiple_values":"Y". Without the prefix, A;B is one value.
• A name that matches no parameter fails the call with Parameter "<name>" not found.

P_APPLY_DEFAULTS (run_report, email_report): Y, the default, applies the defaults of every parameter not sent; N applies only those of hidden and template-excluded parameters, as the run screen does once the user has seen the defaults. P_TEMPLATE_NAME selects a template.

Response format

• ORDS answers with the text "C" : "<value>" (login adds , "S" : "..."), which is not a JSON document on its own: wrap it in { and } before parsing.
• ISG answers {"OutputParameters":{"<OPERATION>":{"C":{"C_ITEM":[{"C":"<value>"}]}}}}.
• <value> is a string; for the report operations it holds a JSON document, which is parsed a second time.
• Errors raised in Blitz Report come back with HTTP 200: either {"status":"E","message":"..."} inside the value (failed validation, missing required parameters, a failed run), or a value starting with XXEN_EXCEPTION: followed by the Oracle error stack. Test for both before reading data.
• HTTP 401 is a missing or expired token or session. On ORDS, HTTP 555 is an Oracle error that escaped the handler; the body carries the error stack.

Running a report

run_report answers {"run_id":5161,"total_batches":1,"current_batch":1,"batch_size":20000000,"total_length":2951,"data":"..."}. A batch holds at most profile Blitz Report Webservice Maximum Response Size million characters (20 when blank). With more than one batch, the output is cached on the server: fetch the batches in order with report_batch and concatenate their data; the cache is deleted when the last batch is read.

Data format

data is the report output as delimited text, not an Excel file. It starts with three header sections separated by /, followed by one section per row, each preceded by /; fields are separated by |:

s|s|s|d|...                      column types: s text, d date, the Excel number format of a number column (empty when it has none)
56.863|24.842|7.192|...          column widths
User Profile Option Name|...     column headings
/Blitz Report Access|...|46115.34775462963|...
/...

• In values and headings, | is written as `^, / as ~^ and \ as @^; split first, then replace them back.
• Dates are Excel serial numbers with the time as fraction; dates before 1900 are text DD-Mon-YYYY. Numbers use a period as decimal separator.
• A template applies its column selection, order and aggregation. Workbook layout, formats and pivot tables exist only in an Excel output.

Excel file

report_file takes the same input as run_report and returns the output file the run screen downloads, with the template's workbook, formats and pivot tables applied: {"status":"S","file_name":"FND Responsibilities 30-Oct-2026 055311.xlsx","row_count":108,"file":"<base64>"}. The file is returned in one response, Base64 encoded, so it is about a third larger than on disk.

Excel output by email

email_report takes the same input as run_report plus P_EMAIL (addresses separated by ;, at most 240 characters) and submits the report as a concurrent request that mails its output, as the run screen does with an Email address. It answers {"status":"S","request_id":6019992,"email":"..."}. Use it for reports that run longer than the HTTP timeout of the client or middle tier: run_report and report_file run the report inside the web request, and its database session keeps running after the client gives up.

Example: curl against ORDS

BASE=https://<ords host>:<port>/ords/xxen
EBS_USER=<user>
read -rsp "EBS password: " EBS_PASSWORD; echo
# CLIENT_ID and CLIENT_SECRET: the xxen_webservices OAuth2 client
TOKEN=$(curl -s -u "$CLIENT_ID:$CLIENT_SECRET" -d grant_type=client_credentials "$BASE/oauth/token" | jq -r .access_token)
call() {
  curl -s -X POST "$BASE/xxen_webservices/$1" -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" \
    --data-binary "$2" | jq -R -s '("{"+.+"}"|fromjson).C'
}
COOKIE=$(curl -s -X POST "$BASE/xxen_webservices/login" -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" \
  --data-binary "{\"P_USER_NAME\":\"$EBS_USER\",\"P_PASSWORD\":\"$(printf '%s' "$EBS_PASSWORD" | base64)\"}" \
  | jq -R -s -r '("{"+.+"}"|fromjson).S')
CTX="\"P_COOKIE_STRING\":\"$COOKIE\",\"P_USER_NAME\":\"$EBS_USER\",\"P_RESPONSIBILITY_KEY\":\"SYSTEM_ADMINISTRATOR\",\"P_APPLICATION_NAME\":\"SYSADMIN\""
call report_list "{$CTX,\"P_SEARCH_TEXT\":\"FND Profile Option Values\"}" | jq -r 'fromjson.reports[]|"\(.id) \(.name)"'
call report_metadata "{$CTX,\"P_REPORT_ID\":72}" | jq -r 'fromjson.parameters[]|"\(.display_name) | \(.parameter_type) | \(.multiple_values)"'
call run_report "{$CTX,\"P_REPORT_ID\":72,\"P_PARAMETERS\":[
 {\"C\":\"System Profile Name starts with\",\"F\":\"Char\",\"V\":\"XXEN_REPORT\"},
 {\"C\":\"Setup Level\",\"F\":\"Char\",\"V\":\"<multiple_values>Site;User\"},
 {\"C\":\"Last Update Date From\",\"F\":\"Date\",\"V\":\"45658\"}]}" > run.json
jq -r 'fromjson|"run \(.run_id): \(.total_batches) batch(es)"' run.json
jq -r 'fromjson.data' run.json | tr '/' '\n'

Report ids differ between instances: take the id from report_list, never hard-code it.

Example: curl against ISG

curl -s -u "<user>:$EBS_PASSWORD" -X POST "https://<ebs host>/webservices/rest/xxen_webservices/run_report/" \
  -H "Content-Type: application/json" --data-binary '{
 "RESTHeader":{"Responsibility":"SYSTEM_ADMINISTRATOR","RespApplication":"SYSADMIN","SecurityGroup":"STANDARD"},
 "InputParameters":{"P_USER_NAME":"<user>","P_RESPONSIBILITY_KEY":"SYSTEM_ADMINISTRATOR","P_APPLICATION_NAME":"SYSADMIN",
  "P_REPORT_ID":72,
  "P_PARAMETERS":{"XXEN_WEBSERVICE_BINDS_TAB_TYPE":[{"C":"System Profile Name starts with","F":"Char","V":"XXEN_REPORT"}]}}}' \
  | jq -r '.OutputParameters.RUN_REPORT.C.C_ITEM[0].C|fromjson|.data'

Example: Oracle APEX against ORDS

The database running APEX needs a network ACL for the ORDS host and, for HTTPS, the ORDS certificate chain in a wallet (instance setting or p_wallet_path). Keep client id, client secret and the EBS password out of the code, for example in APEX web credentials or an encrypted table.

declare
l_base varchar2(200):='https://<ords host>:<port>/ords/xxen';
l_user varchar2(100):='<user>';
l_response clob;
l_cookie varchar2(200);
l_context varchar2(1000);
l_result json_object_t;
function ws_call(p_operation in varchar2, p_body in clob) return clob is
begin
  return apex_web_service.make_rest_request(p_url=>l_base||'/xxen_webservices/'||p_operation,p_http_method=>'POST',p_body=>p_body);
end ws_call;
function value_c(p_response in clob) return json_object_t is
begin
  return json_object_t.parse(json_object_t.parse('{'||p_response||'}').get_clob('C'));
end value_c;
begin
  apex_web_service.oauth_authenticate(p_token_url=>l_base||'/oauth/token',p_client_id=>:client_id,p_client_secret=>:client_secret);
  apex_web_service.g_request_headers.delete;
  apex_web_service.g_request_headers(1).name:='Authorization';
  apex_web_service.g_request_headers(1).value:='Bearer '||apex_web_service.oauth_get_last_token;
  apex_web_service.g_request_headers(2).name:='Content-Type';
  apex_web_service.g_request_headers(2).value:='application/json';
  l_response:=ws_call('login',json_object('P_USER_NAME' value l_user,'P_PASSWORD' value utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(:ebs_password)))));
  l_cookie:=json_object_t.parse('{'||l_response||'}').get_string('S');
  l_context:='"P_COOKIE_STRING":"'||l_cookie||'","P_USER_NAME":"'||l_user||'","P_RESPONSIBILITY_KEY":"SYSTEM_ADMINISTRATOR","P_APPLICATION_NAME":"SYSADMIN"';
  l_result:=value_c(ws_call('run_report','{'||l_context||',"P_REPORT_ID":72,"P_PARAMETERS":[{"C":"System Profile Name starts with","F":"Char","V":"XXEN_REPORT"}]}'));
  if l_result.get_string('status')='E' then
    raise_application_error(-20001,l_result.get_string('message'));
  end if;
  --l_result.get_clob('data') holds the output; fetch batches 2 to total_batches with report_batch
end;

json_object_t.get_clob needs database 18c or later; on 12.2 use apex_json to read the value. A C value starting with XXEN_EXCEPTION: is not JSON and fails the second parse: test for it before parsing.

Generated from Blitz Report 3.3.7 on 30 October 2026.