11
Object-Relational Datatypes

The concepts of binding and defining were introduced and discussed in Chapter 2, "OCI Programming Basics" and in Chapter 5, "Binding and Defining". This chapter provides additional information necessary for users who are developing object applications. The following topics are covered in this chapter:

Overview of OCI Functions for Objects

The OCI datatype mapping and manipulation functions provide the ability to manipulate instances of predefined Oracle C datatypes. These datatypes are used to represent the attributes of user-defined datatypes, including object types in Oracle.

Each group of functions within the OCI is distinguished by a particular naming convention. The datatype mapping and manipulation functions, for example, can be easily recognized because the function names start with the prefix OCI, followed by the name of a datatype, as in OCIDateFromText() and OCIRawSize(). As will be explained later, the names can be further broken down into function groups that operate on a particular type of data.

Additionally, the predefined Oracle C types on which these functions operate are also distinguished by names which begin with the prefix OCI, as in OCIDate or OCIString.

The datatype mapping and manipulation functions are used when an application needs to manipulate, bind, or define attributes of objects that are stored in an Oracle database, or which have been retrieved by a SQL query. Retrieved objects are stored in the client-side object cache, as was described in Chapter 13, "Object Cache Navigation".

This chapter describes the purpose and structure of each of the datatypes that can be manipulated by the OCI datatype mapping and manipulation functions. It also summarizes the different function groups, and gives lists of available functions and their purposes.

This chapter also provides information about how to use these datatypes in bind and define operations within an OCI application.

These functions are valid only when an OCI application is running in object mode. For information about initializing the OCI in object mode, and creating an OCI application that accesses and manipulates objects, refer to the section "Initializing Environment and Object Cache".

See Also:

For detailed information about object types, attributes, and collection datatypes, refer to Oracle9i Database Concepts.

Mapping Oracle Datatypes to C

Oracle provides a rich set of predefined datatypes with which you can create tables and specify user-defined datatypes (including object types). Object types extend the functionality of Oracle by allowing you to create datatypes that precisely model the types of data with which they work. This can provide increased efficiency and ease-of-use for programmers who are accessing the data.

NCHAR and NVARCHAR2 can be used as attributes in objects and map to OCIString * in C.

Database tables and object types are based upon the datatypes supplied by Oracle. These tables and types are created with SQL statements and stored using a specific set of Oracle internal datatypes, like VARCHAR2 or NUMBER. For example, the following SQL statements create a user-defined address datatype and an object table to store instances of that type:

CREATE TYPE address AS OBJECT
(street1    varchar2(50),
street2     varchar2(50),
city        varchar2(30),
state       char(2),
zip         number(5));
CREATE TABLE address_table OF address;

The new address type could also be used to create a regular table with an object column:

CREATE TABLE employees
(name         varchar2(30),
birthday      date,
home_addr     address);

An OCI application can manipulate information in the name and birthday columns of the employees table using straightforward bind and define operations in association with SQL statements. Accessing information stored as attributes of objects requires some extra steps.

The OCI application first needs a way to represent the objects in a C-language format. This is accomplished by using the Object Type Translator (OTT) to generate C struct representations of user-defined types. The elements of these structs have datatypes that represent C language mappings of Oracle datatypes.

See Also:

Table 14-1, "Object Datatype Mappings for Object Type Attributes" for the available Oracle types you can use as object attribute types and their C mappings

An additional C type, OCIInd, is used to represent null indicator information corresponding to attributes of object types.

See Also:

For more information and examples regarding the use of the OTT, refer to Chapter 14, "The Object Type Translator (OTT)".

OCI Type Mapping Methodology

Oracle followed a distinct design philosophy when specifying the mappings of Oracle predefined types. The current system has the following benefits and advantages:

The actual representation of datatypes like OCINumber is opaque to client applications, and the datatypes are manipulated with a set of predefined functions. This allows for the internal representation to change to accommodate future enhancements without breaking user code.
The implementation is consistent with object-oriented paradigms in which class implementation is hidden and only the required operations are exposed.
This implementation can have advantages for programmers. Consider a C program that wants to manipulate Oracle number variables without losing the accuracy provided by Oracle numbers. To do this in Oracle release 7, you would have had to issue a "SELECT ... FROM DUAL" statement. In later releases, this is accomplished by invoking the OCINumber*() functions.

Manipulating C Datatypes With OCI

In an OCI application, the manipulation of data may be as simple as adding together two integer variables and storing the result in a third variable:

int    int_1, int_2, sum;
...
/* some initialization occurs */
...
sum = int_1 + int_2;

The C language provides a set of predefined operations on simple types like integer. However, the C datatypes listed in Table 14-1, "Object Datatype Mappings for Object Type Attributes" are not simple C primitives. Types like OCIString and OCINumber are actually structs with a specific Oracle-defined internal structure. It is not possible to simply add together two OCINumbers and store the value in the third.

The following is not valid:

OCINumber    num_1, num_2, sum;
...
/* some initialization occurs */
...
sum = num_1 + num_2;           /* NOT A VALID OPERATION */

The OCI datatype mapping and manipulation functions are provided to enable you to perform operations on these new datatypes. For example, the above addition of OCINumbers could be accomplished as follows, using the OCINumberAdd() function:

OCINumber    num_1, num_2, sum;
...
/* some initialization occurs */
...
OCINumberAdd(errhp, &num_1, &num_2, &sum): /* errhp is error handle */

