Christopher Jones

Containers for Oracle Instant Client 19 and 21 are now in the GitHub Container Registry for Oracle Linux 7 and Oracle Linux 8:

• oraclelinux7-instantclient
• oraclelinux8-instantclient

Oracle Instant Client enables development and deployment of applications that connect to Oracle Database, either on-premise or in the Cloud. The Instant Client libraries provide the necessary network connectivity and advanced data features to make full use of Oracle Database. The libraries are used by the Oracle APIs of popular languages and environments.

You can pull the images with one of the desired commands:

docker pull ghcr.io/oracle/oraclelinux7-instantclient:19 docker pull ghcr.io/oracle/oraclelinux7-instantclient:21 docker pull ghcr.io/oracle/oraclelinux8-instantclient:19 docker pull ghcr.io/oracle/oraclelinux8-instantclient:21

These containers are built from our existing Dockerfiles and contain the Basic package (for run time libraries), the Devel package (for header files), and the SQL*Plus package (for the SQL*Plus command line SQL query tool). You can extend the images to add other Instant Client packages to suit your needs - the Dockerfiles contain instructions.

The GitHub Container Registry also has containers for some popular programming languages with the appropriate Oracle Database APIs and Instant Client installed.

My thanks to the Oracle GitHub wizards for getting the GitHub Workflow Action enabled to build and push the Instant Client images.

I've created some new 'quick starts' to get you quickly up and running with Node.js and Oracle Database:

Quick Start: Developing Node.js Applications for Oracle Database

and

Quick Start: Developing Node.js Applications for Oracle Autonomous Database

Take a look!  These walk through installing and setting up the environment you need to connect Node.js to Oracle Database.

If your colleagues (or you) are looking to get started with Python and Oracle Database, then point them at our new on-boarding tutorials:

Quick Start: Developing Python Applications for Oracle Database

and

Quick Start: Developing Python Applications for Oracle Autonomous Database

These walk through installing and setting up the environment you need to connect Python to Oracle Database.

Once you have done one of these, then continue with the popular Python and Oracle Database Tutorial: Scripting for the Future to dive deeper to master the Python cx_Oracle interface and see how to build great Oracle Database applications.

Oracle Instant Client 21c is available for Linux x64. You can get it from the usual places: the Linux Instant Client download page or from yum.oracle.com.

Oracle Instant Client enables development and deployment of applications that connect to Oracle Database, either on-premise or in the Cloud. The Instant Client libraries provide the necessary network connectivity and advanced data features to make full use of Oracle Database.

There have been a few changes in this release.

Since the packages comprise more than the Oracle Client Interface libraries, the installation documentation for 21c has moved from the OCI manual to the Database Client Installation Guide for Linux manual.

