30 August 2021

Autonomous DB "You have exceeded the maximum number of web service requests per workspace"

We recently had an experience on an Oracle Autonomous Database where our production instance started giving us lots of errors saying:

ORA-20001: You have exceeded the maximum number of web service requests per workspace. Please contact your administrator.

As these two blog posts tell us, in a self-managed or in-house APEX installation, the page for changing the Maximum Web Service Requests parameter can be found under "Security settings -> Workspace Isolation -> Maximum Web Service Requests".  We can increase the parameter there and fix the issue.

However, on the Autonomous DB these pages are not available.  So the questions become: Can we change this parameter? and if so, how and where?

A further flurry of Googling and a deeper dive into the Oracle Documentation led us to the following page: https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/apex-web-services.html#GUID-DA24C605-384D-4448-B73C-D00C02F5060E

Here we see that there is an APEX instance-level parameter calleMAX_WEBSERVICE_REQUESTS which can be queried and modified using the APEX_INSTANCE_ADMIN package.  The default value of this parameter on an Autonomous DB is currently 50'000 outgoing requests in a rolling 24-hour period.  To run this package you must be connected as the ADMIN user. 

To view the current value of 
MAX_WEBSERVICE_REQUESTS, we can execute the following query that uses the get_parameter function.  

select apex_instance_admin.get_parameter
          ('MAX_WEBSERVICE_REQUESTS') as requests
  from dual;

REQUESTS
--------
50000

To change this value, we can use the SET_PARAMETER procedure:

begin
 apex_instance_admin.set_parameter
     ('MAX_WEBSERVICE_REQUESTS', '250000');  -- increase to 250'000
 commit;
end;
/

If we rerun the preceding query again, we now get a different result that confirms that our change has worked.

select apex_instance_admin.get_parameter
          ('MAX_WEBSERVICE_REQUESTS') as requests
  from dual;

REQUESTS
--------
250000

I hope that this blog post helps someone out there avoid the minor panic that we experienced for a short while today.

Happy APEXing to all!

23 January 2021

Making XML tags dynamic in SQL and PL/SQL

While trying to produce XML using Oracle's native XML functions, I needed some of the XML tags to be dynamic.  To simplify and illustrate the problem that I encountered, I'll show an example that uses the time-tested, traditional EMP and DEPT tables.  

Let's say that we need to produce something like this for all departments.

<departments> <accounting> <employee>Clark</employee> <employee>King</employee> <employee>Miller</employee> </accounting> ... </departments>


Let's start with a short SQL to get an aggregated employee list for each department.  The result is four rows of xmltype - one for each department

select xmlelement("department", xmlagg(xmlelement("employee", initcap(e.ename)))) from dept d left outer join emp e on (e.deptno = d.deptno) group by d.deptno;

Result (4 rows):

<department> <employee>King</employee> <employee>Miller</employee> <employee>Clark</employee> </department>

<department> <employee>Jones</employee> <employee>Adams</employee> <employee>Smith</employee> <employee>Ford</employee> <employee>Scott</employee> </department>

<department> <employee>Blake</employee> <employee>James</employee> <employee>Turner</employee> <employee>Martin</employee> <employee>Ward</employee> <employee>Allen</employee> </department>

<department> <employee></employee> </department>


Next we'll aggregate these inside a single outer tag called "departments"


select xmlelement("departments", xmlagg( xmlelement("department", xmlagg(xmlelement("employee", initcap(ename)))))) from dept d left outer join emp e on (e.deptno = d.deptno) group by d.deptno, d.dname;

Result:

<departments> <department> <employee>King</employee> <employee>Miller</employee> <employee>Clark</employee> </department> <department> <employee>Jones</employee> <employee>Adams</employee> <employee>Smith</employee> <employee>Ford</employee> <employee>Scott</employee> </department> <department> <employee>Blake</employee> <employee>James</employee> <employee>Turner</employee> <employee>Martin</employee> <employee>Ward</employee> <employee>Allen</employee> </department> <department> <employee></employee> </department> </departments>


Now we'll try to change the <department> tag to have the value of the actual department name by replacing xmlelement("department",  with xmlelement(dname,   and we'll see that the value of dname doesn't appear



select xmlelement("departments", xmlagg( xmlelement(dname, xmlagg(xmlelement("employee", initcap(ename)))))) from dept d left outer join emp e on (e.deptno = d.deptno) group by d.deptno, d.dname;

Result:

<departments> <DNAME> <employee>King</employee> <employee>Miller</employee> <employee>Clark</employee> </DNAME> <DNAME> <employee>Jones</employee> <employee>Adams</employee> <employee>Smith</employee> <employee>Ford</employee> <employee>Scott</employee> </DNAME> <DNAME> <employee>Blake</employee> <employee>James</employee> <employee>Turner</employee> <employee>Martin</employee> <employee>Ward</employee> <employee>Allen</employee> </DNAME> <DNAME> <employee></employee> </DNAME> </departments>

So, as we can see, the value of the dname column has not been interpreted and used for the tag.  The query is simply using the string DNAME instead.   The problem now becomes "how can we force our query to use the value of dname as an XML tag?"

And, of course, Oracle have given us a solution - the evalname keyword will tell the query that the expression following it is to be evaluated and that the result of that evaluation should be used as the XML tag.  Armed with this knowledge, we'll now make a small change to the query


select xmlelement("departments", xmlagg( xmlelement(evalname lower(dname), xmlagg(xmlelement("employee", initcap(ename)))))) from dept d left outer join emp e on (e.deptno = d.deptno) group by d.deptno, d.dname;

Result:

<departments> <accounting> <employee>King</employee> <employee>Miller</employee> <employee>Clark</employee> </accounting> <research> <employee>Jones</employee> <employee>Adams</employee> <employee>Smith</employee> <employee>Ford</employee> <employee>Scott</employee> </research> <sales> <employee>Blake</employee> <employee>James</employee> <employee>Turner</employee> <employee>Martin</employee> <employee>Ward</employee> <employee>Allen</employee> </sales> <operations> <employee></employee> </operations> </departments>

So, as we can see, the value of the expression following the evalname keyword has been used as the XML tag.

Of course, the above example is a simple example to illustrate the use of evalname.
The actual problem that I was solving involved calculating multiple tag values according to a reasonably complex piece of business logic that was implemented via PL/SQL packages.  These tag values were then passed as parameters to the procedure generating the XML.  From there it was easy to just use evalname parameter_name for the dynamic tag names.

The above was run and tested on the Oracle Autonomous Database Cloud using an "Always Free" database that is running database version 19c at the time of writing.

I hope that this is useful for some of you.  Happy XMLing with SQL and PL/SQL!