The OCI provides functions to operate on each of the new datatypes. The names of the functions provide information about the datatype on which they operate. The first three letters, OCI, indicate that the function is part of the OCI. The next part of the name indicates the datatype on which the function operates. The following table shows the various function prefixes, along with example function names and the datatype on which those functions operate:

Function Prefix	Example	Operates On
OCIColl	`OCICollGetElem()`	OCIColl, OCIIter, OCITable, OCIArray
OCIDate	`OCIDateDaysBetween()`	OCIDate
OCIDateTime	`OCIDateTimeSubtract()`	OCIDate, OCIDateTime
OCIInter	`OCIInterToText()`	OCIInterval
OCIIter	`OCIIterInit()`	OCIIter
OCINumber	`OCINumberAdd()`	OCINumber
OCIRaw	`OCIRawResize()`	OCIRaw *
OCIRef	`OCIRefAssign()`	OCIRef *
OCIString	`OCIStringSize()`	OCIString *
OCITable	`OCITableLast()`	OCITable *

The structure of each of the datatypes is described later in this chapter, along with a list of the functions that manipulate that type.

Precision of Oracle Number Operations

Oracle numbers have a precision of 38 decimal digits. All Oracle number operations are accurate to the full precision, with the following exceptions:

Inverse trigonometric functions are accurate to 28 decimal digits.
Other transcendental functions, including trigonometric functions, are accurate to approximately 37 decimal digits.
Conversions to and from native floating-point types have the precision of the relevant floating-point type, not to exceed 38 decimal digits.

Date (OCIDate)

The Oracle date format is mapped in C by the OCIDate type, which is an opaque C struct. Elements of the struct represent the year, month, day, hour, minute, and second of the date. The specific elements can be set and retrieved using the appropriate OCI functions.

The OCIDate datatype can be bound or defined directly using the external typecode SQLT_ODT in the bind or define call.

The OCI date manipulation functions are listed in the following tables, which are organized according to functionality. Unless otherwise specified, the term date in these tables refers to a value of type OCIDate.

See Also:

The prototypes and descriptions for all the functions are provided in Chapter 18, "OCI Datatype Mapping and Manipulation Functions".

Date Conversion Functions

The following functions perform date conversion.

Function	Purpose
`OCIDateToText()`	convert date to string
`OCIDateFromText()`	convert text string to date
`OCIDateZoneToZone()`	convert date from one time zone to another

Date Assignment and Retrieval Functions

The following functions retrieve and assign date elements.

Function	Purpose
`OCIDateAssign()`	OCIDate assignment
`OCIDateGetDate()`	get the date portion of an OCIDate
`OCIDateSetDate()`	set the date portion of an OCIDate
`OCIDateGetTime()`	get the time portion of an OCIDate
`OCIDateSetTime()`	set the time portion of an OCIDate

Date Arithmetic and Comparison Functions

The following functions perform date arithmetic and comparison.

Function	Purpose
`OCIDateAddDays()`	add days
`OCIDateAddMonths()`	add months
`OCIDateCompare()`	compare dates
`OCIDateDaysBetween()`	calculate the number of days between two dates

Date Information Accessor Functions

The following functions access date information.

Function	Purpose
`OCIDateLastDay()`	the last day of the month
`OCIDateNextDay()`	the first named day after a given date
`OCIDateSysDate()`	the system date

Date Validity Checking Functions

The following function checks date validity.

Function	Purpose
`OCIDateCheck()`	check whether a given date is valid

Date Example

The following code provides examples of how to manipulate an attribute of type OCIDate using OCI calls. For this example, assume that OCIEnv and OCIError have been initialized as described in Chapter 2, "OCI Programming Basics". See Chapter 13, "Object Cache Navigation" for information about pinning.

#define FMT "DAY, MONTH DD, YYYY"
#define LANG "American"
struct person
{
OCIDate start_date;
};
typedef struct person person;

OCIError *err;
person *tim;
sword status;                      /* error status */
uword invalid;
OCIDate last_day, next_day;
text buf[100], last_day_buf[100], next_day_buf[100];
ub4 buflen = sizeof(buf);

/* Pin tim person object in the object cache. */
/*  For this example, assume that
/* tim is pointing to the pinned object. */
/* set the start date of tim */
OCIDateSetTime(&tim->start_date,8,0,0);
OCIDateSetDate(&tim->start_date,1990,10,5)

/* check if the date is valid */
if (OCIDateCheck(err, &tim->start_date, &invalid) != OCI_SUCCESS)
/* error handling code */

if (invalid)
/* error handling code */

/* get the last day of start_date's month */
if (OCIDateLastDay(err, &tim->start_date, &last_day) != OCI_SUCCESS)
/* error handling code */

/* get date of next named day */
if (OCIDateNextDay(err, &tim->start_date, "Wednesday",    strlen("Wednesday"), 
&next_day) != OCI_SUCCESS)
/* error handling code */
/* convert dates to strings and print the information out */
/* first convert the date itself*/
buflen = sizeof(buf);
if (OCIDateToText(err, &tim->start_date, FMT, sizeof(FMT)-1, LANG, 
    sizeof(LANG)-1,        &buflen, buf) != OCI_SUCCESS)
/* error handling code */

/* now the last day of the month */
buflen = sizeof(last_day_buf);
if (OCIDateToText(err, &last_day, FMT, sizeof(FMT)-1, LANG,    sizeof(LANG)-1, 
&buflen, last_day_buf) != OCI_SUCCESS)
/* error handling code */

