Christopher Jones

Subscribe to Christopher Jones feed
Oracle Blogs
Updated: 6 hours 9 min ago

GDPR: What are the priorities for the IT department?

Mon, 2018-05-21 05:46

All too often it is assumed that GDPR compliance is ‘IT’s problem’ because having your personal data and technology in order are such vital parts of it. But compliance must be an organisation-wide commitment. No individual or single department can make an organisation compliant. However, in planning discussions around GDPR compliance, there are clear areas where IT can add significant value.

 

1. Be a data champion

The potential value of data to organisations is increasing all the time, but many departments, business units and even board members may not realise how much data they have access to, where it resides, how it is created, how it could be used and how it is protected. The IT department can play a clear role in helping organisations understand why data, and by extension GDPR, is so important in order to realise the value of such data and how to use and protect it.

 

2. Ensure data security

GDPR considers protection of personal data a fundamental human right. Organisations need to ensure they understand what personal data they have access to and put in place appropriate protective measures. IT has a role to play in working with the organisation to assess security risks and ensure that appropriate protective measures, such as encryption, access controls, attack prevention and detection are in place.

 

3. Help the organisation be responsive

GDPR requires organisations to not only protect personal data but also respond to requests from individuals who, among others, want to amend or delete data held on them. That means that the personal data must be collected, collated and structured in a way that enables effective and reliable control over all personal data. This means breaking down internal silos and ensuring an organisation has a clear view of its processing activities with regard to personal data.

 

4. Identify the best tools for the job

GDPR compliance is as much about process, culture and planning as it is about technology. However, there are products available which can help organisations with key elements of GDPR compliance, such as data management, security and the automated enforcement of security measures. Advances in automation and artificial intelligence mean many tools offer a level of proactivity and scalability which don’t lessen the responsibility upon people within the organisation, but can reduce the workload and put in place an approach which can evolve with changing compliance requirements.

 

5. See the potential

An improved approach to security and compliance management, fit for the digital economy, can give organisations the confidence to unlock the full potential of their data. If data is more secure, better ordered and easier to make sense of, it stands to reason an organisation can do more with it. It may be tempting to see GDPR as an unwelcome chore. It should however be borne in mind that it is also an opportunity to seek differentiation and greater value, to build new data-driven business models, confident in the knowledge that the data is being used in a compliant way.  Giving consumers the confidence to share their data is also good for businesses.

 

The IT department will know better than most how the full value of data can be unlocked and can help businesses pull away from seeing GDPR as a cost of doing business and start seeing it as an opportunity to do business better.

Autonomous: A New Lens for Analytics

Mon, 2018-05-21 05:45

Welcome to the era of intelligent, self-driving software. Just as self-driving vehicles are set to transform motoring, self-driving software promises to transform our productivity, and strengthen our analytical abilities.

Perhaps you drive an automatic car today—how much are you looking forward to the day your car will automatically drive you? And how much more preferable would smoother, less time-consuming journeys be—always via the best route, with fewer hold-ups, and automatically avoiding unexpected road congestion—where you only have to input your destination? The technology is almost here, and similar advances are driving modern business applications.

AI and machine learning are finally coming of age thanks to the recent advances in big data that created—for the first time—data sets that were large enough for computers to draw inferences and learn from. That, along with years of SaaS application development in cloud computing environments, means that autonomous technology—harnessing both AI and business intelligence—is now fuelling self-driving software… for both cars and cloud applications.

 

Autonomy—beyond automation

Automation has, of course, been around for years. But autonomy—running on AI and machine learning—takes it to new levels. Today’s software is truly self-driving—it eliminates the need for humans to provision, secure, monitor, back-up, recover, troubleshoot or tune. It upgrades and patches itself, and automatically applies security updates, all while running normally. Indeed, an autonomous data warehouse, for example, can reduce administration overheads by up to 80%.

 

Intelligent thinking

But the greatest value is perhaps in what AI enables you to discover from your data. When applied to analytics, it can identify patterns in huge data sets that might otherwise go unnoticed. So, for example, you could apply AI to sales data to identify trends—who bought what, where, when and why?—and apply those to improve the accuracy of your future forecasts.

Alternatively, if you were looking for a vibrant location for new business premises, you might use AI to search for an area with a strong social media buzz around its restaurants and bars. You could teach the software to look for specific words or phrases, and harness machine learning to improve results over time.

AI technology is already widely used in HR to take the slog out of sifting through huge numbers of job applications. As well as being faster and requiring less manpower, it’s able to remove both human bias—critical in the highly subjective area of recruitment—and also identify the best candidates based on factors such as the kind of language they use.

 

Knowledge and power for everyone

These technologies are coming online now—today—for everyone. In the past, most database reporting was typically run by data analysts or scientists to update pre-existing dashboards and reports. Nowadays there are many more business users who are demanding access to such insights, which is being made possible by tools that are far easier to use.

Anyone can experiment with large samples of different data sets, combining multiple data formats—structured and unstructured—and discovering new trends. They can get answers in context, at the right time, and convert them into simple-to-understand insights, enabling decisions to be made more quickly for competitive advantages.

 

Smarter and smarter…

Yet it’s the strength of those insights that’s really compelling. As one commentator observed: ‘Machine intelligence can give you answers to questions that you haven’t even thought of.’ The quality of those answers—and their underlying questions—will only improve over time. That’s why it’s becoming a competitive imperative to embrace the power of intelligent analytics to ensure you can keep pace with market leaders.

 

Discover how…

In my last blog, I shared how organisations can profit from data warehouses and data marts, and how Oracle’s self-driving, self-securing, and self-repairing Autonomous Data Warehouse saves resources on maintenance allowing investment in data analytics.

 

CPQ is an Auditor’s Best Friend

Mon, 2018-05-21 03:00

By Andy Pieroux, Founder and Managing Director of Walpole Partnership Ltd.  

One of the reasons many companies invest in a Configure, Price and Quote (CPQ) system is to provide a robust audit trail for their pricing decisions. Let’s take a look at why, and how CPQ can help.


First, apologies if you are an auditor. I’ve always been on the business side - either in sales, sales management, or as a pricing manager. I can appreciate your view may be different from the other side of the controls. Perhaps by the end of this article our points of view may become closer?

If your business has the potential to get audited, I know that I can speak on your behalf to say we all just love being audited. We love the time taken away from our day jobs. We love the stress of feeling that something may be unearthed that exposes us or gets us in trouble, even if we’ve never knowingly done anything wrong. We love the thought of our practices being exposed as 'in need of improvement' and relish the chance to dig through old documents and folders to try and piece together the story of why we did what we did… especially when it was several years ago. Yes sir, bring on the audit.

The reason we love it so much is that in our heart of hearts, we know audits are needed for our organization to prosper in the future. We dread the thought that our company might be caught up in a scandal like the mis-selling of pensions, or PPI (payment protection insurance), or serious accounting frauds like Enron.

It was scandals like Enron in the early 2000s that gave rise to stricter audit requirements and Sarbanes-Oxley (SOX).  This set a high standard required for internal controls, and much tougher penalties for board members who fail to ensure that financial statements are accurate. The role of pricing decisions (e.g. who authorized what and when), and the accuracy of revenue reporting becomes paramount when evidencing compliance with audit arrangements such as this.

At this point, a CPQ system can be the simple answer to your audit needs. All requests for discount, and the way revenue is allocated across products and services is documented. All approvals can be; attributed to an individual, time stamped, and with reasons captured at the time of approval. More importantly, the ability to show an auditor the entire history of a decision and to follow the breadcrumbs from a signed deal all the way to reported revenue at the click of a button means you have nothing to hide, and a clear understanding of the decisions. This is music to an auditor’s ears. It builds trust and confidence in the process and means any anomalies can be quickly analyzed.

When you have all this information securely stored in the cloud, under controlled access to only those who need it, and a tamper-proof process, that means it is designed with integrity in mind, and makes the process of passing an audit so much easier. All the anxiety and pain mentioned above disappears. Auditors are no longer the enemy. You will find they can help advise on improvements to the rules in your system to make future audits even more enjoyable. Yes - that’s right…. I said it. Enjoyable Audits!

So, CPQ is an auditor’s friend, and an auditee’s friend too. It doesn’t just apply to the big-scale audit requirements like SOX, but any organization that is auditable. Whether you’re a telecommunications company affected by IFRS 15, an organization impacted by GDPR, or any one of a thousand other guidelines, rules or quality policies that get checked - having data and decisions stored in a CPQ system will make you love audits too.

 

 

A node-oracledb Web Service in Docker

Thu, 2018-05-17 02:28

This post shows how to run a node-oracledb application in a Docker Container. For bonus points, the application connects to an Oracle Database running in a second container.

The steps are the 'show notes' from a recent talk at Oracle Code.

The demo app is a simple Express web service that accepts REST calls.

 

DOCKER

Oracle Docker images are available from https://store.docker.com/ and also mirrored on https://container-registry.oracle.com

If you're not familiar with Docker, it helps to know basic terminology:

  • Images: Collection of software to be run as a container. Images are immutable. Changes to an image require a new image build.

  • Registry: Place to store and download images.

  • Container: A lightweight standalone, executable piece of software that includes everything required to run it on a host. Containers are spun up from images. Containers are non-persistent. Once a container is deleted, all files inside that container are gone.

  • Docker engine: The software engine running containers.

  • Volumes: Place to persist data outside the container.

CONFIGURE A DOCKER HOST

For my host, I used Oracle Linux 7, which has the ol7_latest and ol7_uekr4  channels already enabled.

  • Install the Docker engine as the root user by running 'sudo su -', or prefix each command with 'sudo':

    # yum-config-manager --enable ol7_addons # yum install docker-engine # systemctl enable docker # systemctl start docker
