Christopher Jones

Subscribe to Christopher Jones feed
Oracle Blogs
Updated: 1 day 19 hours ago

Oracle Instant Client 19c for AIX and HP-UX Itanium is on OTN

Thu, 2019-05-30 18:12

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

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.

New Release: ROracle 1.3-2, the DBI-compliant driver for R and Oracle Database

Thu, 2019-05-30 17:45

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

Demo: GraphQL with Oracle Database SODA and node-oracledb

Fri, 2019-05-24 02:18

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.

Oracle Instant Client 19c for Linux is Available

Mon, 2019-05-06 21:11

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.

Python cx_Oracle 7.1's Connection Fix-up Callback Improves Application Scalability

Mon, 2019-02-04 18:09

cx_Oracle logo

 

 

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

Use node-oracledb 3.1's connection tagging to efficiently set session state

Tue, 2019-01-22 17:22

Release announcement: The latest version of node-oracledb, the Node.js module for accessing Oracle Database, is on npm.

Top features: Connection tagging; New-connection callback; Explicit session drop; Multi-binary install; Deferred Oracle Client library initialization.

 

 

This release of node-oracledb has features to make installation and configuration easier, and for working with pooled connection more efficiently. The highlights are below, but check the CHANGELOG for all improvements and bug fixes.

  • The pre-built node-oracledb binaries are now bundled into the node-oracledb npm package, making installation easier and allowing applications to be more portable.

  • Loading of the Oracle Client libraries is now deferred until the first use of oracledb.getConnection(), oracledb.createPool(), oracledb.oracleClientVersion, or oracledb.oracleClientVersionString.

  • Pooled connections can now be tagged with a text string when releasing them to the connection pool. This can be used to indicate what 'session state' (e.g. ALTER SESSION settings or PL/SQL package state) the connection has. The application can later call pool.getConnection() and request a connection which has been tagged. This lets the application use a connection with a known session state.

  • A Node.js callback function can be invoked when a pooled connection is newly created and has never been acquired from the pool before, or when requested and actual tags mismatch. Alternatively a PL/SQL procedure can be invoked when requested and actual tags mismatch. This lets applications set session state before the connection is used.

  • Connections in the connection pool can now be forcefully dropped so that the next pool.getConnection() that causes the pool to grow will re-establish a new connection to the database.

Install Changes

The node-oracledb module necessarily has a binary layer that links with Oracle Client libraries to communicate to Oracle Database. (Technically this makes it an 'add-on', not a module.) From node-oracledb 3.1 the pre-built binaries we provide are now bundled into the node-oracledb npm package.

The bootstrap install script no longer does a separate request to download a binary from GitHub - this change may help anyone with network restrictions. After installation, the correct binary (if one is available for your operating system and Node.js version) is loaded by require('oracledb') at run-time. This means that you can upgrade Node.js, or copy the node_modules/oracledb directory to another operating system without always needing to re-install node-oracledb, making it easier to deploy some classes of application.

If you are deploying to a fixed environment, such as a Docker container, and really want to minimize the install footprint, you can remove binaries for other Node.js versions by running 'npm run prune' in the node_modules/oracledb directory. This saves a few megabytes.

Node-oracledb source code remains available at GitHub or git://oss.oracle.com/git/oracle/node-oracledb.git/ which you can use to build node-oracledb binaries on platforms that do not have pre-built binaries.

Deferred Loading

Connections to Oracle are often known as 'sessions'. Each connection can have what is called 'session state' controlling behavior. State can be set in various way such as via environment variables or with ALTER SESSION statements. For node-oracledb, a commonly set environment variable is ORA_SDTZ which controls the default session time zone:

$ export ORA_SDTZ=UTC $ node myapp.js

Instead of setting the value before starting Node, some developers prefer setting it inside the application under the impression there is no possibility of mis-configuration:

process.env.ORA_SDTZ = 'UTC'; const oracledb = require('oracledb');

But a number of people set environment variables too late, or discover insurmountable operating system differences (e.g. between PATH on Windows and LD_LIBRARY_PATH on Linux, which has to be set before a process starts), and basically get confused by trying to set the environment inside applications. Overall I don't recommend this usage because I'm the person who keeps having to help these users!

Luckily for people who ignore my advice, node-oracledb 3.1's new deferred library loading change makes node-oracledb more tolerant of Oracle environment setting placement. Initialization of the Oracle Client libraries is now deferred until the first use of oracledb.getConnection(), oracledb.createPool(), oracledb.oracleClientVersion, or oracledb.oracleClientVersionString. If the Oracle Client cannot be loaded, getConnection() and createPool() will return an error via the callback. Accessing oracledb.oracleClientVersion or oracledb.oracleClientVersionString will throw an error. Previously require('oracledb') would throw an error.

Since Oracle client libraries are not loaded until a connection is used, you can now do:

const oracledb = require('oracledb'); . . . // Some time later process.env.ORA_SDTZ = 'UTC'; let pool = oracledb.createPool( . . . )

More practically, this change makes require('oracledb') always succeed (if the module is installed!), allowing node-oracledb constants and other attributes to be accessed even if the Oracle Client is not available. This makes it easier to include node-oracledb in multi-database applications where not all users will be accessing Oracle Database. It allows code generation tools to access node-oracledb constants without needing Oracle Client installed on the computer that generates Node.js code.

Session State

Getting back to altering session state, some applications do this with explicit ALTER SESSION commands. Pooled connections will retain this session state after they have been released back to the pool with connection.close() but, because pools can grow, or connections in the pool can be recreated, there is no guarantee a subsequent getConnection() call will return a database connection that has any particular state. So in previous versions of node-oracledb, any ALTER SESSION commands had to be re-run after each and every pool.getConnection() call. This added load and reduced system efficiency:

// In the past with node-oracledb 3.0 connection = await pool.getConnection(); await connection.execute(`alter session set time_zone='UTC'`); await connection.execute(`select ...`); // actually use the connection

In node-oracledb 3.1, the new createPool() option sessionCallback can be used to set session state efficiently. You can set sessionCallback to a Node.js function that will be called whenever pool.getConnection() will return a newly created database connection that has not been used before. It is also called when connection tagging is being used and the requested tag does not match the actual tag of the connection returned by the pool. It is called before getConnection() returns in these two cases. It will not be called in other cases. 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 getConnection() can always assume the correct state is set.

The following example prints "In initSession" just once - for the first getConnection() call. The second getConnection() call returns the previously used connection (because poolMax is 1) which has already been initialized. The function initSession is not called a second time:

// New in node-oracledb 3.1 const oracledb = require('oracledb'); function initSession(connection, requestedTag, cb) { console.log("In initSession"); cb(); } (async function() { try { let pool = await oracledb.createPool({ user: 'hr', password: 'welcome', connectString: 'localhost/xe, sessionCallback: initSession, poolMin: 1, poolMax: 1, poolIncrement: 0 }); let connection; connection = await pool.getConnection(); await connection.close(); connection = await pool.getConnection(); await connection.close(); } catch (err) { console.error(err.message); } })();

The initSession() parameter "requestedTag" is only set when tagging is being used, see later.

Although a sessionCallback function can be used for logging, it is more commonly used to set session state. This state should be set in the function before it invokes its own callback "cb". The following example sets two NLS settings in each pooled connection. They are only set the very first time connections are established to the database. Subsequent pool.getConnection() calls that return a previously used connection will not cause initSession to be called:

function initSession(connection, requestedTag, cb) { connection.execute( `alter session set nls_date_format = 'YYYY-MM-DD' nls_language = AMERICAN`, cb); }

If you need to execute multiple SQL statements in the callback, use an anonymous PL/SQL block to avoid repeated execute() calls and save round-trips between node-oracledb and Oracle Database:

function initSession(connection, requestedTag, cb) { connection.execute( `begin execute immediate 'alter session set nls_date_format = ''YYYY-MM-DD'' nls_language = AMERICAN'; -- other SQL statements could be put here end;`, cb); }