/* now the first Wednesday after this date */
buflen = sizeof(next_day_out);
if (OCIDateToText(err, &next_day, FMT, sizeof(FMT)-1, LANG,
   sizeof(LANG)-1, &buflen, next_day_buf) != OCI_SUCCESS)
/* error handling code */

/* print out the info */
printf("For: %s\n", buf);
printf("The last day of the month is: %s\n", last_day_buf);
printf("The next Wednesday is: %s\n", next_day_buf);

The output will be:

For: Monday, May 13, 1996
The last day of the month is: Friday, May 31
The next Wednesday is: Wednesday, May 15

Datetime and Interval (OCIDateTime, OCIInterval)

The OCIDateTime datatype is an opaque structure used to represent Oracle time and timestamp datatypes (TIME, TIME WITH TIME ZONE, TIMESTAMP, TIMESTAMP WITH TIME ZONE) and the ANSI DATE datatype. You can set or retrieve the data in these types (that is, year, day, fractional second) using the appropriate OCI functions.

The OCIInterval datatype is also an opaque structure and is used to represent Oracle interval datatypes (INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND).

You can bind and define OCIDateTime and OCIInterval data using the following external typecodes in the bind or define call:

OCI Datatype	Type of Data	External Typecode for Binding/Defining
OCIDateTime	ANSI DATE	SQLT_DATE
OCIDateTime	TIMESTAMP	SQLT_TIMESTAMP
OCIDateTime	TIMESTAMP WITH TIME ZONE	SQLT_TIMESTAMP_TZ
OCIDateTime	TIMESTAMP WITH LOCAL TIME ZONE	SQLT_TIMESTAMP_LTZ
OCIInterval	INTERVAL YEAR TO MONTH	SQLT_INTERVAL_YM
OCIInterval	INTERVAL DAY TO SECOND	SQLT_INTERVAL_DS

The OCI functions which operate on datetime and interval data are listed in the following tables. More detailed information about these functions can be found in OCI Date, Datetime, and Interval Functions .

In general, functions which operate on OCIDateTime data are also valid for OCIDate data.

Datetime Functions

The following functions operate on OCIDateTime values. Some of these functions also perform arithmetic operations on datetime and interval values. Some functions may only work for certain datetime types. The possible types are:

SQLT_DATE - DATE
SQLT_TIMESTAMP - TIMESTAMP
SQLT_TIMESTAMP_TZ - TIMESTAMP WITH TIME ZONE
SQLT_TIMESTAMP_LTZ -TIMESTAMP WITH LOCAL TIME ZONE

See the individual function descriptions for more information about input types which are valid for a particular function.

Function	Purpose
`OCIDateTimeAssign()`	Performs datetime assignment
`OCIDateTimeCheck()`	Checks if the given date is valid
`OCIDateTimeCompare()`	Compares two datetime values
`OCIDateTimeConstruct()`	Constructs a datetime descriptor
`OCIDateTimeConvert()`	Converts one datetime type to another
`OCIDateTimeFromArray()`	Converts an array of size OCI_DT_ARRAYLEN to an OCIDateTime descriptor
`OCIDateTimeFromText()`	Converts the given string to Oracle datetime type in the OCIDateTime descriptor, according to the specified format
`OCIDateTimeGetDate()`	Gets the date (year, month, day) portion of a datetime value
`OCIDateTimeGetTime()`	Gets the time (hour, minute, second, fractional second) out of a datetime value
`OCIDateTimeGetTimeZoneName()`	Gets the time zone name portion of a datetime value
`OCIDateTimeGetTimeZoneOffset()`	Gets the time zone (hour, minute) portion of a datetime value
`OCIDateTimeIntervalAdd()`	Adds an interval to a datetime to produce a resulting datetime
`OCIDateTimeIntervalSub()`	Subtracts an interval from a datetime and stores the result in a datetime
`OCIDateTimeSubtract()`	Takes two datetimes as input and stores their difference in an interval
`OCIDateTimeSysTimeStamp()`	Gets the system current date and time as a timestamp with time zone
`OCIDateTimeToArray()`	Converts a OCIDateTime descriptor to an array
`OCIDateTimeToText()`	Converts the given date to a string according to the specified format
`OCIDateZoneToZone()`	Converts date from one time zone to another zone

Datetime Example

The following snippet of code shows how to use an OCIDateTime datatype to select data from a TIMESTAMP WITH LOCAL TIME ZONE column:

...

/* allocate the program variable for storing the data */
OCIDateTime *tstmpltz = (OCIDateTime *)NULL;

/* Col1 is a timestamp with local time zone column */
OraText *sqlstmt = (OraText *)"SELECT col1 FROM foo"; 

/* Allocate the descriptor (storage) for the datatype */
status = OCIDescriptorAlloc(envhp,(dvoid **)&tstmpltz, OCI_DTYPE_TIMESTAMP_LTZ,
         0, (dvoid **)0);
....

status = OCIStmtPrepare (stmthp, errhp, sqlstmt, (ub4)strlen ((char *)sqlstmt),
         (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT);

/* specify the define buffer for col1 */
status = OCIDefineByPos(stmthp, &defnp, errhp, 1, &tstmpltz, sizeof(tstmpltz),
         SQLT_TIMESTAMP_LTZ, 0, 0, 0, OCI_DEFAULT));

/* Execute and Fetch */
OCIStmtExecute(svchp, stmthp, errhp, 1, 0,(OCISnapshot *) NULL, 
         (OCISnapshot *)NULL, OCI_DEFAULT)

At this point tstmpltz contains a valid timestamp with local time zone data. You 
can get the time zone name of the datetime data using:

status = OCIDateTimeGetTimeZoneName(envhp, errhp, tstmpltz, (ub1 *)buf,
         (ub4 *)buflen);
...

Interval Functions

The following functions operate exclusively on interval data. In some cases it is necessary to specify the type of interval involved. Possible types include:

SQLT_INTERVAL_YM - interval year to month
SQLT_INTERVAL_DS - interval day to second

See the individual function descriptions for more detailed information.

See Also:

Complete lists of the names and purposes as well as more detailed information about these functions can be found in OCI Date, Datetime, and Interval Functions .

Function	Purpose
`OCIIntervalAdd()`	Adds two intervals to produce a resulting interval
`OCIIntervalAssign()`	Copies one interval to another
`OCIIntervalCheck()`	Checks the validity of an interval
`OCIIntervalCompare()`	Compares two intervals
`OCIIntervalDivide()`	Divides an interval by an Oracle Number to produce an interval
`OCIIntervalFromNumber()`	Converts an Oracle Number to an interval
`OCIIntervalFromText()`	Given an interval string, produces the interval represented by the string
`OCIIntervalGetDaySecond()`	Gets values of day and second from an interval
`OCIIntervalGetYearMonth()`	Gets year and month from an interval
`OCIIntervalMultiply()`	Multiplies an interval by an Oracle Number to produce an interval
`OCIIntervalSetDaySecond()`	Sets day and second in an interval
`OCIIntervalSetYearMonth()`	Sets year and month in an interval
`OCIIntervalSubtract()`	Subtracts two intervals and stores the result in an interval
`OCIIntervalToNumber()`	Converts an interval to an Oracle Number
`OCIIntervalToText()`	Given an interval, produces a string representing the interval

Number (OCINumber)

The OCINumber datatype is an opaque structure used to represent Oracle numeric datatypes (NUMBER, FLOAT, DECIMAL, and so forth). You can bind or define this type using the external typecode SQLT_VNU in the bind or define call.

The OCINumber manipulation functions are listed in the following tables, which are organized according to functionality. Unless otherwise specified, the term number in these tables refers to a value of type OCINumber.

See Also:

The prototypes and descriptions for all the functions are provided in Chapter 18, "OCI Datatype Mapping and Manipulation Functions".

Number Arithmetic Functions

The following functions perform arithmetic operations.

Function	Purpose
`OCINumberAbs()`	get the absolute value of a number
`OCINumberAdd()`	add two numbers together
`OCINumberCeil()`	get the ceiling value of a number
`OCINumberDec()`	decrement a number
`OCINumberDiv()`	divide one number by another
`OCINumberFloor()`	get the floor value of a number
`OCINumberInc()`	increment a number
`OCINumberMod()`	get the modulus from the division of two numbers
`OCINumberMul()`	multiply two numbers together
`OCINumberNeg()`	negate a number
`OCINumberRound()`	round a number to a specified decimal place
`OCINumberShift()`	shifts a number a certain number of decimal places
`OCINumberSign()`	get the sign of a number
`OCINumberSqrt()`	get the square root of a number
`OCINumberSub()`	subtract one number from another
`OCINumberTrunc()`	truncate a number to a specified decimal place
`OCINumberSIgn()`	returns the sign of a given number

Number Conversion Functions

The following functions perform conversions between numbers and reals, integers, and strings.

Function	Purpose
`OCINumberToInt()`	convert number to integer
`OCINumberFromInt()`	convert integer to number
`OCINumberToReal()`	convert number to real
`OCINumberFromReal()`	convert real to number
`OCINumberToText()`	convert number to string
`OCINumberFromText()`	convert string to number

Exponential and Logarithmic Functions

The following functions perform exponential and logarithmic operations.

Function	Purpose
`OCINumberPower()`	take a number base to a given number exponent
`OCINumberExp()`	take the exponent with base e
`OCINumberLog()`	take the logarithm of a given base
`OCINumberLn()`	take the natural logarithm (base e)
`OCINumberIntPower()`	take a number base to a given integer power

Trigonometric Functions

The following functions perform trigonometric operations on numbers.

Function	Purpose
`OCINumberArcCos()`	calculate arc cosine
`OCINumberArcSin()`	calculate arc sine
`OCINumberArcTan()` / `OCINumberArcTan2()`	calculate arc tangent / of two numbers
`OCINumberCos()`	calculate cosine
`OCINumberHypCos()`	calculate cosine hyperbolic
`OCINumberSin()`	calculate sine
`OCINumberHypSin()`	calculate sine hyperbolic
`OCINumberTan()`	calculate tangent
`OCINumberHypTan()`	calculate tangent hyperbolic

Number Assignment, Comparison, and Evaluation Functions

The following functions perform assign and compare operations on numbers.

Function	Purpose
`OCINumberAssign()`	assign one number to another
`OCINumberCmp()`	compare two numbers
`OCINumberIsInt()`	test if an integer
`OCINumberIsZero()`	test if equal to zero
`OCINumberPrec()`	sets the precision
`OCINumberSetPi()`	set a number to pi
`OCINumberSetZero()`	initialize number to zero

Number Example

The following example shows how to manipulate an attribute of type OCINumber.

struct person
{
OCINumber sal;
};
typedef struct person person;
OCIError *err;
person* steve;
person* scott;
person* jason;
OCINumber  *stevesal;
OCINumber  *scottsal;
OCINumber *debsal;
sword   status;
int     inum;
double  dnum;
OCINumber ornum;
char    buffer[21];
ub4     buflen;
sword   result;