DOWNLOAD INSTANT CLIENT AND DATABASE DOCKER IMAGES
  • Sign in to the container registry https://container-registry.oracle.com/ with your (free) Oracle "single sign-on" (SSO) credentials.

  • Accept the license on the container registry.

  • On your OL7 Docker host, log in to the registry. Remember to run Docker commands as 'root':

    # docker login container-registry.oracle.com

    This prompts for your Oracle SSO credentials.

  • Get the Oracle Database and Oracle Instant Client images:

    # docker pull container-registry.oracle.com/database/enterprise:12.2.0.1 # docker pull container-registry.oracle.com/database/instantclient:12.2.0.1

    This can take a while. For testing, you may want to pull the smaller, 'slim' version of the database.

  • View the installed images with:

    # docker images REPOSITORY TAG IMAGE ID CREATED SIZE container-registry.oracle.com/database/enterprise 12.2.0.1 12a359cd0528 7 months ago 3.44GB container-registry.oracle.com/database/instantclient 12.2.0.1 fda46de41de3 7 months ago 407MB
CREATE A DATABASE CONTAINER FROM THE DATABASE IMAGE
  • Start the database container:

    # docker run -d --name demodb -P container-registry.oracle.com/database/enterprise:12.2.0.1

    The '-P' option maps the ports used, allowing access to the database from outside the container.

  • Check for its health and wait until it shows 'healthy'

    # docker ps CONTAINER ID IMAGE COMMAND STATUS PORTS NAMES 9596bc2345d3 [...]/database/[...] "/bin/sh -c '/bin/..." Up 3 hours (healthy) ...->1521/tcp, ...->5500/tcp demodb
  • Find the database container's IP address: # docker inspect -f "{{ .NetworkSettings.IPAddress }}" demodb

    You will use this IP in database connect strings in your applications.

  • You can stop and start the container as desired:

    # docker stop demodb # docker start demodb

    The data is persistent as long as the container exists. Use 'docker ps --all' to show all containers, running or not.

CREATE A NEW SCHEMA
  • Create a SQL file called createschema.sql:

    SET ECHO ON ALTER SESSION SET CONTAINER=orclpdb1; DROP USER scott CASCADE; CREATE USER scott IDENTIFIED BY tiger; GRANT CONNECT, RESOURCE TO scott; ALTER USER scott QUOTA UNLIMITED ON USERS; DROP TABLE scott.bananas; CREATE TABLE scott.bananas (shipment VARCHAR2(4000) CHECK (shipment IS JSON)); INSERT INTO scott.bananas VALUES ('{ "farmer": "Gita", "ripeness": "All Green", "kilograms": 100 }'); INSERT INTO scott.bananas VALUES ('{ "farmer": "Ravi", "ripeness": "Full Yellow", "kilograms": 90 }'); INSERT INTO scott.bananas VALUES ('{ "farmer": "Mindy", "ripeness": "More Yellow than Green", "kilograms": 92 }'); COMMIT; EXIT

    For this demo, you can see I used the Oracle Database 12.1.0.2 JSON data type.

  • Execute createschema.sql in your favorite tool, such as SQL*Plus.

    In my case I actually ran SQL*Plus on my Docker host machine. Cheating a bit on giving details here, I had downloaded the Instant Client Basic and SQL*Plus packages and unzipped as shown in the the Instant Client instructions. I then set my shell to use the SQL*Plus binary:

    # export LD_LIBRARY_PATH=/home/cjones/instantclient_12_2 # export PATH=/home/cjones/instantclient_12_2:$PATH

    Using the database IP address as shown earlier you can now run the script in SQL*Plus against the container database. In my environment the database IP was 172.17.0.2:

    # sqlplus -l sys/Oradoc_db1@172.17.0.2/orclpdb1.localdomain as sysdba @createschema.sql

    The database password and service name shown are the defaults in the image.

CREATE A NODE.JS IMAGE

Let's add Node.js to the Instant Client image.

  • Create a sub-directory nodejs-scripts

    # mkdir nodejs-scripts
  • Create a new file 'nodejs-scripts/Dockerfile'. This is the 'recipe' for building a Docker image. Here Node.js is added to the Instant Client image to create a new image usable by any Node.js application. The Node.js 8 package for Oracle Linux is handy.

    The Dockerfile should contain:

    FROM container-registry.oracle.com/database/instantclient:12.2.0.1 ADD ol7_developer_nodejs8.repo /etc/yum.repos.d/ol7_developer_nodejs8.repo RUN echo proxy=http://my-proxy.example.com:80 >> /etc/yum.conf RUN yum -y update && \ rm -rf /var/cache/yum && \ yum -y install nodejs

    The FROM line shows that we base our new image on the Instant Client image.

    If you are not behind a proxy, you can omit the proxy line. Or change the line to use your proxy.

    For quick testing, you may want to omit the 'yum -y update' command.

  • The Dockerfile ADD command copies 'ol7_developer_nodejs8.repo' from the host file system into the image's file system. Create 'nodejs-scripts/ol7_developer_nodejs8.repo' containing:

    [ol7_developer_nodejs8] name=Oracle Linux $releasever Node.js 8 Packages for Development and test ($basearch) baseurl=https://yum.oracle.com/repo/OracleLinux/OL7/developer_nodejs8/$basearch/ gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle gpgcheck=1 enabled=1
  • Now the new image with Oracle Instant Client and Node.js 8 can be built using this Dockerfile:

    docker build -t cjones/nodejs-image nodejs-scripts

    The 'cjones/nodejs-image' is the image name, not a directory path.

  • You can see the new image has been created:

    # docker images REPOSITORY TAG IMAGE ID CREATED SIZE cjones/nodejs-image latest e048b739bb63 29 minutes ago 1.51GB container-registry.oracle.com/database/enterprise 12.2.0.1 12a359cd0528 7 months ago 3.44GB container-registry.oracle.com/database/instantclient 12.2.0.1 fda46de41de3 7 months ago 407MB
CREATE A NODE.JS DEMO IMAGE

The new Node.js image is refined by installing our demo application. This creates another new image that we can later run whenever we want to use the application.

  • Create a sub-directory 'ws-demo-scripts':

    # mkdir ws-demo-scripts
  • Create a new file 'ws-demo-scripts/Dockerfile' containing:

    FROM cjones/nodejs-image ENV https_proxy=http://my-proxy.example.com:80 WORKDIR workdir COPY package.json package.json COPY server.js server.js RUN npm install CMD ["npm", "start"]

    The first line shows the new image should be based on the Node.js image 'cjones/nodejs-image' created in the section above.

    Again, adjust the proxy line as needed by your network.

    You can see the Dockerfile copies two files from our host file system into the image. These files are shown below.

    When the image is created, the RUN command will install the Node.js dependencies from package.json.

    When a container starts, the CMD action is taken, which runs 'npm start', in turn invoking the 'main' target in package.json. Looking below to the package.json content, you can see this means 'node server.js' is run.

  • Create a file 'ws-demo-scripts/package.json' containing:

    { "name": "banana-farmer", "version": "1.0.0", "description": "RESTful API using Node.js Express Oracle DB", "main": "server.js", "author": "Oracle", "license": "Apache", "dependencies": { "body-parser": "^1.18.2", "express": "^4.16.0", "oracledb": "^2.2.0" } }

    As obvious, this application installs the body-parser module, the node-oracledb module, and also express. This demo is an Express web service application. And yes, it is a Banana Farmer web service.

    The default run target of package.json is the application file 'server.js'.

  • Create the application file 'ws-demo-scripts/server.js' containing the contents from here.

    The demo application is just this one file.

  • Build the demo image:

    # docker build -t cjones/ws-demo ws-demo-scripts

    We now have our fourth image which contains our runnable application:

    # docker images REPOSITORY TAG IMAGE ID CREATED SIZE cjones/ws-demo latest 31cbe6d2ea4e 21 seconds ago 1.51GB cjones/nodejs-image latest e048b739bb63 29 minutes ago 1.51GB container-registry.oracle.com/database/enterprise 12.2.0.1 12a359cd0528 7 months ago 3.44GB container-registry.oracle.com/database/instantclient 12.2.0.1 fda46de41de3 7 months ago 407MB
DEMO APPLICATION OVERVIEW

The Banana Farmer scenario is that shipments of bananas from farmers are recorded. They can have a farmer name, ripeness, and weight. Shipments can be inserted, queried, updated or deleted.

Let's look at a couple of snippets from ws-demo-scripts/server.js.

A connection helper creates a pool of database connections:

