Exposing the Inner Workings of the World's Most Powerful Database

Category Archives: Uncategorized

It’s that time again. Collaborate 2015 is fast approaching and, I have to say, I’m pretty damn psyched! This is the first time my personal company, NEXTGRES, will be demonstrating its game-changing database compatibility features to Oracle DBAs, developers, and users. I’d love for anyone reading my blog to drop by, whether it’s just to say “hi” or to see what we’ve been up to; We’re on Main Street at Booth 956. Also, thanks to friend and Oracle performance colleague, Craig Shallahamer, I’ll be presenting a completely revamped version of my Life of an Oracle Query session, Visualizing Oracle Query Execution. That’s right, I’ve come up with a good way to visualize the entire execution of a query in real-time as it flows from the client, to the server, through each component of the server, and is returned back to the client. I look forward to seeing you there!

First, I’d like to apologize to our good friend SQLLIB. Those of you who have been working with the Oracle Database for some time will notice that, while it too is a common data access library, I’ve omitted it from this series of posts.

No, it’s not because of some personal vendetta against SQLLIB. In fact, I like SQLLIB (primarily for internals-related reasons), but I’ve excluded it because it’s not a user-oriented data access library and as such, I don’t consider it a first class citizen. And, for those of you who may find yourself asking, “what the hell is this SQLLIB thing Jonah’s going on about?”, it’s a library commonly used by the Oracle Precompiler suite, not something people develop directly against. Though, if there’s enough interest, I may do a post on SQLLIB in the future.

Regardless, in this series of articles, I’m going to discuss and demonstrate the evolution of Oracle’s UPI and OCI data access APIs. The articles are arranged as follows:

Article 1: The User Program Interface (UPI)

Article 2: The Oracle Call Interface Version 7 (OCI7)

Article 3: The Oracle Call Interface Version 8 (OCI8)

But, before we get too deep into UPI, we need to start at the beginning.

What is a Data Access Layer?
A data access layer is a programming interface which allows developers to write programs capable of interacting with a database. So, whether you’re using Toad, SQL Developer, SQL*Plus, or a custom application, each relies on a data access layer to talk to the Oracle Database. However, in this set of articles, we won’t be discussing standard data access APIs like ODBC or JDBC. Instead, we’ll be taking a low-level look at proprietary Oracle APIs.

Oracle-supported APIs can be classified into two sets, public and internal-only.

Public Oracle Data Access APIs
These are APIs which Oracle has documented, supported, and wants customers to use.

Public APIs include:

Oracle Call Interface (OCI)

Oracle C++ Call Interface (OCCI)

Standard APIs (ODBC, JDBC, .NET, OLE DB, …)

Private, internal-only Oracle Data Access APIs
These are APIs which Oracle uses internally, does not provide external documentation for, and does not want customers using for various reasons.

Private APIs include:

User Program Interface (UPI)

SQL Library Runtime (SQLLIB)

Even older stuff than this…

In this article, we’ll focus on the User Program Interface, as it has been the core of Oracle’s data access for some time.

The User Program Interface
While UPI was once a popular internal-only API used within Oracle, it is in a constant state of being phased out in favor of the Oracle Call Interface (OCI). Now, you may be asking, “why are you doing a blog on something that’s being phased out?” Well, I’m doing it for three reasons: 1. Over the years, quite a few people have asked me about it, 2. UPI is still in use, and 3. I think it’s good for people to understand the evolutionary progress of how things got to where they are now.

So, how are we going to see some basic UPI in action? Bring on the example!

A UPI-based Example Program
Because I strongly believe concepts are best demonstrated using an applied method, I’ve taken an old Oracle OCI demonstration program and turned it into a very basic interactive SQL utility that allows you to execute basic SQL queries. The code kinda sucks because I spent very little time cleaning up the original, merged using a different style, don’t do great error checking, and could do a few things more correctly. But, it’s still a basic working example.

However, before we get into the fun stuff, we have to deal with the legal issues.