/* For this example, assume OCIEnv and OCIError are initialized. */
/* For this example, assume that steve, scott and jason are pointing to 
  person objects which have been pinned in the object cache. */
stevesal = &steve->sal;
scottsal = &scott->sal;
debsal = &jason->sal;

/* initialize steve's salary to be $12,000 */
inum = 12000;
status = OCINumberFromInt(err, &inum, sizeof(inum), OCI_NUMBER_SIGNED, 
    stevesal);
if (status != OCI_SUCCESS)  /* handle error from OCINumberFromInt */;

/* initialize scott's salary to be same as steve */
OCINumberAssign(err, stevesal, scottsal);

/* initialize jason's salary to be 20% more than steve's */
dnum = 1.2;
status = OCINumberFromReal(err, &dnum, DBL_DIG, &ornum);
if (status != OCI_SUCCESS)  /* handle error from OCINumberFromReal */;
status = OCINumberMul(err, stevesal, &ornum, debsal);
if (status != OCI_SUCCESS)  /* handle error from OCINumberMul */;

/* give scott a 50% raise */
dnum = 1.5;
status = OCINumberFromReal(err, &dnum, DBL_DIG, &ornum);
if (status != OCI_SUCCESS)  /* handle error from OCINumberFromReal */;
status = OCINumberMul(err, scottsal, &ornum, scottsal);
if (status != OCI_SUCCESS)  /* handle error from OCINumberMul */;

/* double steve's salary */
status = OCINumberAdd(err, stevesal, stevesal, stevesal);
if (status != OCI_SUCCESS)  /* handle error from OCINumberAdd */;

/* get steve's salary in integer */
status = OCINumberToInt(err, stevesal, sizeof(inum), OCI_NUMBER_SIGNED, 
    &inum);
if (status != OCI_SUCCESS)  /* handle error from OCINumberToInt */;

/* inum is set to 24000 */
/* get jason's salary in double */
status = OCINumberToReal(err, debsal, sizeof(dnum), &dnum);
if (status != OCI_SUCCESS)  /* handle error from OCINumberToReal */;

/* dnum is set to 14400 */
/* print scott's salary as DEM0001`8000.00 */
buflen = sizeof(buffer);
status = OCINumberToText(err, scottsal, "C0999G9999D99", 13, 
    "NLS_NUMERIC_CHARACTERS='.`' NLS_ISO_CURRENCY='Germany'", 
    54, &buflen, buffer);
if (status != OCI_SUCCESS)  /* handle error from OCINumberToText */;
printf("scott's salary = %s\n", buffer);

/* compare steve and scott's salaries */
status = OCINumberCmp(err, stevesal, scottsal, &result);
if (status != OCI_SUCCESS)  /* handle error from OCINumberCmp */;

/* result is positive */
/* read jason's new salary from string */
status = OCINumberFromText(err, "48`000.00", 9, "99G999D99", 9,
    "NLS_NUMERIC_CHARACTERS='.`'", 27, debsal);
if (status != OCI_SUCCESS)  /* handle error from OCINumberFromText */;
/* jason's salary is now 48000.00 */

Fixed or Variable-Length String (OCIString)

Fixed or variable-length string data is represented to C programs as an OCIString *.

The length of the string does not include the null character.

For binding and defining variables of type OCIString * use the external typecode SQLT_VST.

See Also:

The prototypes and descriptions for all the functions are provided in Chapter 18, "OCI Datatype Mapping and Manipulation Functions".

String Functions

The following functions allow the C programmer to manipulate an instance of a string.

Function	Purpose
`OCIStringAllocSize()`	get allocated size of string memory in bytes
`OCIStringAssign()`	assign one string to another
`OCIStringAssignText()`	assign text string to string
`OCIStringPtr()`	get pointer to string part of string
`OCIStringResize()`	resize string memory
`OCIStringSize()`	get string size

String Example

This example assigns a text string to a string, then gets a pointer to the string part of the string, as well as the string size, and prints it out.

Note the double indirection used in passing the vstring1 parameter in OCIStringAssignText().

OCIEnv       *envhp;
OCIError     *errhp;
OCIString     *vstring1 = (OCIString *)0;
OCIString     *vstring2 = (OCIString *)0;
text          c_string[20];
text         *text_ptr;
sword        status;

strcpy(c_string, "hello world");
/* Assign a text string to an OCIString */
status = OCIStringAssignText(envhp, errhp, c_string, 
      (ub4)strlen(c_string),&vstring1);
/* Memory for vstring1 is allocated as part of string assignment */

status = OCIStringAssignText(envhp, errhp, "hello again", 
       (ub4)strlen("This is a longer string."),&vstring1);
/* vstring1 is automatically resized to store the longer string */

/* Get a pointer to the string part of vstring1 */
text_ptr = OCIStringPtr(envhp, vstring1);
/* text_ptr now points to "hello world" */
printf("%s\n", text_ptr);

Raw (OCIRaw)

Variable-length raw data is represented in C using the OCIRaw *datatype.

For binding and defining variables of type OCIRaw *, use the external typecode SQLT_LVB.

See Also:

The prototypes and descriptions for all the functions are provided in Chapter 18, "OCI Datatype Mapping and Manipulation Functions"

Raw Functions

The following functions perform OCIRaw operations.

