Anton Nielsen
apex_debug

Follow

apex_debug

Follow
File Based Development without SQL*Net

File Based Development without SQL*Net

If you have APEX and ORDS, that's probably all you need...oh and a special JDBC driver.

Anton Nielsen's photo
Anton Nielsen
·Feb 13, 2023·

2 min read

I often have access to an environment "only" through the APEX user interface, but I still want to be able to do file-based development. You can do this with SQL over REST (assuming you have a reasonably new install of ORDS that allows it).

TLDR

  1. Create a new APEX workspace user (e.g. anton_sqld) and give the user the group "SQL Developer."

  2. Download the JDBC driver from this location
    https://www.oracle.com/database/sqldeveloper/technologies/db-actions/download/
    and put it in your sqlcl/lib directory. (Read the Readme at the link above if you want more info.)

  3. Run SQLcl and connect to SQL over REST
    anton% sql anton_sqld@https://myapexurl/ords/my_workspace/
    or
    anton% sql /nolog
    SQL> connect anton_sqld@https://myapexurl/ords/my_workspace/

  4. That's it, just start doing your SQL thing.

Caveat: This is a stateless connection--so everything will be committed right away. Don't expect to be able to rollback transactions.

Some Details

You don't have to create another user, you could just add the "SQL Developer" group to your existing APEX user. This uses basic authentication, though. Yes, your traffic is encrypted with TLS (assuming you have an https URL above), but it's still not great to have your real password floating around. It's best to create a different user for this and change the password independently.

You might not be able to do this in every APEX environment. ORDS has to be a recent version and configured to allow it.

You can use the same driver with other applications (not just SQLcl). Check out the Readme for more info on that.

You can see also see more details here: https://www.youtube.com/watch?v=Oear6q9b4C8

If you want to see what is happening underneath it all, you can do the same thing with curl.

curl -i -X POST --user ANTON_SQLD:yourSuperSecretPasswrd --data-binary "select sysdate from dual" -H "Content-Type: application/sql" -k https://myapexurl/ords/my_workspace/_/sql

 
Share this