The other changes are with RPM packaging. The RPMs no longer have a version number in the package name (yes the version number is in the file name, but it's not in the package name itself). This aids in-place upgrades, letting each new available Oracle "Release Update" be automatically installed when you update your server. Also aiding upgrades is the way the packages now install into a directory with just the major version e.g. /usr/*/oracle/21/client64/... instead of the major number and release update number like /usr/*/oracle/19.9/client64/....

If you get the packages from yum.oracle.com, the channels have changed. Details are on the Instant Client for Oracle Linux page. Our Dockerfiles are also useful references. Get them here for OL8 and here for OL7. In summary:

• On Oracle Linux 8:

  sudo dnf install oracle-instantclient-release-el8 sudo dnf install oracle-instantclient-basic

• On Oracle Linux 7:

  sudo yum install oracle-instantclient-release-el7 sudo yum install oracle-instantclient-basic

If you don't want automatic upgrades to future new major releases, then use version lock.

• For Oracle Linux 8:

  sudo dnf install yum-plugin-versionlock sudo dnf versionlock oracle-instantclient-release-el8

• For Oracle Linux 7:

  sudo yum install yum-plugin-versionlock sudo yum versionlock oracle-instantclient-release-el7

 

Release announcement: A new release of node-oracledb, the Node.js module for accessing Oracle Database, is available from npm.

Top features: Support for Oracle Database 21's faster JSON features; automatic numeric suffixes for duplicate column names; more control for fetching Oracle Database Object and Collections.

 

 

To accompany the Oracle Database 21c release, we are happy to bring you the node-oracledb 5.1 update with:

• Enhanced JSON functionality to support Oracle Database 21's native OSON storage format for JSON. A new node-oracledb type oracledb.DB_TYPE_JSON was added.

  JavaScript objects can now be bound directly when using the new storage type:

  const sql = `INSERT INTO mytable (myjson) VALUES (:bv)`; const data = { "userId": 1, "userName": "Anna", "location": "Australia" }; await connection.execute(sql, { bv: { val: data, type: oracledb.DB_TYPE_JSON } });

  To try the advantages of the new storage type, you can use Oracle Database 21c in Oracle Cloud. See my earlier blog post How to connect to Oracle Autonomous Cloud Databases. Oracle Instant Client 21 is currently available only for Linux.

  Existing node-oracledb JSON features - which also support the BLOB, CLOB and VARCHAR storage for JSON available since Oracle Database 12c - remain unchanged.

  See the node-oracledb Oracle Database JSON Data Type documentation and the Database JSON Developer's Guide for more goodness about working with JSON.

• A new oracledb.dbObjectAsPojo setting.

  This mode specifies whether Oracle Database named objects and collections that are queried should be returned to the application as "plain old JavaScript objects" or kept as database-backed objects. Regardless of the value, the interface to access objects is the same.

  Setting dbObjectAsPojo to true can avoid overhead if object attributes are repeatedly accessed. It also allows applications to close connections before any attributes are accessed (unless LOBs are involved). If you are not accessing all attributes or objects, it may be more efficient to use the default value of false.

• Numeric suffixes are now added to duplicate SELECT column names when using oracledb.OUT_FORMAT_OBJECT mode, allowing all columns to be represented in the JavaScript result object without needing the SQL to be altered.

  The code:

  sql = `SELECT dummy as emp1, dummy as emp1 from dual`; const result = await conn.execute(sql, [], { outFormat: oracledb.OUT_FORMAT_OBJECT }); console.log(result.rows);

  appends a numeric suffix to the duplicated column name:

  [ { EMP1: 'X', EMP1_1: 'X' } ]

• SODA (the NoSQL-style API) now takes advantage of Oracle Database 21c's JSON storage format by default. If you're using SODA with different versions of Oracle Client library connected to Oracle Database 21, review the node-oracledb SODA Requirements.

  Now that Oracle Instant Client 21 is available, don't forget that some existing node-oracledb 5.0 SODA features such as sodaCollection.save(), sodaCollection.saveAndGet(), and sodaCollection.truncate() that require recent client libraries are easier for you to take advantage of. These also work when connected to older Oracle database versions once node-oracledb is using the new client libraries.

For all the node-oracledb changes see the CHANGELOG.

Installing or Upgrading node-oracledb

You can install or upgrade node-oracle by updating your package.json requirements:

"dependencies": { "oracledb": "^5.1" },

See Installing node-oracledb for more details.

Resources

Node-oracledb installation instructions are here.

Node-oracledb documentation is here.

Node-oracledb change log is here.

Issues and questions about node-oracledb can be posted on GitHub or Slack (link to join Slack).

Follow us on Twitter or Facebook.

Finally, contributions to node-oracledb are more than welcome, see CONTRIBUTING.

 

 

cx_Oracle 8.1, the extremely popular Oracle Database interface for Python, is now Production on PyPI.

cx_Oracle is an open source package for the Python Database API specification with many additions to support advanced Oracle Database features.

 

 

The top features in cx_Oracle 8.1 are:

• Support for Python 3.9 was added.

  Also support Python 3.5 was removed, since it has reached end-of-life.

• Enhanced JSON functionality to support Oracle Database 21's native OSON storage format for JSON. A new cx_Oracle type cx_Oracle.DB_TYPE_JSON was added.

  Python dicts and lists can now be bound directly when using the new storage type:

  sql = "insert into customers values (:1)" data = dict(name="Rod", dept="Sales", location="Germany") cursor.setinputsizes(cx_Oracle.DB_TYPE_JSON) cursor.execute(sql, [data])

  To try the advantages of the new storage type, you can use Oracle Database 21c in Oracle Cloud. See my earlier blog post How to connect to Oracle Autonomous Cloud Databases. Oracle Instant Client 21 is currently available only for Linux.

  Existing cx_Oracle JSON features - which also support the BLOB, CLOB and VARCHAR storage for JSON available since Oracle Database 12c - remain unchanged.

  See the cx_Oracle Working with the JSON Data Type documentation and the Database JSON Developer's Guide for more goodness about working with JSON.

• SODA (the NoSQL-style API) in Oracle Database 21c now takes advantage of the new JSON storage format by default. If you're using SODA with different versions of Oracle Client library connected to Oracle Database 21, review the cx_Oracle SODA Requirements.

  Now that Oracle Instant Client 21 is available, don't forget that some existing cx_Oracle 8.0 SODA features such as SodaCollection.save(), SodaCollection.saveAndGet(), and SodaCollection.truncate() that require recent client libraries are easier for you to take advantage of. These also work when connected to older Oracle database versions once cx_Oracle is using the new client libraries.

See the release notes for all changes.

Installing or Upgrading cx_Oracle

You can install or upgrade cx_Oracle by running:

python -m pip install cx_Oracle --upgrade

The pip options --force-reinstall, --proxy, and --user may be useful in some environments. See cx_Oracle Installation for details.

cx_Oracle References

Home page: oracle.github.io/python-cx_Oracle/index.html

Installation instructions: cx-oracle.readthedocs.io/en/latest/installation.html

Documentation: cx-oracle.readthedocs.io/en/latest/index.html

Release Notes: cx-oracle.readthedocs.io/en/latest/release_notes.html

Source Code Repository: github.com/oracle/python-cx_Oracle

Facebook group: www.facebook.com/groups/oraclescripting/

Questions: github.com/oracle/python-cx_Oracle/issues

Here's a demo showing the connection syntax for node-oracledb in various proxy and external authentication connection scenarios.

The script mentions Oracle 12.2 because that is what I was testing way back at the time I wrote the script. I was also checking an enhancement in 18.3. You may find that most syntaxes are usable with earlier Oracle versions.

We also tweaked some behavior in node-oracleb 3.1, so if you have an older version you will see differences in cases 4 and 14, and will see few message changes in some other cases. You should upgrade!

 

/* 1. Before running this script, set up some schemas: In the CDB: alter system set remote_os_authent=true scope=spfile; Note this is insecure, but is a simple way of allowing OS authentication in a development environment. In the PDB that the script connects to: drop user myproxyuser cascade; drop user mysessionuser1 cascade; drop user mysessionuser2 cascade; drop user ops$oracle cascade; create user myproxyuser identified by welcome; grant create session to myproxyuser; create user mysessionuser1 identified by itdoesntmatter; grant create session to mysessionuser1; alter user mysessionuser1 grant connect through myproxyuser; create user ops$oracle identified externally; grant connect, resource to ops$oracle; alter user ops$oracle default tablespace users; create user mysessionuser2 identified by itdoesntmatter; grant create session to mysessionuser2; alter user mysessionuser2 grant connect through ops$oracle; 2. Run this script with node-oracledb 3.1 or later, when logged in to the OS as the user 'oracle' */ const oracledb = require('oracledb'); async function ShowUserInfo(conn) { let result = await conn.execute(` select sys_context('USERENV', 'PROXY_USER'), sys_context('USERENV', 'SESSION_USER') from dual`); console.log(" Proxy User:", result.rows[0][0]); console.log(" Session User:", result.rows[0][1]); console.log(); }; (async function() { let conn, pool, config, testdesc; // -------------------- STANDALONE CONNECTIONS -------------------- console.log("(1) Standalone: Basic Auth"); // Using 12.2 or later client libraries gives: // Proxy User: null // Session User: MYPROXYUSER try { config = { connectString: "localhost/orclpdb1", user: "myproxyuser", password: "welcome" }; conn = await oracledb.getConnection(config); await ShowUserInfo(conn); } catch (e) { console.log(e.message + "\n"); } console.log("(2) Standalone: External Auth"); // Using 12.2 or later client libraries gives: // Proxy User: null // Session User: OPS$ORACLE try { config = { connectString: "localhost/orclpdb1", externalAuth: true }; conn = await oracledb.getConnection(config); await ShowUserInfo(conn); } catch (e) { console.log(e.message + "\n"); } console.log("(3) Standalone: Basic Auth with proxy"); // Using 12.2 or later client libraries gives: // Proxy User: MYPROXYUSER // Session User: MYSESSIONUSER1 try { config = { connectString: "localhost/orclpdb1", user: "myproxyuser[mysessionuser1]", password: "welcome" }; conn = await oracledb.getConnection(config); await ShowUserInfo(conn); } catch (e) { console.log(e.message + "\n"); } console.log("(4) Standalone: External Auth with proxy"); // Using 12.2 or later client libraries gives: // Proxy User: OPS$ORACLE // Session User: MYSESSIONUSER2 try { config = { connectString: "localhost/orclpdb1", user: "[mysessionuser2]", externalAuth: true }; conn = await oracledb.getConnection(config); await ShowUserInfo(conn); } catch (e) { console.log(e.message + "\n"); } console.log("(5) Standalone: External Auth with proxy no brackets (expect DPI-1069)"); // Using 12.2 or later client libraries gives: // DPI-1069: proxy user name must be enclosed in [] when using external authentication try { config = { connectString: "localhost/orclpdb1", user: "mysessionuser2", externalAuth: true }; conn = await oracledb.getConnection(config); await ShowUserInfo(conn); } catch (e) { console.log(e.message + "\n"); } // -------------------- POOLED CONNECTIONS -------------------- console.log("(6) Pooled: Basic Auth"); // Using 12.2 or later client libraries gives: // Proxy User: null // Session User: MYPROXYUSER try { config = { connectString: "localhost/orclpdb1", user: "myproxyuser", password: "welcome" }; pool = await oracledb.createPool(config); conn = await pool.getConnection(); await ShowUserInfo(conn); } catch (e) { console.log(e.message + "\n"); } console.log("(7) Pooled: External Auth"); // Using 12.2 or later client libraries gives: // Proxy User: null // Session User: OPS$ORACLE try { config = { connectString: "localhost/orclpdb1", externalAuth: true }; pool = await oracledb.createPool(config); conn = await pool.getConnection(); await ShowUserInfo(conn); } catch (e) { console.log(e.message + "\n"); } console.log("(8) Pooled: Basic Auth with proxy in create"); // Using 12.2 or later client libraries gives: // Proxy User: MYPROXYUSER // Session User: MYSESSIONUSER1 try { config = { connectString: "localhost/orclpdb1", user: "myproxyuser[mysessionuser1]", password: "welcome" }; pool = await oracledb.createPool(config); conn = await pool.getConnection(); await ShowUserInfo(conn); } catch (e) { console.log(e.message + "\n"); } console.log("(9) Pooled: Basic Auth with proxy in acquire (Heterogeneous pool)"); // Using 12.2 or later client libraries gives: // Proxy User: MYPROXYUSER // Session User: MYSESSIONUSER1 try { config = { connectString: "localhost/orclpdb1", user: "myproxyuser", password: "welcome", homogeneous: false }; pool = await oracledb.createPool(config); conn = await pool.getConnection({ "user": "mysessionuser1" }); await ShowUserInfo(conn); } catch (e) { console.log(e.message + "\n"); } console.log("(10) Pooled: Basic Auth with proxy in acquire (Heterogeneous pool) (Expect ORA-00987)"); // Using 12.2 or later client libraries gives: // ORA-00987 missing or invalid username(s) try { config = { connectString: "localhost/orclpdb1", user: "myproxyuser", password: "welcome", homogeneous: false }; pool = await oracledb.createPool(config); conn = await pool.getConnection({ "user": "[mysessionuser1]" }); await ShowUserInfo(conn); } catch (e) { console.log(e.message + "\n"); } console.log("(11) Pooled: External Auth with proxy in create (Expect DPI-1032)"); // Using 12.2 or later client libraries gives // DPI-1032 user name and password cannot be set when using external authentication try { config = { connectString: "localhost/orclpdb1", user: "[mysessionuser2]", externalAuth: true }; pool = await oracledb.createPool(config); conn = await pool.getConnection(); await ShowUserInfo(conn); } catch (e) { console.log(e.message + "\n"); } console.log("(12) Pooled: External Auth with proxy no brackets in create (Expect DPI-1032)"); // Using 12.2 or later client libraries gives: // DPI-1032: user name and password cannot be set when using external authentication try { config = { connectString: "localhost/orclpdb1", user: "mysessionuser2", externalAuth: true }; pool = await oracledb.createPool(config); conn = await pool.getConnection(); await ShowUserInfo(conn); } catch (e) { console.log(e.message + "\n"); } console.log("(13) Pooled: External Auth with proxy in acquire"); // Using 18.3 or later client libraries gives // Proxy User: OPS$ORACLE // Session User: MYSESSIONUSER2 // This was an enhancement in Oracle 18.3 try { config = { connectString: "localhost/orclpdb1", externalAuth: true }; pool = await oracledb.createPool(config); conn = await pool.getConnection({ user: "[mysessionuser2]" }); await ShowUserInfo(conn); } catch (e) { console.log(e.message + "\n"); } console.log("(14) Pooled: External Auth with proxy no brackets in acquire (Expect DPI-1069)"); // Using 12.2 or later Oracle client libraries gives: // DPI-1069: proxy user name must be enclosed in [] when using external authentication try { config = { connectString: "localhost/orclpdb1", externalAuth: true }; pool = await oracledb.createPool(config); conn = await pool.getConnection({ user: "mysessionuser2" }); await ShowUserInfo(conn); } catch (e) { console.log(e.message + "\n"); } })();

Instant Client 19.8 for macOS is now available on OTN. This release introduces notarized DMG packages, making installation on recent macOS versions smoother: there is no need for manual approval of the shared libraries or running xattr commands. The ZIP files are still available, but I'm told that in some future version only DMGs will be released.

Oracle Instant Client gives free, light-weight, and easily installed Oracle Database tools, libraries and SDKs.

Update: Instant Client 19.8 has been certified on Big Sur.  It is also certified on High Sierra, Mojave, and Catalina.

Manual Instant Client Installation on macOS

In each package downloaded from OTN there is an INSTALL_IC_README.txt file that gives the instructions:

1. In Finder, double click on all desired Instant Client .dmg packages to mount them

2. Open a terminal window and change directory to one of the packages, for example:

   $ cd /Volumes/instantclient-basic-macos.x64-19.8.0.0.0dbru

3. Run the install_ic.sh script:

   $ ./install_ic.sh

   This copies the contents of all currently mounted Instant Client .dmg packages to $HOME/Downloads/instantclient_19_8

   If you have multiple DMG packages mounted, you only need to run install_ic.sh once

4. In Finder, eject the mounted Instant Client packages

Scripted Instant Client Installation on macOS

Installation can still be scripted. Determine which packages to use from OTN and then run something like:

cd $HOME/Downloads curl -O https://download.oracle.com/otn_software/mac/instantclient/198000/instantclient-basic-macos.x64-19.8.0.0.0dbru.dmg curl -O https://download.oracle.com/otn_software/mac/instantclient/198000/instantclient-sqlplus-macos.x64-19.8.0.0.0dbru.dmg hdiutil mount instantclient-basic-macos.x64-19.8.0.0.0dbru.dmg hdiutil mount instantclient-sqlplus-macos.x64-19.8.0.0.0dbru.dmg /Volumes/instantclient-basic-macos.x64-19.8.0.0.0dbru/install_ic.sh hdiutil unmount /Volumes/instantclient-basic-macos.x64-19.8.0.0.0dbru hdiutil unmount /Volumes/instantclient-sqlplus-macos.x64-19.8.0.0.0dbru

The extracted files will be in $HOME/Downloads/instantclient_19_8.

To connect your applications to an Oracle Autonomous Cloud Database - any of the Oracle Autonomous Database services like Autonomous Data Warehouse (ADW), Autonomous Transaction Processing (ATP), or the new Oracle Autonomous JSON Database (AJD) - then you will need to download and use a 'wallet'. This gives mutual TLS which provides enhanced security for authentication and encryption.

This post shows how to download a wallet and use it to connect to your Autonomous database from applications in Python, Node.js, PHP, Go, Ruby, and in tools like SQL*Plus. Basically in anything that uses Oracle's C stack "Oracle Call Interface" API.

Pre-requisites

The starting point for this post assumes you've already created a cloud database. But if you don't already have one, then you can quickly get an 'always free' account for no cost and create a database. There are other posts and documentation that show this, such as this blog. In summary, log in to your cloud account and, from the left hand menu, select which of the three kinds of database services suit your workload: ADW, ATP, or AJD. (Choose ATP if you are unsure). Click through the few prompts and enter required information, such as the database name. In my example I've used CJJSON.

During creation the 'Allow secure access from everywhere' option is selected. This gives easy access the database from your applications.

During creation you'll also set a password for the privileged database ADMIN user. (See my earlier blog about how to use ADMIN to create a 'normal' un-privileged user).

Download the Oracle Database wallet files

Downloading the wallet is simple.

• Navigate to the database console page and select 'DB Connection':

  

• Then select 'Download Wallet':

  

  This will prompt for a wallet password. (For Node.js, Python, PHP, Go, Ruby and anything else that internally uses Oracle's "Oracle Call Interface" libraries, this wallet password won't actually be used at runtime).

• Unzip the downloaded wallet ZIP file.

Finding the Easy Connect String from the tnsnames.ora File

People have been asking me about the "Easy Connect Plus" syntax so I'll show that first. Easy Connect is the simple syntax used in Oracle tools and APIs to identify the machine and database that you want to connect to. You might have used an Easy Connect string like localhost:1521/XEPDB1 for a local database. It identifies the host computer, port number, and the database service name that is running on that computer.

In the tnsnames.ora file extracted from the wallet ZIP, there are several network service names entries. (Whereas the three cloud "database service" options gave you a choice of workload capabilities, these "network services" provide different levels of performance and concurrency for a particular database. The differences between the network services are described here).

The first entry will be like:

cjjson_high = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.ap-sydney-1.oraclecloud.com))(connect_data=(service_name=abc_cjjson_high.adb.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adb.ap-sydney-1.oraclecloud.com,OU=Oracle ADB SYDNEY,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))

The mapping from tnsnames.ora to the Easy Connect Plus string follows the form:

protocol://host:port/service_name?wallet_location=/my/dir&retry_count=N&retry_delay=N

For the cjjson_high service, the equivalent Easy Connect Plus string is:

tcps://adb.ap-sydney-1.oraclecloud.com:1522/abc_cjjson_high.adb.oraclecloud.com?wallet_location=/Users/cjones/Cloud/CJJSON&retry_count=20&retry_delay=3

The only non-obvious parameter is the wallet_location which needs to be set to the directory containing the cwallet.sso file from the wallet ZIP. The other wallet files, including tnsnames.ora, are not needed when you use the Easy Connect Plus syntax.

If you are using this string in a terminal window, you may have to quote or escape some of the special characters.

Optional Extras

For quick testing you can simplify the connection string string to:

tcps://adb.ap-sydney-1.oraclecloud.com:1522/abc_cjjson_high.adb.oraclecloud.com?wallet_location=/Users/cjones/Cloud/CJJSON

If you are behind a firewall, then HTTP options can be used:

tcps://adb.ap-sydney-1.oraclecloud.com:1522/abc_cjjson_high.adb.oraclecloud.com?wallet_location=/Users/cjones/Cloud/CJJSON&https_proxy=proxy.example.com&https_proxy_port=80

Note connecting over HTTP is not recommended for production use due to the overheads.

For reference, the syntax is covered nicely in the technical brief Oracle Database 19c Easy Connect Plus Configurable Database Connection Syntax.

Connecting to an Autonomous Database with an Easy Connect String

To check database connection, I typically use SQL*Plus. Like the languages mentioned at the start, this uses Oracle Call Interface libraries, so the stack is consistent for my purposes. If you don't already have SQL*Plus and the Oracle Client libraries, you can get them from the free Oracle Instant Client.

Remember you need Oracle Client 19c and tools for Easy Connect Plus syntax syntax. If you have an older version then see the next section. However, since Oracle has client-server version interoperability, meaning Oracle Client 19c libraries can connect to Oracle Database 11.2 or later, I'd recommend upgrading your client-side tooling.

• From the wallet ZIP file put cwallet.sso in a secure directory. You don't need any other files. Unlike some other Oracle wallets, the ADB cloud wallet doesn't contain database username and password credentials but you should still keep it secure.

  I put cwallet.sso in the directory /Users/cjones/Cloud/CJJSON

• Now run SQL*Plus. You will be prompted for the ADMIN password you chose during database creation:

  $ sqlplus -l admin@tcps://adb.ap-sydney-1.oraclecloud.com:1522/abc_cjjson_high.adb.oraclecloud.com?wallet_location=/Users/cjones/Cloud/CJJSON SQL*Plus: Release 19.0.0.0.0 - Production on Mon Sep 14 18:18:46 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Enter password: Last Successful login time: Mon Aug 17 2020 18:22:52 +10:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.5.0.0.0 SQL> show user USER is "ADMIN" SQL>

  I used a bash terminal window on macOS. In other environments, or when using more options, you may need to quote the string:

  $ sqlplus -l admin@'tcps://adb.ap-sydney-1.oraclecloud.com:1522/abc_cjjson_high.adb.oraclecloud.com?wallet_location=/Users/cjones/Cloud/CJJSON&https_proxy=proxy.example.com&https_proxy_port=80' SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 15 18:05:23 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Enter password: Last Successful login time: Tue Sep 15 2020 18:05:09 +10:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.5.0.0.0 SQL>

• If you are using other languages you would use the connect string in the normal way. For example in Python:

  username = "admin" password = os.environ.get("ADMIN_PASSWORD") connect_string = "tcps://adb.ap-sydney-1.oraclecloud.com:1522/abc_cjjson_high.adb.oraclecloud.com?wallet_location=/Users/cjones/Cloud/CJJSON" connection = cx_Oracle.connect(username, password, connect_string) . . .

Connecting to Autonomous Database using pre-19c Oracle Client tools

If you don't want to use the Easy Connect Plus syntax - or your Oracle client libraries are older than Oracle 19c so you can't use the syntax - then there are some extra steps.

• Download the wallet ZIP file, as shown earlier

• Extract the cwallet.sso, sqlnet.ora, and tnsnames.ora files. Keep the files in a secure location:

  -rw-r--r--@ 1 cjones staff 6725 15 Aug 00:12 cwallet.sso -rw-r--r-- 1 cjones staff 134 15 Aug 10:13 sqlnet.ora -rw-r--r--@ 1 cjones staff 1801 15 Aug 00:12 tnsnames.ora

  The other files in the ZIP are not needed for the tools I mentioned at the start. They would be used, for example, if you were going to connect via JDBC.

• There are now two options:

  • Move the three files to the network/admin directory of the client libraries used by your application. For example if you are using Instant Client 18c, you would put them in /home/cjones/instantclient_18_5/network/admin/.

  • Alternatively, move them to any secure, accessible directory, for example /Users/cjones/Cloud/CJJSON.

    Now here's the important step: Edit sqlnet.ora and change the wallet location directory to the directory containing the cwallet.sso file:

    WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/Users/cjones/Cloud/CJJSON"))) SSL_SERVER_DN_MATCH=yes

    Then you need to indicate to tools where the tnsnames.ora and sqlnet.ora files are, for example by setting the environment variable TNS_ADMIN to /Users/cjones/Cloud/CJJSON. Alternatively some APIs like cx_Oracle 8 let you pass the directory at runtime using functions like init_oracle_client(). Neither of these are needed, and you don't need to edit sqlnet.ora, if you have put all the files in the network/admin directory.

• Now you can run SQL*Plus or other tools using one of the network service names in the tnsnames.ora file:

  $ sqlplus -l admin@cjjson_high

This post shows how to install and create a Python Flask application for Oracle Database.

Flask is a Python web framework. It is a 'micro framework' that doesn't enforce dependencies, so Oracle's excellent cx_Oracle module can be easily used to work with the database.

  

The demo app is a little service that lets you insert people's names into a 'user' table. You can query names by their id number.

Flask apps typically handle multiple parallel application users, so it's important to create a pool of database connections in the application. Other sample apps tends to skip this, so I particularly wanted to show it. Connection pooling is important for performance when applications frequently connect and disconnect from the database. Not only are pools faster, but they also provide and support Oracle's high availability features. They are recommended for all applications that must be reliable. This also means that small pools can be useful for applications that want a few connections available for infrequent use.

To run the application:

• Install Python 3, if not already installed.

• Install the Flask module: pip install --upgrade Flask. For details see the Flask installation documentation.

• Install Oracle's cx_Oracle 8 module and the Oracle Client libraries (if they not already available): pip install --upgrade cx_Oracle. For details see the Quick Start cx_Oracle Installation documentation.

  You can get Oracle Instant Client freely from the Oracle Instant Client Downloads page. Note you don't need this if Python is running on the same computer as your database.

• If you don't have a database, you can install Oracle Express Edition or create a permanent, free Oracle Cloud Database. Mac users can check out The Easiest Way to Install Oracle Database on Apple macOS.

• Save the code shown below as demo.py. (You can also download it from demo.zip.)

  Review demo.py. Note I've used cx_Oracle 8's init_oracle_client() function at line 28 to tell cx_Oracle where to find the Oracle Client libraries. Check the cx_Oracle 8 Initialization chapter for details on how this is used, or whether it is needed in your environment.

  In the demo, a pool of connections is created in start_pool() (at line 70) during application startup. Then each application route will get a connection from the pool and release it back to the pool after using it.

  The main Flask functionality is provided by three routes.

  • The first for / is implemented at line 141 in index() and simply returns a welcome message

  • A /post/<username> route is handled at line 150 by post() which inserts the given name.

  • A /users/<n> route is handled at line 163 by show_username() which queries the database and returns the username associated with given id number.

• Set the environment variables PYTHON_USERNAME and PYTHON_PASSWORD to your database username and password. Set PYTHON_CONNECTSTRING to the connection string for your database. For example if you have installed Oracle XE then you would set it to something like "example.com/XEPDB1".

  $ export PYTHON_USERNAME=cj $ export PYTHON_PASSWORD=welcome $ export PYTHON_CONNECTSTRING=example.com/XEPDB1

• Run the file. For simple testing I run:

  $ python demo.py

  but you may prefer a more official Flask invocation, for example FLASK_APP=demo.py flask run. See the Flask quickstart.

  Output will be like:

  $ python demo.py Connecting to example.com/orclpdb1 * Serving Flask app "demo" (lazy loading) * Environment: production WARNING: This is a development server. Do not use it in a production deployment. Use a production WSGI server instead. * Debug mode: off * Running on http://127.0.0.1:8080/ (Press CTRL+C to quit)

• In a browser, open the URL http://127.0.0.1:8080/ and you will see the demo message from the index() function:

  

• Now query the precreated user who has an id of 1. Load http://127.0.0.1:8080/user/1

  

• If you try to query an id that doesn't exist with http://127.0.0.1:8080/user/2 you'll get the error message:

  

• To insert a new name, load http://127.0.0.1:8080/post/alison

  

• Now this name can be queried back

  

That's it from the Oracle side. The rest is all beautification.

Demo Code

The demo application code is below (download from demo.zip):

""" demo.py Christopher Jones, 10 Sep 2020 Demo of using flask with Oracle Database Before running, set these environment variables: PYTHON_USERNAME - your DB username PYTHON_PASSWORD - your DB password PYTHON_CONNECTSTRING - the connection string to the DB, e.g. "example.com/XEPDB1" PORT - port the web server will listen on. The default in 8080 """ import os import sys import cx_Oracle from flask import Flask ################################################################################ # # On macOS tell cx_Oracle 8 where the Instant Client libraries are. You can do # the same on Windows, or add the directories to PATH. On Linux, use ldconfig # or LD_LIBRARY_PATH. cx_Oracle installation instructions are at: # https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html if sys.platform.startswith("darwin"): cx_Oracle.init_oracle_client(lib_dir=os.environ.get("HOME")+"/instantclient_19_3") elif sys.platform.startswith("win32"): cx_Oracle.init_oracle_client(lib_dir=r"c:\oracle\instantclient_19_8") ################################################################################ # # Start a connection pool. # # Connection pools allow multiple, concurrent web requests to be efficiently # handled. The alternative would be to open a new connection for each use # which would be very slow, inefficient, and not scalable. Connection pools # support Oracle high availability features. # # Doc link: https://cx-oracle.readthedocs.io/en/latest/user_guide/connection_handling.html#connection-pooling # init_session(): a 'session callback' to efficiently set any initial state # that each connection should have. # # If you have multiple SQL statements, then put them all in a PL/SQL anonymous # block with BEGIN/END so you only call execute() once. This is shown later in # create_schema(). # # This particular demo doesn't use dates, so sessionCallback could be omitted, # but it does show settings many apps would use. # # Note there is no explicit 'close cursor' or 'close connection'. At the # end-of-scope when init_session() finishes, the cursor and connection will be # closed automatically. In real apps with a bigger code base, you will want to # close each connection as early as possible so another web request can use it. # # Doc link: https://cx-oracle.readthedocs.io/en/latest/user_guide/connection_handling.html#session-callbacks-for-setting-pooled-connection-state # def init_session(connection, requestedTag_ignored): cursor = connection.cursor() cursor.execute(""" ALTER SESSION SET TIME_ZONE = 'UTC' NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI'""") # start_pool(): starts the connection pool def start_pool(): # Generally a fixed-size pool is recommended, i.e. pool_min=pool_max. # Here the pool contains 4 connections, which is fine for 4 concurrent # users. # # The "get mode" is chosen so that if all connections are already in use, any # subsequent acquire() will wait for one to become available. pool_min = 4 pool_max = 4 pool_inc = 0 pool_gmd = cx_Oracle.SPOOL_ATTRVAL_WAIT print("Connecting to", os.environ.get("PYTHON_CONNECTSTRING")) pool = cx_Oracle.SessionPool(user=os.environ.get("PYTHON_USERNAME"), password=os.environ.get("PYTHON_PASSWORD"), dsn=os.environ.get("PYTHON_CONNECTSTRING"), min=pool_min, max=pool_max, increment=pool_inc, threaded=True, getmode=pool_gmd, sessionCallback=init_session) return pool ################################################################################ # # create_schema(): drop and create the demo table, and add a row # def create_schema(): connection = pool.acquire() cursor = connection.cursor() cursor.execute(""" begin begin execute immediate 'drop table demo'; exception when others then if sqlcode <> -942 then raise; end if; end; execute immediate 'create table demo ( id number generated by default as identity, username varchar2(40))'; execute immediate 'insert into demo (username) values (''chris'')'; commit; end;""") ################################################################################ # # Specify some routes # # The default route will display a welcome message: # http://127.0.0.1:8080/ # # To insert a new user 'fred' you can call: # http://127.0.0.1:8080/post/fred # # To find a username you can pass an id, for example 1: # http://127.0.0.1:8080/user/1 # app = Flask(__name__) # Display a welcome message on the 'home' page @app.route('/') def index(): return "Welcome to the demo app" # Add a new username # # The new user's id is generated by the DB and returned in the OUT bind # variable 'idbv'. As before, we leave closing the cursor and connection to # the end-of-scope cleanup. @app.route('/post/<string:username>') def post(username): connection = pool.acquire() cursor = connection.cursor() connection.autocommit = True idbv = cursor.var(int) cursor.execute(""" insert into demo (username) values (:unbv) returning id into :idbv""", [username, idbv]) return 'Inserted {} with id {}'.format(username, idbv.getvalue()[0]) # Show the username for a given id @app.route('/user/<int:id>') def show_username(id): connection = pool.acquire() cursor = connection.cursor() cursor.execute("select username from demo where id = :idbv", [id]) r = cursor.fetchone() return (r[0] if r else "Unknown user id") ################################################################################ # # Initialization is done once at startup time # if __name__ == '__main__': # Start a pool of connections pool = start_pool() # Create a demo table create_schema() # Start a webserver app.run(port=int(os.environ.get('PORT', '8080')))

If installing the PHP OCI8 extension for Oracle Database using:

$ pecl install oci8

fails with the error:

fatal error: oci8_dtrace_gen.h: No such file or directory

then set the PHP_DTRACE environment variable before doing the install:

$ export PHP_DTRACE=yes $ pecl install oci8

The failure happens if your PHP installation was built with DTrace enabled. Due to some limitations with the PHP build & configuration files, the PECL OCI8 install needs the environment variable hint.

What is DTrace? It is a "comprehensive, advanced tracing tool for troubleshooting systematic problems in real time". It's available on a number of operating systems, including Oracle Linux, see Oracle Linux DTrace. PHP has various probes enabled and you may want to check the OCI8 and DTrace Dynamic Tracing manual for information on using DTrace with OCI8.

My Stackoverflow answer about installing OCI8 with DTrace seems to be popular, and others have contributed their experiences for their various environments - if the above doesn't work for you, check them out.

If you're new to PHP OCI8, the back half of my old, free Underground PHP and Oracle Manual e-book still has a lot of useful material.

Are you installing Go, Node.js, Python or PHP on Linux? Then check out Oracle's sample Dockerfiles at github.com/oracle/docker-images/tree/master/OracleLinuxDevelopers. Even if you're not using Docker, these files show the Linux repos and packages to use on bare Oracle Linux.

Let's create great software!

PS you may be interested in the two part blog series Docker for Oracle Database Applications in Node.js and Python.

A question came up on Stack Overflow about how to install PHP OCI8 on XAMPP on macOS to connect to Oracle Database. (Windows XAMPP users should follow Installing XAMPP on Windows for PHP and Oracle Database).

XAMPP is an open source package that contains Apache, PHP and many PHP 'extensions'. 

This is what I did with XAMPP 7.4.9 on macOS Mojave. I had done a basic XAMPP native install (using xampp-osx-7.4.9-0-installer.dmg, not the VM) with the Developer files option selected - this was the default.

It seems like XAMPP for macOS doesn't have the OCI8 extension built and sadly the 'obvious' method of installing OCI8 with the pecl command fails to correctly find the right PHP version. E.g. running:

sudo /Applications/XAMPP/xamppfiles/bin/pecl install oci8

and answering:

instantclient,/Applications/XAMPP/xamppfiles/lib/instantclient-11.2.0.3.0/

fails with checking PHP version... Unknown option: n.

The PECL config looked OK, but there must have been some conflict with the native PHP version or native tooling.

Anyway, the slightly longer manual install of OCI8 works. The instructions are below.

• I prefer to use a more recent Instant Client, so download the latest Instant Client Basic and SDK packages from here:

  cd $HOME/Downloads curl -O https://download.oracle.com/otn_software/mac/instantclient/instantclient-basic-macos.zip curl -O https://download.oracle.com/otn_software/mac/instantclient/instantclient-sdk-macos.zip

• Extract the Instant Client (substitute your download directory path):

  cd /Applications/XAMPP/xamppfiles/lib sudo unzip /Users/cjones/Downloads/instantclient-basic-macos.zip sudo unzip /Users/cjones/Downloads/instantclient-sdk-macos.zip

• You can optionally do some cleanup. Get rid of the old Instant Client:

  sudo rm -rf /Applications/XAMPP/xamppfiles/lib/instantclient-11.2.0.3.0

  and remove new libraries not needed by OCI8:

  sudo rm -f /Applications/XAMPP/xamppfiles/lib/instantclient_19_3/{*jdbc*,*occi*,*mysql*,*jar,uidrvci,genezi,adrci}

• Download the OCI8 extension from PECL and extract it:

  cd $HOME/Downloads curl -O https://pecl.php.net/get/oci8-2.2.0.tgz tar -xzf oci8-2.2.0.tgz

• Build and install OCI8. You'll need some kind of compiler available; I have XCode installed.:

  cd oci8-2.2.0 /Applications/XAMPP/xamppfiles/bin/phpize ./configure --with-php-config=/Applications/XAMPP/xamppfiles/bin/php-config --with-oci8=shared,instantclient,/Applications/XAMPP/xamppfiles/lib/instantclient_19_3 make sudo make install

• Then, tell XAMPP to enable the extension:

  sudo /Applications/XAMPP/xamppfiles/xampp oci8

  At the prompt, enter the path to the Instant Client directory /Applications/XAMPP/xamppfiles/lib/instantclient_19_3 like:

  Please enter the path to your Oracle or Instant Client installation: [/Applications/XAMPP/xamppfiles/lib/instantclient-11.2.0.3.0] /Applications/XAMPP/xamppfiles/lib/instantclient_19_3 installing symlink... patching php.ini... OCI8 add-on activation likely successful. XAMPP: Stopping Apache...ok. XAMPP: Starting Apache...ok.

• Now when you check localhost/dashboard/phpinfo.php you should see the OCI8 section, and will be able to run PHP OCI8 applications.

  

I noticed the XAMPP VM install from xampp-osx-7.4.9-0-vm.dmg also didn't have the OCI8 extension, but I haven't played with it yet. Since this runs a Linux guest OS, the instructions will need some tweaking.

You may be interested in how to 'run' an Oracle DB on macOS, which can be done in a VirtualBox VM like shown in The Easiest Way to Install Oracle Database on Apple macOS. Other people use a Docker container for the same thing.

The launch of the great new Oracle Autonomous JSON Database cloud service was widely covered, for example in the detailed blog post Introducing Oracle Autonomous JSON Database for application developers by my colleague Beda Hammerschmidt. To quote his post: "Today, Oracle announced the availability of Autonomous JSON Database — a new cloud service built for developers who are looking for an easy to use, cost-effective JSON database with simple NoSQL API's. Autonomous JSON Database provides all the core capabilities of MongoDB along with high performance, simple elasticity, full ACID support and complete SQL functionality."

Wow!

Beda shows the few steps needed to create a database. He then gives examples using the web SQL Developer query tool. I want to continue on from this and show how to connect to the JSON database service from your own computer using Oracle's popular Node.js node-oracledb module.

Create an Oracle Autonomous JSON Database

Follow the steps in Beda's blog to create an AJD database service. The AJD service was also made available as part of the Oracle Database Free tier, so if you don't have an Oracle cloud account and want to try it yourself, you can quickly get up and running for no cost.

During creation the 'Allow secure access from everywhere' option is selected. This gives easy access the database from a laptop.

During creation you'll also set a password for the database ADMIN user. This is the privileged account for database access. It will be used later to create a 'normal' (un-privileged) application user.

Download the Oracle Database wallet files

To connect to the database remotely, you need some 'wallet' files on the computer where you will run Node.js. The wallet enforces network security.

To get the wallet, navigate to the database console page and select 'DB Connection':

Then select 'Download Wallet':

This will prompt for a wallet password. (For Node.js, Python, PHP, Go, Ruby and anything else that internally uses Oracle's "Oracle Call Interface" libraries, this wallet password won't actually be used at runtime).

Once the wallet ZIP file has been downloaded, extract the cwallet.sso, sqlnet.ora, and tnsnames.ora files. Unlike some other Oracle wallets, this one doesn't contain database username and password credentials but you should still keep the wallet and files in a secure location. I put the wallet in the directory /Users/cjones/q/Cloud/ATP-S-CJJSON and extracted just the three files needed:

-rw-r--r--@ 1 cjones staff 20531 15 Aug 10:12 Wallet_CJJSON.zip -rw-r--r--@ 1 cjones staff 6725 15 Aug 00:12 cwallet.sso -rw-r--r-- 1 cjones staff 134 15 Aug 10:13 sqlnet.ora -rw-r--r--@ 1 cjones staff 1801 15 Aug 00:12 tnsnames.ora

The other files in the ZIP would be used, for example, if you were going to connect via JDBC.

Now here's the important step: Edit sqlnet.ora and change the wallet location directory to the directory containing the cwallet.sso file:

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/Users/cjones/q/Cloud/ATP-S-CJJSON"))) SSL_SERVER_DN_MATCH=yes

Sidebar: if you have Oracle Client libraries 19c or later, you can simplify the steps above and use an 'Easy Connect Plus' connection string. See How to connect to Oracle Autonomous Cloud Databases.

Check you can connect

To check you can connect to the database. I recommend using SQL*Plus. This uses the Oracle Call Interface libraries just like the Node.js node-oracledb module does. So if SQL*Plus can connect, then you know that your Node.js apps will also. SQL Developer commonly uses a JDBC stack and configuration is different. If you don't already have it, you can get SQL*Plus from the free Oracle Instant Client ZIP packages.

View the tnsnames.ora file. It will contain some network service names (to the left of the "=" signs) that you will use in your applications, for example:

cjjson_high = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.ap-sydney-1.oraclecloud.com))(connect_data=(service_name=xxxxxxxxxxxxxxx_cjjson_high.adb.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adb.ap-sydney-1.oraclecloud.com,OU=Oracle ADB SYDNEY,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))

