\nYou like SQL Developer because it is easy to install (just unzip a jar) and has a lot of features? Me too. It can be even easier if it is provided as a web application: no installation, and no java to take all my laptop RAM…
\nWhen I say no installation, you will see that you have some little things to setup here in DBaaS. That will probably be done for you in the managed services (PDBaaS) such as ‘Express’ and ‘Autonomous’ ones.
\n
\n
![\"CaptureSDW010\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureSDW0101.png\")
<\/a>\nBe careful, Oracle is a Top-Down deployment company. It seems that new products are announced first and then people have to work hard to make them available. Which means that if, like me, you want to test them immediately you may encounter some disappointment.
\nThe announce was there. The documentation was there, mentioning that the Cloud Tooling must be upgraded to 18.2.3. But 18.2.3 was there only a few days later. You can check it from the place where the DBaaS looks for its software. Check from https:\/\/storage.us2.oraclecloud.com\/v1\/dbcsswlibp-usoracle29538\/dbaas_patch if you a are not sure.<\/p>\n
So, before being able to see SQL Developer in the colorful DBaaS landing page (where you can also access APEX for example) there’s a bit of command line stuff to do as root.<\/p>\n
Install the latest Cloud Tooling<\/h3>\n
SQL Developer Web needs to be installed with the latest version of ORDS, which is installed with the latest version of Cloud Tooling aka dbaastools.rpm<\/p>\n
You need to connect as root, so opc and then sudo<\/p>\n
\nssh opc@144.21.89.223\nsudo su\n<\/code><\/pre>\nCheck if there is a new version to install:<\/p>\n
\ndbaascli dbpatchm --run -list_tools | awk '\/Patchid\/{id=$3}END{print id}'\n<\/code><\/pre>\nIf something is returned (such as 18.2.3.1.0_180505.1604) you install it:<\/p>\n
\ndbaascli dbpatchm --run -toolsinst -rpmversion=$(dbaascli dbpatchm --run -list_tools | awk '\/Patchid\/{id=$3}END{print id}')\n<\/code><\/pre>\nActually I got an error, and I had to ^C:<\/p>\n
\n[root@DB18c opc]# dbaascli dbpatchm --run -toolsinst -rpmversion=$(dbaascli dbpatchm --run -list_tools | awk '\/Patchid\/{id=$3}END{print id}')\nDBAAS CLI version 1.0.0\nExecuting command dbpatchm --run -toolsinst -rpmversion=18.2.3.1.0_180505.1604 -cli\n\/var\/opt\/oracle\/patch\/dbpatchm -toolsinst -rpmversion=18.2.3.1.0_180505.1604 -cli\nUse of uninitialized value in concatenation (.) or string at \/var\/opt\/oracle\/patch\/dbpatchm line 4773.\n^C\n<\/code><\/pre>\nBut finally, it was installed because the ‘list_tools’ above returns nothing.<\/p>\n
Enable SQL Developer Web<\/h3>\n
SQL Developer Web (SDW) is running in ORDS (Oracle REST Data Services) and must be enabled with the ORDS Assistant with the enable_schema_for_sdw action.
\nHere I’ll enable it at CDB level. I provide a password for the SDW schema. I create it in a file:<\/p>\n
\ncat > password.txt <<<'Ach1z0#d'\n<\/code><\/pre>\nYou may secure that better than I do, as I’m putting the password on command line here. But this is only a test.<\/p>\n
Then, still as root, I call the ORDS assistant to install SDW in C##SQLDEVWEB (as I’m installing it in CDB$ROOT I need a common user name).<\/p>\n
\n\/var\/opt\/oracle\/ocde\/assistants\/ords\/ords -ords_action=enable_schema_for_sdw -ords_sdw_schema=\"C##SQLDEVWEB\" -ords_sdw_schema_password=$PWD\/password.txt -ords_sdw_schema_enable_dba=true\n<\/code><\/pre>\nHere is the output. The last lines are important:<\/p>\n
\nWARNING: Couldn't obtain the \"dbname\" value from the assistant parameters nor the \"$OCDE_DBNAME\" environment variable\nStarting ORDS\nLogfile is \/var\/opt\/oracle\/log\/ords\/ords_2018-05-10_10:44:12.log\nConfig file is \/var\/opt\/oracle\/ocde\/assistants\/ords\/ords.cfg\nINFO: Starting environment summary checks...\nINFO: Database version : 18000\nINFO: Database CDB : yes\nINFO: Original DBaaS Tools RPM installed : dbaastools-1.0-1+18.1.4.0.0_180123.1336.x86_64\nINFO: Actual DBaaS Tools RPM installed : dbaastools-1.0-1+18.2.3.1.0_180505.1604.x86_64\nINFO: DBTools JDK RPM installed : dbtools_jdk-1.8.0-2.74.el6.x86_64\nINFO: DBTools JDK RPM \"\/var\/opt\/oracle\/rpms\/dbtools\/dbtools_jdk-1.8.0-2.74.el6.x86_64.rpm\" MD5 : 48f13bb401677bfc7cf0748eb1a6990d\nINFO: DBTools ORDS Standalone RPM installed : dbtools_ords_standalone-18.1.0.11.22.15-1.el6.x86_64\nINFO: DBTools ORDS Standalone RPM \"\/var\/opt\/oracle\/rpms\/dbtools\/dbtools_ords_standalone-18.1.0.11.22.15-1.el6.x86_64.rpm\" MD5 : 480355ac3ce0f357d5741c2c2f688901\nINFO: DBTools DBaaS Landing Page RPM installed : dbtools_dbaas_landing_page-2.0.0-1.el6.x86_64\nINFO: DBTools DBaaS Landing Page RPM \"\/var\/opt\/oracle\/rpms\/dbtools\/dbtools_dbaas_landing_page-2.0.0-1.el6.x86_64.rpm\" MD5 : af79e128a56b38de1c3406cfcec966db\nINFO: Environment summary completed...\nINFO: Action mode is \"full\"\nINFO: Database Role is \"PRIMARY\"\nINFO: Enabling \"C##SQLDEVWEB\" schema in \"CDB$ROOT\" container for SQL Developer Web...\n \nSQL*Plus: Release 18.0.0.0.0 Production on Thu May 10 10:44:27 2018\nVersion 18.1.0.0.0\n \nCopyright (c) 1982, 2017, Oracle. All rights reserved.\n \n \nConnected to:\nOracle Database 18c EE Extreme Perf Release 18.0.0.0.0 - Production\nVersion 18.1.0.0.0\n \nSQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL Developer Web user enable starting...\nEnabling \"C##SQLDEVWEB\" user for SQL Developer Web...\n \nPL\/SQL procedure successfully completed.\nPL\/SQL procedure successfully completed.\nCreating \"C##SQLDEVWEB\" user\nPL\/SQL procedure successfully completed.\nPL\/SQL procedure successfully completed.\nPL\/SQL procedure successfully completed.\nCall completed.\nCommit complete.\nPL\/SQL procedure successfully completed.\nSession altered.\nPL\/SQL procedure successfully completed.\nPL\/SQL procedure successfully completed.\n \n\"C##SQLDEVWEB\" user enabled successfully. The schema to access SQL Developer Web\nis \"c_sqldevweb\"...\n \nPL\/SQL procedure successfully completed.\n \nSQL Developer Web user enable finished...\nDisconnected from Oracle Database 18c EE Extreme Perf Release 18.0.0.0.0 - Production\nVersion 18.1.0.0.0\nINFO: To access SQL Developer Web through DBaaS Landing Page, the schema \"c_sqldevweb\" needs to be provided...\nINFO: \"C##SQLDEVWEB\" schema in the \"CDB$ROOT\" container for SQL Developer Web was enabled successfully...\n \n<\/code><\/pre>\nThe information to remember here is that I will have to provide the c_sqldevweb schema name (which is the schema name I’ve provided but lowercased and with sequences of ‘special’ characters replaced by an underscore). It is lowercased, but it seems that the schemaname has to be provided in uppercase.<\/p>\n
Basically what has been done is quite simple: create the C##SQLDEVWEB user and call ORDS.ENABLE_SCHEMA to enable it and map it to the url.<\/p>\n
DBCS Landing Page 2.0.0<\/h3>\n
Now I’m ready to see SQL Developer on the DBCS Landing Page. You access this page by:<\/p>\n
\n- Enabling https access from internet (in Access Rules, enable ora_p2_httpssl)<\/li>\n
- going to default web page for your service, in my case https:\/\/144.21.89.223<\/li>\n<\/ol>\n
You may have to accept some self-signed certificates<\/p>\n
And here it is with SQL Developer Web in the middle:
\n![\"CaptureSDW011\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureSDW011.png\")
<\/a><\/p>\nThe above shows PDB1\/pdbadmin for the schema but I installed it at CDB level and the log above tells me that the schema is c_sqldevweb, so given the input, I change the schema to c_sqldevweb then on the login page. Finally, the direct url in my example is https:\/\/144.21.89.223\/ords\/c_sqldevweb\/_sdw.<\/p>\n
I enter C##SQLDEVWEB (uppercase here) as the user and Ach1z0#d as the password.<\/p>\n