Mostly Making oracle easy, mostly:
Search for:
In ORDS

Oracle SQLcl and REST Data Services 23.2 are now available!

4 Mins Read

SQLcl

Easier & More Secure Connections

You can now SAVE and REUSE connections to your Oracle Database in SQLcl. The big benefits here are SECURITY and EASE-OF-USE.

If you choose to store your passwords in a secured WALLET, you won’t have to worry about them showing up in your command history or SCRIPT OUTPUT logs anymore. And, it’ll be much easier to connect to your databases.

There is a new commend, CONNMGR, and the CONNECT command has been enhanced.

Let’s connect as we normally would using EZCONNECT syntax. And then let’s save the connection, including it’s password.

conn -save HRPWD -savepwd

This is telling SQLcl to create a new named connection ‘HRPWD’, based on the current connection properties, and I’m using the ‘-savepwd’ parameter to have the password stored as well.

Now let’s use our connection.

sql -name HRPWD

SQLcl knows what this connection is, just like it would be if you were using a connection in the connection tree of SQL Developer.

Once I’m connected, we can confirm everything by using the ‘show connection’ command.

Collecting/Shipping database stats to OCI Metrics Service

You can now use SQLcl to actively query various database performance statistics, and then have those numbers copied up to your OCI Metrics Service.

Don’t use the Oracle Cloud, yet? Then you can skip this section, for now 🙂

What you need:

  • An OCI account
  • Your OCI Profile setup on your machine
  • An Oracle Database to monitor
  • A highly privileged user
  • SQLcl 23.2
  • The OCID for the COMPARTMENT you want to stow the stats in

I set my profile –

oci profile DEFAULT

Now I can run the ocidbmetrics command.

ocidbmetrics collect -compartmentId ocid1.compa… -region eu-frankfurt-1

Once that’s going, and it will KEEP GOING until you KILL IT, SQLcl will continously run a ton of queries.

When you’re ready to see the stats, hop on over to your OCI Console.

You’ll need to set the Metric Namespace to ‘sqlcl_db_metrics’

Dirty Secret: the Metrics service doesn’t CARE where these stats come from. I’m storing the data collected from my on-premises copy of Database 23c Free Developer Edition VirtualBox appliance!

I can do a lot of things with these stats, including setting alarms! I did a talk at Cloud World last year about making your Oracle Database be more ‘Cloud Native’ – this is a continuation of that idea.

The Talk

More on our OCI Metrics Service in combination with your Oracle Databases

ORDS

There are some pretty significant updates in this ORDS release. For one, we now have a default landing page when you are running ORDS Standalone. Instead of a 404 or redirect for APEX when you go to /ords

Calls to / will redirect to _/landing – unless you already have a static index.html in your docroot

If you don’t like this (APEX users), there’s a config parameter you can use to turn this off.

There’s a LOT more to talk about these releases, this post is just a tease/summary. Be on the look-out for more details going forward.

Well, maybe I should end with just one more tease.

“The Dead Pool”

I can now tell ORDS to destroy a pool. This will cause the connection pool to be ‘killed,’ and then subsequently reloaded on the next request for a resource in that pool. That means ORDS will re-read the config files. So instead of having to BOUNCE ORDS to have a pool config take effect, you can now use the DESTROY command.

ords pool –pid 12345 destroy “pool-name”

For this to work, you’ll need to know the process ID that ORDS is running with. Under the covers, we’re using the JMX features to do the Oracle Universal Connection Pool (UCP) work.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