Function	Purpose
`OCIRawAllocSize()`	get the allocated size of raw memory in bytes
`OCIRawAssignBytes()`	assign raw data (ub1 ) to OCIRaw
`OCIRawAssignRaw()`	assign one OCIRaw * to another
`OCIRawPtr()`	get pointer to raw data
`OCIRawResize()`	resize memory of variable-length raw data
`OCIRawSize()`	get size of raw data

Raw Example

In this example, a raw data block is set up and a pointer to its data is obtained.

Note the double indirection in the call to OCIRawAssignBytes().

OCIEnv      *envhp;
OCIError    *errhp;
sword       status;
ub1         data_block[10000];
ub4         data_block_len = 10000;
OCIRaw      *raw1;
ub1 *raw1_pointer;

/* Set up the RAW */
/* assume 'data_block' has been initialized */
status = OCIRawAssignBytes(envhp, errhp, data_block, data_block_len, &raw);

/* Get a pointer to the data part of the RAW */
raw1_pointer = OCIRawPtr(envhp, raw1);

Collections (OCITable, OCIArray, OCIColl, OCIIter)

Oracle provides two types of collections: variable-length arrays (VARRAYs) and nested tables. In C applications, varrays are represented as OCIArray *, and nested tables are represented as OCITable *. Both of these datatypes (along with OCIColl and OCIIter, described later) are opaque structures.

A variety of generic collection functions enable you to manipulate collection data. You can use these functions on both varrays and nested tables. In addition, there is a set of functions specific to nested tables.

See Also:

"Nested Table Manipulation Functions"

You can allocate an instance of a varray or nested table using OCIObjectNew() and free it using OCIObjectFree().

See Also:

The prototypes and descriptions for all the functions are provided in "OCI Collection and Iterator Functions"

Generic Collection Functions

Oracle provides two types of collections: variable-length arrays (varrays) and nested tables. Both varrays and nested tables can be viewed as sub-types of a generic collection type.

In C, a generic collection is represented as OCIColl *, a varray is represented as OCIArray *, and a nested table as OCITable *. Oracle provides a set of functions to operate on generic collections (such as OCIColl *). These functions start with the prefix OCIColl, as in OCICollGetElem(). The OCIColl*() functions can also be called to operate on varrays and nested tables.

The generic collection functions are grouped into two main categories:

manipulating varray or nested table data
scanning through a collection with a collection iterator

The generic collection functions represent a complete set of functions for manipulating varrays. Additional functions are provided to operate specifically on nested tables. They are identified by the prefix OCITable, as in OCITableExists().

See Also:

"Nested Table Manipulation Functions"

Note:

Indexes passed to collection functions are zero-based

Collection Data Manipulation Functions

The following generic functions manipulate collection data:

Function	Purpose
`OCICollAppend()`	append an element
`OCICollAssignElem()`	assign element at given index
`OCICollAssign()`	assign one collection to another
`OCICollGetElem()`	get pointer to an element given its index
`OCICollMax()`	get upper bound of collection
`OCICollSize()`	get current size of collection
`OCICollTrim()`	trim n elements from the end of the collection

Collection Scanning Functions

The following generic functions enable you to scan collections with a collection iterator. The iterator is of type OCIIter, and is created by first calling OCIIterCreate().

Function	Purpose
`OCIIterCreate()`	create an iterator for scanning collection
`OCIIterDelete()`	delete iterator
`OCIIterGetCurrent()`	get pointer to current element pointed by iterator
`OCIIterInit()`	initialize iterator to scan the given collection
`OCIIterNext()`	get pointer to next element
`OCIIterPrev()`	get pointer to previous element

Varray/Collection Iterator Example

This example creates and uses a collection iterator to scan through a varray.

OCIEnv       *envhp; 
OCIError     *errhp; 
text         *text_ptr; 
sword        status; 
OCIArray     *clients; 
OCIString    *client_elem; 
OCIIter      *iterator; 
boolean      eoc; 
dvoid        *elem;
OCIInd       *elemind;
 
/* Assume envhp, errhp have been initialized */
/* Assume clients points to a varray */ 

/* Print the elements of clients */ 
/* To do this, create an iterator to scan the varray */ 
status = OCIIterCreate(envhp, errhp, clients, &iterator); 

/* Get the first element of the clients varray */ 
printf("Clients' list:\n"); 
status = OCIIterNext(envhp, errhp, iterator, &elem,
                    (dvoid **) &elemind, &eoc);

while (!eoc && (status == OCI_SUCCESS))
{
  client_elem = *(OCIString)**elem; 
                             /* client_elem points to the string */ 

 /* 
    the element pointer type returned by OCIIterNext() through 'elem' is
    the same as that of OCICollGetElem(). Refer to OCICollGetElem() for
    details.  */

  /* 
    client_elem points to an OCIString descriptor, so to print it out, 
    get a pointer to where the text begins
  */ 
  text_ptr = OCIStringPtr(envhp, client_elem);

  /* 
    text_ptr now points to the text part of the client OCIString, which is a 
NULL-terminated string
  */ 
  printf("  %s\n", text_ptr); 
  status = OCIIterNext(envhp, errhp, iterator, &elem,
                      (dvoid **)&elemind, &eoc);
}

if (status != OCI_SUCCESS)
{
  /* handle error */
}

/* destroy the iterator */
status = OCIIterDelete(envhp, errhp, &iterator);

Nested Table Manipulation Functions

As its name implies, one table may be nested, or contained within another, as a variable, attribute, parameter or column. Nested tables may have elements deleted, by means of the OCITableDelete() function.