Let's say that you implement initSession in a micro-service. The service simply connects, does one query, and then disconnects. Compare it with node-oracledb 3.0 where you would have to execute ALTER SESSION each time you call getConnection(). The new version will only execute ALTER SESSION once for each connection in the pool. When poolMax is 4 and you call the service 1000 times, the number of statement executions required for each version can be compared:

Node-oracledb version Service Called poolMax ALTER SESSION calls SELECT calls Statement Executions 3.0 1000 4 1000 1000 2000 3.1 1000 4 4 1000 1004  

Using sessionCallback in node-oracledb 3.1 significantly reduces the number of statement executions and therefore reduces the round-trips between node-oracledb and Oracle Database. In one basic comparison of an application like this, the average response time went down from 0.35 ms to 0.27 ms by using a sessionCallback function.

Tagging

Using a simple sessionCallback is great when you want every connection to have the same session settings. If some connections need different state to others, then you can set the desired state in a connection (e.g. with ALTER SESSION) and set an application-specific string 'tag' on the connection to record the session state. Later, other pool.getConnection() calls can request a connection with a specified tag, i.e. a connection that has a known session state:

// Get initial connection connection = await pool.getConnection(); // Change the session state and set the tag to record that state. await connection.execute(`ALTER SESSION SET TIME_ZONE='UTC'`); connection.tag = "TZ=UTC"; // With 12.2 client libs the tag must be like "k=v" . . . // Use connection // Closing saves the current connection.tag value for that connection await connection.close(); . . . // Ask to be given a connection which has a specific tag. If no // suitable connection exists in the pool, a brand new connection may // be returned; this won't have a tag set. connection = await pool.getConnection({tag: "TZ=UTC"}); console.log('Connection tag is ' + connection.tag); if (connection.tag !== "TZ=UTC") { await connection.execute(`ALTER SESSION SET TIME_ZONE='UTC'`); connection.tag = "TZ=UTC"; } // Else got an initialized session so don't need to do ALTER SESSION. // The aim is to avoid doing ALTER SESSION unless necessary. . . . // Use connection

You would use tagging where the aim is to get connections which have one of several different states. Do not use tagging if all connections should have the same state - you can simply use sessionCallback as shown earlier. Also there is little point using tagging with huge numbers of different tags since the chances of getConnection() returning an already initialized connection are low, so most connections will need some kind of session reset, and the cost of tag management will add overhead to the situation. This is also true if the pool frequently grows and shrinks.

As you can guess, sessionCallback can be used with tagging to set state. When the requested tag doesn't match the connection's actual tag, the sessionCallback function will be called before pool.getConnection() returns. The function can check the requestedTag parameter against connection.tag, and then adjust the session state and connection.tag value as desired.

// The pool sessionCallback function function initSession(connection, requestedTag, cb) { console.log(`initSession() requested tag: ${requestedTag}, actual tag: ${connection.tag}`); // requestedTag and connection.tag can be parsed to decide what state to set . . . connection.execute(`ALTER SESSION SET . . .`, (err) => { connection.tag = ". . ."; // Update connection.tag to match the new state cb(err); // Call cb() after setting any state } ); }

In initSession(), the value of requestedTag will be different to connection.tag by definition because the callback is not called if the tags are the same. The edge-case exception is if the getConnection() call asked for a tag of "" (empty string) and the connection is new and hasn't had a tag set. However, to use tagging efficiently, you should request initialized sessions, and not mix getConnection() calls that request tags with calls that request no tags.

A best-practice recommendation is to set connection.tag in the callback where you update the session state, however it can be set anytime prior to the connection being closed if needed.

The underlying Oracle Session Pool has various modes and heuristics to optimally choose the connection to return from the pool. Overall, a pool.getConnection() call will try to return a connection which has the requested tag, therefore avoiding invoking the sessionCallback function. If no available connection with that tag exists in the pool, an untagged connection or a connection with a new session will be returned, and thus invoke the sessionCallback function; in this case the connection.tag property at the time the sessionCallback is invoked will be an empty string. If the optional getConnection() attribute 'matchAnyTag' is true, then a connection that has a different tag may be returned and connection.tag in the sessionCallback function will have that different tag. In summary, Oracle will try its best to reuse connections, but it is up to the sessionCallback function to check the actual and requested tags and make necessary session state changes.

When node-oracledb is using Oracle Client libraries 12.2 or later, node-oracledb always uses Oracle's multi-property tagging mode and the tag string must be of the form of one or more "name=value" pairs, separated by a semi-colon, for example "loc=uk;lang=cy" or "TIME_ZONE=UTC;NLS_DATE_FORMAT=YYYY-MM-DD" (otherwise you'll get an error like 'ORA-24487: Invalid properties or values provided for OCISessionGet'). The property keys and values in a multi-property tag are case sensitive. The order of properties influences which connection is returned by the connection pool, so put the most important properties early in the tag. You can use a multi-property tag to record various session settings so your sessionCallback function can parse the tag and decide which specific settings should be reset. Note that the underlying Oracle session pool parses tag properties so "a=b;c=d" is considered the same as "c=d;a=b" when choosing which connection in the pool should be used. However the Node.js callback is invoked after a simple string comparison of the full tag strings and would treat these examples as different.

You can make the callback as complex or as simple as needed, depending on the business requirements and what tag properties are expected. This example assumes that tags have the format USER_TZ=X, where X is a valid Oracle timezone:

// See examples/sessiontagging1.js // The connection callback function function initSession(connection, requestedTag, cb) { console.log(`In initSession. requested tag: ${requestedTag}, actual tag: ${connection.tag}`); const tagParts = requestedTag.split('='); if (tagParts[0] != 'USER_TZ') { cb(new Error('Error: Only property USER_TZ is supported')); return; } // Execute the session state change. connection.execute( `ALTER SESSION SET TIME_ZONE = '${tagParts[1]}'`, (err) => { connection.tag = requestedTag; // Record the new state of the connection cb(err); } ); } // Start a connection pool try { await oracledb.createPool({ user: 'hr', password: 'welcome', connectString: 'localhost/XE', sessionCallback: initSession }); } catch (err) { console.error('init() error: ' + err.message); } // . . . Later use the pool // Request a connection from the pool cache with a given tag, but // accept any tag being returned. let connection = await oracledb.getConnection( {poolAlias: 'default', tag: "USER_TZ=UTC", matchAnyTag: true}); // What happens: // (i) If a brand new connection was created in the pool, initSession() is // called to set the state and set connection.tag. // (ii) If a matching connection was found in the pool, connection.tag // will equal "USER_TZ=UTC". In this case initSession() is not called. // (iii) If another connection is returned, it will have no tag (if it // is a new or recreated connection) or a different tag (because // matchAnyTag was true) that another user has set. In this case, // initSession() is called to set the state and update connection.tag. // Use the connection, which is in the UTC timezone let result = connection.execute(. . .); // Closing the connection retains the value of connection.tag await connection.close(); . . . }

A more complete example of a callback is shown in examples/sessiontagging2.js.

When using Oracle Client 12.2, a PL/SQL session state fix-up procedure can alternatively be called instead of the Node.js function. The PL/SQL procedure is called only when the properties in the requested connection tag do not match the properties in the actual tag. Instead of setting sessionCallback to a Node.js function, set it to a string containing the name of your PL/SQL procedure:

