To interact with external services XML or JSON format is necessary. It's not that hard to generate XML or JSON from the database.
Statement for xml
SELECT dbms_xmlgen.getxml ('SELECT team,categorie from FWD_TEAMS) XML
FROM dual;
Output
<ROWSET>
<ROW>
<TEAM>JA1</TEAM>
<CATEGORIE>...
</ROW>
</ROWSET>
Statement for JSON (in apex in on-demand-process?)
declare
l_sql varchar2(200);
BEGIN
l_sql := 'SELECT team,categorie from fwd_teams';
APEX_UTIL.JSON_FROM_SQL(l_sql);
END;
Output
{"row":[{"TEAM":"JD3","CATEGORIE":"Jeugd"}
,{"TEAM":"M3F1","CATEGORIE":"Jongste Jeugd"}]}
In Apex of course you can define a region as a webservice. This webservice returns JSON or XML.
It's possible to create a report in Apex that works as a service.
To be able to define a webservice the administrator has to make a change in the default configuration of the apex workspace.
- Allow Restfull access
Only than developers are able to define a report as as webservice.
- Create region
- Modify attributes
- Define a static id for the region
- Enable Restful Access, the url will be shown
Ready.
The url looks like this:
http://<host>/pls/apex/apex_rest.getReport?app=613&page=5&reportid=hekr01&parmvalues=H01&output=xml
app = Application name/number
page = Pagenumber
reportid = Unique identifier report (defined at creation)
output = Format (XML or JSON)
parmvalues = additional parameters (value)
The value of <H01> will be assigned to the first itemof region
Use of multiple parameters:
First the values (comma seperated), than the items.
http://<host>/pls/apex/apex_rest.getReport?app=613&page=5&reportid=hekr01&parmvalues=H01,H02&output=xml
The values are assigned sequentially to the items of the report based on their (internal) ids.
So, H01 will be assigned to the item with the lowest ID and H02 will be assigned to the next item notwithstanding which region.