For example, suppose a table is created with 10 elements, and OCITableDelete() is used to delete elements at index 0 through 4 and 9. The first existing element is now element 5, and the last existing element is element 8.

As noted above, the generic collection functions may be used to map to and manipulate nested tables. In addition, the following functions are specific to nested tables. They should not be used on varrays.

Function	Purpose
`OCITableDelete()`	delete an element at a given index
`OCITableExists()`	test whether an element exists at a given index
`OCITableFirst()`	return index for first existing element of table
`OCITableLast()`	return index for last existing element of table
`OCITableNext()`	return index for next existing element of table
`OCITablePrev()`	return index for previous existing element of table
`OCITableSize()`	return table size, not including deleted elements

Nested Table Element Ordering

When a nested table is fetched into the object cache, its elements are given a transient ordering, numbered from zero to the number of elements, minus 1. For example, a table with 40 elements would be numbered from 0 to 39.

You can use these position ordinals to fetch and assign the values of elements (for example, fetch to element i, or assign to element j, where i and j are valid position ordinals for the given table).

When the table is copied back to the database, its transient ordering is lost. Delete operations may be performed against elements of the table. Delete operations create transient holes; that is, they do not change the position ordinals of the remaining table elements.

Nested Table Locators

You can retrieve a locator to a nested table. A locator is like a handle to a collection value, and it contains information about the database snapshot which exists at the time of retrieval. This snapshot information helps the database retrieve the correct instantiation of a collection value at a later time when collection elements are fetched using the locator.

Unlike a LOB locator, a collection locator cannot be used to modify a collection instance, they merely locate the correct data. Using the locator enables an application to return a handle to a nested table without having to retrieve the entire collection, which may be quite large.

A user specifies when a table is created if a locator should be returned when a collection column or attribute is fetched, using the RETURN AS LOCATOR specification.

See Also:

Oracle9i SQL Reference for more information

You can use the OCICollIsLocator() function to determine whether a collection is a locator.

Multilevel Collection Types

The collection element itself can be directly or indirectly another collection type. Multilevel collection type is the name given to such a top-level collection type.

Multilevel collections have the following characteristics:

They can be collections of other collection types.
They can be collections of objects with collection attributes.
They have no limit to the number of nesting levels.
They can contain any combination of varrays and nested tables.
They can be used as columns in tables.

OCI routines work with multilevel collections. The following routines can return in parameter *elem a OCIColl*, which can be used in any of the collection routines:

OCICollgetElem()
OCIIterGetCurrent()
OCIIterNext()
OCIIterPrev()

The following functions take a collection element and add it to an existing collection. Parameter elem could be an OCIColl* if the element type is another collection:

OCICollAssignElem()
OCICollAppend()

Multilevel Collection Type Example

Assume that the following types and tables are used for the example:

type_1 (a NUMBER, b NUMBER)
NT1 TABLE OF type_1
NT2 TABLE OF NT1

The following snippet of code iterates over the multilevel collection:

...
OCIColl *outer_coll;
OCIColl *inner_coll;
OCIIter *itr1, *itr2;
Type_1 *type_1_instance;
....
/* assume outer_coll points to a valid coll of type NT2 */
checkerr(OCIIterCreate(envhp, errhp, outer_coll, &itr1));
for(eoc = FALSE;!OCIIterNext(envhp, errhp, itr, (dvoid **) &elem,
                               (dvoid **) &elem_null, &eoc) && !eoc;)
{
   inner_coll = (OCIColl *)elem;
   /* iterate over inner collection.. */
   checkerr(errhp, OCIIterCreate(envhp, errhp, inner_coll, &itr2));
   for(eoc2 = FALSE;!OCIIterNext(envhp, errhp, itr2, (dvoid **)&elem2, 
              (dvoid **) &elem2_null, &eoc2) && !eoc2;)
       {
        type_1_instance = (Type_1 *)elem2;
        /* use the fields of type_1_instance */
       }
   /* close iterator over inner collection */
   checkerr(errhp, OCIIterDelete(envhp, errhp, &itr2));
}
/* close iterator over outer collection */
checkerr(errhp, OCIIterDelete(envhp, errhp, &itr));
...

REF (OCIRef)

A REF (reference) is an identifier to an object. It is an opaque structure that uniquely locates the object. An object may point to another object by way of a REF.

In C applications, the REF is represented by OCIRef *.

See Also:

The prototypes and descriptions for all the functions are provided in Chapter 18, "OCI Datatype Mapping and Manipulation Functions".

REF Manipulation Functions

The following functions perform REF operations.

Function	Purpose
`OCIRefAssign()`	assign one `REF` to another
`OCIRefClear()`	clear or nullify a `REF`
`OCIRefFromHex()`	convert hexadecimal string to a `REF`
`OCIRefHexSize()`	return size of hex string representation of `REF`
`OCIRefIsEqual()`	compare two `REFs` for equality
`OCIRefIsNull()`	test whether a `REF` is `NULL`
`OCIRefToHex()`	convert `REF` to a hexadecimal string

REF Example

This example tests two REFs for NULL, compares them for equality, and assigns one REF to another. Note the double indirection in the call to OCIRefAssign().

OCIEnv       *envhp;
OCIError     *errhp;
sword        status;
boolean      refs_equal;
OCIRef       *ref1, *ref2;

/* assume refs have been initialized to point to valid objects */
/*Compare two REFs for equality */
refs_equal = OCIRefIsEqual(envhp, ref1, ref2);
printf("After first OCIRefIsEqual:\n");
if(refs_equal)
   printf("REFs equal\n");