try { let pool = await oracledb.createPool({ user: 'hr', password: 'welcome', connectString: 'localhost/XE', sessionCallback: "myPackage.myPlsqlCallback" }); . . . let connection = await pool.getConnection({tag: "DF=YYYY-MM-DD"}); . . . // Use connection // The session state will be whatever myPlsqlCallback sets it to. // connection.tag will always be the requested tag "DF=YYYY-MM-DD" await conn.close(); }

The PL/SQL callback declaration is:

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

When DRCP connections are being used, a PL/SQL callback procedure avoids the round-trips of a Node.js callback function. Without DRCP, it requires just one round-trip whereas a complex (or badly coded) Node.js function could require lots. An example procedure is shown in the documentation.

Since DRCP sessions are a database-server resource that can be shared between a number of applications, multi-property tagging can be particularly useful to let your callback procedure decide which parts of the session state are relevant for your application, and which parts can be ignored.

Dropping Pooled Connections

Sometimes, instead of releasing a connection back to a pool for reuse, you just want to get rid of it. Perhaps you have set some special session state and it's easier to drop the connection now so that the sessionCallback function can reset the normal state when the connection is recreated. In node-oracledb 3.1 you can now force connections to be dropped from the pool:

await connection.close({drop: true});

Remember that normally you don't want to do this, because it means a new connection will have to be created and initialized the next time one is needed.

For non-pooled connections, using a simple connection.close() continues to completely terminate a connection.

Summary

Overall, the node-oracledb 3.1 release brings some welcome usability and performance benefits giving you greater control over your applications in dynamic environments.

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.

ODPI-C 3.1 is Available

Mon, 2019-01-21 16:31

Release 3.1 of Oracle Database Programming Interface for C (ODPI-C) is now available on GitHub

ODPI-C is an open source library of C code that simplifies access to Oracle Database for applications written in C or C++.

Top features: Multi-property Session Tags

The ODPI-C 3.1 release introduces some small enhancements and corrects a number of small issues that were discovered over the past few months. The main change is support for Oracle Call Interface 12.2's multi-property session tagging, allowing connections in a session pool to be assigned a semi-arbitrary string tag that you can use to represent the session state (e.g. ALTER SESSION values) of each connection. With multi-property tagging you can assign a PL/SQL procedure to 'fix-up' the session state, if necessary, before a connection is returned to the application from the pool. This is an efficient way to make sure connections have a required state.

See the release notes for all the changes.

ODPI-C References

Home page: https://oracle.github.io/odpi/

Code: https://github.com/oracle/odpi

Documentation: https://oracle.github.io/odpi/doc/index.html

Release Notes: https://oracle.github.io/odpi/doc/releasenotes.html

Installation Instructions: oracle.github.io/odpi/doc/installation.html

Report issues and discuss: https://github.com/oracle/odpi/issues

Node-oracledb Connection Samples: Proxies and External Authentication

Mon, 2018-12-17 22:19

We recently reviewed 'external authentication' and 'proxy connections' support in node-oracledb and did a few tweaks that will appear in the future node-oracledb 3.1.

You can use 'external authentication' to connect without requiring a password being stored in your Node.js applications. This is useful, for example, to authenticate via LDAP or use an Oracle Wallet.

The idea of a proxy connection is to create a schema in one database user name. Privilege is granted on that schema to other database users so they can access the schema and manipulate its data. This aids three-tier applications where one user owns the schema while multiple end-users access the data.

You can use external authentication and proxy connections together or separately.

In the 'so I can find it again' category, here are some behaviors of node-oracledb connections.

For ease of testing, my external authentication was via Operating System authentication using an 'OPS$' schema. Since my database was remote to the node-oracledb application I used the INSECURE setting 'alter system set remote_os_authent=true scope=spfile;'. Do not do use this in real life!

SQL:

create user mynormaluser identified by mynormaluserpw; grant create session to mynormaluser; create user myproxyuser identified by myproxyuserpw; grant create session to myproxyuser; create user mysessionuser1 identified by doesnotmatter; grant create session to mysessionuser1; alter user mysessionuser1 grant connect through myproxyuser; -- I logged into my computer as the 'oracle' OS user: create user ops$oracle identified externally; grant connect, resource to ops$oracle; alter user ops$oracle default tablespace users; alter user mysessionuser2 grant connect through ops$oracle;

JavaScript:

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"); // Gives: // Proxy User: null // Session User: MYNORMALUSER try { config = { connectString: "localhost/orclpdb", user: "mynormaluser", password: "mynormaluserpw" }; conn = await oracledb.getConnection(config); await ShowUserInfo(conn); } catch (e) { console.log(e.message + "\n"); } console.log("(2) Standalone: External Auth"); // Gives: // Proxy User: null // Session User: OPS$ORACLE try { config = { connectString: "localhost/orclpdb", 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"); // Gives: // Proxy User: MYPROXYUSER // Session User: MYSESSIONUSER1 try { config = { connectString: "localhost/orclpdb", user: "myproxyuser[mysessionuser1]", password: "myproxyuserpw" }; conn = await oracledb.getConnection(config); await ShowUserInfo(conn); } catch (e) { console.log(e.message + "\n"); } console.log("(4) Standalone: External Auth with proxy in brackets"); // With node-oracledb 3.0 gives: // DPI-1032: user name and password cannot be set when using external authentication // With node-oracledb 3.1 gives: // Proxy User: OPS$ORACLE // Session User: MYSESSIONUSER2 try { config = { connectString: "localhost/orclpdb", 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"); // With node-oracledb 3.0 gives: // DPI-1032: user name and password cannot be set when using external authentication // With node-oracledb 3.1 gives: // DPI-1069: proxy user name must be enclosed in [] when using external authentication try { config = { connectString: "localhost/orclpdb", 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"); // Gives: // Proxy User: null // Session User: MYNORMALUSER try { config = { connectString: "localhost/orclpdb", user: "mynormaluser", password: "mynormaluserpw" }; 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"); // Gives: // Proxy User: null // Session User: OPS$ORACLE try { config = { connectString: "localhost/orclpdb", 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 pool creation"); // Gives: // Proxy User: MYPROXYUSER // Session User: MYSESSIONUSER1 try { config = { connectString: "localhost/orclpdb", user: "myproxyuser[mysessionuser1]", password: "myproxyuserpw" }; 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 connection"); // Gives: // Proxy User: MYPROXYUSER // Session User: MYSESSIONUSER1 try { config = { connectString: "localhost/orclpdb", user: "myproxyuser", password: "myproxyuserpw", 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 brackets in connection"); // Gives: // ORA-00987 missing or invalid username(s) try { config = { connectString: "localhost/orclpdb", user: "myproxyuser", password: "myproxyuserpw", 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 brackets in pool creation"); // Gives: // DPI-1032 user name and password cannot be set when using external authentication try { config = { connectString: "localhost/orclpdb", 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 in pool creation"); // Gives: // DPI-1032: user name and password cannot be set when using external authentication try { config = { connectString: "localhost/orclpdb", 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 brackets in connection"); // Using Oracle 12.2 client libraries gives // Proxy User: null // Session User: OPS$ORACLE // Using Oracle 18.3 client libraries gives // Proxy User: OPS$ORACLE // Session User: MYSESSIONUSER2 // This was an enhancement in Oracle 18.3 try { config = { connectString: "localhost/orclpdb", 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 in connection"); // With node-oracledb 3.0 gives: // Proxy User: null // Session User: OPS$ORACLE // With node-oracledb 3.1 gives: // DPI-1069: proxy user name must be enclosed in [] when using external authentication // This change in node-oracledb 3.1 prevents connecting with an unexpected session user try { config = { connectString: "localhost/orclpdb", externalAuth: true }; pool = await oracledb.createPool(config); conn = await pool.getConnection({ user: "mysessionuser2" }); await ShowUserInfo(conn); } catch (e) { console.log(e.message + "\n"); } })();

Summary:

  • (4) "Standalone: External Auth with proxy": Fixed in node-oracledb 3.1

  • (5) "Standalone: External Auth with proxy no brackets": Error message was improved in node-oracledb 3.1

  • (13) "Pooled: External Auth with proxy in brackets in connection": Works with Oracle Client 18.3+

  • (14) "Pooled: External Auth with proxy in connection": In node-oracledb 3.1 connection fails due to improved validation. In node-oracledb 3.0 connection succeeded despite the options being inconsistent.

We've had some discussions about the use of "[]" brackets - which is a standard Oracle syntax passed through to the Oracle client libraries. We could have done some manipulation in node-oracledb for consistency, but we decided not to make node-oracledb behave differently than other Oracle language interfaces.

Historical Links for Oracle Net Services aka SQL*Net

Wed, 2018-12-12 00:06

Sometime you just have to do a cleanup but it seems a waste not to keep information that may still be valuable for the historical record. This is one such time. Here are some of the older resources about Oracle Net Services, aka SQL*Net taken from a web page that is about to be revamped.

Of course, if you want current information, visit the Oracle Net Service page, the Net Services Net Services Reference, or the latest Net Services Adminstrator's Guide.

