First, I\u2019d like to apologize to our good friend SQLLIB.\u00a0 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\u2019ve omitted it from this series of posts.<\/p>\n
No, it\u2019s not because of some personal vendetta against SQLLIB.\u00a0 In fact, I like SQLLIB (primarily for internals-related reasons), but I\u2019ve excluded it because it\u2019s not a user-oriented data access library and as such, I don\u2019t consider it a first class citizen.\u00a0 And, for those of you who may find yourself asking, \u201cwhat the hell is this SQLLIB thing Jonah\u2019s going on about?\u201d, it\u2019s a library commonly used by the Oracle Precompiler suite,\u00a0not something people develop directly against<\/a>.\u00a0 Though, if there\u2019s enough interest, I may do a post on SQLLIB in the future.<\/p>\n Regardless, in this series of articles, I\u2019m going to discuss and demonstrate the evolution of Oracle\u2019s UPI and OCI data access APIs.\u00a0 The articles are arranged as follows:<\/p>\n But, before we get too deep into UPI, we need to start at the beginning.<\/p>\n What is a Data Access Layer?<\/strong> Oracle-supported APIs can be classified into two sets, public and internal-only.<\/p>\n Public Oracle Data Access APIs<\/strong> Public APIs include:<\/p>\n Private, internal-only Oracle Data Access APIs<\/strong> Private APIs include:<\/p>\n In this article, we\u2019ll focus on the User Program Interface, as it has been the core of Oracle\u2019s data access for some time.<\/p>\n The User Program Interface<\/strong> So, how are we going to see some basic UPI in action?\u00a0 Bring on the example!<\/p>\n A UPI-based Example Program<\/strong> However, before we get into the fun stuff, we have to deal with the legal issues.<\/p>\n The Inevitable Disclaimer<\/strong> OK, now we can get to the fun stuff :-)<\/p>\n Prerequisite Knowledge<\/strong> OK, let\u2019s start!<\/p>\n Including the Oracle C Data Types<\/strong> Next, we need the C complier to know something about UPI.<\/p>\n Including the UPI Definitions<\/strong> While I\u2019ve made several requests for a copy of Oracle\u2019s official upidef.h, they have been true to their internal-only mission and have never graced me with a copy.\u00a0 Consequently, it has taken me almost ten years to collect and research only a subset of the entire API.\u00a0 However, looking (optimistically) toward the future, I\u2019d like to avoid changing anything in my code should I eventually get a copy of the real upidef.h (hint, hint).\u00a0 Accordingly, I\u2019ve named my version of the definitions identically, and included the header file as follows:<\/p>\n Now that we\u2019ve included the UPI definitions and function prototypes, we\u2019re ready to start building our program.\u00a0 The first step is to define an HDA.<\/p>\n Defining UPI\u2019s Host Data Area Structure<\/strong> Defining a Cursor Using UPI<\/strong> Logon to Oracle<\/strong> Opening a Cursor<\/strong> Parsing a SQL Statement<\/strong> Describing the Select-List<\/strong> Defining Output Variables<\/strong> Executing the Query<\/strong> Fetching Data<\/strong> Conclusion<\/strong> Full Program Listing<\/strong> UPI is fun! :)<\/p>\n","protected":false},"excerpt":{"rendered":" First, I\u2019d like to apologize to our good friend SQLLIB.\u00a0 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\u2019ve omitted it from this series of posts. No, it\u2019s not because of some personal vendetta against SQLLIB.\u00a0 In […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"yoast_head":"\n\n
\nA data access layer is a programming interface which allows developers to write programs capable of interacting with a database.\u00a0 So, whether you\u2019re using\u00a0Toad<\/a>,\u00a0SQL Developer<\/a>,\u00a0SQL*Plus<\/a>, or a custom application, each relies on a data access layer to talk to the Oracle Database.\u00a0 However, in this set of articles, we won\u2019t be discussing standard data access APIs like\u00a0ODBC<\/a>\u00a0or\u00a0JDBC<\/a>.\u00a0 Instead, we\u2019ll be taking a low-level look at proprietary Oracle APIs.<\/p>\n
\nThese are APIs which Oracle has documented, supported, and wants customers to use.<\/p>\n\n
\nThese are APIs which Oracle uses internally, does not provide external documentation for, and does not want customers using for various reasons.<\/p>\n\n
\nWhile 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).\u00a0 Now, you may be asking, \u201cwhy are you doing a blog on something that\u2019s being phased out?\u201d\u00a0 Well, I\u2019m 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\u2019s good for people to understand the evolutionary progress of how things got to where they are now.<\/p>\n
\nBecause I strongly believe concepts are best demonstrated using an applied method, I\u2019ve 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.\u00a0 The code kinda sucks because I spent very little time cleaning up the original, merged using a different style, don\u2019t do great error checking, and could do a few things more correctly.\u00a0 But, it\u2019s still a basic working example.<\/p>\n
\nIt must be noted that I haven\u2019t (yet) worked for Oracle, had access to the source code, or can guarantee proper execution of this on your database version or platform.\u00a0 The following is based on years of personal observation and research and while I\u2019ve tested it on 8i, 9i, 10g, and 11g, I make no guarantees.\u00a0\u00a0THE SOFTWARE AND EXAMPLES PROVIDED ON THIS PAGE ARE PROVIDED \u201cAS IS\u201d AND WITHOUT WARRANTY OF ANY KIND.\u00a0 THE AUTHOR DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.\u00a0 IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES.\u00a0\u00a0THIS SOFTWARE IS PROVIDED FOR DEMONSTRATION PURPOSES ONLY AND IS NOT ENDORSED, VERIFIED, OR SUPPORTED BY ORACLE CORPORATION.\u00a0 THIS SOFTWARE SHOULD NOT BE USED IN A PRODUCTION ENVIRONMENT UNDER ANY CIRCUMSTANCES.<\/strong><\/span><\/p>\n
\nThis 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.<\/p>\n
\nBefore we can use UPI, we must first include the Oracle platform type definitions, oratypes.h.\u00a0 If you\u2019ve done any OCI programming, you\u2019ve no doubt run across this file.\u00a0 This file, located in $ORACLE_HOME\/rdbms\/public, can easily be included using:<\/p>\n#include \"oratypes.h\" \/* Oracle-declared Platform Types *\/<\/pre>\n
\nIn 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.<\/p>\n#include \"upidef.h\" \/* Jonah's Spiffy UPI Definitions *\/<\/pre>\n
\nSimilar 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.\u00a0 Internally, UPI maps offsets within this block to members of a C structure.\u00a0 As for definition, we\u2019re going to define an array containing 512 elements of type ub1 (unsigned char\/uint8_t).\u00a0 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.<\/p>\nstatic ub1 hda[512] = { 0 }; \/* Host Data Area for UPI *\/<\/pre>\n
\nIn UPI, cursors are represented simply by a number.\u00a0 Accordingly, we define a cursor as an unsigned word.<\/p>\nstatic uword cnum; \/* Cursor Number *\/<\/pre>\n
\nNext, we need to make a connection to the Oracle database.\u00a0 UPI provides a couple different logon functions, but we\u2019ll use upilog.\u00a0 upilog will connect to the orcl database and create a session using scott\/tiger.\u00a0 During this process, the HDA structure will be updated to contain some information about this connection.<\/p>\n if (upilog(&hda[0],\r\n (text *) \"scott\/tiger@orcl\",\r\n -1, 0,\r\n -1, 0,\r\n -1, UPILFDEF, UPILMDEF))<\/pre>\n
\nNow that we\u2019re connected to the server, we need to get ready to process a statement.\u00a0 However, before we can process a statement, we must open a cursor.\u00a0 UPI provides the upiopn call for this.<\/p>\n if (upiopn(&hda[0], &cnum, -1))<\/pre>\n
\nNow that the user has passed-in a query for processing, we need to parse the query.\u00a0 UPI provides several functions for parsing, including the upipse and upiosd functions.\u00a0 In our example, we\u2019ll use upiosd, which parses the SQL statement and associates it with our cursor.\u00a0 In our usage, we\u2019re telling UPI to defer the actual parse [using (ub4) 1] until we call a describe\/execution function.\u00a0 Similarly, rather than specific version 6\/7 mode, we\u2019re telling the system to parse the statement in standard (default) mode [via (sword) 1].<\/p>\n if (upiosd(&hda[0], cnum, (text *) sql_statement, -1,\r\n (ub4) 1, (sword) 1))<\/pre>\n
\nAs we\u2019re 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.\u00a0 UPI provides several functions for describing the select list, including the upidpr, upidqr, and upidsc functions.\u00a0 In our example, we\u2019ll 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.<\/p>\n ret = upidsc(&hda[0], cnum, pos,\r\n &pmxl[pos], &pvll[pos],\r\n &perc[pos], &podt[pos], (text *)&coln[pos],\r\n &coll[pos], &pmxdisl[pos],\r\n &ppre[pos], &pscl[pos],\r\n &pnul[pos]);<\/pre>\n
\nThe next step is to define the output variables for each select-list item in the query.\u00a0 To define output variables, UPI provides the upidfc, upidfn, and upidfps functions.\u00a0 In the example, we\u2019ll use upidfn, which is a non-piecewise define for a select-list item by position.\u00a0 In our call, we\u2019re making sure to pass reference to our indicator value for this item as well.\u00a0 Failure to do so will result in errors when attempting to fetch a column containing a NULL value.<\/p>\n if (upidfn(&hda[0], cnum, (sword) i,\r\n obuf[i], coll[i],\r\n (sword) SQLT_STR, (b2 *) &oind[i],\r\n (text *) 0, (size_t) 0,\r\n (ub2 *) 0, (ub2 *) 0,\r\n (sword) 0))<\/pre>\n
\nNow that we\u2019ve defined our output variables, we\u2019re ready to execute the query.\u00a0 The order of describe\/define\/execution can be different in different situations.\u00a0 Regardless, UPI provides several calls for execution including, upiefn, upiexe, and upiexn.\u00a0 In our example, we\u2019ll use upiexe.<\/p>\n if (upiexe(&hda[0], cnum))<\/pre>\n
\nSo, we\u2019ve defined our output variables and executed the statement.\u00a0 If all has gone well so-far, we can start fetching data.\u00a0 UPI provides several different calls including upifch and upifcn.\u00a0 We could also use upiefn (execute+fetch), but are instead going to use the simplest version, upifch.\u00a0 The upifch function performs a row-at-a-time fetch on our cursor.<\/p>\n while (upifch(&hda[0], cnum) == 0)<\/pre>\n
\nThis has been a brief example of writing a simple application using Oracle\u2019s UPI data access API.\u00a0 In the next two articles, you\u2019ll see how this same application can be improved and made more descriptive using OCI7 and the current version of OCI, OCI8.<\/p>\n
\nThe following is the entire source code to the extremely simple interactive SQL client built using the User Program Interface.\u00a0 Given that Oracle still considers UPI proprietary, I will not be attaching nor sending my UPI definitions header file, upidef.h.\u00a0 If you\u2019d 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.<\/p>\n\/* ========================================================================= *\/\r\n\/* INTERACTIVE SQL EXAMPLE *\/\r\n\/* Oracle User Program Interface (UPI) *\/\r\n\/* *\/\r\n\/* Jonah H. Harris <jonah.harris@gmail.com> *\/\r\n\/* http:\/\/www.oracle-internals.com\/ *\/\r\n\/* ========================================================================= *\/\r\n\r\n\/* Standard Headers *\/\r\n#include <stdio.h> \/* Standard C Input\/Output *\/\r\n#include <stdlib.h> \/* Standard C Library *\/\r\n\r\n\/* Oracle-specific Headers *\/\r\n#include <oratypes.h> \/* Oracle-declared Platform Types *\/\r\n#include \"upidef.h\" \/* My UPI Definitions *\/\r\n\r\n\/* ========================================================================= *\/\r\n\/* << DEFINITIONS >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> *\/\r\n\/* ========================================================================= *\/\r\n\r\n#define MAXATTR 1024 \/* Max number of attributes in select-list *\/\r\n\r\n\/* ========================================================================= *\/\r\n\/* << STATIC VARIABLES >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> *\/\r\n\/* ========================================================================= *\/\r\n\r\nstatic ub1 hda[512] = { 0 }; \/* Host Data Area for UPI *\/\r\nstatic uword cnum; \/* Cursor Number *\/\r\n\r\n\/* ========================================================================= *\/\r\n\/* << FORWARD DECLARATIONS >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> *\/\r\n\/* ========================================================================= *\/\r\n\r\nstatic void upi_error (void);\r\n\r\n\/* ========================================================================= *\/\r\n\/* << PUBLIC FUNCTIONS >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> *\/\r\n\/* ========================================================================= *\/\r\n\r\nint\r\nmain (int argc, char **argv)\r\n{\r\n char sql_statement[256];\r\n sword i, pos;\r\n sword ret;\r\n ub1 podt[MAXATTR], ppre[MAXATTR], pnul[MAXATTR];\r\n ub2 pmxl[MAXATTR], coll[MAXATTR],\r\n pmxdisl[MAXATTR], pvll[MAXATTR];\r\n sb1 pscl[MAXATTR];\r\n sb2 perc[MAXATTR];\r\n text coln[MAXATTR][31];\r\n text *obuf[MAXATTR];\r\n sb2 oind[MAXATTR];\r\n\r\n \/* Logon *\/\r\n if (upilog(&hda[0],\r\n (text *) \"scott\/tiger@orcl\",\r\n -1, 0,\r\n -1, 0,\r\n -1, UPILFDEF, UPILMDEF))\r\n {\r\n printf(\"Cannot connect as scott. Exiting...\\n\");\r\n exit(1);\r\n }\r\n\r\n \/* Open a cursor *\/\r\n if (upiopn(&hda[0], &cnum, -1))\r\n {\r\n printf(\"couldn't open cursor!\\n\");\r\n upi_error();\r\n exit(1);\r\n }\r\n\r\n \/* Main loop *\/\r\n for (;;)\r\n {\r\n ub4 rnum = 0; \/* A generic record number counter *\/\r\n\r\n printf(\"\\nEnter a query or \\\"exit\\\"> \");\r\n fgets(sql_statement, sizeof(sql_statement), stdin);\r\n\r\n \/* If user typed, \"exit\", bail! *\/\r\n if (strncmp(sql_statement, \"exit\", 4) == 0)\r\n break;\r\n\r\n \/* Parse the statement *\/\r\n if (upiosd(&hda[0], cnum, (text *) sql_statement, -1,\r\n (ub4) 1, (sword) 1))\r\n {\r\n upi_error();\r\n continue;\r\n }\r\n\r\n \/* Iterate over each attribute in the select-list *\/\r\n for (pos = 1; pos < MAXATTR; pos++)\r\n {\r\n coll[pos] = sizeof(coln[pos]);\r\n\r\n \/* Describe the select-list item *\/\r\n ret = upidsc(&hda[0], cnum, pos,\r\n &pmxl[pos], &pvll[pos],\r\n &perc[pos], &podt[pos], (text *)&coln[pos],\r\n &coll[pos], &pmxdisl[pos],\r\n &ppre[pos], &pscl[pos],\r\n &pnul[pos]);\r\n\r\n if (coll[pos] > sizeof(coln[pos]))\r\n coll[pos] = sizeof(coln[pos]);\r\n\r\n \/* Make sure this string is null-terminated *\/\r\n coln[pos][coll[pos]] = '\\0';\r\n\r\n if (ret)\r\n {\r\n if (ret == 1007)\r\n break;\r\n upi_error();\r\n continue;\r\n }\r\n }\r\n\r\n \/*\r\n * Print out the total count and the names of the select-list\r\n * items, column sizes, and datatype codes.\r\n *\/\r\n pos--;\r\n printf(\"\\nThere were %d select-list items.\\n\", pos);\r\n printf(\"Item name Length Datatype\\n\");\r\n printf(\"\\n\");\r\n for (i = 1; i <= pos; i++)\r\n {\r\n printf(\"%*.*s\", coll[i], coll[i], coln[i]);\r\n printf(\"%*c\", 31 - coll[i], ' ');\r\n printf(\"%6d %8d\\n\", coll[i], podt[i]);\r\n\r\n \/* Allocate the output *\/\r\n obuf[i] = (text *) malloc(coll[i]+1);\r\n if (obuf[i] == NULL)\r\n {\r\n fprintf(stderr, \"Failed to allocate %u bytes!\\n\",\r\n coll[i]+1);\r\n exit(EXIT_FAILURE);\r\n }\r\n\r\n \/* Define the output parameter *\/\r\n if (upidfn(&hda[0], cnum, (sword) i,\r\n obuf[i], coll[i],\r\n (sword) SQLT_STR, (b2 *) &oind[i],\r\n (text *) 0, (size_t) 0,\r\n (ub2 *) 0, (ub2 *) 0,\r\n (sword) 0))\r\n {\r\n upi_error();\r\n break;\r\n }\r\n }\r\n\r\n \/* Execute *\/\r\n if (upiexe(&hda[0], cnum))\r\n {\r\n upi_error();\r\n continue;\r\n }\r\n\r\n \/* Fetch & Display *\/\r\n while (upifch(&hda[0], cnum) == 0)\r\n {\r\n printf(\"\\n-- RECORD %04u -------------------------\\n\", ++rnum);\r\n for (i = 1; i <= pos; i++)\r\n {\r\n printf(\"%s\", coln[i]);\r\n printf(\"%*c \", 31 - coll[i], ':');\r\n printf(\"%s\\n\", (oind[i]) ? \"NULL\" : obuf[i]);\r\n }\r\n }\r\n\r\n \/* Display the summary *\/\r\n printf(\"\\n%u record%s returned.\\n\", rnum, (rnum == 1) ? \"\" : \"s\");\r\n\r\n \/* Cleanup *\/\r\n for (i = 1; i <= pos; i++)\r\n if (obuf[i] != NULL)\r\n free(obuf[i]);\r\n\r\n } \/* for (;;) *\/\r\n\r\n \/* Close the cursor *\/\r\n upicls(&hda[0], cnum);\r\n\r\n \/* Logoff *\/\r\n upilof(&hda[0]);\r\n\r\n \/* Bail! *\/\r\n exit(EXIT_SUCCESS);\r\n\r\n} \/* main() *\/\r\n\r\n\/* ========================================================================= *\/\r\n\/* << PRIVATE FUNCTIONS >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> *\/\r\n\/* ========================================================================= *\/\r\n\r\nstatic void\r\nupi_error (void)\r\n{\r\n text msg[512] = { 0 };\r\n\r\n fprintf(stderr, \"\\nOracle ERROR\\n\");\r\n upigem(&hda[0], (text *) &msg);\r\n fprintf(stderr, \"%s\", msg);\r\n fflush(stderr);\r\n\r\n} \/* upi_error() *\/<\/pre>\n