else
   printf("REFs not equal\n");

/*Assign ref1 to ref2 */
status = OCIRefAssign (envhp, errhp, ref1, &ref2);
if(status != OCI_SUCCESS) 
/*error handling*/

/*Compare the two REFs again for equality */
refs_equal = OCIRefIsEqual(envhp, ref1, ref2);
printf("After second OCIRefIsEqual:\n");
if(refs_equal)
   printf("REFs equal\n");
else
   printf("REFs not equal\n");

Object Type Information Storage and Access

Descriptor Objects

When a given type is created with the CREATE TYPE statement, it is stored in the server and associated with a type descriptor object (TDO). In addition, the database stores descriptor objects for each data attribute of the type, each method of the type, each parameter of each method, and the results returned by methods. The following table lists the OCI datatypes associated with each type of descriptor object.

Information Type	OCI Datatype
Type	OCIType
Type Attributes Collection Elements Method Parameters Method Results	OCITypeElem
Method	OCITypeMethod

Several OCI functions (including OCIBindObject() and OCIObjectNew()) require a TDO as an input parameter. An application can obtain the TDO by calling OCITypeByName(), which gets the type's TDO in an OCIType variable. Once you obtain the TDO, you can pass it, as necessary to other calls.

AnyType, AnyData and AnyDataSet Interfaces

These capabilities allow you to model self-descriptive data. You can store heterogeneous data types in the same column and query the type of data in an application.

These definitions are used in the discussion in the following sections:

Persistent types. These are created using the SQL statement CREATE TYPE. They are stored persistently in the database.
Transient types. Anonymous type descriptions that are not stored persistently in the database.They are created by programs on the fly. They are useful for exchanging type information, if necessary, between various components of an application in a dynamic fashion.
Self-descriptive data. Data encapsulating type information with its actual contents. The OCIAnyData datatype models such data in OCI. A data value of most SQL types can be converted to an OCIAnyData which can then be converted back to the old data value. The type SYS.ANYDATA models such data in SQL or PL/SQL.
Self-descriptive dataset. Encapsulation of a set of data instances (all of the same type) along with their type description. They should all have the same type description.The OCIDataAnySet datatype models this data in OCI. The type SYS.ANYDATASET models such data in SQL or PL/SQL.

Interfaces are available in both OCI (C language) as well as in SQL and PL/SQL for constructing and manipulating these type descriptions as well as self-descriptive data. The following sections describe the relevant OCI interfaces.

See Also:

For more information see "Persistent Objects, Transient Objects, and Values", and Oracle9i SQL Reference, section "Oracle-Supplied Types" for an overview

Type Interfaces

The type interfaces can be used to construct named as well as anonymous transient object types (structured with attributes) and collection types. The OCITypeBeginCreate() call is used to begin type construction of transient object types as well as collection types (the typecode parameter determines which one is being constructed).

You need to allocate a parameter handle using OCIDescriptorAlloc(). Subsequently, type information (for attributes of an object type as well as for the collection element's type) needs to be set using OCIAttrSet(). For object types, use OCITypeAddAttr() to add the attribute information to the type. After information on the last attribute has been added, you must call OCITypeEndCreate().

For example:

OCITypeBeginCreate( ...)        /* Begin Type Creation */
OCIDescriptorAlloc(...)
OCIAttrSet(...)
OCITypeAddAttr(...)             /* Add attribute 1 */
OCIAttrSet(...)
OCITypeAddAttr(...)             /* Add attribute 2 */
...
OCITypeEndCreate(...)           /* End Type Creation */

For collection types, the information on the collection element type needs to be set with OCITypeSetCollection(). Subsequently, OCITypeEndCreate() is called to finish construction.

For example:

OCITypeBeginCreate( ...)        /* Begin Type Creation */
OCIDescriptorAlloc(...)
OCIAttrSet(...)
OCITypeSetCollection(...)       /* Set information on collection element etc.*/
OCITypeEndCreate(...)           /* End Type Creation */

The OCIDescribeAny() call can be used to obtain the OCIType corresponding to a persistent type.

11 Object-Relational Datatypes

Overview of OCI Functions for Objects

Mapping Oracle Datatypes to C

OCI Type Mapping Methodology

Manipulating C Datatypes With OCI

Precision of Oracle Number Operations

Date (OCIDate)

Date Conversion Functions

Date Assignment and Retrieval Functions

Date Arithmetic and Comparison Functions

Date Information Accessor Functions

Date Validity Checking Functions

Date Example

Datetime and Interval (OCIDateTime, OCIInterval)

Datetime Functions

Datetime Example

Interval Functions

Number (OCINumber)

Number Arithmetic Functions

Number Conversion Functions

Exponential and Logarithmic Functions

Trigonometric Functions

Number Assignment, Comparison, and Evaluation Functions

Number Example

Fixed or Variable-Length String (OCIString)

String Functions

String Example

Raw (OCIRaw)

Raw Functions

Raw Example

Collections (OCITable, OCIArray, OCIColl, OCIIter)

Generic Collection Functions

Collection Data Manipulation Functions

Collection Scanning Functions

Varray/Collection Iterator Example

Nested Table Manipulation Functions

Nested Table Element Ordering

Nested Table Locators

Multilevel Collection Types

Multilevel Collection Type Example

REF (OCIRef)

REF Manipulation Functions

REF Example

Object Type Information Storage and Access

Descriptor Objects

AnyType, AnyData and AnyDataSet Interfaces

Type Interfaces

11
Object-Relational Datatypes