Overview

Oracle Net Services provides a scalable, secure, and easy-to-use high-availability network infrastructure for Oracle environment. It eases the complexities of network configuration and management, maximizes performance, and improves network security and diagnostic capabilities as summarized hereafter.

  • Connectivity: Oracle Net Services enables a network session from a client application to an Oracle database server. Once a network session is established, Oracle Net acts as the data courier for both the client application and the database server. It is responsible for establishing and maintaining the connection between the client application and database server, as well as exchanging messages between them.
  • Manageability: it encompasses location transparency, centralized configuration and management, quick installation and configuration. Location Transparency service allows a database client to identify the target database server; to achieve this goal, several naming methods are available today: Oracle Net Directory naming, Local naming (TNSNAMES.ORA), Host naming, and External naming.
  • Centralized Configuration and Management: allows administrators, in large network environments, to easily access a centralized repository (i.e., LDAP-compliant directory server such as Oracle Internet Directory) to specify and modify the network configuration.
  • Quick Installation and Configuration: networking components for the Oracle database server and clients are preconfigured for most environments. The Oracle database service is resolved using various naming methods. As a result, clients and servers are ready to immediately connect when installed.
  • Performance and Scalability: features such as Database Resident Connection Pool (connection pooling), Shared Server (session multiplexing), and scalable event models (polling) enable performance and high scalability.
  • Network Security: Oracle Net Services enables database access control using features of firewall access control and protocol access control.
  • Diagnosability: a diagnostic and performance analysis tool, Trace Assistant, provides detailed information about the source and context of problems as they arise.
What's New in Oracle Database 12c

The new features for Net Services in Oracle Database 12c include:

  • Internet Protocol Version 6 (IPv6) Addressing and Connectivity support over Public VIPs to Real Application Clusters (please refer the IPv6 Statement of Direction below for more information)
  • Networking support for new architectures, such as, Database Consolidation and Global Data Services
  • Large Session Data Unit (SDU) sizes, with a new upper limit of 2 MB. The larger SDU size can be used to achieve better utilization of available bandwidth in networks that have high bandwidth delay products and host resources, according to application characteristics.
  • Advanced Network Compression. This feature can be used to reduce the amount of data transmitted over the network. New parameters for the sqlnet.ora file enable compression and selection of the preferred compression level. Please refer the Advanced Network Compression white paper below for more information.
  • Dead Connection Detection has been enhanced to reduce the amount of time taken to detect terminated connections. The SQLNET.EXPIRE_TIME parameter must be set in the sqlnet.ora file to detect terminated clients. If the system supports TCP keepalive tuning, then Oracle Net Services automatically uses the enhanced detection model, and tunes the TCP keepalive parameters at a per-connection level.
  • Intelligent Client Connection establishment feature reduces the priority of connection attempts to unresponsive nodes in the address string of connect descriptors. No configuration changes are required to use this feature.
  • Incident Generation for Process Failures for the following Oracle Net Services components has been added:
    • Oracle Net Listener
    • Listener control utility (LSNRCTL)
    • Connection Manager Administration (CMADMIN)
    • Oracle Connection Manager gateway (CMGW)
What's New in Oracle Database 11gR1 & 11gR2

The new features for Net Services in Oracle Database 11g Release 2 include:

  • Internet Protocol Version 6 (IPv6) Addressing and Connectivity for single-instance database (please refer the IPv6 Statement of Direction below for more information)
  • Oracle Restart in single instance environment for restarting the listener, the database and other Oracle components following hardware/software failure or host restart.
  • Support for CONNECT_TIMEOUT and TRANSPORT_CONNECT_TIMEOUT for each IP address
  • CIDR notation and wildcard support for valid node checking
  • The new features for Net Services in Oracle Database 11g Release 1 include:
    • Support for authenticated LDAP access for Net Naming
    • Performance Improvements
    • Fastpath for common usage scenarios, enforced transparently (no configuration required)
    • Efficient network support for bulk/large data transfers (such as, SecureFile LOBs)
    • Efficient event dispatch mechanisms for PHP and process-based systems (enforced transparently, no config. required)
    • Fast Reconnects for High Availability: efficient detection of terminated instances and conect time failover
    • Support for Database Resident Connection Pool
    • Enhancements to the Easy Connect Naming method

For more details see the Oracle Database Net Services Administrator's Guide

Technical Information

Oracle OpenWorld

Oracle Database 12c

Oracle Database 11gR2

Oracle Database 11gR1

Oracle Database 10g

Oracle Database 9i

ODPI-C: A Light Weight Driver for Oracle Database

Tue, 2018-12-11 18:52

This is a guest post by Oracle’s Vaddeti Karthik Raju, a contributor to the ODPI-C project.

What is ODPI-C ?

Oracle Database Programming Interface for C (ODPI-C) is an open source library of C code that simplifies the use of common Oracle Call Interface (OCI) features for Oracle Database drivers and user applications which are written in C or C++. It is a wrapper over Oracle Call Interface (OCI) that makes applications and language interfaces easier to develop.

For complete details of ODPI-C, visit the homepage. Here you can find a list of all its powerful features, links to the source code, the documentation, and some samples.

Why ODPI-C ?

Since ODPI-C is a wrapper on top of OCI and exposes common OCI functionality, why use ODPI-C? Though OCI is highly efficient and flexible, it requires a lot of code and technical skill to use well. ODPI-C reduces the amount of code and the skill level required, which is particularly useful for language driver creators. Thus it reduces the amount of time required for a developer to implement new Oracle features in a particular language or application.

User applications and language drivers have been written in ODPI-C. Drivers currently using ODPI-C include:

Oracle Drivers

  • cx_Oracle Python interface

  • node-oracledb Node.js module

Third-party Drivers

  • go-goracle Go Driver

  • mirmir Rust Bindings

  • odpic-raw Haskell Raw Bindings

  • ruby-ODPI Ruby Interface

  • rust-oracle Driver for Rust

Common setup

Let us go through few example programs which demonstrates how to perform operations in ODPI-C.

For installation of ODPI-C, visit the installation instructions. In summary, the supplied Makefile or Makefile.win32 can be used to build a shared library, which can be linked with samples and tests. Once the library has been built, locate the directory it is in and add the directory name to your system library search path, e.g. PATH on Windows or LD_LIBRARY_PATH on Linux. You'll also need some Oracle client libraries, such as from the Oracle Instant Client "Basic" package.

Before going further, create the database objects using the below code. Here I am using database credentials scott/tiger@localhost/orclpdb:

$ sqlplus scott/tiger@localhost/orclpdb create table TestTable ( IntCol number(9) not null, StringCol varchar2(100) );

Include the below headers, defines and printError( ) function for all the programs shown in this blog post: this code is common for all programs. Make sure that you connect to the same schema where you created the table.

#include <dpi.h> #include <stdlib.h> #include <stdio.h> #include <string.h> #define USER "scott" #define PASSWORD "tiger" #define CONNECT_STRING "localhost/orclpdb" static dpiContext *gContext = NULL; static dpiErrorInfo gErrorInfo; //----------------------------------------------------------------------------- // printError() // Prints the error message. The error is first fetched // from the global DPI context. //----------------------------------------------------------------------------- int printError(void) { if (gContext) dpiContext_getError(gContext, &gErrorInfo); fprintf(stderr, " [FAILED]\n"); fprintf(stderr, " FN: %s\n", gErrorInfo.fnName); fprintf(stderr, " ACTION: %s\n", gErrorInfo.action); fprintf(stderr, " MSG: %.*s\n", gErrorInfo.messageLength, gErrorInfo.message); fflush(stderr); return DPI_FAILURE; } Inserting

The program insert.c shows how to perform simple insert operation that adds couple of records to the table.