The Inevitable Disclaimer
It must be noted that I haven’t (yet) worked for Oracle, had access to the source code, or can guarantee proper execution of this on your database version or platform. The following is based on years of personal observation and research and while I’ve tested it on 8i, 9i, 10g, and 11g, I make no guarantees. THE SOFTWARE AND EXAMPLES PROVIDED ON THIS PAGE ARE PROVIDED “AS IS” AND WITHOUT WARRANTY OF ANY KIND. THE AUTHOR DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES. THIS SOFTWARE IS PROVIDED FOR DEMONSTRATION PURPOSES ONLY AND IS NOT ENDORSED, VERIFIED, OR SUPPORTED BY ORACLE CORPORATION. THIS SOFTWARE SHOULD NOT BE USED IN A PRODUCTION ENVIRONMENT UNDER ANY CIRCUMSTANCES.

OK, now we can get to the fun stuff

Prerequisite Knowledge
This blog entry assumes that you have a basic understanding of the C programming language as well as a rough idea of data access concepts such as parsing, binding, defining, and execution.

OK, let’s start!

Including the Oracle C Data Types
Before we can use UPI, we must first include the Oracle platform type definitions, oratypes.h. If you’ve done any OCI programming, you’ve no doubt run across this file. This file, located in $ORACLE_HOME/rdbms/public, can easily be included using:

#include "oratypes.h" /* Oracle-declared Platform Types */

Next, we need the C complier to know something about UPI.

Including the UPI Definitions
In order for our program to make use of UPI, we must now include the UPI function prototypes and definitions, which are contained in a file called upidef.h.

While I’ve made several requests for a copy of Oracle’s official upidef.h, they have been true to their internal-only mission and have never graced me with a copy. Consequently, it has taken me almost ten years to collect and research only a subset of the entire API. However, looking (optimistically) toward the future, I’d like to avoid changing anything in my code should I eventually get a copy of the real upidef.h (hint, hint). Accordingly, I’ve named my version of the definitions identically, and included the header file as follows:

#include "upidef.h" /* Jonah's Spiffy UPI Definitions */

Now that we’ve included the UPI definitions and function prototypes, we’re ready to start building our program. The first step is to define an HDA.

Defining UPI’s Host Data Area Structure
Similar to the concept of opaque handles, UPI uses an old technique of having the developer allocate a block of data which is passed back-and-forth to UPI. Internally, UPI maps offsets within this block to members of a C structure. As for definition, we’re going to define an array containing 512 elements of type ub1 (unsigned char/uint8_t). Depending on the platform and alignment-related issues, the HDA structure may also be allocated using an array of ub1, ub2, or ub4 with array-length changes being made accordingly.

static ub1 hda[512] = { 0 }; /* Host Data Area for UPI */

Defining a Cursor Using UPI
In UPI, cursors are represented simply by a number. Accordingly, we define a cursor as an unsigned word.

static uword cnum; /* Cursor Number */

Logon to Oracle
Next, we need to make a connection to the Oracle database. UPI provides a couple different logon functions, but we’ll use upilog. upilog will connect to the orcl database and create a session using scott/tiger. During this process, the HDA structure will be updated to contain some information about this connection.

Opening a Cursor
Now that we’re connected to the server, we need to get ready to process a statement. However, before we can process a statement, we must open a cursor. UPI provides the upiopn call for this.

if (upiopn(&hda[0], &cnum, -1))

Parsing a SQL Statement
Now that the user has passed-in a query for processing, we need to parse the query. UPI provides several functions for parsing, including the upipse and upiosd functions. In our example, we’ll use upiosd, which parses the SQL statement and associates it with our cursor. In our usage, we’re telling UPI to defer the actual parse [using (ub4) 1] until we call a describe/execution function. Similarly, rather than specific version 6/7 mode, we’re telling the system to parse the statement in standard (default) mode [via (sword) 1].

Describing the Select-List
As we’re allowing the user to enter dynamic SQL queries, we need to describe the select-list prior to defining the output variables and fetching the data. UPI provides several functions for describing the select list, including the upidpr, upidqr, and upidsc functions. In our example, we’ll rely on upidsc, which returns the maximum size, datatype, column name, length of the column name, maximum display size, precision of numeric items, scale of numerics, whether null values are allowed, and a couple other things.