oracledb.createPool({  user: process.env.NODE_ORACLEDB_USER, password: process.env.NODE_ORACLEDB_PASSWORD, connectString: process.env.NODE_ORACLEDB_CONNECTIONSTRING }, . . .

The credentials are taken from environment variables. When we run the app container we will pass value for those environment variables into the container.

The application has Express routes for REST GET, POST, PUT and DELETE calls. The code to handle a GET request looks like:

// HTTP method: GET // URI : /bananas/FARMER // Get the banana shipment for FARMER app.get('/bananas/:FARMER', function (req, res) { doGetConnection(res, function(err, connection) { if (err) return; connection.execute( "SELECT b.shipment FROM bananas b WHERE b.shipment.farmer = :f", { f: req.params.FARMER }, function (err, result) { if (err) { res.set('Content-Type', 'application/json'); res.status(500).send(JSON.stringify({ status: 500, message: "Error getting the farmer's profile", detailed_message: err.message })); } else if (result.rows.length < 1) { res.set('Content-Type', 'application/json'); res.status(404).send(JSON.stringify({ status: 404, message: "Farmer doesn't exist", detailed_message: "" })); } else { res.contentType('application/json'); res.status(200).send(JSON.stringify(result.rows)); } doRelease(connection, "GET /bananas/" + req.params.FARMER); }); }); });

Express makes it easy. It handles the routing to this code when a GET request with the URL '/bananas/<name>' e.g. '/bananas/Gita' is called. This simply binds the URL route parameter containing the farmer’s name into the SELECT statement. Binding is important for security and scalability, as you know. The SQL syntax used is the JSON 'dot' notation of Oracle Database 12.2 but it could be rewritten to work with 12.1.0.2.

The bulk of the code is error handling, looking after the cases where there was a processing error or no rows returned. It sends back HTTP status codes 500 or 404, respectively.

The success code path sends back the query output 'result.rows' as a JSON string, with the HTTP success status code 200.

START THE DEMO CONTAINER

Let's run the application.

  • Create a file 'ws-demo-scripts/envfile.list' with the credentials for the application. Use the IP address of your database container found with the 'docker inspect' command shown previously. In my environment, the database IP address was '172.17.0.2'

    NODE_ORACLEDB_USER=scott NODE_ORACLEDB_PASSWORD=tiger NODE_ORACLEDB_CONNECTIONSTRING=172.17.0.2/orclpdb1.localdomain
  • Start the Node.js web service container

    # docker run -d --name nodejs -P --env-file ws-demo-scripts/envfile.list cjones/ws-demo
STATUS CHECK
  • To recap what's happened, the Docker images are:

    # docker images REPOSITORY TAG IMAGE ID CREATED SIZE cjones/ws-demo latest 25caede29b17 12 minutes ago 1.51GB cjones/nodejs-image latest 138f2b76ffe7 13 minutes ago 1.51GB container-registry.oracle.com/database/enterprise 12.2.0.1 12a359cd0528 7 months ago 3.44GB container-registry.oracle.com/database/instantclient 12.2.0.1 fda46de41de3 7 months ago 407MB

    Two base images were downloaded, An image with Node.js was created from the Instant Client image. Finally a fourth image 'cjones/ws-demo' with the Node.js, Instant Client and the application code was created.

  • We have started database ('demodb') and application containers ('nodejs'):

    # docker ps CONTAINER ID IMAGE COMMAND STATUS PORTS NAMES 2924e1225290 cjones/ws-demo ”npm start" Up 3 hours nodejs 9596bc2345d3 [...]/database/[...] "/bin/sh -c '/bin/..." Up 3 hours (healthy) ...->1521/tcp, ...->5500/tcp demodb

    We found the IP address of the database container, and knew (by reading the container registry documentation) the default credentials of the SYS user.

    We created a schema SCOTT on the database, with a table containing some JSON data.

    An application container was started, with the database application credentials and connection string specified in an environment file outside the container.

SUBMIT REST REQUESTS

Now we can call our application, and it will access the database.

  • Install the browser extension HttpRequester (in Firefox) or Postman (in Chrome).

  • Find the IP of the demo web service container:

    # docker inspect -f "{{ .NetworkSettings.IPAddress }}" nodejs

    In my environment, it was '172.17.0.3'. Use this with the port (3000) and various endpoints (e.g. '/bananas/<farmer>') defined in server.js for REST requests.

  • In the HttpRequester or Postman extensions you can make various REST calls.

    Get all shipments:

    GET http://172.17.0.3:3000/bananas

    Get one farmer's shipment(s):

    GET http://172.17.0.3:3000/bananas/Gita

    New data:

    POST http://172.17.0.3:3000/bananas { "farmer" : "CJ", "ripeness" : "Light Green", "kilograms" : 50 }

    Update data:

    PUT http://172.17.0.3:3000/bananas/CJ { "farmer" : "CJ", "ripeness" : "50% Green, 50% Yellow", "kilograms" : 45 }

    Here's a screenshot of HttpRequester in action doing a GET request to get all banana shipments. On the left, the red boxes show the URL for the '/bananas' endpoint was executed as a GET request. On the right, the response shows the success HTTP status code of 200 and the returned data from the request:

    Screenshot of HttpRequester
  • When you are finished with the containers you can stop them:

    # docker stop demodb # docker stop nodejs

    If you haven't tried Docker yet, now is the perfect time! They make deployment and development easy. Oracle's Docker images let you get started with Oracle products very quickly.

Efficient and Scalable Batch Statement Execution in Python cx_Oracle

Fri, 2018-04-27 00:06
cx_Oracle logo

 

 

Today's guest post is by Oracle's Anthony Tuininga, creator and lead maintainer of cx_Oracle, the extremely popular Oracle Database interface for Python.

 

 

 

Introduction

This article shows how batch statement execution in the Python cx_Oracle interface for Oracle Database can significantly improve performance and make working with large data sets easy.

In many cx_Oracle applications, executing SQL and PL/SQL statements using the method cursor.execute() is perfect. But if you intend to execute the same statement repeatedly for a large set of data, your application can incur significant overhead, particularly if the database is on a remote network. The method cursor.executemany() gives you the ability to reduce network transfer costs and database load, and can significantly outperform repeated calls to cursor.execute().

SQL

To help demonstrate batch execution, the following tables and data will be used:

create table ParentTable ( ParentId number(9) not null, Description varchar2(60) not null, constraint ParentTable_pk primary key (ParentId) ); create table ChildTable ( ChildId number(9) not null, ParentId number(9) not null, Description varchar2(60) not null, constraint ChildTable_pk primary key (ChildId), constraint ChildTable_fk foreign key (ParentId) references ParentTable ); insert into ParentTable values (10, 'Parent 10'); insert into ParentTable values (20, 'Parent 20'); insert into ParentTable values (30, 'Parent 30'); insert into ParentTable values (40, 'Parent 40'); insert into ParentTable values (50, 'Parent 00'); insert into ChildTable values (1001, 10, 'Child A of Parent 10'); insert into ChildTable values (1002, 20, 'Child A of Parent 20'); insert into ChildTable values (1003, 20, 'Child B of Parent 20'); insert into ChildTable values (1004, 20, 'Child C of Parent 20'); insert into ChildTable values (1005, 30, 'Child A of Parent 30'); insert into ChildTable values (1006, 30, 'Child B of Parent 30'); insert into ChildTable values (1007, 40, 'Child A of Parent 40'); insert into ChildTable values (1008, 40, 'Child B of Parent 40'); insert into ChildTable values (1009, 40, 'Child C of Parent 40'); insert into ChildTable values (1010, 40, 'Child D of Parent 40'); insert into ChildTable values (1011, 40, 'Child E of Parent 40'); insert into ChildTable values (1012, 50, 'Child A of Parent 50'); insert into ChildTable values (1013, 50, 'Child B of Parent 50'); insert into ChildTable values (1014, 50, 'Child C of Parent 50'); insert into ChildTable values (1015, 50, 'Child D of Parent 50'); commit; Simple Execution

To insert a number of rows into the parent table, the following naive Python script could be used:

data = [ (60, "Parent 60"), (70, "Parent 70"), (80, "Parent 80"), (90, "Parent 90"), (100, "Parent 100") ] for row in data: cursor.execute(""" insert into ParentTable (ParentId, Description) values (:1, :2)""", row)

This works as expected and five rows are inserted into the table. Each execution, however, requires a "round-trip" to the database. A round-trip is defined as the client (i.e. the Python script) making a request to the database and the database sending back its response to the client. In this case, five round-trips are required. As the number of executions increases, the cost increases in a linear fashion based on the average round-trip cost. This cost is dependent on the configuration of the network between the client (Python script) and the database server, as well as on the capability of the database server.

Batch Execution

Performing the same inserts using executemany() would be done as follows:

data = [ (60, "Parent 60"), (70, "Parent 70"), (80, "Parent 80"), (90, "Parent 90"), (100, "Parent 100") ] cursor.executemany(""" insert into ParentTable (ParentId, Description) values (:1, :2)""", data)

In this case there is only one round-trip to the database, not five. In fact, no matter how many rows are processed at the same time there will always be just one round-trip. As the number of rows processed increases, the performance advantage of cursor.executemany() skyrockets. For example, on my machine inserting 1,000 rows into the same table in a database on the local network using cursor.execute() takes 410 ms, whereas using cursor.executemany() requires only 20 ms. Increasing the number to 10,000 rows requires 4,000 ms for cursor.execute() but only 60 ms for cursor.executemany()!

For really huge data sets there may be external buffer or network limits to how many rows can be processed at one time. These limits are based on both the number of rows being processed as well as the "size" of each row that is being processed. The sweet spot can be found by tuning your application. Repeated calls to executemany() are still better than repeated calls to execute().

As mentioned earlier, execution of PL/SQL statements is also possible. Here is a brief example demonstrating how to do so:

data = [[2], [6], [4]] var = cursor.var(str, arraysize = len(data)) data[0].append(var) # OUT bind variable ':2' cursor.executemany(""" declare t_Num number := :1; t_OutValue varchar2(100); begin for i in 1..t_Num loop t_OutValue := t_OutValue || 'X'; end loop; :2 := t_OutValue; end;""", data) print("Result:", var.values)

This results in the following output:

Result: ['XX', 'XXXXXX', 'XXXX'] Using executemany()

With the significant performance advantages that can be seen by performing batch execution of a single statement it would seem obvious to use cursor.executemany() whenever possible. Let's look at some of the other features of executemany() useful for common data handling scenarios.

Scenario 1: Getting Affected Row Counts

One scenario that may arise is the need to determine how many rows are affected by each row of data that is passed to cursor.executemany(). Consider this example:

for parentId in (10, 20, 30): cursor.execute("delete from ChildTable where ParentId = :1", [parentId]) print("Rows deleted for parent id", parentId, "are", cursor.rowcount)

This results in the following output:

Rows deleted for parent id 10 are 1 Rows deleted for parent id 20 are 3 Rows deleted for parent id 30 are 2

Since each delete is performed independently, determining how many rows are affected by each delete is easy to do. But what happens if we use cursor.executemany() in order to improve performance as in the following rewrite?

data = [[10], [20], [30]] cursor.executemany("delete from ChildTable where ParentId = :1", data) print("Rows deleted:", cursor.rowcount)

This results in the following output:

Rows deleted: 6

You'll note this is the sum of all of the rows that were deleted in the prior example, but the information on how many rows were deleted for each parent id is missing. Fortunately, that can be determined by enabling the Array DML Row Counts feature, available in Oracle Database 12.1 and higher:

data = [[10], [20], [30]] cursor.executemany("delete from ChildTable where ParentId = :1", data, arraydmlrowcounts = True) for ix, rowsDeleted in enumerate(cursor.getarraydmlrowcounts()): print("Rows deleted for parent id", data[ix][0], "are", rowsDeleted)

This results in the same output as was shown for the simple cursor.execute():

Rows deleted for parent id 10 are 1 Rows deleted for parent id 20 are 3 Rows deleted for parent id 30 are 2 Scenario 2: Handling Bad Data

Another scenario is handling bad data. When processing large amounts of data some of that data may not fit the constraints imposed by the database. Using cursor.execute() such processing may look like this:

data = [ (1016, 10, 'Child B of Parent 10'), (1017, 10, 'Child C of Parent 10'), (1018, 20, 'Child D of Parent 20'), (1018, 20, 'Child D of Parent 20'), # duplicate key (1019, 30, 'Child C of Parent 30'), (1020, 30, 'Child D of Parent 40'), (1021, 600, 'Child A of Parent 600'), # parent does not exist (1022, 40, 'Child F of Parent 40'), ] for ix, row in enumerate(data): try: cursor.execute(""" insert into ChildTable (ChildId, ParentId, Description) values (:1, :2, :3)""", row) except cx_Oracle.DatabaseError as e: print("Row", ix, "has error", e)

This results in the following output:

Row 3 has error ORA-00001: unique constraint (EMDEMO.CHILDTABLE_PK) violated Row 6 has error ORA-02291: integrity constraint (EMDEMO.CHILDTABLE_FK) violated - parent key not found

If you make use of cursor.executemany(), however, execution stops at the first error that is encountered:

data = [ (1016, 10, 'Child B of Parent 10'), (1017, 10, 'Child C of Parent 10'), (1018, 20, 'Child D of Parent 20'), (1018, 20, 'Child D of Parent 20'), # duplicate key (1019, 30, 'Child C of Parent 30'), (1020, 30, 'Child D of Parent 40'), (1021, 600, 'Child A of Parent 600'), # parent does not exist (1022, 40, 'Child F of Parent 40'), ] try: cursor.executemany(""" insert into ChildTable (ChildId, ParentId, Description) values (:1, :2, :3)""", data) except cx_Oracle.DatabaseError as e: errorObj, = e.args print("Row", cursor.rowcount, "has error", errorObj.message)

This results in the following output:

Row 3 has error ORA-00001: unique constraint (EMDEMO.CHILDTABLE_PK) violated

Fortunately there is an option to help here as well, using the Batch Errors feature available in Oracle Database 12.1 and higher. This can be seen using the following code:

data = [ (1016, 10, 'Child B of Parent 10'), (1017, 10, 'Child C of Parent 10'), (1018, 20, 'Child D of Parent 20'), (1018, 20, 'Child D of Parent 20'), # duplicate key (1019, 30, 'Child C of Parent 30'), (1020, 30, 'Child D of Parent 40'), (1021, 600, 'Child A of Parent 600'), # parent does not exist (1022, 40, 'Child F of Parent 40'), ] cursor.executemany(""" insert into ChildTable (ChildId, ParentId, Description) values (:1, :2, :3)""", data, batcherrors = True) for errorObj in cursor.getbatcherrors(): print("Row", errorObj.offset, "has error", errorObj.message)

This results in the following output, which is identical to the example that used cursor.execute():

Row 3 has error ORA-00001: unique constraint (EMDEMO.CHILDTABLE_PK) violated Row 6 has error ORA-02291: integrity constraint (EMDEMO.CHILDTABLE_FK) violated - parent key not found

In both the execute() and executemany() cases, rows that were inserted successfully open a transaction which will need to be either committed or rolled back with connection.commit() or connection.rollback(), depending on the needs of your application. Note that if you use autocommit mode, the transaction is committed only when no errors are returned; otherwise, a transaction is left open and will need to be explicitly committed or rolled back.

Scenario 3: DML RETURNING Statements

The third scenario that I will consider is that of DML RETURNING statements. These statements allow you to bundle a DML statement (such as INSERT, UPDATE, DELETE and MERGE statements) along with a query to return some data at the same time. With cursor.execute() this is done easily enough using something like the following code:

childIdVar = cursor.var(int) cursor.setinputsizes(None, childIdVar) for parentId in (10, 20, 30): cursor.execute(""" delete from ChildTable where ParentId = :1 returning ChildId into :2""", [parentId]) print("Child ids deleted for parent id", parentId, "are", childIdVar.values)

This produces the following output:

Child ids deleted for parent id 10 are [1001] Child ids deleted for parent id 20 are [1002, 1003, 1004] Child ids deleted for parent id 30 are [1005, 1006]

Support for DML RETURNING in cursor.executemany() was introduced in cx_Oracle 6.3. Because it was supported only in execute() prior to cx_Oracle 6.3, the cx_Oracle.__future__ object must have the attribute "dml_ret_array_val" set to True to allow multiple values to be returned by executemany(). Failing to set this to True when calling executemany() will result in an error. Finally, the variable created to accept the returned values must have an array size large enough to accept the rows that are returned (one array of output data is returned for each of the input records that are provided).

The following code shows the new executemany() support in cx_Oracle 6.3:

cx_Oracle.__future__.dml_ret_array_val = True data = [[10], [20], [30]] childIdVar = cursor.var(int, arraysize = len(data)) cursor.setinputsizes(None, childIdVar) cursor.executemany(""" delete from ChildTable where ParentId = :1 returning ChildId into :2""", data) for ix, inputRow in enumerate(data): print("Child ids deleted for parent id", inputRow[0], "are", childIdVar.getvalue(ix))

This results in the same output as was seen with cursor.execute():

Child ids deleted for parent id 10 are [1001] Child ids deleted for parent id 20 are [1002, 1003, 1004] Child ids deleted for parent id 30 are [1005, 1006]

Note: that using "dml_ret_array_val" set to True with execute() causes arrays to be returned for each bind record. In any future cx_Oracle 7 this will become the only behavior available.

Scenario 4: Variable Data lengths

When multiple rows of data are being processed there is the possibility that the data is not uniform in type and size. cx_Oracle makes some effort to accommodate such differences. For example, type determination is deferred until a value that is not None is found in the data. If all values in a particular column are None, then cx_Oracle assumes the type is a string and has a length of 1. cx_Oracle will also adjust the size of the buffers used to store strings and bytes when a longer value is encountered in the data. These sorts of operations, however, will incur overhead as cx_Oracle has to reallocate memory and copy all of the data that has been processed thus far. To eliminate this overhead, the method cursor.setinputsizes() should be used to tell cx_Oracle about the type and size of the data that is going to be used. For example:

data = [ (110, "Parent 110"), (2000, "Parent 2000"), (30000, "Parent 30000"), (400000, "Parent 400000"), (5000000, "Parent 5000000") ] cursor.setinputsizes(None, 20) cursor.executemany(""" insert into ParentTable (ParentId, Description) values (:1, :2)""", data)

In this example, without the call to cursor.setinputsizes(), cx_Oracle would perform five allocations of increasing size as it discovered each new, larger string. The value 20, however, tells cx_Oracle that the maximum size of the strings that will be processed is 20 characters. Since cx_Oracle allocates memory for each row based on this value it is best not to oversize it. Note that if the type and size are uniform (like they are for the first column in the data being inserted), the type does not need to be specified and None can be provided, indicating that the default type (in this case cx_Oracle.NUMBER) should be used.

Conclusion

As can be seen by the preceding examples, cursor.executemany() lets you manage data easily and enjoy high performance at the same time!

Python cx_Oracle 6.3 Supports DML RETURNING for Batch Statement Execution

Thu, 2018-04-26 23:26

cx_Oracle logo

cx_Oracle 6.3, 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.

Top Feature: Cursor.executemany() support for OUT bind variables in DML RETURNING statements.

 

This release contains a number of bug fixes and useful improvements. For the full list, see the Release Notes, but I wanted to highlight a few features:

  • Support for binding integers and floats as cx_Oracle.NATIVE_FLOAT.

  • Support for true heterogeneous session pools that use different username/password combinations for each session acquired from the pool.

  • All cx_Oracle exceptions raised by cx_Oracle now produce a cx_Oracle._Error object.

  • Support for getting the OUT values of bind variables bound to a DML RETURNING statement when calling Cursor.executemany(). For technical reasons, this requires setting a new attribute in cx_Oracle.__future__. As an example:

    cx_Oracle.__future__.dml_ret_array_val = True data = [[10], [20], [30]] childIdVar = cursor.var(int, arraysize = len(data)) cursor.setinputsizes(None, childIdVar) cursor.executemany(""" delete from ChildTable where ParentId = :1 returning ChildId into :2""", data) for ix, inputRow in enumerate(data): print("Child ids deleted for parent id", inputRow[0], "are", childIdVar.getvalue(ix))

    Want to know what this displays? Stay tuned to this blog site for an upcoming post on using executemany() in cx_Oracle!

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

ODPI-C 2.3.1 is now on GitHub

Wed, 2018-04-25 17:36
ODPI-C logo

Release 2.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++.

 

 

Today a minor patch update release of ODPI-C was pushed to GitHub. Check the Release Notes for details on the handful of fixes that landed.

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

ODPI-C 2.3 is now on GitHub

Mon, 2018-04-02 21:33
ODPI-C logo

Release 2.3 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: Improve Batch Statement Execution

 

ODPI-C 2.3 improves support for Batch Statement execution with dpiStmt_executeMany(). To support DML RETURNING producing multiple rows for each iteration, a new function dpiVar_getReturnedData() was added, replacing the function dpiVar_getData() which will be deprecated in a future release. A fix for binding LONG data in dpiStmt_executeMany() also landed.

If you haven't heard of Batch Statement Executation (sometimes referred to as Array DML), check out this Python cx_Oracle example or this Node.js node-oracledb example.

A number of other issues were addressed in ODPI-C 2.3. See the release notes for more information.

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 2.2 with Batch Statement Execution (and more) is out on npm

Mon, 2018-04-02 17:16

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

Top features: Batch Statement Execution

In the six-or-so weeks since 2.1 was released, a bunch of new functionality landed in node-oracledb 2.2. This shows how much engineering went into the refactored lower abstraction layer we introduced in 2.0, just to make it easy to expose Oracle features to languages like Node.js.

The top features in node-oracledb 2.2 are:

  • Added oracledb.edition to support Edition-Based Redefinition (EBR). The EBR feature of Oracle Database allows multiple versions of views, synonyms, PL/SQL objects and SQL Translation profiles to be used concurrently. This lets database logic be updated and tested while production users are still accessing the original version.

    The new edition property can be set at the global level, when creating a pool, or when creating a standalone connection. This removes the need to use an ALTER SESSION command or ORA_EDITION environment variable.

  • Added oracledb.events to allow the Oracle client library to receive Oracle Database service events, such as for Fast Application Notification (FAN) and Runtime Load Balancing (RLB).

    The new events property can be set at the global level, when creating a pool, or when creating a standalone connection. This removes the need to use an oraaccess.xml file to enable event handling, making it easier to use Oracle high availablility features, and makes it available for the first time to users who are linking node-oracledb with version 11.2 Oracle client libraries.

  • Added connection.changePassword() for changing passwords. Passwords can also be changed when calling oracledb.getConnection(), which is the only way to connect when a password has expired.

  • Added connection.executeMany() for efficient batch execution of DML (e.g. INSERT, UPDATE and DELETE) and PL/SQL execution with multiple records. See the example below.

  • Added connection.getStatementInfo() to find information about a SQL statement without executing it. This is most useful for finding column types of queries and for finding bind variables names. It does require a 'round-trip' to the database, so don't use it without reason. Also there are one or two quirks because the library underneath that provides the implementation has some 'historic' behavior. Check the manual for details.

  • Added connection.ping() to support system health checks. This verifies that a connection is usable and that the database service or network have not gone down. This requires a round-trip to the database so you wouldn't use it without reason. Although it doesn't replace error handling in execute(), sometimes you don't want to be running a SQL statement just to check the connection status, so it is useful in the arsenal of features for keeping systems running reliably.

See the CHANGELOG for all changes.

One infrastructure change we recently made was to move the canonical home for documentation to GitHub 'pages'. This will be kept in sync with the current production version of node-oracledb. If you update your bookmarks to the new locations, it will allow us to update the source code repository documentation mid-release without confusing anyone about available functionality.

Batch Statement Execution

The new connection.executeMany() method allows many sets of data values to be bound to one DML or PL/SQL statement for execution. It is like calling connection.execute() multiple times for one statement but requires fewer round-trips overall. This is an efficient way to handle batch changes, for example when inserting or updating multiple rows, because the reduced cost of round-trips has a significant affect on performance and scalability. Depending on the number of records, their sizes, and on the network speed to the database, the performance of executeMany() can be significantly faster than the equivalent use of execute().

In one little test I did between Node.js on my laptop and a database running on my adjacent desktop, I saw that executeMany() took 16 milliseconds whereas execute() took 2.3 seconds to insert 1000 rows, each consisting of a number and a very short string. With larger data sizes and slower (or faster!) networks the performance characteristics will vary, but the overall benefit is widespread.

The executeMany() method supports IN, IN OUT and OUT variables. Binds from RETURNING INTO clauses are supported, making it easy to insert a number of rows and find, for example, the ROWIDs of each.

With an optional batchErrors mode, you can insert 'noisy' data easily. Batch Errors allows valid rows to be inserted and invalid rows to be rejected. A transaction will be started but not committed, even if autocommit mode is enabled. The application can examine the errors, find the bad data, take action, and explicitly commit or rollback as desired.

To give one example, let's look at the use of batchErrors when inserting data:

var sql = "INSERT INTO childtab VALUES (:1, :2, :3)"; // There are three value in each nested array since there are // three bind variables in the SQL statement. // Each nested array will be inserted as a new row. var binds = [ [1016, 10, "apples"], [1017, 10, "bananas"], [1018, 20, "cherries"], [1018, 20, "damson plums"], // duplicate key [1019, 30, "elderberry"], [1020, 40, "fig"], [1021, 75, "golden kiwifruit"], // parent does not exist [1022, 40, "honeydew melon"] ]; var options = { autoCommit: true, // autocommit if there are no batch errors batchErrors: true, // identify invalid records; start a transaction for valid ones bindDefs: [ // describes the data in 'binds' { type: oracledb.NUMBER }, { type: oracledb.NUMBER }, { type: oracledb.STRING, maxSize: 16 } // size of the largest string, or as close as possible ] }; connection.executeMany(sql, binds, options, function (err, result) { if (err) consol.error(err); else { console.log("Result is:", result); } });

Assuming appropriate data exists in the parent table, the output might be like:

Result is: { rowsAffected: 6, batchErrors: [ { Error: ORA-00001: unique constraint (CJ.CHILDTAB_PK) violated errorNum: 1, offset: 3 }, { Error: ORA-02291: integrity constraint (CJ.CHILDTAB_FK) violated - parent key not found errorNum: 2291, offset: 6 } ] }

This shows that 6 records were inserted but the records at offset 3 and 6 (using a 0-based index into the 'binds' variable array) were problematic. Because of these batch errors, the other records were not committed, despite autoCommit being true. However they were inserted and the transaction could be committed or rolled back.

We know some users are inserting very large data sets so executeMany() will be very welcome. At the very huge end of the data spectrum you may want to call executeMany() with batches of data to avoid size limitations in various layers of the Oracle and operating system stack. Your own testing will determine the best approach.

See Batch Execution in the manual for more information about the modes of executeMany() and how to use it in various cases. There are runnable examples in the GitHub examples directory. Look for the files prefixed 'em_'. There are two variants of each sample: one uses call-back style, and the other uses the Async/Await interface available with Node.js 8.

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.

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

Python cx_Oracle questions? Ask us live online at March 13 at 20:00-20:30 UTC

Mon, 2018-03-12 18:12

Join me, @AnthonyTuininga, and @OraBlaineOS for the first Python and cx_Oracle monthly 'Office Hours' session tomorrow, March 13 at 20:00-20:30 UTC. The theme for this month is 'Connections' but we're open to any other cx_Oracle questions. Join the video feed or go audio-only. All the details are on the Python and Oracle Database Office Hours page.  

Python cx_Oracle 6.2 is out on PyPI

Mon, 2018-03-05 19:01

cx_Oracle logo

cx_Oracle 6.2, 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.



This release:

  • Adds support for creating temporary CLOBs, BLOBs or NCLOBs via a new method Connection.createlob().

  • Adds support for binding a LOB value directly to a cursor.

  • Adds support for closing the connection when reaching the end of a 'with' code block controlled by the connection as a context manager. See cx_Oracle.__future__ for more information.

  • Was internally updated to the newest ODPI-C data access layer, which brings numerous stability fixes and code improvements including:

    • Open statements and LOBs are tracked and automatically closed when the related connection is closed; this eliminates the need for users of cx_Oracle to track them, and removes the error "DPI-1054: connection cannot be closed when open statements or LOBs exist".

    • Errors during implicit rollback at connection close are ignored - but if an error does occur, ensure the connection is dropped from the connection pool. This reduces app errors in cases like where a DBA has killed a session.

    • Avoids an unnecessary round trip to the database when a connection is released back to the pool by preventing a rollback from being called when no transaction is in progress.

  • There was also an internal code restructure to simplify maintenance and consolidate transformations to/from Python objects.

See the Release Notes for all the fixes.

To upgrade to cx_Oracle 6.2 most users will be able to run:

python -m pip install cx_Oracle --upgrade

Spread the word!

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

ODPI-C 2.2 Release: Powering Oracle Database Access

Mon, 2018-03-05 16:24

ODPI-C 2.2.1 has been tagged for release.

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. The ODPI-C project is open source and maintained by Oracle Corp.

ODPI-C is used as a data access layer in drivers for Node.js, Python, Ruby, Go, Rust, Haskell and more.

Changes in ODPI-C 2.2 from 2.1 include:

  • Open statements and LOBs are tracked and automatically closed when the related connection is closed; this eliminates the need for users of the driver to do so and removes the error "DPI-1054: connection cannot be closed when open statements or LOBs exist".

  • Errors during implicit rollback at connection close are ignored - but if an error does occur, ensure the connection is dropped from the connection pool. This reduces app errors in cases like where a DBA has killed a session.

  • Avoid a round trip to the database when a connection is released back to the pool by preventing a rollback from being called when there is no transaction in progress.
  • A new, optional, way of including the source code in your projects: embed/dpi.c was added. This simply includes all other source files. You can reliably link with just dpi.c and not have to update your projects if, and when, new ODPI-C versions have new source files.

  • Many stability fixes, code improvements, new tests, and documentation updates.

See the release notes for all changes.

In my opinion, the stability fixes justify upgrading immediately.

The eagle-eyed will note that today is a 2.2.1 release but we actually tagged 2.2.0 a few weeks ago. ODPI-C 2.2.0 was tagged solely to give an identifiable base for node-oracledb 2.2 to use. However Anthony had some ODPI-C fixes queued up in areas of code not used by node-oracledb, hence today's "official" ODPI-C 2.2.1 announcement.

ODPI-C References

Home page: oracle.github.io/odpi

Code: github.com/oracle/odpi

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

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

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

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

Installing the Oracle ODBC Driver on macOS

Thu, 2018-02-22 23:25

A bonus for today is a guest post by my colleague Senthil Dhamotharan. He shares the steps to install the Oracle Instant Client ODBC driver and the unixODBC Driver Manager on macOS.

ODBC is an open specification for accessing databases. The Oracle ODBC driver for Oracle Database enables ODBC applications to connect to Oracle Database. In addition to standard ODBC functions, users can leverage Oracle specific features for high performance data access.

Install the unixODBC Driver Manager
  • Download unixODBC from ftp.unixodbc.org/pub/unixODBC. I used unixODBC-2.3.1.tar.gz.

  • Extract the package:

    tar -zxvf unixODBC-2.3.1.tar.gz
  • Configure unixODBC:

    cd unixODBC-2.3.1 ./configure

    Note if you use the configure option "--prefix" to install into locations other than the default directory (/usr/local) then macOS's SIP features may prevent the unixODBC libraries being located correctly by the ODBC driver.

  • Build and install unixODBC:

    make sudo make install
Install the Oracle ODBC Driver
  • Download the Oracle 12.2 Instant Client Basic and ODBC packages from Instant Client Downloads for macOS (Intel x86).

    To reduce the installation size, the Basic Light package be used instead of Basic, if its character sets and languages are sufficient.

  • Extract both ZIP files:

    unzip instantclient-basic-macos.x64-12.2.0.1.0-2.zip unzip instantclient-odbc-macos.x64-12.2.0.1.0-2.zip

    This will create a subdirectory instantclient_12_2

  • The Oracle Instant Client libraries need to be in the macOS library search path, generally either in /usr/lib/local or in your home directory under ~/lib. I did:

    mkdir ~/lib cd instantclient_12_2 ln -s $(pwd)/libclntsh.dylib.12.1 $(pwd)/libclntshcore.dylib.12.1 ~/lib
  • With version 12.2, a small patch to the driver name in instantclient_12_2/odbc_update_ini.sh is required on macOS. I changed line 101 from:

    SO_NAME=libsqora.so.12.1

    to

    SO_NAME=libsqora.dylib.12.1
  • Run the configuration script

    cd instantclient_12_2 sudo odbc_update_ini.sh /usr/local sudo chown $USER ~/.odbc.ini

    This creates a default DSN of "OracleODBC-12c"

  • Edit the new ~/.odbc.ini configuration file and add the Oracle Database connection string. My database is running on the same machine as ODBC (inside a VirtualBox VM) and has a service name of 'orclpdb', so my connection string is 'localhost/orclpdb'. I changed:

    ServerName =

    to

    ServerName = localhost/orclpdb
Verify the installation

Run the isql utility to verify installation. Pass in the DSN name, and an existing database username and password:

$ isql OracleODBC-12c scott tiger +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL>

You can execute SQL statements and quit when you are done.

Test Program

To test a program that makes ODBC calls, download odbcdemo.c.

  • Edit odbcdemo.c and set the USERNAME and PASSWORD constants to the database credentials.

  • Build it:

    gcc -o odbcdemo -g -lodbc odbcdemo.c
  • Run it

    ./odbcdemo

The output will be like:

Connecting to the DB .. Done Executing SQL ==> SELECT SYSDATE FROM DUAL Result ==> 2018-02-21 02:53:47 Summary

ODBC is a popular API for accessing databases. The Oracle ODBC Driver is the best way to access Oracle Database.

Resources

Using the Oracle ODBC Driver.

Oracle ODBC Drivers

Discussion Forum

Oracle Instant Client ODBC Release Notes

Instant Client Downloads

Installing XAMPP for PHP and Oracle Database

Thu, 2018-02-22 22:19

Today's guest post comes from Tianfang Yang who's been working with the Oracle Database extensions for PHP.

This post shows how to install XAMPP on Windows to run PHP applications that connect to a remote Oracle Database.

XAMPP is an open source package that contains Apache, PHP and many PHP 'extensions'. One of these extension is PHP OCI8 which connects to Oracle Database.

To install XAMPP:

  1. Download "XAMPP for Windows" and follow the installer wizard. I installed into my D: drive.

  2. Start the Apache server via the XAMPP control panel.


    screenshot of XAMPP control panel
  3. Visit http://localhost/dashboard/phpinfo.php via your browser to see the architecture and thread safety mode of the installed PHP. Please note this is the architecture of the installed PHP and not the architecture of your machine. It’s possible to run a x86 PHP on an x64 machine.


    screenshot of PHP configuration showing the PHP OS architecture as x86
  4. [Optional] Oracle OCI8 is pre-installed in XAMPP but if you need a newer version you can download an updated OCI8 PECL package from pecl.php.net. Pick an OCI8 release and select the DLL according to the architecture and thread safety mode. For example, if PHP is x86 and thread safety enabled, download "7.2 Thread Safe (TS) x86". Then replace "D:\xampp\php\ext\php_oci8_12c.dll" with the new "php_oci8_12c.dll" from the OCI8 PECL package.


    screenshot of PECL OCI8 download page

  5. Edit "D:\xampp\php\php.ini" and uncomment the line "extension=oci8_12c". Make sure "extension_dir" is set to the directory containing the PHP extension DLLs. For example,

    extension=oci8_12c extension_dir="D:\xampp\php\ext"
  6. Download the Oracle Instant Client Basic package from OTN.

    Select the correct architecture to align with PHP's. For Windows x86 download "instantclient-basic-nt-12.2.0.1.0.zip" from the Windows 32-bit page.


    screenshot of Oracle Instant Client download page
  7. Extract the file in a directory such as "D:\Oracle". A subdirectory "D:\Oracle\instantclient_12_2" will be created.

    Add this subdirectory to the PATH environment variable. You can update PATH in Control Panel -> System -> Advanced System Settings -> Advanced -> Environment Variables -> System Variables -> PATH. In my example I set it to "D:\Oracle\instantclient_12_2".

  8. Restart the Apache server and check the phpinfo.php page again. It shows the OCI8 extension is loaded successfully.


    screenshot of PHP configuration page showing a section for OCI8

    If you also run PHP from a terminal window, make sure to close and reopen the terminal to get the updated PATH value.

  9. To run your first OCI8 application, create a new file in the XAMPP document root "D:\xampp\htdocs\test.php". It should contain:

    <?php error_reporting(E_ALL); ini_set('display_errors', 'On'); $username = "hr"; // Use your username $password = "welcome"; // and your password $database = "localhost/orclpdb"; // and the connect string to connect to your database $query = "select * from dual"; $c = oci_connect($username, $password, $database); if (!$c) { $m = oci_error(); trigger_error('Could not connect to database: '. $m['message'], E_USER_ERROR); } $s = oci_parse($c, $query); if (!$s) { $m = oci_error($c); trigger_error('Could not parse statement: '. $m['message'], E_USER_ERROR); } $r = oci_execute($s); if (!$r) { $m = oci_error($s); trigger_error('Could not execute statement: '. $m['message'], E_USER_ERROR); } echo "<table border='1'>\n"; $ncols = oci_num_fields($s); echo "<tr>\n"; for ($i = 1; $i <= $ncols; ++$i) { $colname = oci_field_name($s, $i); echo " <th><b>".htmlspecialchars($colname,ENT_QUOTES|ENT_SUBSTITUTE)."</b></th>\n"; } echo "</tr>\n"; while (($row = oci_fetch_array($s, OCI_ASSOC+OCI_RETURN_NULLS)) != false) { echo "<tr>\n"; foreach ($row as $item) { echo "<td>"; echo $item!==null?htmlspecialchars($item, ENT_QUOTES|ENT_SUBSTITUTE):"&nbsp;"; echo "</td>\n"; } echo "</tr>\n"; } echo "</table>\n"; ?>

    You need to edit this file and set your database username, password and connect string. If you are using Oracle Database XE, then the connect string should be "localhost/XE".

    The SQL query can also be changed. Currently it queries the special DUAL table, which every user has.

  10. Load the test program in a browser using http://localhost/test.php. The output will be the single value "X" in the column called "DUMMY".


You can read more about PHP OCI8 in the PHP manual, and in the free Underground PHP and Oracle Manual from Oracle.

Enjoy your coding with OCI8!

node-oracledb 2.1 is now available from npm

Wed, 2018-02-14 17:12

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

Top features: Privileged connections such as SYSDBA. A new 'queryStream()' Stream 'destroy()' method

After the big refactoring of node-oracledb 2.0, we're pleased to push node-oracledb forward with the release of 2.1.0.

 

Changes in node-oracledb 2.1.0 include:

  • Support for SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSDG, SYSKM, and SYSRAC privileges in standalone connections. You can now connect like:

    oracledb.getConnection( { user: 'sys', password: 'secret', connectString: 'localhost/orclpdb', privilege: oracledb.SYSDBA }, function(err, connection) { if (err) console.error(err); else console.log('I have power'); } // . . . );
  • The internal implementations of 'direct fetches' and 'queryStream()' have been simplified by reusing the ResultSet changes of 2.0. You might notice speedups in some cases.

  • The 'queryStream()' result Stream now supports Node 8's destroy() method, which obsoletes the previous experimental '_close()' method of node-oracledb.

    If you are using '_close()' in Node 8 or later, you should:

    • Change the method name from '_close()' to 'destroy()'.

    • Stop passing a callback.

    • Optionally pass an error.

  • Improved the Error object with new 'errorNum' and 'offset' properties. The properties will be included when you get errors from Oracle Database such as this:

    connection.execute( "select wrong_col from departments", function(err, result) { if (err) console.error(err); else console.log(result.rows); });

    The output is:

    { Error: ORA-00904: "WRONG_COL": invalid identifier errorNum: 904, offset: 7 }

    The offset is relative to the start of the SQL statement. For non SQL errors, the offset will be 0.

  • New 'versionSuffix' and 'versionString' properties to the oracledb object to aid showing the release status and version. The 'versionSuffix' attribute will be an empty string for production releases, but may be something like '-dev' or '-beta.1' for other builds.

    The existing 'version' attribute is great for runtime comparisons, but not as handy as the new 'versionString' attribute for displaying to users.

  • With a code contribution from Sagie Gur-Ari an annoying little gotcha was fixed. Now 'connectionString' is an alias for 'connectString'.

    oracledb.getConnection( { user: 'hr', password: 'welcome', connectionString: 'localhost/orclpdb', }, function(err, connection) { if (err) console.error(err); else console.log('I am HR'); } );
  • The underlying data access layer ODPI-C had some improvements which flow through to node-oracledb.

    • Connections can now be closed when ResultSets and Lobs are open. This removes the DPI-1054 error you might have seen if you closed resources in the 'wrong' order.

    • At connection close there is no longer an unconditional rollback sent to the database. Instead ODPI-C makes use of some internal state to avoid the overhead of the 'round trip' to the database when it is known that there is no open transaction.

  • Node-oracledb 2.1 no longer compiles with the long-obsolete Node 0.10 or 0.12 versions. We'd stopped testing and claiming support for these versions a while back, but technically compilation had been possible until now. Updates to NAN API usage and other cleanups have lead to this change.

  • Our testing and documentation improved, as always.

See the CHANGELOG for other changes in node-oracledb 2.1

Finally, you may be interested to know about these cool new resources:

Resources

Node-oracledb installation instructions are here.

Node-oracledb documentation is here.

Node-oracledb change log is here.

For information on migrating see Migrating from node-oracledb 2.0 to node-oracledb 2.1.

Issues and questions about node-oracledb can be posted on GitHub.

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

PHP and Oracle DB: RPMs for Easy Install on Linux

Tue, 2018-02-06 22:05

The Oracle Linux team is now making updated PHP RPMs available on yum.oracle.com.  The good news: an RPM for the PHP OCI8 extension is included.  Yay!  My colleague, Sergio, has a blog post Connect PHP 7.2 to Oracle Database 12c using Oracle Linux Yum Server with the details.

Node-oracledb v2 Query Methods and Fetch Tuning

Thu, 2017-12-14 01:09
Computer screen showing random javascript code (Photo by Markus Spiske on unsplash.com)

 

For our Node.js node-oracledb v2 add-on for Oracle Database we revamped its lower data access layer and made it into a standalone project ODPI-C, which is being reused by several other language APIs. ODPI-C gave us a reason and opportunity to simplify some of internal query handling code in node-oracledb.

To recap, node-oracledb has four ways to execute queries against an Oracle Database. These are the same in version 1 and version 2:

  • Direct Fetches - these are non-ResultSet, non- queryStream() fetches. All rows are returned in one big array, limited to maxRows (v2 allows an unlimited array size).

  • ResultSet getRow() - return one row on each call until all rows are returned.

  • ResultSet getRows(numRows) - return batches of rows in each call until all rows are returned.

  • queryStream() - stream rows until all rows are returned.

The changes in node-oracledb v2 are:

  • Enhanced direct fetches to allow an unlimited number of rows to be fetched, and made this the default. This occurs when maxRows = 0

  • Replaced prefetchRows (previously used for internal fetch buffering and tuning) with a new property fetchArraySize; the default size is 100.

    fetchArraySize affects direct fetches, ResultSet getRow() and queryStream().

  • getRows(numRows,...) internal fetch buffering is now only tuned by the numRows value. Previously prefetchRows could also affect internal buffering.

  • queryStream() now use fetchArraySize for internal buffer sizing.

  • Implemented getRow() in JavaScript for better performance and use fetchArraySize as the way to tune internal buffer sizes.

The change in v2 that I want to discuss is how a 'direct fetch' does its internal buffering.

To make fetching an unlimited number of rows feasible for direct fetches, data is now internally fetched from Oracle Database in batches of size fetchArraySize and then concatenated to form one big array of results that is returned in the callback. This lets you use fetchArraySize to tune fetch performance. In node-oracledb v1, one big array of size maxRows was allocated before the data was fetched from the database. (With node-oracledb v2 you can have the same behavior, if you really want it, by setting fetchArraySize = maxRows, where maxRows > 0).

Let's look at two different scenarios that fetch 50000 rows with fetchArraySize of 100 (the default) and 1000. The code is at the end of the post.

Direct fetch: rows: 50000, batch size: 100, seconds: 4.054 Direct fetch: rows: 50000, batch size: 1000, seconds: 1.643

You can see in this case (with a local database) that increasing fetchArraySize improved performance. There may be various factors for this, but the common one is reduced network cost and reduced load on the database because there were fewer 'round trips' to get batches of records. Each query and environment will be different, and require its own tuning.

The benefits of using fetchArraySize for direct fetches are:

  • Performance of batching and network transfer of data can be tuned.

  • Memory can incrementally grow when the number of query rows is unknown, or varies from execution to execution. A single large chunk of memory (based on maxRows in v1) doesn't need to pre-allocated to handle the 'worst case' of a large number of rows.

There are two drawbacks with direct fetches:

  • One big array of results is needed. This is the same in v1 and v2.

  • The concatenation of batches of records can use more memory than the final array requires, and can cause fragmentation.

Let's look at timings for all query methods. This is one run; there was expected variability each time I ran the scripts. The 'batch size' number is numRows for getRows(numRows) calls, or fetchArraySize for the other fetch methods.

Direct fetch: rows: 50000, batch size: 100, seconds: 4.054 ResultSet getRow(): rows: 50000, batch size: 100, seconds: 1.625 ResultSet getRows(): rows: 50000, batch size: 100, seconds: 1.586 queryStream(): rows: 50000, batch size: 100, seconds: 1.691 Direct fetch: rows: 50000, batch size: 1000, seconds: 1.643 ResultSet getRow(): rows: 50000, batch size: 1000, seconds: 1.471 ResultSet getRows(): rows: 50000, batch size: 1000, seconds: 1.327 queryStream(): rows: 50000, batch size: 1000, seconds: 1.415

The ResultSet and queryStream() methods don't have to store all rows in memory at once so there is less memory management involved. The outlier is obviously the first result: the memory management of concatenating small chunks of memory together is big. We have some ideas on what we can do inside node-oracledb to improve this a bit, but that is a future project to be investigated, and can't solve the first drawback that all rows eventually have to be held in memory at the same time.

The conclusion is to use ResultSets or streaming for large numbers of rows. This is the same recommendation we gave for v1.

For small numbers of rows, the various query methods perform pretty much the same. The timings are so short that you can treat the differences in the one run shown below as noise. Here each query only returned 1 row:

Direct fetch: rows: 1, batch size: 100, seconds: 0.011 ResultSet getRow(): rows: 1, batch size: 100, seconds: 0.012 ResultSet getRows(): rows: 1, batch size: 100, seconds: 0.013 queryStream(): rows: 1, batch size: 100, seconds: 0.013 Direct fetch: rows: 1, batch size: 1, seconds: 0.012 ResultSet getRow(): rows: 1, batch size: 1, seconds: 0.012 ResultSet getRows(): rows: 1, batch size: 1, seconds: 0.013 queryStream(): rows: 1, batch size: 1, seconds: 0.013

Although the timings are small, I suggest using a small fetchArraySize or numRows if you are querying a small handful of rows, particularly if the number of rows is known (such as 1). This reduces the amount of memory that needs to be allocated throughout node-oracledb, the Oracle client libraries, and also in the database.

References

Node.oracledb documentation is here.

If you are currently using node-oracledb v1, you may be interested in the documentation on Migrating from node-oracledb 1.13 to node-oracledb 2.0.

Code

Here are the rudimentary test scripts I used. The ResultSet code originated in the v1 examples from https://jsao.io/2015/07/an-overview-of-result-sets-in-the-nodejs-driver/

The config.js file is at the end. The dbconfig.js file is the same as in the examples.

The timings include statement execution in the DB, though this is not controlled by node-oracledb. With direct fetches there isn't a way in JavaScript to distinguish the query execute cost from the data fetch costs that fetchArraySize and numRows control.

Direct Fetch

// direct fetch var oracledb = require('oracledb'); var dbConfig = require('./dbconfig.js'); var config = require('./config.js'); oracledb.getConnection( dbConfig, function(err, connection) { if (err) throw err; var rowsProcessed = 0; var startTime = Date.now(); connection.execute( 'select * from all_objects where rownum <= :mr', [ config.maxRows ], { fetchArraySize: config.batchSize }, function(err, results) { if (err) throw err; rowsProcessed = results.rows.length; // do work on the rows here var t = ((Date.now() - startTime)/1000); console.log('Direct fetch: rows: ' + rowsProcessed + ', batch size: ' + config.batchSize + ', seconds: ' + t); connection.release(function(err) { if (err) console.error(err.message); }); }); });

ResultSet getRow()

// ResultSet getRow() var oracledb = require('oracledb'); var dbConfig = require('./dbconfig.js'); var config = require('./config.js'); oracledb.getConnection( dbConfig, function(err, connection) { if (err) throw err; var rowsProcessed = 0; var startTime = Date.now(); connection.execute( 'select * from all_objects where rownum <= :mr', [ config.maxRows ], { resultSet: true, fetchArraySize: config.batchSize }, function(err, results) { if (err) throw err; function processResultSet() { results.resultSet.getRow(function(err, row) { if (err) throw err; if (row) { rowsProcessed++; // do work on the row here processResultSet(); // try to get another row from the result set return; // exit recursive function prior to closing result set } var t = ((Date.now() - startTime)/1000); console.log('ResultSet getRow(): rows: ' + rowsProcessed + ', batch size: ' + config.batchSize + ', seconds: ' + t); results.resultSet.close(function(err) { if (err) console.error(err.message); connection.release(function(err) { if (err) console.error(err.message); }); }); }); } processResultSet(); } ); } );

ResultSet getRows()

// ResultSet getRows() var oracledb = require('oracledb'); var dbConfig = require('./dbconfig.js'); var config = require('./config.js'); oracledb.getConnection( dbConfig, function(err, connection) { if (err) throw err; var rowsProcessed = 0; var startTime = Date.now(); oracledb.fetchArraySize = 1; connection.execute( 'select * from all_objects where rownum <= :mr', [ config.maxRows ], { resultSet: true }, function(err, results) { var rowsProcessed = 0; if (err) throw err; function processResultSet() { results.resultSet.getRows(config.batchSize, function(err, rows) { if (err) throw err; if (rows.length) { rows.forEach(function(row) { rowsProcessed++; // do work on the row here }); processResultSet(); // try to get more rows from the result set return; // exit recursive function prior to closing result set } var t = ((Date.now() - startTime)/1000); console.log('ResultSet getRows(): rows: ' + rowsProcessed + ', batch size: ' + config.batchSize + ', seconds: ' + t); results.resultSet.close(function(err) { if (err) console.error(err.message); connection.release(function(err) { if (err) console.error(err.message); }); }); }); } processResultSet(); }); });

queryStream()

// queryStream() var oracledb = require('oracledb'); var dbConfig = require('./dbconfig.js'); var config = require('./config.js'); oracledb.getConnection( dbConfig, function(err, connection) { if (err) throw err; var rowsProcessed = 0; var startTime = Date.now(); var stream = connection.queryStream( 'select * from all_objects where rownum <= :mr', [ config.maxRows ], { fetchArraySize: config.batchSize } ); stream.on('data', function (data) { // do work on the row here rowsProcessed++; }); stream.on('end', function () { var t = ((Date.now() - startTime)/1000); console.log('queryStream(): rows: ' + rowsProcessed + ', batch size: ' + config.batchSize + ', seconds: ' + t); connection.close( function(err) { if (err) { console.error(err.message); } }); }); });

The Configuration File

// config.js var maxRows; // number of rows to query var batchSize; // batch size for fetching rows maxRows = 50000; batchSize = 1000 exports.maxRows = maxRows; exports.batchSize = batchSize;

node-oracledb 2.0 with pre-built binaries is on npm

Thu, 2017-12-14 00:54

Release announcement: Node-oracledb 2.0.15, the Node.js add-on for Oracle Database, is now on npm for general use.

Top features: Pre-built binaries, Query fetch improvements

It's time to shake things up. Node-oracledb version 1 has been stable for a while. Our tight, hard working team is now proud to deliver node-oracledb 2 to you. With improvements throughout the code and documentation, this release is looking great. There are now over 3000 functional tests, as well as solid stress tests we run in various environments under Oracle's internal testing infrastructure.

Review the CHANGELOG for all changes. For information on migrating see Migrating from node-oracledb 1.13 to node-oracledb 2.0.

node-oracledb v2 highlights
  • node-oracledb 2.0 is the first release to have pre-built binaries. These are provided for convenience and will make life a lot easier, particularly for Windows users.

    Binaries for Node 4, 6, 8 and 9 are available for Windows 64-bit, macOS 64-bit, and Linux 64-bit (built on Oracle Linux 6).

    Simply add oracledb to your package.json dependencies or manually install with:

    npm install oracledb

    (Yes, Oracle Client libraries are still required separately - these do all the heavy lifting.)

    We've got great error messages when a binary isn't available, and improved the messages when require('oracledb') fails, however Linux users with older glibc libraries may get Linux runtime errors - you should continue building node-oracledb from source in this case, see below.

    There is support for hosting binary packages on your own internal web server, which will be great for users with a large number of deployments. See package/README.

    This is the first release to use the ODPI-C abstraction layer which is also used by Python's cx_Oracle 6.x API, as well as 3rd party APIs for other languages. Using ODPI is the the main change that allowed node-oracledb 2.0 binaries to be distributed. As another consequence of ODPI-C, any node-oracledb 2 binary will run with any of the Oracle client 11.2, 12.1 or 12.2 libraries without needing recompilation. This improves portability when node-oracledb builds are copied between machines. Since the available Oracle functionality will vary with different Oracle Client (and Oracle Database!) versions, it's important to test your applications using your intended deployment environment.

  • The driver can still be built from source code if you need, or want, to do so. Compiling is now simpler than in version 1, since you no longer need Oracle header files, and no longer need OCI_*_DIR environment variables.

    To build from source you need to pull the code from a GitHub branch or tag - generally the most recent release tag is what you want. Make sure you have Python 2.7, the 'git' utility, and a compiler, and add oracle/node-oracledb.git#v2.0.15 to your package.json dependencies. Or manually run the install:

    npm install oracle/node-oracledb.git#v2.0.15

    Users without 'git', or with older versions of Node that won't pull the ODPI submodule code, can use the source package:

    npm install https://github.com/oracle/node-oracledb/releases/download/v2.0.15/oracledb-src-2.0.15.tgz

    I've noticed GitHub can be a little slow to download the source before compilation can begin, so bear with it.

  • Improved query handling:

    • Enhanced direct fetches to allow an unlimited number of rows to be fetched and changed the default number of rows fetched by this default method to be unlimited. The already existing ResultSet and Streaming methods are still recommended for large numbers of rows.

    • Since ODPI-C internally uses 'array fetches' instead of 'prefetching' (both are underlying methods for buffering/tuning differing only in where the buffering takes place - both are transparent to applications), we've replaced prefetchRows with a new, almost equivalent property fetchArraySize..

    • We've moved the buffering or rows for getRow() into JavaScript for better performance. It no longer needs to call down into lower layers as often.

  • We tightened up some resource handling to make sure applications don't leak resources. If you inadvertently try to close a connection when a LOB or ResultSet is open, you will see a new error DPI-1054.

  • The node-oracledb size restrictions on LOB fetchAsString and fetchAsBuffer queries, and also on LOB binds. In node-oracledb 1 these were particularly low when Oracle 11gR2 client libraries were used, so this should be welcome for people who have not updated the Oracle client. Node.js and V8 will still limit sizes, so continue to use the Stream interface for large LOBs.

  • Added support for ROWID and UROWID. Data is fetched as a String

  • Added support for fetching columns types LONG (as String) and LONG RAW (as Buffer).

  • Added support for TIMESTAMP WITH TIME ZONE date type. These are mapped to a Date object in node-oracledb using LOCAL TIME ZONE. The TIME ZONE component is not available in the Date object.

  • Added query support for NCHAR, NVARCHAR2 and NCLOB columns. Binding for DML may not insert data correctly, depending on the database character set and the database national character set.

Plans for Version 1

Our stated plan was to cease formal support for version 1 when Node 4 LTS maintenance ended in April 2018. We're pleased 1.13.1 has been stable for some time, and we are not anticipating needing any further node-oracledb 1 releases, unless exceptional circumstances occur.

Plans for Version 2

We are now testing with Node 4, 6, 8 and 9. This list will, of course, change as new Node versions are released. The pre-built binaries will also change and availability is not guaranteed.

ODPI-C forms a solid base to extend node-oracledb. Users of Python cx_Oracle 6, which is also based on ODPI-C, are appreciating all the advanced Oracle features that are available. Many of these features have also been requested by node-oracledb users. As with any open source project, there are no hard and fast guarantees for node-oracledb, but you can see the general direction we'd like to head in. Pull Requests are welcome.

One little thing you might be unaware of is that along the way we have been testing (and helping create) the next major release of Oracle Database, so sometimes we've had to take detours from direct node-oracledb work order to move the whole of Oracle along. Whether we work on the "client" or the "server", we look forward to bringing you great things in future releases.

Resources

Node-oracledb installation instructions are here.

Node-oracledb documentation is here.

Node-oracledb change log is here.

For information on migrating see Migrating from node-oracledb 1.13 to node-oracledb 2.0.

Issues and questions about node-oracledb can be posted on GitHub.

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

cx_Oracle 6.1 with Oracle Database Sharding Support is now Production on PyPI

Tue, 2017-12-12 14:45

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

cx_Oracle logo

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

In the words of the creator and maintainer, Anthony Tuininga: The cx_Oracle 6.1 release has a number of enhancements building upon the release of 6.0 made a few months ago. Topping the list is support for accessing sharded databases via new shardingkey and supershardingkey parameters for connections and session pools. Support for creating connections using the SYSBACKUP, SYSDG, SYDKM and SYSRAC roles was also added, as was support for identifying the id of the transaction which spawned a subscription message. For those on Windows, improved error messages were created for when the wrong architecture Oracle Client is in the PATH environment variable. Improvements were also made to the debugging infrastructure and a number of bugs were squashed. The test suite has also been expanded. See the full release notes for more information.

cx_Oracle References

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

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

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

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

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

ODPI-C 2.1 is now available for all your Oracle Database Access Needs

Tue, 2017-12-12 13:56
ODPI-C logo ODPI-C

Anthony Tuininga has just released version 2.1 of the Oracle Database Programming Interface for C (ODPI-C) 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++.

Key new feature: support for accessing sharded databases.

 

In the words of Anthony: This release has a number of small enhancements intended to build upon the release of 2.0 made a few months ago. Topping the list is support for accessing sharded databases, a new feature in Oracle Database 12.2. Support for creating connections using the SYSBACKUP, SYSDG, SYDKM and SYSRAC roles was also added, as was support for identifying the id of the transaction which spawned a subscription message. For those on Windows, improved error messages were created for when the wrong architecture Oracle Client is in the PATH environment variable. Improvements were also made to the debugging infrastructure. The test suite has also been expanded considerably. A number of bugs were squashed to improve ODPI-C and in preparation for the upcoming releases of cx_Oracle 6.1 and node-oracledb 2.0, both of which use ODPI-C. See the full release notes for more information.

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

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

Pages