// insert.c int main() { const char *insertSql = "insert into TestTable values (:1, :2)"; dpiData intColValue, stringColValue; dpiConn *conn; dpiStmt *stmt; // create context if (dpiContext_create(DPI_MAJOR_VERSION, DPI_MINOR_VERSION, &gContext, &gErrorInfo) < 0) return printError(); // create connection if (dpiConn_create(gContext, USER, strlen(USER), PASSWORD, strlen(PASSWORD), CONNECT_STRING, strlen(CONNECT_STRING), NULL, NULL, &conn) < 0) return printError(); // prepare insert statement for execution if (dpiConn_prepareStmt(conn, 0, insertSql, strlen(insertSql), NULL, 0, &stmt) < 0) return printError(); // create first row dpiData_setInt64(&intColValue, 1); if (dpiStmt_bindValueByPos(stmt, 1, DPI_NATIVE_TYPE_INT64, &intColValue) < 0) return printError(); dpiData_setBytes(&stringColValue, "Test data 1", strlen("Test data 1")); if (dpiStmt_bindValueByPos(stmt, 2, DPI_NATIVE_TYPE_BYTES, &stringColValue) < 0) return printError(); if (dpiStmt_execute(stmt, DPI_MODE_EXEC_DEFAULT, NULL) < 0) return printError(); // create second row dpiData_setInt64(&intColValue, 2); if (dpiStmt_bindValueByPos(stmt, 1, DPI_NATIVE_TYPE_INT64, &intColValue) < 0) return printError(); dpiData_setBytes(&stringColValue, "Test data 2", strlen("Test data 2")); if (dpiStmt_bindValueByPos(stmt, 2, DPI_NATIVE_TYPE_BYTES, &stringColValue) < 0) return printError(); if (dpiStmt_execute(stmt, DPI_MODE_EXEC_DEFAULT, NULL) < 0) return printError(); // commit changes if (dpiConn_commit(conn) < 0) return printError(); // cleanup dpiStmt_release(stmt); dpiConn_release(conn); return DPI_SUCCESS; }

Remember to add the headers, defines and printError() to insert.c before compiling it. I recommend using the Makefile or Makefile.win32 from the samples directory to help you compile.

Let us walk through each function of the code:

  1. Create the ODPI-C context which is the first function that must be called in all ODPI-C applications (dpiContext_create()).

  2. Create a standalone connection to the database (dpiConn_create()).

  3. Prepare a statement for execution (dpiConn_prepareStmt()).

  4. Populate data in structures and bind it to the statement (dpiData_setInt64(), dpiData_setBytes(), dpiStmt_bindValueByPos()).

  5. Execute the statement (dpiStmt_execute()).

  6. Repeat steps 4 and 5 for the second row.

  7. Commit the data (dpiConn_commit()).

  8. Clean up (dpiStmt_release(), dpiConn_release()).

Inserting multiple records

In insert.c we saw how to insert single record at a time. If you want to insert multiple records into a table you can use the dpiStmt_executeMany() function. In insertmany.c we will add multiple records to the table more efficiently by using an array bind operation.

// insertmany.c int main() { const char *insertSql = "insert into TestTable values (:1, :2)"; dpiData *intData, *strData; uint32_t numRows = 5, i; dpiVar *intVar, *strVar; char buffer[100]; dpiConn *conn; dpiStmt *stmt; // create context if (dpiContext_create(DPI_MAJOR_VERSION, DPI_MINOR_VERSION, &gContext, &gErrorInfo) < 0) return printError(); // create connection if (dpiConn_create(gContext, USER, strlen(USER), PASSWORD, strlen(PASSWORD), CONNECT_STRING, strlen(CONNECT_STRING), NULL, NULL, &conn) < 0) return printError(); // prepare and bind insert statement if (dpiConn_prepareStmt(conn, 0, insertSql, strlen(insertSql), NULL, 0, &stmt) < 0) return printError(); if (dpiConn_newVar(conn, DPI_ORACLE_TYPE_NUMBER, DPI_NATIVE_TYPE_INT64, numRows, 0, 0, 0, NULL, &intVar, &intData) < 0) return printError(); if (dpiStmt_bindByPos(stmt, 1, intVar) < 0) return printError(); if (dpiConn_newVar(conn, DPI_ORACLE_TYPE_VARCHAR, DPI_NATIVE_TYPE_BYTES, numRows, 100, 1, 0, NULL, &strVar, &strData) < 0) return printError(); if (dpiStmt_bindByPos(stmt, 2, strVar) < 0) return printError(); // populate some dummy data for (i = 0; i < numRows; i++) { dpiData_setInt64(&intData[i], i + 1); sprintf(buffer, "Test data %d", i + 1); if (dpiVar_setFromBytes(strVar, i, buffer, strlen(buffer)) < 0) return printError(); } // perform execute many if (dpiStmt_executeMany(stmt, DPI_MODE_EXEC_DEFAULT, numRows) < 0) return printError(); // commit changes if (dpiConn_commit(conn) < 0) return printError(); // cleanup if (dpiVar_release(intVar) < 0) return printError(); if (dpiVar_release(strVar) < 0) return printError(); dpiStmt_release(stmt); dpiConn_release(conn); return DPI_SUCCESS; }

Let us walk through each function of the code:

  1. Create ODPI-C context which is the first function that must be called in all ODPI-C applications (dpiContext_create()).

  2. Create standalone connection to the database (dpiConn_create()).

  3. Prepare statement for execution (dpiConn_prepareStmt()).

  4. Create variables and bind them to the statement by position. (dpiConn_newVar(), dpiStmt_bindByPos()).

  5. Populate data into bound variables (dpiData_setInt64(), dpiVar_setFromBytes()).

  6. Execute the statement by specifying the number of times (dpiStmt_executeMany()).

  7. Commit the data (dpiConn_commit()).

  8. Clean up (dpiVar_release(), dpiStmt_release(), dpiConn_release()).

Fetching

So far we have seen how to insert data into a table. In the following program we will see how to fetch records from the table. In order to make sure some records exist in the table run insertmany before running fetch.

// fetch.c int main() { const char *selectSql = "select IntCol, StringCol from TestTable"; dpiData *intColValue, *stringColValue; dpiNativeTypeNum nativeTypeNum; uint32_t bufferRowIndex; dpiConn *conn; dpiStmt *stmt; int found; // create context if (dpiContext_create(DPI_MAJOR_VERSION, DPI_MINOR_VERSION, &gContext, &gErrorInfo) < 0) return printError(); // create connection if (dpiConn_create(gContext, USER, strlen(USER), PASSWORD, strlen(PASSWORD), CONNECT_STRING, strlen(CONNECT_STRING), NULL, NULL, &conn) < 0) return printError(); // prepare select statement if (dpiConn_prepareStmt(conn, 0, selectSql, strlen(selectSql), NULL, 0, &stmt) < 0) return printError(); if (dpiStmt_execute(stmt, DPI_MODE_EXEC_DEFAULT, NULL) < 0) return printError(); // fetch rows while (1) { if (dpiStmt_fetch(stmt, &found, &bufferRowIndex) < 0) return printError(); if (!found) break; if (dpiStmt_getQueryValue(stmt, 1, &nativeTypeNum, &intColValue) < 0) return printError(); if (dpiStmt_getQueryValue(stmt, 2, &nativeTypeNum, &stringColValue) < 0) return printError(); printf("Int = %ld String = '%.*s'\n", intColValue->value.asInt64, stringColValue->value.asBytes.length, stringColValue->value.asBytes.ptr); } // cleanup dpiStmt_release(stmt); dpiConn_release(conn); return DPI_SUCCESS; }

When I run the program, the output is:

Int = 1 String = 'Test data 1' Int = 2 String = 'Test data 2' Int = 3 String = 'Test data 3' Int = 4 String = 'Test data 4' Int = 5 String = 'Test data 5'

Let us walk through each function of the code:

  1. Create ODPI-C context which is the first function that must be called in all ODPI-C applications (dpiContext_create()).

  2. Create standalone connection to the database (dpiConn_create()).

  3. Prepare statement for execution (dpiConn_prepareStmt()).

  4. Execute the statement (dpiStmt_execute()).

  5. Fetch the row and get column values (dpiStmt_fetch(), dpiStmt_getQueryValue()).

  6. Clean up (dpiStmt_release(), dpiConn_release()).

Scrolling

In the above example rows are retrieved in order from the statement until the rows are exhausted.

Now let us see an example on how to scroll between rows using the dpiStmt_scroll() function. To make sure some records exist in the table, run insertmany before running this program.

