Section 7.2. What Is OCI?

7.2 What Is OCI?

As we've discussed, Oracle's Oracle Call Interface is the comprehensive API that is used to connect internally to the Oracle database server. Here is a sampling of what OCI has to offer. Oracle::OCI allows Perl programs to access all of these capabilities; in a few cases, we'll note what Oracle::OCI 's interface offers us over that provided historically by Perl DBI:

• OCI provides tight low-level control over all aspects of program flow, from server connections to the management of networked transactions, all accomplished in a highly efficient and scalable way.

• OCI's dynamic structures can define virtually any arbitrary data structure.

• OCI provides a complete metadata feature set, enabling drill-down discoveries on the database's entire structural architecture.

• OCI offers asynchronous event notification. This feature allows program clients to register an interest in such notifications and the ability to propagate messages, enabling domino effects to ripple through a system.

• OCI gives us enhanced DML (Data Manipulation Language) capabilities, including the ability to do direct data loading (this is similar to what can be done with SQL*Loader). This feature is particularly useful for applications that need to fill data warehouses under tight time constraints.

• Using OCI directly, Perl-based applications can service an increased number of users and requests without requiring an additional hardware investment. OCI does this by reducing SQL round-trips, using piggy -backing processes, and sharing logins and transactions. User handling can be considerably simplified.

• OCI can manipulate large objects in chunks and streams. Although binary large object (BLOB) features are available within standard Perl DBI, if you need fine-grained LOB access via Perl, Oracle::OCI is the way to go. For instance, if a BLOB contains XML data (as many applications now do), Oracle::OCI provides the perfect way to parse this data. (For more information, see the discussion of data munging with XML in Appendix D.)

• OCI offers us a back-stage pass into Oracle's tactical core . For instance, it can perform such complex underlying activities as cache pinning, advanced queuing, and parallel server management.

• OCI provides access to the latest Oracle object development techniques and many of its data transformations ” for example, string substitution, decoding, and so on. These aren't available using a more generic API, such as basic Perl DBI or ODBC.

• OCI provides all of the capabilities summarized here with high performance and thread safety as a consequence of its fine- tuned low-level optimization.

The interleaved relationship between Oracle::OCI and Perl DBI/ DBD::Oracle ( illustrated earlier in Figure 7-1) also allows us to mix the calls to either API and to reuse handles and object instances. This is impossible in languages other than Perl. You'd either have to use reams of pure OCI or choose an alternative interface at a much higher level (for example, ODBC). There is no way to work on the middle ground in between the two. In Perl, however, you can get the best of both worlds .

Most DBAs will never need the low-level capabilities offered by OCI and available via the Oracle::OCI interface. If you are in this category, you can safely ignore this chapter. However, if you do need to include any of the functionality listed previously in your own applications, and Perl DBI falls just short of your personal summit (or if you are just curious about what all the excitement is about), then please read on.

7.2.1 Why Oracle::OCI Instead of C?

Let's assume that you're convinced now that OCI is a great thing. But why choose Oracle::OCI to build your applications? Why not just use C, the traditional choice of the professional? To convince the jury, let's take a brief look at what we needed to be able to do in order to write effective OCI programs before Oracle::OCI arrived on the scene:

1. We had to be fully competent in our chosen 3GL. For example, in C, you had to be comfortable with pointers, voids, casts, and the asterisk-laden shooting match, which is what drove many wizened C programmers over to Perl in the first place. ^[1] (Witnessing a thousand lines of difficult C code being shrunk to ten of Perl for the first time, without spotting the dreaded malloc anywhere , was divine revelation for many.)

   ^[1] At least one of your authors still has nightmares about linked lists.