Defining Output Variables
The next step is to define the output variables for each select-list item in the query. To define output variables, UPI provides the upidfc, upidfn, and upidfps functions. In the example, we’ll use upidfn, which is a non-piecewise define for a select-list item by position. In our call, we’re making sure to pass reference to our indicator value for this item as well. Failure to do so will result in errors when attempting to fetch a column containing a NULL value.

Executing the Query
Now that we’ve defined our output variables, we’re ready to execute the query. The order of describe/define/execution can be different in different situations. Regardless, UPI provides several calls for execution including, upiefn, upiexe, and upiexn. In our example, we’ll use upiexe.

if (upiexe(&hda[0], cnum))

Fetching Data
So, we’ve defined our output variables and executed the statement. If all has gone well so-far, we can start fetching data. UPI provides several different calls including upifch and upifcn. We could also use upiefn (execute+fetch), but are instead going to use the simplest version, upifch. The upifch function performs a row-at-a-time fetch on our cursor.

while (upifch(&hda[0], cnum) == 0)

Conclusion
This has been a brief example of writing a simple application using Oracle’s UPI data access API. In the next two articles, you’ll see how this same application can be improved and made more descriptive using OCI7 and the current version of OCI, OCI8.

Full Program Listing
The following is the entire source code to the extremely simple interactive SQL client built using the User Program Interface. Given that Oracle still considers UPI proprietary, I will not be attaching nor sending my UPI definitions header file, upidef.h. If you’d like to actually run the program below, you can fairly easily recreate the UPI function prototypes based on the data types and argument ordering provided.

MySQL Server Emulation
Now that I’ve fully completed the MySQL server emulation component, something I discussed with the Oracle Data Access guys at OpenWorld, here’s a couple examples for you.

Say you have an application that runs on MySQL and you’d like to migrate it to Postgres, but don’t want to do any code changes. Well, if you’re using fairly standard ODBC/JDBC, you don’t have much to worry about. But what if it’s a PHP application using the mysql_* calls, or an application using the MySQL client libraries, or a third-party application you don’t have the code for? The answer is to use NEXTGRES Gateway.

NEXTGRES Gateway allows you migrate your data to another database transparently to the application. The general process for using NEXTGRES Gateway is as follows:

Migrate Data (MySQL to Postgres/Oracle/SQL Server)

Shutdown MySQL

Start NEXTGRES Gateway in MySQL Emulation mode and point it to the Postgres/Oracle/SQL Server data

Test

You’re done!

Unlike other databases which claim to be compatible, NEXTGRES Gateway allows you to migrate an application to another database server with no application changes.

In the following example, I’m using the native MySQL client to connect to a PostgreSQL 8.3 database. It’s important to note that no changes have been made to the MySQL client, it’s just connecting to NEXTGRES Gateway which is emulating the MySQL server by performing SQL syntax and protocol translation to Postgres.

For those that didn’t catch it, NEXTGRES Gateway performed a simple SQL translation from MySQL to Oracle syntax on:

SELECT table_name FROM all_tables LIMIT 5;

to

SELECT table_name FROM all_tables WHERE ROWNUM < 6;

If you want to see more, I’ll be happy to demonstrate MySQL, Postgres, and Oracle emulation at theSoutheastern Oracle Users Conference, February 24 & 25 in Charlotte, North Carolina. I’ll also be presenting my session, “Listening In: Passive Capture and Analysis of Oracle Network Traffic”. This session is designed to help you diagnose issues with and optimize applications for, the Oracle network protocol.

Post navigation

About Me

Jonah H. Harris
Ambler, PA

I'm an avid Oracle Database user and internals researcher since 1996. I'm intimately familiar with the inner-workings of every version of Oracle since version 7, particularly items related to performance, file formats, memory structures, and the network protocol. Oracle is a large, fast, and complex system which provides an endless array of things to learn.

Disclaimer

This site and its author are in no way affiliated with Oracle Corporation.