// scroll.c int main() { const char *sql = "select IntCol from TestTable order by IntCol"; dpiNativeTypeNum nativeTypeNum; uint32_t numQueryColumns; uint32_t bufferRowIndex; dpiData *data; dpiStmt *stmt; dpiConn *conn; int found; // create context if (dpiContext_create(DPI_MAJOR_VERSION, DPI_MINOR_VERSION, &gContext, &gErrorInfo) < 0) return printError(); // create connection if (dpiConn_create(gContext, USER, strlen(USER), PASSWORD, strlen(PASSWORD), CONNECT_STRING, strlen(CONNECT_STRING), NULL, NULL, &conn) < 0) return printError(); // prepare select statement if (dpiConn_prepareStmt(conn, 1, sql, strlen(sql), NULL, 0, &stmt) < 0) return printError(); // number of rows fetched from the database if (dpiStmt_setFetchArraySize(stmt, 5) < 0) return printError(); if (dpiStmt_execute(stmt, DPI_MODE_EXEC_DEFAULT, &numQueryColumns) < 0) return printError(); // set the relative position to scroll if (dpiStmt_scroll(stmt, DPI_MODE_FETCH_RELATIVE, 4, 0) < 0) return printError(); // fetch the row if (dpiStmt_fetch(stmt, &found, &bufferRowIndex) < 0) return printError(); if (dpiStmt_getQueryValue(stmt, 1, &nativeTypeNum, &data) < 0) return printError(); printf("\nIntCol = %ld\n", data->value.asInt64); // cleanup dpiStmt_release(stmt); dpiConn_release(conn); return DPI_SUCCESS; }

When I run scroll the output is:

IntCol = 5

Let us walk through each function of the code:

  1. Create ODPI-C context which is the first function that must be called in all ODPI-C applications (dpiContext_create()).

  2. Create standalone connection to the database (dpiConn_create()).

  3. Prepare statement for execution (dpiConn_prepareStmt()).

  4. Set the fetch array size (dpiStmt_setFetchArraySize()).

  5. Execute the statement (dpiStmt_execute()).

  6. Scroll the statement to the specified position (dpiStmt_scroll()).

  7. Fetch the row and get column value (dpiStmt_fetch(), dpiStmt_getQueryValue()).

  8. Clean up (dpiStmt_release(), dpiConn_release()).

Wrap up

ODPI-C has a number of advanced features letting you create powerful applications.

For more ODPI-C tests and samples visit the tests and samples directories.

For complete documentation details visit the ODPI-C documentation page.

If you have questions, feel free to contact us on GitHub.

Vaddeti Karthik Raju is a Senior Member Technical Staff for Oracle Bengaluru. He contributes to a number of projects, including ODPI-C.

PHP OCI8 2.2.0 has database call timeouts

Mon, 2018-12-10 22:55

Hot on the heels of PHP 7.3 being released, I've just pushed PHP OCI8 2.2.0 to PECL.

This release will install on PHP 7.0 to 7.3

One new feature available when OCI8 is compiled with Oracle Client 18c libraries is a database call timeout. A new oci_set_call_timeout() function is available. This will interrupt long running calls to the database, allowing better control over application behavior.

Install with 'pecl install oci8'. Note that currently the Windows builds are not using 18c, so do not have oci_set_call_timeout(). (Yet another reason to move to Linux!)

The OCI8 2.2 code line will also be included in future PHP7.2.14 and PHP 7.3.1 full software releases.

Looking for documentation? As well as php.net/oci8, there is still a lot of useful information in The Underground PHP and Oracle Manual.

"Python and Oracle Database on the Table" Conference Recording

Sun, 2018-10-28 10:20

Anthony Tuininga's recent Oracle Code One conference session on Python cx_Oracle best practices was recorded and is available on YouTube:

The slides are here.

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

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

Oracle Instant Client-based Middle Tiers Such As Docker Without a Click-through

Sun, 2018-10-28 05:13

The Oracle Linux group is pushing forward hard and their latest news is that Instant Client RPMs for Oracle Linux are downloadable for Oracle Linux 7 and Oracle Linux 6 without needing a click-through. This means you can now more easily provsion machines using an automated install process. Sergio's blog post has the announcement and steps to use the packages.

Our Instant Client 18.3 Dockerfile has also been updated. As you'd expect, 'docker build' now does the download and install of Instant Client. Since 18.3 is effectively the renamed 12.2.0.2, and you can connect to Oracle Database 11.2 or later (the same as with the Instant Client 12.2), using the new Dockerfile is an obvious win.

What are you doing at Oracle OpenWorld and Code One 2018?

Tue, 2018-10-09 02:11

The time for Oracle OpenWorld and CodeOne conferences is fast approaching.  These two conferences run concurrently in San Francisco over October 22 - 25.  If you are flying in, you will have already sorted out your conference tickets, but if you are local there are options from the free Discover pass (search the page for 'Discover') on upwards that you should take advantage of.

There are plenty of things to attend and do - you can get your hands dirty in any number of ways! There are so many sessions running that you need to keep a clear head.  If you want to see sessions that are related to the areas this blog covers check out our 'Focus On' document.  There is a short URL: https://tinyurl.com/AppDevOOW18 which will also take you there.  This document has the abstracts and will be up to date if there are any changes, but for ease of reference here is the current list of wonderful events:

HOL (Hands-on Lab) Sessions

Python and Oracle Database: Scripting for the Future - BYOL [HOL5052]
Monday, Oct 22, 9:00 a.m. - 11:00 a.m. | Moscone West - Overlook 2A (HOL)

Python and Oracle Database 18c: Scripting for the Future [HOL6329]
Tuesday, Oct 23, 2:15 p.m. - 3:15 p.m. | Marriott Marquis (Yerba Buena Level) - Salon 3/4

Developer Sessions

Getting Started with R and ROracle for Data Science with Oracle Database [DEV5049]
Monday, Oct 22, 11:30 a.m. - 12:15 p.m. | Moscone West - Room 2016

The Go Language: Principles and Practices for Oracle Database [DEV5047]
Monday, Oct 22, 12:30 p.m. - 1:15 p.m. | Moscone West - Room 2001

How to Build Geospatial Analytics with Python and Oracle Database [DEV5185]
Monday, Oct 22, 1:30 p.m. - 2:15 p.m. | Moscone West - Room 2003

How Does Ruby on Rails Work with MySQL, Oracle Database, and Other Databases [DEV4948]
Monday, Oct 22, 2:30 p.m. - 3:15 p.m. | Moscone West - Room 2014

Getting Started with GraphQL APIs on Oracle Database with Node.js [DEV4879]
Tuesday, Oct 23, 11:30 a.m. - 12:15 p.m. | Moscone West - Room 2012

Product Training Sessions

Oracle Net Services: Best Practices for Database Performance and High Availability [TRN4073]
Monday, Oct 22, 3:45 p.m. - 4:30 p.m. | Moscone West - Room 3009

A Database Proxy for Transparent High Availability, Performance, Routing, and Security [TRN4070]
Wednesday, Oct 24, 11:15 a.m. - 12:00 p.m. | Moscone West - Room 3009

Application High Availability Best Practices and New Features [TRN4078]
Thursday, Oct 25, 10:00 a.m. - 10:45 a.m. | Moscone West - Room 3009

Using Location in Cloud Applications with Python, Node.js, and More [TRN4089]
Thursday, Oct 25, 11:00 a.m. - 11:45 a.m. | Moscone West - Room 3001

Building Real-Time Data in Web Applications with Node.js [TRN4081]
Thursday, Oct 25, 1:00 p.m. - 1:45 p.m. | Moscone West - Room 3009

IoT for Oracle Database: Soldering, Python, and a Little PL/SQL [TRN4077]
Thursday, Oct 25, 2:00 p.m. - 2:45 p.m. | Moscone West - Room 3009

Tips and Tricks Sessions

Python and Oracle Database on the Table [TIP4076]
Tuesday, Oct 23, 12:30 p.m. - 1:15 p.m. | Moscone West - Room 3009