23 Comments

  1. apodictus Reply

    Loving the named connection, however there appears to be a quirk I can’t google my way out of
    sql -name myconn => works fine, starts and connects as expected
    sql -name myconn @myfile.sql => Connection spec cannot be used with connection name

    It appears that if I’m using named connections the [start] argument a connect spec instead of a file to execute

    Also there is a strange quirk if i start sql /nolog. it still executes my login.sql (which causes the commands in there to complain there is no connection.

    Furthermore loving sqlcl

  2. Pavol Babel Reply

    Hi Jeff,

    How do I delete imported connections from connmgr?

    Regards
    Pavol

    • Pavol Babel

      OK it seems sqlcl connections are hidden under
      ~/.dbtools/connections
      and we are missing API to detele a connection know (however the structure is very straightforward as of now – maybe subject to change soon 🙂 ).

  3. Carsten Reply

    I also run in an issue with saving a connection.

    sql /nolog
    set cloudconfig mywalletfile.zip
    connect username@DBCC13_HIGH
    password…

    All good. After loging in I tried to save this connection:

    SQL> conn -save CC13a -savepwd
    Name: CC13a
    Connect String: DBCC13_HIGH
    User: username
    Password: not saved
    oracle.net.wallet_location: (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=${TNS_ADMIN})))
    TNSNAMES.ORA: C:\Users\myusername\DBTools\connections\CC13a\tnsnames.ora
    TNS Descriptor: DBCC13_HIGH = (description=(retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.***.oraclecloud.com))(connect_data=(service_name=***dbcc13_high.atp.oraclecloud.com))(security=(ssl_server_dn_match=yes)))

    As you can see the password is not saved. If I try then to use this connection I have to enter my password but it will not connect:

    sql -name CC13a

    SQLcl: Release 23.2 Production on Fri Jul 21 07:40:24 2023

    Copyright (c) 1982, 2023, Oracle. All rights reserved.

    Password? (**********?) ************
    USER = username
    URL = jdbc:oracle:thin:@DBCC13_HIGH
    Error Message = IO Error: The Network Adapter could not establish the connection (CONNECTION_ID=***)
    IO Error: The Network Adapter could not establish the connection (CONNECTION_ID=***)

  4. Carsten Reply

    Hi Jeff,

    I tried to import an SQLDeveloper connection and run in an issue ( SQLcl Release 23.2):

    SQL> connmgr import -duplicates replace DBCC13.json
    2023-07-20 08:42:42.615 SEVERE oracle.dbtools.connections.db.DatabaseProviderStorageFactory createObject DBCC13_COPY: invalid URI format for property sqldev.cloud.configfile(C:\Users\myusername\Downloads\sqlcl\bin\Wallet_DBCC13.zip)
    Encryption Key? ********
    2023-07-20 08:42:45.865 SEVERE oracle.dbtools.connections.db.DatabaseProviderStorageFactory createObject DBCC13_COPY: invalid URI format for property sqldev.cloud.configfile(C:\Users\myusername\Downloads\sqlcl\bin\Wallet_DBCC13.zip)
    2023-07-20 08:42:45.883 SEVERE oracle.dbtools.util.URIUtils newURI Illegal character in opaque part at index 2: C:\Users\myusername\Downloads\sqlcl\bin\Wallet_DBCC13.zip at java.base/java.net.URI$Parser.fail(URI.java:2974)
    java.net.URISyntaxException: Illegal character in opaque part at index 2: C:\Users\myusername\Downloads\sqlcl\bin\Wallet_DBCC13.zip
    at java.base/java.net.URI$Parser.fail(URI.java:2974)
    at java.base/java.net.URI$Parser.checkChars(URI.java:3145)
    at java.base/java.net.URI$Parser.parse(URI.java:3181)
    at java.base/java.net.URI.(URI.java:623)
    at oracle.dbtools.util.URIUtils.newURI(URIUtils.java:41)
    at oracle.dbtools.util.URIUtils.newURI(URIUtils.java:22)
    at oracle.dbtools.plusplus.connections.db.imp.CloudConverterFactory$CloudConverter.(CloudConverterFactory.java:71)
    at oracle.dbtools.plusplus.connections.db.imp.CloudConverterFactory.createConverter(CloudConverterFactory.java:37)
    at oracle.dbtools.plusplus.connections.db.imp.ConnectionsConverterFactory.getConverter(ConnectionsConverterFactory.java:35)
    at oracle.dbtools.plusplus.connections.db.imp.DatabaseProviderImporter.importConnection(DatabaseProviderImporter.java:29)
    at oracle.dbtools.plusplus.connections.db.imp.LegacyImporterFactory$LegacyImporter.importConnection(LegacyImporterFactory.java:118)
    at oracle.dbtools.plusplus.connections.db.imp.LegacyImporterFactory$LegacyImporter.importConnection(LegacyImporterFactory.java:58)
    at oracle.dbtools.plusplus.connections.db.imp.Importer.importConnections(Importer.java:69)
    at oracle.dbtools.plusplus.connections.db.ConnectionStoreCommand.handleImportCommand(ConnectionStoreCommand.java:197)
    at oracle.dbtools.plusplus.connections.db.ConnectionStoreCommand.handleEvent(ConnectionStoreCommand.java:115)
    at oracle.dbtools.raptor.newscriptrunner.CommandRegistry.fireListeners(CommandRegistry.java:448)
    at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:241)
    at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:355)
    at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:238)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:1142)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.runSqlcl(SqlCli.java:1323)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:350)

    2023-07-20 08:42:45.905 SEVERE oracle.dbtools.raptor.newscriptrunner.ScriptExecutor run java.base/java.nio.file.Path.of(Path.java:197)
    java.lang.NullPointerException: Cannot invoke “java.net.URI.getScheme()” because “uri” is null
    at java.base/java.nio.file.Path.of(Path.java:197)
    at java.base/java.nio.file.Paths.get(Paths.java:98)
    at oracle.dbtools.plusplus.connections.db.imp.CloudConverterFactory$CloudConverter.(CloudConverterFactory.java:71)
    at oracle.dbtools.plusplus.connections.db.imp.CloudConverterFactory.createConverter(CloudConverterFactory.java:37)
    at oracle.dbtools.plusplus.connections.db.imp.ConnectionsConverterFactory.getConverter(ConnectionsConverterFactory.java:35)
    at oracle.dbtools.plusplus.connections.db.imp.DatabaseProviderImporter.importConnection(DatabaseProviderImporter.java:29)
    at oracle.dbtools.plusplus.connections.db.imp.LegacyImporterFactory$LegacyImporter.importConnection(LegacyImporterFactory.java:118)
    at oracle.dbtools.plusplus.connections.db.imp.LegacyImporterFactory$LegacyImporter.importConnection(LegacyImporterFactory.java:58)
    at oracle.dbtools.plusplus.connections.db.imp.Importer.importConnections(Importer.java:69)
    at oracle.dbtools.plusplus.connections.db.ConnectionStoreCommand.handleImportCommand(ConnectionStoreCommand.java:197)
    at oracle.dbtools.plusplus.connections.db.ConnectionStoreCommand.handleEvent(ConnectionStoreCommand.java:115)
    at oracle.dbtools.raptor.newscriptrunner.CommandRegistry.fireListeners(CommandRegistry.java:448)
    at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:241)
    at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:355)
    at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:238)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:1142)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.runSqlcl(SqlCli.java:1323)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:350)

    Any idea?

    • thatjeffsmith Post Author

      Are your sqldev and sqlcl on the same machine, or did you move the json file?

      Also try this workaround, export everything but your Cloud Wallet type connections, does that work?

      jeff

  5. Simon Pane Reply

    Hi Jeff,

    I’ve found an interesting APEX (23.1.2 with patchset 2) quirk the new ORDS (23.2) default landing page after testing in multiple environments. (But all NOT in stand-alone mode – all tested using Tomcat 9.)

    For non-SSL URLs, when the ORDS default landing page comes up it works and shows APEX as being available and with the “GO” button right away.

    But for SSL protected URLs (with a self-signed cert), the APEX option is always greyed out with the “App Unavailable” message. That is UNTIL you navigate to some other URL (i.e. https://:8443/ords/apex) and back. Once you go to some other …/ords/ page and back to the ORDS landing page, APEX shows as available. But not until then. Just refreshing or even restarting the webserver does not correct is.

    Interestingly using any, even a non-existent URL works. For example navigating to https://:8443/ords/garbage brings up an expected 404 error – but when you go back to just …/ords, then the landing page is updated and APEX app then correctly shows as being available.

    FYI

    Simon

    • Carsten

      Yes, sqldev and sqlcl are on the same machine. I exported the .json file into the same folder where sql.exe is located.

      With your workaround, exporting a connection without wallet, it works.

      SQL> connmgr import -duplicates replace DQP001.json
      Encryption Key? ********
      Importing connection DQP001_ERMDATA: Success
      1 connection(s) processed

    • Hector

      I am facing the same issue. Do you have some idea to solve it?

    • Dima

      ORDS 23.2.2 Standalone – Landing Page

      Hello everyone

      I use APEX.

      Why is it that after loading ORDS, the Landing page is loaded at the first navigation, and not the page that is specified in the “misc.defaultPage” parameter?

      In the future, any subsequent request loads the page that is specified in the “misc.defaultPage” parameter – what is expected and was before ORDS version 23.2.

      Is it even possible to completely disable the Landing page with links to SQL Developer Web, APEX, OAuth Clients?

      Thank you in advance for any assistance

    • thatjeffsmith Post Author

      Make sure this is set in global settings.xml

      apex

      Then going to /ords will again default to apex. Unless you don’t have apex, then you’ll get the old 404.

      Confirmed this is working as expected in ORDS 23.2.2

  6. Rajeshwaran, Jeyabal Reply

    Jeff,

    apart from EZConnect , dont this save command works with TNS Entries too ?


    rajesh@DECSDEV>
    rajesh@DECSDEV> show connection
    COMMAND_PROPERTIES:
    type: ORACLE
    user: rajesh
    url: decsdev
    CONNECTION:
    RAJESH@jdbc:oracle:thin:@decsdev
    CONNECTION_IDENTIFIER:
    decsdev
    CONNECTION_DB_VERSION:
    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    Version 19.20.0.1.0
    NOLOG:
    false
    PRELIMAUTH:
    false
    rajesh@DECSDEV> conn -save rajeshdecsdev -savepwd
    Name: rajeshdecsdev
    Connect String: decsdev
    User: rajesh
    Password: ******
    rajesh@DECSDEV>
    rajesh@DECSDEV> connmgr test rajeshdecsdev
    Connection Test Failed - IO Error: The Network Adapter could not establish the connection (CONNECTION_ID=f+Y0RfkkS5qEXGMnX/1PBA==)
    rajesh@DECSDEV>

    • Pavol Babel

      I am also missing TNS Entries handling. connmgr is not even able to import TNS connections from SQL Developer

      Importing connection ESKJJC: Failure – Unable to convert Oracle connection type TNS

    • Pavol Babel

      Thanks Jeff, hopefully fix of bug will really come in sqlcl 23.3. Not like the sqldeveloper bug which is not displaying temporary clob data correctly (from instance sqlfulltext from v$sql), where we have been waiting for the logged bug resolution for quite a long time.

    • thatjeffsmith Post Author

      I know the sqldev fixes are taking longer than they should, you have my sincere apologies.

    • thatjeffsmith Post Author

      The developer just put in the merge requests for these connections fixes, so yes on 23.3 end of month for SQLcl.

    • Pavol Babel

      Thanks Jeff for great news regarding sqldev 23.3 and temporary clob display fix.

      BR
      Pavol

  7. christian Reply

    hallo Jeff,
    according to the sqlcl ReleaseNotes you posted above, there should be a ‘connmgr’ program to import connections from sqldeveloper.
    in my download the connmgr does not exist.
    ~/Home_Windows/develop/programme/SQLcl23.2/sqlcl$ cat 23.2.0.178.1027
    #
    # Oracle SQLcl Version 23.2.0.178.1029
    #
    Greetings Christian

    • thatjeffsmith Post Author

      It’s a COMMAND, not a PROGRAM.

      SQL> help connmgr import
      Import one or more connections into the common connection store

      Usage:
      connmgr|cm import {OPTIONS} {PARAMETERS}

      Options:
      -duplicates
      How to handle imported connections whose names match an existing connection
      IGNORE: (Default) skip the duplicate connections
      RENAME: rename the duplicate connections by appending a unique number to the end
      of the name
      REPLACE: replace the existing connection with the new connection

      Parameters: A single JSON file containing connections exported from SQL Developer

      More help topics:
      CONNMGR IMPORT EXAMPLES

Write A Comment