SQLDeveloper Web Docker Extension

SQLDeveloper Web Extension for Docker Desktop

Manual Installation

Until this extension is ready at Docker Extension Hub you can install just by executing:

$ docker extension install mochoa/sdw-docker-extension:23.4.0
Extensions can install binaries, invoke commands and access files on your machine. 
Are you sure you want to continue? [y/N] y
Installing new extension "mochoa/sdw-docker-extension:23.4.0"
Installing service in Desktop VM...
Setting additional compose attributes
VM service started
Installing Desktop extension UI for tab "SQLDeveloper Web"...
Extension UI tab "SQLDeveloper Web" added.
Extension "Oracle SQLDeveloper Web client tool" installed successfully

Note: Docker Extension CLI is required to execute above command, follow the instructions at Extension SDK (Beta) -> Prerequisites page for instructions on how to add it.

Using SQLDeveloper Web Docker Extension

Once the extension is installed a new extension is listed at the pane Extension (Beta) of Docker Desktop.

By clicking at SQLDeveloper Web icon the extension main window will display a progress bar for a few second and finally SQLDeveloper Web is launched.

SQLDeveloper Web is not logged into the Oracle RDBMS you should log using this page, put ADMIN/Oracle_2022 for OracleXE local installation.

Note that this extension is pre-configured to connect to an OracleXE running as Docker Extension.

It means OracleXE started using OracleXE Docker Desktop Extension means for a SQLDeveloper Web pool.xml:

• Hostname/address: host.docker.internal
• Port: 1521
• PDB: freepdb1
• Username: ORDS_PUBLIC_USER
• Password: Oracle_2023

Note: If you are using SQLDeveloper Web with Oracle XE Desktop Extension make sure that you have almost 5GB RAM setting on Docker Desktop (Settings -> Resources -> Memory: 5GB).

Creating ADMIN SQLDeveloper Web user

Once you got above image on Docker Desktop page you can create an ADMIN user to use SQLDeveloper Web just open SQLcl extension or log into OracleXE console and run:

create user admin identified by Oracle_2023
       default tablespace sysaux
       temporary tablespace temp;
grant connect,dba to ADMIN;
grant execute on dbms_soda_admin to admin;
BEGIN
        ords_admin.enable_schema(
        p_enabled => TRUE,
        p_schema => 'ADMIN',
        p_url_mapping_type => 'BASE_PATH',
        p_url_mapping_pattern => 'admin',
        p_auto_rest_auth => TRUE
      );
      commit;
END;
/

Enable scott user to use SQLDeveloper Web

If you want to enable scott user or any other database user to use SQLDeveloper Web tool just connect as ADMIN/Oracle_2022 and execute:

GRANT SODA_APP,CREATE VIEW to scott;
BEGIN
  ords_admin.enable_schema(
   p_enabled => TRUE,
   p_schema => 'SCOTT',
   p_url_mapping_type => 'BASE_PATH',
   p_url_mapping_pattern => 'scott',
   p_auto_rest_auth => TRUE
  );
  commit;
END;
/

Sample upload using SODA interface

You can load this sample purchase-order data set into a collection purchaseorder on your Autonomous Database with SODA for REST, using these curl commands:

curl -X GET "https://raw.githubusercontent.com/oracle/db-sample-schemas/master/order_entry/POList.json" -o POList.json

curl -X PUT -u 'scott:tiger' \
"http://localhost:59891/ords/scott/soda/latest/purchaseorder"

curl -X POST -u 'scott:tiger' -H 'Content-type: application/json' -d @POList.json \
"http://localhost:59891/ords/scott/soda/latest/purchaseorder?action=insert"

You can then use this purchase-order data to try out examples in Oracle Database JSON Developer’s Guide.

For example, the following query selects both the id of a JSON document and values from the JSON purchase-order collection stored in column json_document of table purchaseorder. The values selected are from fields PONumber, Reference, and Requestor of JSON column json_document, which are projected from the document as virtual columns (see SQL NESTED Clause Instead of JSON_TABLE for more information).

SELECT id, t.*
  FROM purchaseorder
    NESTED json_document COLUMNS(PONumber, Reference, Requestor) t;

Cleanup config before update to a new release

If you are upgrading from previous release, before log again just execute from command line:

docker exec mochoa_sdw-docker-extension-desktop-extension-service /home/sdw/cleanup.sh

This is to ensure that new PLSQL package version will be upgraded from previous installation.

Enable MongoDB support

If you want to enable MongDB compatible API suport for Oracle RDBMS you can execute this commands:

docker exec mochoa_sdw-docker-extension-desktop-extension-service /home/sdw/enablemongo.sh

And thats all, just open a mongo shell command line tool to connect to Oracle XE using Mongo DB compaitible API. Note: By enabling Mongo DB API SQLDeveloper Web is not avaible, to re-enable Web interface execute:

docker exec mochoa_sdw-docker-extension-desktop-extension-service /home/sdw/disablemongo.sh

Connect SQLDeveloper Web to Autonomos DB

Starting with 22.3.0 release there is a quick setup for using SQLDeveloper connected to your autonomos DB, here the instructions to connect using the extension. Note: /home/sdw/adb.pwd includes at the first line your ADMIN Adb password, next two lines must have a new random strong password used for ORDS_PUBLIC_USER2 schema.

docker cp /home/mochoa/Downloads/Wallet_DBparquet.zip mochoa_sdw-docker-extension-desktop-extension-service:/home/sdw/Wallet.zip
docker exec -ti mochoa_sdw-docker-extension-desktop-extension-service bash
bash-5.1# vi /home/sdw/adb.pwd
bash-5.1# cat /home/sdw/adb.pwd
bash-5.1# /home/sdw/cleanup.sh
bash-5.1# mv /home/sdw/sdw.sh /home/sdw/sdw.sh.old
bash-5.1# cp /home/sdw/adb.sh /home/sdw/sdw.sh
bash-5.1# exit
docker restart mochoa_sdw-docker-extension-desktop-extension-service

Uninstall

To uninstall the extension just execute:

$ docker extension uninstall mochoa/sdw-docker-extension:23.4.0
Extension "Oracle SQLDeveloper Web client tool" uninstalled successfully

Source Code

As usual the code of this extension is at GitHub, feel free to suggest changes and make contributions, note that I am a beginner developer of React and TypeScript so contributions to make this UI better are welcome.