Node.js: Async Data In and Data Out with Oracle Database [TIP4080]
Thursday, Oct 25, 11:00 a.m. - 11:45 a.m. | Moscone West - Room 3009

Performance and Scalability Techniques for Oracle Database Applications [TIP4075]
Thursday, Oct 25, 12:00 p.m. - 12:45 p.m. | Moscone West - Room 3009
Meet the Experts

Node.js, Python, PHP, and Go with Oracle Database [MTE6765]
Wednesday, Oct 24, 3:00 p.m. - 3:50 p.m. | Moscone West - The Hub - Lounge B

Demos

We'll also have a demo booth in one of the demo halls:

Python, Node.js, Go, C, and C++ Application Development for Oracle Database [APD-A03]

Other Happenings

Some other 'Focus On' Documents from my wider group are:

 

node-oracledb 3.0 Introduces SODA Document Storage

Mon, 2018-10-01 17:11

node-oracledb icon

 

 

Release announcement: Node-oracledb 3.0.0, the Node.js module for accessing Oracle Database, is on npm.

Top features: Simple Oracle Document Access (SODA) preview, Connection Pool draining, Call timeouts.

 

Node-oracledb 3 has been released, which is very exciting. This release adds support for some Oracle Database and Oracle Client 18c features.

As with earlier versions, node-oracledb 3 is usable with Oracle Client libraries 11.2 onwards. This allows it to connect to Oracle Database 9.2 or later, depending on the client version. But only when using Oracle Client 18.3 libraries and connected to Oracle Database 18.3 will you get the latest and greatest set of Oracle features, which we are pleased to bring you.

Here are the highlights of thise node-oracledb 3.0 release:

  • Oracle Simple Document Access (SODA) support - an exciting addition to the standard relational access model is the new set of APIs for Oracle SODA. See the section lower down for examples.

  • A new drainTime argument to pool.close() allows pools to be force-closed after a specified number of seconds. This feature was a contribution from Danilo Silva. Thanks Danilo!

    When a pool is closed with a given drainTime, any subsequent getConnection() calls will fail, but connections currently in use will continue working. This allows code to complete and outstanding transactions to be committed. When no connections are in use, or at the end of the drain time (whichever occurs first), the pool and all its connections will be forcibly closed.

    Although the words 'forcibly closed' seem harsh, the drain time actually allows more graceful shutdown of applications, so that users will get clear messages that the pool is closing (or has closed), and letting the database-side sessions be cleanly freed without waiting for them to timeout. The drainTime can also be zero, forcing the immediate close of all sessions - this is handy when you want to kill an app but be nice to the database.

  • Installation of the pre-built node-oracledb binaries got a bit easier with basic proxy authentication support. Also the 'npm config' proxy value is now used when installing if there are no proxy environment variables set. These changes were a contribution from Cemre Mengu. Thank you Cemre!

  • Added a connection.callTimeout property to interrupt long running database calls. This is available when node-oracledb 3 is using Oracle Client libraries version 18.1, or later, regardless of Oracle Database version.

    The call timeout is a useful backstop to prevent out-of-control SQL and PL/SQL statement execution. For background, the main code layer beneath node-oracledb's implementation is Oracle Call Interface. This API handles all the network connectivity to Oracle Database. For each OCI function executed by node-oracledb, zero or more 'round-trips' to the database can occur - calling the database and getting a response back.

    The callTimeout value applies to each round-trip individually, not to the sum of all round-trips. Time spent processing in node-oracledb before or after the completion of each round-trip is not counted.

    • If the time from the start of any one round-trip to the completion of that same round-trip exceeds callTimeout milliseconds, then the operation is halted and an error is returned.

    • In the case where a node-oracledb operation requires more than one round-trip and each round-trip takes less than callTimeout milliseconds, then no timeout will occur, even if the sum of all round-trip calls exceeds callTimeout.

    • If no round-trip is required, the operation will never be interrupted.

    When callTimeout is exceeded, node-oracledb attempts to clean up the internal connection state. The cleanup is allowed to take another callTimeout milliseconds.

    If the cleanup was successful, a "DPI-1067: call timeout of N ms exceeded with ORA-XXX" error will be returned and the application can continue to use the connection.

    For small values of callTimeout, the connection cleanup may not complete successfully within the additional callTimeout period. In this case an ORA-3114 is returned and the connection will no longer be usable. It should be closed.

  • On Windows, node-oracledb will now attempt to load the Oracle Client libraries from the 'node_modules\oracledb\build\Release' directory before doing the standard Windows library directory search i.e. of the PATH directories.

    This new feature could be useful if you are bundling up applications on Windows and want to include the Oracle Instant Client. By putting the client libraries in the 'node_modules\oracledb\build\Release' directory there is no need to set PATH, no need to worry about users changing PATH, and no need to worry about having multiple versions of Oracle Client libraries in PATH.

  • poolPingInterval functionality has been 're-enabled' when using the connection pool with Oracle Client libraries 12.2, or later. Previously it was deliberately not enabled with these versions, since an internal check in those Oracle clients is very efficient for seeing if the network has dropped out. However users were unhappy that the internal check does not identify connections that are unusable because they have exceeded database session resource limits (e.g. return ORA-02396 when used), or have been explicitly closed by DBAs (e.g. return ORA-00028). This is where poolPingInterval helps.

    This change can make your apps seem more highly available but there is a drawback: your apps may be silently reconnecting more than is optimal, and you might not be aware of connection storms if a large pool needs to be re-established. You should monitor AWR reports to see if connections are occurring too frequently, and then work with your network and DBA administrators to prevent idle sessions being killed.

These are just the highlights. For other changes and improvements see the CHANGELOG. But read on to hear more about SODA . . . .

Simple Oracle Document Access (SODA) in node-oracledb

Oracle Simple Document Access (SODA) is a set of NoSQL-style APIs that let you create and store collections of documents (in particular JSON) in Oracle Database, retrieve them, and query them, without needing to know SQL. SODA support is now available in node-oracledb 3 when using Oracle Database 18.3 and Oracle Client 18.3, or higher. SODA APIs are also available for Python, C, Java, PL/SQL and via REST, so it is widely accessible and bound to be a useful tool in your data toolkit. We are currently labelling node-oracledb 3 SODA APIs as a 'preview' but, with a future version of the Oracle Client libraries, this will change.

The class diagram of node-oracledb shows the separation of the relational and SODA worlds:

node-oracledb class overview diagram

In reality, SODA is backed by Oracle Database tables, providing a well known, secure, and efficient storage solution. You could access those tables via SQL but this would rarely be needed, perhaps for some advanced Oracle Database functionality such as analytics for reporting.

Instead you will almost certainly just use the new classes and methods. Once a DBA has granted you the SODA_APP privilege, you can simply create collections and store documents (e.g JSON) in them. Some basic examples are:

// Create the parent object for SODA. soda = await connection.getSodaDatabase(); // Create a new SODA collection, if it doesn't exist. // 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); key = doc.key; console.log("The key of the new SODA document is: ", key);

You can then get documents back via a key look up, or by a search. A key lookup is straightforward:

// Fetch the document back doc = await collection.find().key(key).getOne(); // A SodaDocument content = doc.getContent(); // A JavaScript object console.log('Retrieved SODA document as an object:'); console.log(content);

For documents that can be converted to JSON you can alternatively get them as a string:

content = doc.getContentAsString(); // A JSON string console.log('Retrieved SODA document as a string:'); console.log(content);

The find() method is an operation builder, with methods that allow progressively limiting criteria to be set, reducing the set of documents that are then operated on by a terminal method such as getOne(), getDocuments() or count(), amongst others.

With JSON documents, a complete filtering specification language can be used for query-by-example (QBE) to find documents. A brief example is:

// Find all documents with city names starting with 'S' documents = await collection.find() .filter({"address.city": {"$like": "S%"}}) .getDocuments(); for (let i = 0; i < documents.length; i++) { content = documents[i].getContent(); console.log(' city is: ', content.address.city); }

A runnable example is in soda1.js

Check out the node-oracledb SODA manual section and the Introduction to Simple Oracle Document Access (SODA) manual to see its power and simplicity.