There are several network service name entries in the file. The differences are described here.

Setting the TNS_ADMIN environment variable is standard for Oracle tools. It lets them locate the tnsnames.ora and sqlnet.ora files:

$ export TNS_ADMIN=/Users/cjones/q/Cloud/ATP-S-CJJSON

Now you can run SQL*Plus as the ADMIN user and enter the ADMIN user password that you set when the database was originally created:

$ sqlplus -l admin@cjjson_high SQL*Plus: Release 19.0.0.0.0 - Production on Sat Aug 15 10:22:55 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.5.0.0.0 SQL>

Success!

If you are behind a firewall, you can copy one of the tnsnames.ora entries, give it a new alias, and add https_proxy and https_proxy_port settings. For example:

cjjson_high_proxy = (description= (retry_count=20)(retry_delay=3)(address=(https_proxy=your-proxy.example.com)(https_proxy_port=80)(protocol=tcps)(port=1522)(host=adb.ap-sydney-1.oraclecloud.com))(connect_data=(service_name=xxxxxxxxxxxxxxx_cjjson_high.adb.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adb.ap-sydney-1.oraclecloud.com,OU=Oracle ADB SYDNEY,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))

Then cjjson_high_proxy would be used where I show cjjson_high. Note this is not recommended for production use due to the overheads.

Create an application user

First I want to create a 'normal' database user for general application access. I have a hack script createuser.sql that I tend to use. You can customize it for your needs:

-- createuser.sql -- -- Creates a DB user for quick testing define USERNAME = &1 -- Uncomment if you want to clean up a previous user -- begin execute immediate 'drop user &USERNAME cascade'; exception when others then if sqlcode <> -1918 then raise; end if; end; -- / create user &USERNAME; alter user &USERNAME default tablespace data temporary tablespace temp account unlock quota unlimited on data; grant connect , resource to &USERNAME; grant create session , create table , create procedure , create sequence , create trigger , create view , create synonym , alter session , create type , soda_app to &USERNAME; password &USERNAME

Start SQL*Plus as the ADMIN user:

$ sqlplus -l admin@cjjson_high

Then run the createuser.sql script. Pass the name of your desired user as the script argument. I used cj. The script will prompt for the password to set for this new user:

SQL> @createuser.sql cj old 1: create user &USERNAME new 1: create user cj User created. old 1: alter user &USERNAME new 1: alter user cj User altered. old 3: to &USERNAME new 3: to cj Grant succeeded. old 11: to &USERNAME new 11: to cj Grant succeeded. Changing password for cj New password: Retype new password: Password changed SQL> quit

Now you can check that connection succeeds:

$ sqlplus -l cj@cjjson_high [ . . .] SQL> show user USER is "CJ" SQL> quit Run a Node.js application

First, install Node.js and node-oracledb. The instructions are here.

Create a test file, such as test.js. This uses Oracle's great SODA NoSQL-style API that lets you access Oracle Autonomous JSON Database. SODA stands for 'Simple Oracle Document Access'. It gives you a simple document-store interface to store JSON documents in collections:

// test.js const oracledb = require('oracledb'); if (process.platform === 'darwin') { oracledb.initOracleClient({ libDir: '/Users/cjones/instantclient_19_3', // On Windows use double backslashes for each dir separator: 'c:\\instantclient_19_8' configDir: '/Users/cjones/q/Cloud/ATP-S-CJJSON' }); } // The general recommendation for simple SODA usage is to enable autocommit oracledb.autoCommit = true; async function run() { let connection; try { connection = await oracledb.getConnection( { user: 'cj', password: process.env.MYPW, connectString: 'cjjson_high' }); // Create the parent object for SODA const soda = connection.getSodaDatabase(); // Create a new SODA collection // This will open an existing collection, if the name is already in use. collection = await soda.createCollection("mycollection"); // Insert a document. A system generated key is created by default. content = {name: "Matilda", address: {city: "Melbourne"}}; doc = await collection.insertOneAndGet(content); const key = doc.key; console.log("The key of the new SODA document is: ", key); // Fetch the document back doc = await collection.find().fetchArraySize(0).key(key).getOne(); // A SodaDocument content = doc.getContent(); // A JavaScript object console.log(content); } catch (err) { console.error(err); } finally { if (connection) { try { await connection.close(); } catch (err) { console.error(err); } } } } run();

You can see I used the node-oracledb 5.0 oracledb.initOracleClient() function to set the Oracle Client library directory, and to give the directory containing the wallet network configuration files. Technically the latter isn't necessary because TNS_ADMIN was already set, but it is handy to use in general apps. The initOracleClient() function is most useful for Windows and macOS. On Linux you will still need to set the system library search path to the Oracle Client library directory before you run Node.js. For more information on initOracleClient(), see my blog post here and the documentation here.

SODA has extensive functionality. The test.js script simply creates a collection (collections can hold zero or more documents), inserts one document, and then retrieves that document. When using Oracle Client libraries 19.5 or later, you can increase the fetchArraySize() value to get better performance when selecting multiple records.

The script accesses the database password from an environment variable, so set this to the password you entered when you created the application user:

$ export MYPW=myverysecretpassword

Now run Node.js and the results will be shown:

$ node test.js The key of the new SODA document is: 7DA3104D6CF84FD9BF59832FA070BC99 { name: 'Matilda', address: { city: 'Melbourne' } }

Another example is in examples/soda1.js; this shows SODA functionality such as how to search JSON documents using query-by-example (QBE) methods.

Summary

The new Oracle Autonomous JSON Database service makes a great database platform for developers who want to take advantage of document storage. Simple Oracle Document Access (SODA) is a powerful NoSQL-style API available in Node.js, Python, Java, PL/SQL, C and via REST. The combination gives developers great access to modern technology.

Documentation on AJD is here. Documentation on using SODA in Node.js is here. (For Python users who have read this far, SODA documentation is here).

If you want to learn more about AJD and Oracle Database, join one of Beda's excellent JSON in Oracle Database Office Hours sessions.

After installing the Node.js node-oracledb module, beginner users will often see a DPI-1047 error when running their first script. For example on Apple macOS:

$ node example.js Error: DPI-1047: Cannot locate a 64-bit Oracle Client library: "dlopen(libclntsh.dylib, 1): image not found". See https://oracle.github.io/node-oracledb/INSTALL.html for help Node-oracledb installation instructions: https://oracle.github.io/node-oracledb/INSTALL.html

This wouldn't happen if the installation instructions had been followed - but a doc writer can only dream that people actually read manuals!

Downloading Oracle Instant Client

Installing the Oracle Client libraries is easy: they can be freely downloaded from here for macOS and here for Windows 64-bit. Simply download either the latest Basic or Basic Light package and unzip it.

I like to automate things. This is easy on macOS:

cd $HOME/Downloads curl -O https://download.oracle.com/otn_software/mac/instantclient/198000/instantclient-basic-macos.x64-19.8.0.0.0dbru.dmg hdiutil mount instantclient-basic-macos.x64-19.8.0.0.0dbru.dmg /Volumes/instantclient-basic-macos.x64-19.8.0.0.0dbru/install_ic.sh hdiutil unmount /Volumes/instantclient-basic-macos.x64-19.8.0.0.0dbru

At time of writing, this will install the Oracle Instant Client 19.8 libraries and create a new directory ~/Downloads/instantclient_19_8.

Telling Node.js where Instant Client is

Now you just need to tell your application where the libraries are. In node-oracledb 5, there are several ways to do this. My favorite is the new oracledb.initOracleClient() function. This is useful on macOS and Windows - particularly with recent macOS changes restricting library access, and changes to how Node.js is compiled. The function can also be used on Linux but there are some limitations - read the manual.

With Instant Client installed on macOS as above, add this to your scripts:

const oracledb = require('oracledb'); if (process.platform === 'darwin') { try { oracledb.initOracleClient({libDir: process.env.HOME + '/Downloads/instantclient_19_8'}); } catch (err) { console.error('Whoops!'); console.error(err); process.exit(1); } }

On Windows, if you unzipped the Oracle Instant Client Basic package to C:\oracle\instantclient_19_8, then you no longer necessarily need to add this directory to PATH. Instead use:

const oracledb = require('oracledb'); if (process.platform === 'win32') { try { oracledb.initOracleClient({libDir: 'C:\\oracle\\instantclient_19_8'}); // note the double backslashes } catch (err) { console.error('Whoops!'); console.error(err); process.exit(1); } } More

In node-oracledb 5 for macOS we also extended a feature previously usable on Windows to allow Instant Client libraries to be in the same directory as the node-oracledb binary module. Instead of using initOracleClient() macOS users can instead have a package.json script section:

"scripts": { "postinstall": "ln -s $HOME/Downloads/instantclient_19_8/libclntsh.dylib $(npm root)/oracledb/build/Release" },

With this, then anytime you install your application on macOS the Oracle Client libraries will be used.

For more information, and other uses of oracledb.initOracleClient(), such as passing the location of optional configuration files like tnsnames.ora, see Initializing Node-oracledb in the node-oracledb documentation.

A quick announcement: Oracle Instant Client 19.3 is now available on OTN for:

• AIX (PPC64)
• AIX (PPC32)
• HP-UX Itanium (64-bit)
• HP-UX Itanium (32-bit)

Oracle Instant Client comprises tools, libraries and SDKs for building and connecting applications to Oracle Database.

I had a few comments here about 19.3.

ROracle 1.3-2, the DBI-compliant driver for R and Oracle Database, has been released.

R is a powerful statistical language. ROracle can be used with Oracle Database to perform sophisticated analysis. Many users are also using it as part of Oracle R Enterprise,

ROracle 1.3-2 source code, documentation, and binaries (built for R-3.6.0 on Linux and Windows) are available for ROracle on OTN. This patch update isn't on CRAN due to an ongoing issue with CRAN infrastructure.

Don't all rush! There are only a couple of small changes in this update:

• you can now specify the type of the Oracle Database column using the ora.type attribute in a dataframe when using dbWriteTable

• ora.encoding is used for DML, when specified

Questions and comments can be posted on the OTN R Technologies forum

This is a GraphQL demo that stores data in an Oracle Database SODA collection. GraphQL is a query language for services. Oracle Database SODA is a NoSQL-like API for storing documents in Oracle Database. The demo follows on from my previous "Demo: GraphQL with Oracle Database and node-oracledb" which shows the same demonstration using relational tables and SQL queries. Read that post to learn about GraphQL and GraphQL queries. Also see the associated SODA presentation Getting Started with GraphQL APIs on Oracle Database with Node.js which shows some SODA features.

Demo prerequisites:

• Oracle Database 18.3, or later

• Oracle Client libraries 18.5 or 19.5, or later

Steps to run the demo:

1. Download the source code from here and extract it with tar -xf graphql_oracle_soda.tgz

2. Change to the graphql_oracle_soda directory, edit dbconfig.js and set your database schema credentials.

3. Run npm install. This will install dependencies and run setup.js to create the SODA schema.

4. Run npm start to start the GraphQL server.

5. Load the URL http://localhost:3000/graphql

6. In the browser, execute GraphQL requests shown in SAMPLE_QUERIES.txt

Since my previous blog discussed the behavior of the GraphQL inputs and outputs, let's look at the SODA implementation. In setup.js a collection is created:

const sodaCollectionName = "myBlogs"; let collection = await soda.openCollection(sodaCollectionName);

This method will actually open the collection if it already exists, so the next commands drop the collection so the demo can recreate it and run with a known, clean collection:

collection.drop(); await conn.execute(`begin execute immediate 'drop sequence "mySequence"'; exception when others then if sqlcode <> -2289 then raise; end if; end;`);

The cleanup is in two parts because the collection that will be created uses a sequence for the document keys. While SODA will auto-create the sequence, the collection.drop() method doesn't automatically drop the created sequence.

The collection is then created using custom metadata:

const metaData = { "schemaName": config.user.toUpperCase(), "tableName": "myBlogs", "keyColumn": { "name": "ID", "assignmentMethod": "SEQUENCE", "sequenceName": "mySequence" }, "contentColumn": { "name": "JSON_DOCUMENT", "sqlType": "BLOB", "compress": "NONE", "cache": true, "encrypt": "NONE", "validation": "STANDARD" }, "versionColumn": { "name": "VERSION", "method": "SHA256" }, "lastModifiedColumn": { "name": "LAST_MODIFIED" }, "creationTimeColumn": { "name": "CREATED_ON" }, "readOnly": false}; collection = await soda.createCollection(sodaCollectionName, {metaData: metaData});

I only needed to specify the metadata because I wanted to change the default key generation from a SHA hash to be a sequence, to align with GraphQL's use of integers for identifiers.

The final part of the setup is creation of a couple of documents in the collection:

await collection.insertOne({"title": "Blog Title 1", "content": "This is blog 1"}); await collection.insertOne({"title": "Blog Title 2", "content": "This is blog 2"});

I could have done all the setup in a SQL file, but decided to be more Node.js-centric in this particular demo.

Let's look at the GraphQL code in graphql_oracle_soda.js. The GraphQL typedefs and resolvers are the same as the previous SQL example - as is the output from running the demo. The updated helpers that interact with the database are the interesting parts.

In the new SODA example, the helper to get one document aka blog is:

async function getOneBlogHelper(id) { let conn = await oracledb.getConnection(); let soda = conn.getSodaDatabase(); let collection = await soda.openCollection(sodaCollectionName); let document = await collection.find().key(id.toString()).getOne(); let j = null; if (document) { let c = document.getContent(); j = {id: id, title: c.title, content: c.content}; } await conn.close(); return j; }

The SODA filter find() is used to find the SODA document with the requested id. The document content is extracted with getConection() and the document values are mapped to a JavaScript object returned back up via the GraphQL resolver. The other helpers are similarly straightforward.

This simple demo shows how to use some of Oracle's exciting SODA features. SODA APIs are available in a number of languages including Java and Python, letting you access your stored data from familar environments. If you do interesting things with SODA, let us know.

Good news - Oracle Instant Client 19.3 is available for Linux on OTN and yum. A Docker file is also available on GitHub.

Oracle Instant Client comprises tools, libraries and SDKs for building and connecting applications to Oracle Database. It comes in a number of packages: 'Basic' and 'Basic Light' packages provide a cornerstone, and additional packages can be installed to get extra functionality. Typical uses of Instant Client are for Oracle Database APIs like Python cx_Oracle and Node.js node-oracledb.

As the first yearly release (following on from 18c) in the new Oracle Database numbering system, the 19c development cycle seemed short. However there have been useful changes and fixes. Check out the documentation for the areas you are interested in. One thing I like is the enhanced Easy Connect Plus syntax, which removes the need to have a sqlnet.ora file for some commonly used connection options.

For RPM users, there are two things to note about Instant Client 19c package installation:

• The global system library search path is now automatically set during installation of the (requisite) Basic and Basic Light packages. You no longer need to use ldconfig or set LD_LIBRARY_PATH.

• You can only have one version of the packages installed by default. For example, the 19c RPMs will conflict with the 18c RPMs.

These two notes do not apply to the ZIP files: you still have to manually configure the library search path to suit the directory you installed into. And you can have as many ZIP versions installed concurrently as you like.

Some old news, but worth repeating is that the RPM packages on yum do not require a login or click through to download.

 

 

cx_Oracle 7.1, the extremely popular Oracle Database interface for Python, is now Production on PyPI.

 

Another great release of cx_Oracle is available from PyPI, this time with a focus on session pooling. There were also a number of incremental improvements and fixes, all detailed in the release notes.

Session Pooling

When applications use a lot of connections for short periods, Oracle recommends using a session pool for efficiency. The session pool is a pool of connections to Oracle Database. (For all practical purposes, a 'session' is the same as a 'connection'). Many applications set some kind of state in connections (e.g. using ALTER SESSION commands to set the date format, or a time zone) before executing the 'real' application SQL. Pooled connections will retain this state after they have been released back to the pool with conn.close() or pool.release(), and the next user of the connection will see the same state. However, because the number of connections in a pool can vary over time, or connections in the pool can be recreated, there is no guarantee a subsequent pool.acquire() call will return a database connection that has any particular state. In previous versions of cx_Oracle, any ALTER SESSION commands had to be run after each and every pool.acquire() call. This added load and reduced system efficiency.

In cx_Oracle 7.1, a new cx_Oracle.SessionPool() option 'sessionCallback' reduces configuration overhead, as featured in the three scenarios shown below. Further details on session callbacks can be found in my post about the equivalent feature set in node-oracledb.

Scenario 1: All Connections Should Have the Same State

When all connections in a pool should have exactly the same state, you can set sessionCallback to a Python function:

def InitSession(conn, requestedTag): cursor = conn.cursor() cursor.execute("alter session ....") pool = cx_Oracle.SessionPool(un, pw, connectstring, sessionCallback=InitSession, threaded=True) . . .

The function InitSession will be called whenever a pool.acquire() call selects a newly created database connection in the pool that has not been used before. It will not be called if the connection in the pool was previously used by the application. It is called before pool.acquire() returns. The big advantage is that it saves the cost of altering session state if a previous user of the connection has already set it. Also the current caller of pool.acquire() can always assume the correct state is set.

If you need to execute more than one SQL statement in the callback, use a PL/SQL block to reduce round-trips between Python and the database:

def InitSession(conn, requestedTag): cursor = conn.cursor() cursor.callproc( """begin execute immediate 'alter session set nls_date_format = ''YYYY-MM-DD'' nls_language = AMERICAN'; -- other SQL statements could be put here end;""")

The requestedTag parameter is shown in the next section.

Scenario 2: Connections Need Different State

When callers of pool.acquire() need different session states, for example if they need different time zones, then session tagging can be used in conjunction with sessionCallback. See SessionCallback.py for a runnable example.

A tag is a semi-arbitrary string that you assign to connections before you release them to the pool. Typically a tag represents the session state you have set in the connection. Note that when cx_Oracle is using Oracle Client 12.2 (or later) libraries then tags are multi-property and must be in the form of one or more "name=value" pairs, separated by a semi-colon. You can choose the property names and values.

Subsequent pool.acquire() calls may request a connection be returned that has a particular tag already set, for example:

conn = pool.acquire(tag="NLS_DATE_FORMAT=SIMPLE")

This will do one of:

• Select an existing connection in the pool that has the requested tag. In this case, the sessionCallback function is NOT called.

• Select a new, previously unused connection in the pool (which will have no tag) and call the sessionCallback function.

• Will select a previously used connection with a different tag. The existing session and tag are cleared, and the sessionCallback function is called.

An optional matchanytag parameter can be used:

conn = pool.acquire(tag="TIME_ZONE=MST", matchanytag=True)

In this case, a connection that has a different tag may be selected from the pool (if a match can't be found) and the sessionCallback function will be invoked.

When the callback is executed, it can compare the requested tag with the tag that the connection currently has. It can then set the desired connection state and update the connection's tag to represent that state. The connection is then returned to the application by the pool.acquire() call:

def InitSession(conn, requestedTag): # Display the requested and actual tags print("InitSession(): requested tag=%r, actual tag=%r" % (requestedTag, conn.tag)) # Compare the requested and actual tags and set some state . . . cursor = conn.cursor() cursor.execute("alter session ....") # Assign the requested tag to the connection so that when the connection # is closed, it will automatically be retagged conn.tag = requestedTag

The sessionCallback function is always called before pool.acquire() returns.

The underlying Oracle Session Pool tries to optimally select a connection from the pool. Overall, a pool.acquire() call will try to return a connection which has the requested tag string or tag properties, therefore avoiding invoking the sessionCallback function.

Scenario 3: Using Database Resident Connection Pooling (DRCP)

When using Oracle client libraries 12.2 (or later) the sessionCallback can alternatively be a PL/SQL procedure. Instead of setting sessionCallback to a Python function, set it to a string containing the name of a PL/SQL procedure, for example:

pool = cx_Oracle.SessionPool(un, pw, connectstring, sessionCallback="myPlsqlCallback", threaded=True)

The procedure has the declaration:

PROCEDURE myPlsqlCallback ( requestedTag IN VARCHAR2, actualTag IN VARCHAR2 );

For an example PL/SQL callback, see SessionCallbackPLSQL.py.

The PL/SQL procedure is called only when the properties in the requested connection tag do not match the properties in the actual tag of the connection that was selected from the pool. The callback can then change the state before pool.acquire() returns to the application.

When DRCP connections are being used, invoking the PL/SQL callback procedure does not need round-trips between Python and the database. In comparison, a complex (or badly coded) Python callback function could require lots of round-trips, depending on how many ALTER SESSION or other SQL statements it executes.

A PL/SQL callback can also be used without DRCP; in this case invoking the callback requires just one round-trip.

Summary

cx_Oracle 7.1 is a solid release which should particularly please session pool users.

cx_Oracle References

Home page: oracle.github.io/python-cx_Oracle/index.html

Installation instructions: cx-oracle.readthedocs.io/en/latest/installation.html

Documentation: cx-oracle.readthedocs.io/en/latest/index.html

Release Notes: cx-oracle.readthedocs.io/en/latest/releasenotes.html

Source Code Repository: github.com/oracle/python-cx_Oracle

Facebook group: https://www.facebook.com/groups/418337538611212/

Questions: github.com/oracle/python-cx_Oracle/issues