2. You probably had to write huge source code files for even trivial jobs. (Even logging on, within OCI, can take pages of code, as we'll witness shortly.) The point of Perl DBI was to be the tip of an iceberg, to hide the gory details of OCI behind a simple API. It was also able to provide easy Perl-based access to all of the other hundreds of Perl modules available out there (e.g., Apache::DBI , DBD::Chart , Perl/Tk, etc.). When encountering a situation that really did require that low-level OCI functionality, many people who had become downright comfortable with Perl had to throw all of that advantage away, and begin again with their dusted-down Kernighan and Ritchie. ^[2]

   ^[2] The classic text for C is The C Programming Language , by Brian W. Kernighan and Dennis M. Ritchie (Prentice Hall); it is surely one of the finest technical books of all time.

3. You needed to compile the source files down to object code with a native compiler, and link it to the OCI libraries, thereby making the final application machine-dependent . This seemed a shame, because OCI is the most widely available interface for connecting Oracle to the outside world. And porting 3GL code to other systems, even if you're a believer in strict ANSI C, is more than a trivial afterthought ( especially if like Gulliver on his travels , you get your Big-Endians mixed up with your Lilliputians).

The 3GL compilation process is illustrated in Figure 7-3. It works, but it's certainly not ideal. It would be nice to overcome this one-way track to binary-only solutions. It would be great if we could write shorter, machine-independent OCI programs, in clear understandable Perl code.

That's what this chapter is all about.

Figure 7-3. Constructing 3GL OCI applications

7.2.2 For More Information on OCI

We've introduced OCI, but there is much more to learn. At last count (in OCI 8.1), there were 530 distinct functions! We have found the following resources to be the most useful; note that most of these references are to the very helpful Oracle Technology Network (OTN).

The guiding aim of the Oracle::OCI project is to keep synchronized with OCI itself and thus to ensure that the official Oracle Corporation OCI documentation always remains simultaneously the documentation for Oracle::OCI .

http://technet.oracle.com:

Main technical reference for all Oracle products. Once you've set up a free login user, search with the string "OCI" and you should get access to a great many useful references.

http://technet.oracle.com/tech/oci:

Good general reference kick-off point for drilling down into OCI.

http://otn.oracle.com/tech/oci/htdocs/faq.html:

Comprehensive FAQ.

http://www.orafaq.org/faqoci.htm:

Another more independent, FAQ.

7.2.3 OCI Functions

Basically, if there is an OCI function supplied by Oracle Corporation within your local version of OCI (the one that comes with your database), then you can assume that once we build Oracle::OCI , there will be a corresponding function available for use within Perl. See Figure 7-2 for a diagrammatic representation of this one-to-one mapping.

OCI functions can be broken down into four main categories as follows . Because there are so many OCI functions, we haven't attempted to list them all. For all but the second category (where there are only four functions in all), we've simply provided examples of the most common functions. Check out the documentation listed in the previous section for much more.

OCI relational functions

These OCI functions are the common functions used to deal with the normal operations of a relational database, such as logging on, executing statements, managing database access, processing SQL statements, and so on. We provide some examples of these in Table 7-1.

OCI external procedure functions

These OCI functions are used to connect with extproc_plsql , a module we describe in Chapter 8, and with other external C libraries. These functions are listed in Table 7-2.

OCI navigational and type functions

These OCI functions are used to navigate between objects supplied by the Oracle Enterprise database server. Table 7-3 provides examples.

OCI datatype mapping and manipulation functions

These OCI functions supply data attribute manipulation functions for the Enterprise Server ” for example, string handling. Examples are provided in Table 7-4.

Table 7-1. OCI relational functions

Functional area	Example function
Advanced Queuing	OCIAQListen listens on queues for agents
Handles and descriptors	OCIDescriptorAlloc allocates and initializes a LOB locator
Bind and define	OCIStmtGetBindInfo gets the bind and indicator variables
Direct path loading	OCIDirPathFinish finishes and commits loaded data
Connect and authorize	OCIEnvCreate creates and initializes an OCI environment
Large objects	OCILobFileOpen opens LOB files
Statement handling	OCIStmtFetch fetches rows from queries
Thread management	OCIThreadCreate creates new threads
Transactions	OCITransRollback rolls back transactions
Miscellaneous	OCIBreak carries out an immediate asynchronous break

Table 7-2. OCI external procedure functions

OCI function	Description
OCIExtProcAllocCallMemory	Allocates memory for external procedures
OCIExtProcRaiseExcp	Raises PL/SQL exceptions
OCIExtProcRaiseExcpWithMsg	Raises exceptions along with a message
OCIExtProcGetEnv	Gets the handles detailing the OCI environment

Table 7-3. OCI navigational and type functions

Functional area	Example function
Flush and refresh	OCICacheRefresh refreshes pinned persistent objects
Mark cache objects	OCIObjectMarkDelete marks an object as deleted
Get object status	OCIObjectExists checks if an instance of an object exists
General navigation	OCIObjectGetObjectRef returns a reference to a given object
Pin, unpin , and free	OCIObjectPin pins objects in the cache
Type information	OCITypeByName gets Type Descriptor Objects (TDOs) by name

Table 7-4. OCI datatype mapping functions

Functional Area	Example function
Collectors and iterators	OCIIterDelete deletes an iterator
Date functions	OCIDateAddDays adds or subtracts days
Number functions	OCINumberAbs works out an absolute value
Raw functions	OCIRawAllocSize allocates raw memory
REF functions	OCIRefIsEqual compares two REFs for equality
String functions	OCIStringAssignText assigns text to a string
Table functions	OCITableFirst returns the first index of a table