You don't have Oracle Database 18.3 yet? Get it from here. Or you may be interested in using JSON with older versions of Oracle Database.

Summary

We are continuing to introduce important features to node-oracledb to make your development experience better. We have a long wishlist and will continue our work. Contributions from the community are always welcome, and we thank the people who have contributed to this and previous releases of node-oracledb for helping to make it better.

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).

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

Follow us on Twitter or Facebook.

Node.js node-oracledb: "[POLL] Most Wanted Enhancement"

Wed, 2018-09-26 22:08

Danilo Silva, a community member much involved with node-oracledb development, has opened a poll to identify the most requested node-oracledb features.  This will help prioritize our development efforts. 

You can vote here!

Danilo most recently contributed connection pool draining support to node-oracledb, which will be available with node-oracledb 3.0.

ODPI-C 3.0 Introduces SODA Document Storage

Fri, 2018-09-14 06:59
ODPI-C logo

Release 3.0 of Oracle Database Programming Interface for C (ODPI-C) is now available on GitHub

ODPI-C is an open source library of C code that simplifies access to Oracle Database for applications written in C or C++.

 

 

This release introduces support for Simple Oracle Document Access (SODA) when using Oracle Database 18c. SODA provides a non-SQL API for storing and accessing documents. Commonly documents are JSON, but other types can also be used. In this release the SODA API is a Preview. More details about SODA are in the companion release announcement for Python cx_Oracle 7.0.

Also introduced is a call timeout feature for Oracle Client 18c users. This allows applications direct control over how long database operations are allowed to run, making it easier for applications to control outcomes and keep control of user interaction. Again, details are in the cx_Oracle announcement.

If you're creating Windows applications for distribution, a change to how Oracle client libraries are located will be helpful. ODPI-C will try to load the Oracle client from the same directory as the ODPI-C binary, before defaulting to the standard search, i.e. using PATH. This means you can bundle a specific version of Instant Client with your application and know that it will be used in preference to any other Oracle libraries on the system.

There are a raft of other tweaks and improvements which can be found in the release notes.

ODPI-C References

Home page: https://oracle.github.io/odpi/

Code: https://github.com/oracle/odpi

Documentation: https://oracle.github.io/odpi/doc/index.html

Release Notes: https://oracle.github.io/odpi/doc/releasenotes.html

Installation Instructions: oracle.github.io/odpi/doc/installation.html

Report issues and discuss: https://github.com/oracle/odpi/issues

Python cx_Oracle 7 Introduces SODA Document Storage

Thu, 2018-09-13 22:48

cx_Oracle logo

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

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

 

 

Anthony Tuininga has just released cx_Oracle 7.0. This release brings some key technologies and new features to the Python developer:

  • Oracle Simple Document Access (SODA) support - an exciting addition to the standard relational access model is the new set of APIs for Oracle SODA. See below.

  • Added Connection.callTimeout to support call timeouts when cx_Oracle is using Oracle Client 18.1 and higher. This is a useful backstop to prevent out-of-control SQL and PL/SQL statement execution.

    The main code layer beneath cx_Oracle's implementation is Oracle Call Interface. This API handles all the network connectivity to Oracle Database. For each OCI function executed by cx_Oracle, zero or more 'round-trips' to the database can occur - calling the database and getting a response back.

    The callTimeout value applies to each round-trip individually, not to the sum of all round-trips. Time spent processing in cx_Oracle before or after the completion of each round-trip is not counted.

    • If the time from the start of any one round-trip to the completion of that same round-trip exceeds callTimeout milliseconds, then the operation is halted and error "DPI-1067: call timeout of N ms exceeded with ORA-XXX" is returned.

    • In the case where a cx_Oracle operation requires more than one round-trip and each round-trip takes less than callTimeout milliseconds, then no timeout will occur, even if the sum of all round-trip calls exceeds callTimeout.

    • If no round-trip is required, the operation will never be interrupted.

    After a timeout occurs, cx_Oracle attempts to clean up the internal connection state. The cleanup is allowed to take another callTimeout milliseconds.

    If the cleanup was successful, the DPI-1067 error will be returned and the application can continue to use the connection.

    For small values of callTimeout, the connection cleanup may not complete successfully within the additional callTimeout period. In this case an ORA-3114 is returned and the connection will no longer be usable. It should be closed.

  • Added support for closing a session pool via the function SessionPool.close(). This is useful for being 'nice' to the database and making sure that database sessions are not left dangling until the database cleans them up. In particular the optional 'force' argument is handy when you need to suddenly halt a Python application and immediately free all the sessions in the database.

  • Added support for getting the contents of a SQL collection object as a dictionary, where the keys are the indices of the collection and the values are the elements of the collection. See function Object.asdict().

  • On Windows, cx_Oracle will now attempt to load the Oracle client libraries from the same directory as the cx_Oracle module before doing the standard Windows library location search, e.g. in the directories in the PATH environment variable. This new feature could be useful if you are bundling up applications and want to include the Oracle Instant Client. By putting the client in the same directory as the cx_Oracle library there is no need to set PATH, no need to worry about users changing PATH, and no need to worry about having multiple versions of Oracle client libraries in PATH.

  • A change in cx_Oracle 7 is that when a DML RETURNING statement is executed, variables bound to it will return an array when calling Variable.getvalue(). Attempts to set cx_Oracle.__future__.dml_ret_array_val are now ignored.

  • When a connection is used as a context manager, the connection is now closed when the block ends. Attempts to set cx_Oracle.__future__.ctx_mgr_close are now ignored.

The full release notes show the other new features and changes. Review this list before you upgrade:

python -m pip install cx_Oracle --upgrade SODA in Python cx_Oracle

Oracle Simple Document Access (SODA) support was originally introduced in Java and recently exposed to C. Python support for SODA is now available in cx_Oracle 7 when using Oracle client 18.3 libraries and connecting to Oracle Database 18.1 or higher. SODA is all hot and new and under rapid development. For this cx_Oracle release we're labelling SODA support as a 'preview'. With a future version of the Oracle Client libraries this will change.

SODA is typically used to store JSON documents in Oracle Database, but has flexibility to let you store other types of content.

Once a DBA has granted you the SODA_APP privilege, you can simply create collections and store documents in them. Some basic examples are:

# Create the parent object for SODA soda = connection.getSodaDatabase() # Create a new SODA collection # This will open an existing collection, if the name is already in use. collection = soda.createCollection("mycollection") # Insert a document # A system generated key is created by default. content = {'name': 'Matilda', 'address': {'city': 'Melbourne'}} doc = collection.insertOneAndGet(content) key = doc.key print('The key of the new SODA document is: ', key)

You can then get documents back via a key look up, or by a search. A key lookup is straightforward:

# Fetch the document back doc = collection.find().key(key).getOne() # A SodaDocument content = doc.getContent() # A JavaScript object print('Retrieved SODA document dictionary is:') print(content)

For documents that can be converted to JSON you can alternatively get them as string:

content = doc.getContentAsString() # A JSON string print('Retrieved SODA document string is:') print(content)

The find() method is an operation builder, with methods that allow progressive filtering criteria to be set, limiting the set of documents that are then operated on by a terminal method such as getOne(), getDocuments() and count().

With JSON documents, a complete filtering specification language can be used to pattern match documents. A brief example is:

# Find all documents with names like 'Ma%' print("Names matching 'Ma%'") documents = collection.find().filter({'name': {'$like': 'Ma%'}}).getDocuments() for d in documents: content = d.getContent() print(content["name"])

A runnable example is in SodaBasic.py

Check out the cx_Oracle SODA manual and the Introduction to Simple Oracle Document Access (SODA) manual to see its power and simplicity.

Check it out!

PS the photo is one I took last weekend on a beach in Australia, which is coming into Spring. I thought you'd like it better than corporate clip art.

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

Node.js on Oracle Linux: It's So Easy

Mon, 2018-09-10 18:39
This is a quick placeholder cross-post of the 'so I can find it again' category: Sergio Leunissen from our Linux group has a blog post on using Node.js and node-oracledb on Oracle Linux.  You can find the post here.  The Oracle Linux RPMs packages make it all so easy!

Pages