How I got SQL Developer working on a new MacBook Pro (MacOS Monterey - M1 Pro)

Note: See updates 2021-11-23, 2022-02-17 and 2022-06-29 below concerning the use of GraalVM's JDK as an alternative to JDK 17. This is the solution that I am currently using. I'm hoping that this will be of help to others facing similar issues.

I got my new MacBook Pro (M1 Pro) a few days ago, and then set it up by restoring a Time Machine backup from my MacMini (also M1 architecture) and quickly started using my new laptop.

Very soon, I saw that SQL Developer was crashing. Sometimes it would crash immediately, sometimes after a few minutes.

While looking for a solution, I took a look at some of the forum posts on https://community.oracle.com/tech/developers/categories/sql_developer. Most of what I did comes from what I gleaned reading various posts there. 100% of the credit goes to those that contributed in the forum.

I'll spare you all the various different combinations and attempts that I made that didn't work. The following is what actually worked for me.

Download and install JDK 17

I went to the Oracle Java Downloads page at https://www.oracle.com/java/technologies/downloads/

and downloaded the file: jdk-17.0.1_macos-aarch64_bin.dmg

Opened the .dmg and double-clicked on the JDK 17.0.1.pkg installation package to open the installer.

Followed all the steps to install JDK 17.

After the installation, I checked my folder /Library/Java/JavaVirtualMachines to verify that JDK 17 was installed there.

Change the SQL Developer products.conf file to use JDK 17

To make SQL Developer use the new JDK, I needed to locate and edit the products.conf file for my version of SQL Developer.  These files are found in the hidden.sqldeveloper directory under your home directory.

As you can see here there are a lot of directories from the various versions of SQL Developer that I've installed and used over the years.  My current version is 21.2.1, so this is the directory that I want to change my file in.

I edited the product.conf file using vi and added the following line to make sure that this version of SQL Developer would use the new JDK 17 that I installed.  The SetJavaHome entry sets the Java Home to the directory containing this newly installed version.

SetJavaHome /Library/Java/JavaVirtualMachines/jdk-17.0.1.jdk/Contents/Home

This is what that section of my product.conf file looked like after editing.

Start SQL Developer

When starting SQL Developer, I get an "Unsupported JDK version" page that immediately pops up.  I choose to ignore this warning and click on "Yes" to continue anyway.

The next message that I get concerns JavaFX. 

I was a little worried when I first saw this JavaFX pop-up, but then I read Jeff Smith's post from last November which was reassuring.  According to Jeff, JavaFX is only used in a few screens within SQL Developer, and I can certainly live with this issue for now.

Conclusion

So that's it.  I have a working version of SQL Developer on my MacBook Pro.
It worked for me.  I hope that it works for you or at least gets you moving closer to a solution.

Happy Developing!

Update 2021-11-23 - using GraalVM's JDK 11 as an alternative JDK

In his SQL Developer community forum post, Philipp Salvisberg suggests using the GraalVM's JDK 11 which can be downloaded here.  I have tested his solution and it works for me - even the Welcome Page of SQL Developer works using this method.  Thanks Philipp.

Note: I had to remove the quarantine attribute with the following command: 

sudo xattr -r -d com.apple.quarantine /Library/Java/JavaVirtualMachines/graalvm-ce-java11-21.2.0
 

Update 2022-02-17 - SQL Developer 21.4.2

I have just upgraded to 21.4.2.  I once again edited the product.conf file to point to the GraalVM JDK.  Works just fine for now.

SetJavaHome /Library/Java/JavaVirtualMachines/graalvm-ce-java11-21.2.0/Contents/Home

Update 2022-06-29 - SQL Developer 22.2.0

I have just upgraded to 22.2.0.  Works fine.  No issues to report for now.

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.

After some investigation, I found a couple of interesting blog posts by Dimitri Gielis and Scott Wesley that seemed to address this exact issue.

https://dgielis.blogspot.com/2018/09/increasing-maximum-web-service-requests.html

http://www.grassroots-oracle.com/2019/03/oracle-apex-web-service-request-limit.html

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 called MAX_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!

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!