Oracle Call Interface Programmer's Guide
Release 9.0.1

Part Number A89857-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to next page

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 and Object 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.

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:

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:

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.

#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);
     
/* For this example, assume the OCIEnv and OCIError have been
* initialized as described in Chapter 2. */
/* Pin tim person object in the object cache. See Chapter 13 for
* information about pinning. 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:

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:

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 */
OCINumberInit(err, stevesal);
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:

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:

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:

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:

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.

Any Type, 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:

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.

Creating a Parameter Descriptor for OCIType Calls

The OCIDescriptorAlloc() call can be used to allocate an OCIParam (with the parent handle being the environment handle). Subsequently, OCIAttrSet() can be called with the following allowed attribute types to set relevant type information:

To set numeric precision. Pass a (ub1 *) attribute value to the buffer holding precision value.

To set numeric scale. Pass a (sb1 *) attribute value to the buffer holding scale value.

To set the character set id for character types. Pass a (ub2 *) attribute value to the buffer holding char set id.

To set the character set form for character types. Pass a (ub1 *) attribute value to the buffer holding character set form value.

Length of VARCHAR2, RAW, etc. Pass a (ub4 *) attribute value to the buffer holding length.

To set typecode. Pass a (ub2 *) attribute value to the buffer holding typecode. This attribute needs to be set first.

To set OCIType of an object or collection attribute. Pass a (OCIType *) attribute value to the OCIType corresponding to the attribute. It is your responsibility to make sure that the OCIType is pinned when this OCIParam is used during AnyType construction. If it is a transient type attribute, its allocation duration should be at least as much as the top level OCIType being created. There will be an exception returned otherwise.

Obtaining the OCIType for Persistent Types

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

OCIDescribeAny(svchp, errhp. (dvoid *)"SCOTT.EMP", (ub4)strelen("SCOTT.EMP"),
               (ub1)OCI_OTYPE_NAME, (ub1)OCI_DEFAULT, OCI_PTYPE_TYPE, dschp);

From the describe handle (dschp), the OCIType can be obtained using OCIAttrGet() calls.

Type Access Calls

OCIDescribeAny() can be called with these transient type descriptions for a dynamic description of the type. The OCIType pointer can be passed directly to OCIDescribeAny() (with objtype set to OCI_OTYPE_PTR). This provides a way to obtain attribute information by name as well as position.

Extensions to OCIDescribeAny()

For transient types that represent builtin types (created with a builtin typecode), the parameter handle that describes these types (which will be of type OCI_PTYPE_TYPE) will support the following extra attributes.

OCI_ATTR_DATA_SIZE,

OCI_ATTR_TYPECODE,

OCI_ATTR_DATA_TYPE,

OCI_ATTR_PRECISION,

OCI_ATTR_SCALE,

OCI_ATTR_CHARSET_ID

OCI_ATTR_CHARSET_FORM,

OCI_ATTR_LFPRECISION,

OCI_ATTR_FSPRECISION

These attributes will have the usual meanings they have while describing a type attribute.


Note:

These attributes are supported only for transient builtin types. The attributes OCI_ATTR_IS_TRANSIENT_TYPE and OCI_ATTR_IS_PREDEFINED_TYPE are true for these types. For persistent types, these attributes are supported only from the parameter handle of the type's attributes (which will be of type OCI_PTYPE_TYPE_ATTR). 


OCIAnyData Interfaces

An OCIAnyData encapsulates type information as well as a data instance of that type (that is, self descriptive data). An OCIAnyData can be created from any builtin or user-defined type instance using the OCIAnyDataConvert() call. This call does a conversion (cast) to an OCIAnyData.

Alternatively, object types and collection types can be constructed piece by piece (an attribute at a time for object types or a collection element at a time). To construct in this fashion, OCIAnyDataBeginCreate() should be called with the type information (OCIType). Subsequently OCIAnyDataAttrSet() can be used for object types and OCIAnyDataCollAddElem() can be used for collection types. OCIAnyDataEndCreate() must then be called to finish the construction process.

Subsequently, the access routines can be invoked. To convert (cast) an OCIAnyData to the corresponding type instance, the OCIAnyDataAccess() call can be used.

An OCIAnyData that is based on an object or collection type can also be accessed piece by piece.

Special collection construction and access calls are provided for performance improvement. These calls can be used to avoid unnecessary creation and copying of the entire collection in memory. For example:

OCIAnyDataConvert(...)        /* Cast a builtin or user-defined type instance
                                 to an OCIAnyData in 1 call. */

OCIAnyDataBeginCreate(...)    /* Begin AnyData Creation */

OCIAnyDataAttrSet(...)        /* Attribute-wise construction for object types */

or,

OCIAnyDataCollAddElem(...)    /* Element-wise construction for collections */

OCIAnyDataEndCreate(...)      /* End OCIAnyData Creation */

OCIAnyDataSet Interfaces

An OCIAnyDataSet encapsulates type information as well as a set of instances of that type. OCIAnyDataSetBeginCreate() is called to begin the construction process. OCIAnyDataSetAddInstance() is called to add a new instance and this call returns the OCIAnyData corresponding to that instance.

Then, the OCIAnyData functions can be invoked to construct this instance. OCIAnyDataSetEndCreate() is called once all instances have been added.

For access, call OCIAnyDataSetGetInstance() to get the OCIAnyData corresponding to the instance. Only sequential access is supported. Subsequently, the OCIAnyData access functions can be invoked.For example:

OCIAnyDataSetBeginCreate(...)   /* Begin AnyDataSet Creation */								
OCIAnyDataSetAddInstance(...)   /* Add a new instance to the AnyDataSet */
                                /* Use the OCIAnyData*() functions to create
                                   the instance */
OCIAnyDataSetEndCreate(...)     /* End OCIAnyDataSet Creation */


Note:

For complete descriptions of all the calls in these interfaces, see Chapter 20, "OCI Any Type and Data Functions"


Binding Named Datatypes

This section provides information on binding named datatypes, such as objects and collections, and REFs.

Named Datatype Binds

For a named datatype (object type or collection) bind, a second bind call is necessary following OCIBindByName(), or OCIBindByPos(). The OCI Bind Object Type call, OCIBindObject(), sets up additional attributes specific to the object type bind. An OCI application uses this call when fetching data from a table which has a column with an object datatype.

The OCIBindObject() call takes, among other parameters, a Type Descriptor Object (TDO) for the named data type. The TDO, of datatype OCIType is created and stored in the database when a named data type is created. It contains information about the type and its attributes. An application can obtain a TDO by calling OCITypeByName().

The OCIBindObject() call also sets up the indicator variable or structure for the named data type bind.

When binding a named data type, use the SQLT_NTY datatype constant to indicate the datatype of program variable being bound. SQLT_NTY indicates that a C struct representing the named data type is being bound. A pointer to this structure is passed to the bind call.

With inheritance and instance substitutability, you can bind a subtype instance where the supertype is expected.

It is possible that working with named data types may require the use of three bind calls in some circumstances. For example, to bind a static array of named data types to a PL/SQL table, three calls must be invoked: OCIBindByName(), OCIBindArrayOfStruct(), and OCIBindObject().

See Also:

 

Binding REFs

As with named data types, binding REFs is a two-step process. First, call OCIBindByName() or OCIBindByPos(), and then call OCIBindObject().

REFs are bound using the SQLT_REF datatype. When SQLT_REF is used, then the program variable being bound must be of type OCIRef *.

With inheritance and REF substitutability, you can bind a REF value to a subtype instance where a REF to the supertype is expected.

See Also:

 

Information for Named Datatype and REF Binds

This section presents some additional important information to keep in mind when working with named data type and REF binds. It includes pointers about memory allocation and indicator variable usage.

Information Regarding Array Binds

For doing array binds of named data types or REFs, for array inserts or fetches, the user needs to pass in an array of pointers to buffers (pre-allocated or otherwise) of the appropriate type. Similarly, an array of scalar indicators (for SQLT_REF types) or an array of pointers to indicator structs (for SQLT_NTY types) needs to be passed.

See Also:

For more information about SQLT_NTY, see the section "New External Datatypes"

Defining Named Datatypes

This section provides information on defining named data types (for example, objects, collections) and REFs.

Defining Named Datatype Output Variables

For a named datatype (object type, nested table, varray) define, two define calls are necessary. The application should first call OCIDefineByPos(), specifying SQLT_NTY in the dty parameter. Following OCIDefineByPos(), the application must call OCIDefineObject(). In this case, the data buffer pointer in OCIDefineByPos() is ignored and additional attributes pertaining to a named data type define are set up using the OCI Define Object attributes call, OCIDefineObject().

There SQLT_NTY datatype constant is specified for a named datatype define. In this case, the application fetches the result data into a host-language representation of the named data type. In most cases, this will be a C struct generated by the Object Type Translator.

When making an OCIDefineObject() call, a pointer to the address of the C struct (preallocated or otherwise) must be provided. The object may have been created with OCIObjectNew(), allocated in the cache, or with user-allocated memory.

However, in the presence of inheritance, we strongly recommend using objects in the object cache and not passing objects allocated out of user memory from the stack. The reason is that due to instance substitutability, the server may send back a subtype instance when the client is expecting a supertype instance. This requires the server to dynamically re-size the object -- which is possible only for objects in the cache.


Note:

Please refer to the section"Information for Named Datatype and REF Defines, and PL/SQL OUT Binds" for more important information about defining named data types. 


Defining REF Output Variables

As with named data types, defining for a REF output variable is a two-step process. The first step is a call to OCIDefineByPos(), and the second is a call to OCIDefineObject(). Also as with named data types, the SQLT_REF datatype constant is passed to the dty parameter of OCIDefineByPos().

SQLT_REF indicates that the application will be fetching the result data into a variable of type OCIRef *. This REF can then be used as part of object pinning and navigation, as described in Chapter 6.


Note:

Please refer to the section"Information for Named Datatype and REF Defines, and PL/SQL OUT Binds" for more important information about defining REFs


Information for Named Datatype and REF Defines, and PL/SQL OUT Binds

This section presents some additional important information to keep in mind when working with named data type and REF defines. It includes pointers about memory allocation and indicator variable usage.

A PL/SQL OUT bind refers to binding a placeholder to an output variable in a PL/SQL block. Unlike a SQL statement, where output buffers are set up with define calls, in a PL/SQL block, output buffers are set up with bind calls. Refer to the section "Binding Placeholders in PL/SQL" for more information.

For an object define with type SQLT_NTY, client applications wanting to pre-allocate object memory must use the OCIObjectNew() function. Client applications should not allocate the object in its own private memory space, such as with malloc() or on the stack. The OCIObjectNew() function allocates the object in the object cache. The allocated object can be freed using OCIObjectFree(). Refer to Chapter 17, "OCI Navigational and Type Functions" for details on OCIObjectNew() and OCIObjectFree().


Note:

There is no change to the behavior of OCIDefineObject() when the user does not pre-allocate the object memory and instead initializes the output variable to null pointer value. In this case, the object will be implicitly allocated in the object cache by the OCI library. 


Information About Array Defines

For doing array defines of named data types or REFs, the user needs to pass in an array of pointers to buffers (pre-allocated or otherwise) of the appropriate type. Similarly, an array of scalar indicators (for SQLT_REF types) or an array of pointers to indicator structs (for SQLT_NTY types) needs to be passed.

Binding And Defining Oracle C Datatypes

Previous chapters of this book have discussed OCI bind and define operations. "What is Binding?" discussed the basics of OCI bind operations, while "What is Defining?" discusses the basics of OCI define operations. Information specific to binding and defining named data types and REFs is found in Chapter 5, "Binding and Defining".

The sections covering basic bind and define functionality showed how an application could use a scalar variable or array of scalars as an input (bind) value in a SQL statement, or as an output (define) buffer for a query.

The sections covering named data types and REFs showed how to bind or define an object or reference. Chapter 10, "OCI Object-Relational Programming" expanded on this to talk about pinning object references, object navigation, and fetching embedded instances.

The purpose of this section is to cover binding and defining of individual attribute values, using the datatype mappings explained in this chapter.

Variables of one of the types defined in this chapter, such as OCINumber or OCIString, can typically be declared in an application and used directly in an OCI bind or define operation as long as the appropriate datatype code is specified. The following table lists the datatypes that can be used for binds and defines, along with their C mapping, and the OCI external datatype which must be specified in the dty (datatype code) parameter of the bind or define call.

Table 11-1 Datatype Mappings for Binds and Defines  
Datatype  C Mapping  OCI External Datatype and Code 

Oracle number 

OCINumber 

VARNUM (SQLT_VNU) 

Oracle date 

OCIDate 

SQLT_ODT 

VARCHAR2 

OCIString * 

SQLT_VST (see Note 1 below) 

RAW 

OCIRaw * 

SQLT_LVB (see Note 1 below) 

CHAR 

OCIString * 

SQLT_VST 

OBJECT 

struct * 

Named Data Type (SQLT_NTY) 

REF 

OCIRef * 

REF (SQLT_REF) 

VARRAY 

OCIArray * 

Named Data Type (SQLT_NTY) 

Nested Table 

OCITable * 

Named Data Type (SQLT_NTY) 

DATETIME 

OCIDateTime * 

See "Datetime and Interval (OCIDateTime, OCIInterval)"

INTERVAL 

OCIInterval * 

See "Datetime and Interval (OCIDateTime, OCIInterval)"


Note:

Before fetching data into a define variable of type OCIString *, the size of the string must first be set using the OCIStringResize() routine. This may require a describe operation to obtain the length of the select-list data. Similarly, an OCIRaw * must be first sized with OCIRawResize()


The following section presents examples of how to use C-mapped datatypes in an OCI application.

See Also:

For a discussion of OCI external datatypes, and a list of datatype codes, refer to Chapter 3, "Datatypes"

Bind and Define Examples

The examples in this section demonstrate how variables of type OCINumber can be used in OCI bind and define operations.


Note:

The examples in this section are intended to demonstrate the flow of calls used to perform certain OCI tasks. An expanded pseudocode is used for the examples in this section. Actual function names are used, but for the sake of simplicity not all parameters and typecasts are filled in. Additionally, other necessary OCI calls, like handle allocations, have been omitted. 


Assume, for this example, that the following person object type was created:

CREATE TYPE person AS OBJECT
(name     varchar2(30),
salary     number);

This type is then used to create an employees table which has a column of type person.

CREATE TABLE employees
(emp_id    number,
job_title  varchar2(30),
emp        person);

The Object Type Translator (OTT) generates the following C struct and null indicator struct for person:

struct person
{   OCIString * name;
   OCINumber salary;};
typedef struct person person;

struct person_ind
{   OCIInd  _atomic;
   OCIInd  name;
   OCIInd  salary;}
typedef struct person_ind person_ind;

See Also:

For a complete discussion of OTT, see Chapter 14, "The Object Type Translator (OTT)" 

Assume that the employees table has been populated with values, and an OCI application has declared a person variable:

person *my_person;

and fetched an object into that variable through a SELECT statement, like

text *mystmt = (text *) "SELECT person FROM employees
                        WHERE emp.name='Andrea'";

This would require defining my_person to be the output variable for this statement, using appropriate OCI define calls for named datatypes, as described in the section "Advanced Define Operations". Executing the statement would retrieve the person object named Andrea into the my_person variable.

Once the object is retrieved into my_person, the OCI application now has access to the attributes of my_person, including the name and the salary.

The application could go on to update another employee's salary to be the same as Andrea's, as in

text *updstmt = (text *) "UPDATE employees SET emp.salary = :newsal 
                            WHERE emp.name = 'MONGO'"

Andrea's salary (stored in my_person->salary) would be bound to the placeholder :newsal, specifying an external datatype of VARNUM (datatype code=6) in the bind operation:

OCIBindByName(...,":newsal",...,&my_person->salary,...,6,...);
OCIStmtExecute(...,updstmt,...)

Executing the statement updates Mongo's salary in the database to be equal to Andrea's, as stored in my_person.

Conversely, the application could update Andrea's salary to be the same as Mongo's, by querying the database for Mongo's salary, and then making the necessary salary assignment:

text *selstmt = (text *) "SELECT emp.salary FROM employees 
                           WHERE emp.name = 'MONGO'"
OCINumber mongo_sal;
...
OCIDefineByPos(...,1,...,&mongo_sal,...,6,...);
OCIStmtExecute(...,selstmt,...);
OCINumberAssign(...,&mongo_sal, &my_person->salary);

In this case, the application declares an output variable of type OCINumber and uses it in the define step. In this case we define an output variable for position 1, and use the appropriate datatype code (6 for VARNUM).

The salary value is fetched into the mongo_sal OCINumber, and the appropriate OCI function, OCINumberAssign(), is used to assign the new salary to the copy of the Andrea object currently in the cache. To modify the data in the database, the change must be flushed to the server.

Salary Update Examples

The examples in the previous section should give some idea of the flexibility which the Oracle datatypes provide for bind and define operations. The goal of this section is to show how the same operation can be performed in several different ways. The goal is to give you some idea of the variety of ways in which these datatypes can be used in OCI applications.

The examples in this section are intended to demonstrate the flow of calls used to perform certain OCI tasks. An expanded pseudocode is used for the examples in this section. Actual function names are used, but for the sake of simplicity not all parameters and typecasts are filled in. Additionally, other necessary OCI calls, like handle allocations, have been omitted.

The Scenario

The scenario for these examples is as follows:

  1. An employee named BRUCE exists in the employees table for a hospital. See person type and employees table creation statements in the previous section.

  2. Bruce's current job title is RADIOLOGIST.

  3. Bruce is being promoted to RADIOLOGY_CHIEF, and along with the promotion comes a salary increase.

  4. Hospital salaries are in whole dollar values, are set according to job title, and stored in a table called salaries, defined as follows:

    CREATE TABLE salaries
    (job_title   varchar2(20),
    salary       integer));
    
    
  5. Bruce's salary needs to be updated to reflect his promotion.

Accomplishing the above task requires that the application retrieve the salary corresponding to RADIOLOGY_CHIEF from the salaries table, and update Bruce's salary. A separate step would write his new title and the modified object back to the database.

Assuming that a variable of type person has been declared

person * my_person;

and the object corresponding to Bruce has been fetched into it, the following sections present three different ways in which the salary update could be performed.

Method 1 - fetch, convert, assign

This example uses the following method:

  1. Do a traditional OCI define using an integer variable to retrieve the new salary from the database.

  2. Convert the integer to an OCINumber.

  3. Assign the new salary to Bruce.

    #define INT_TYPE 3        /* datatype code for sword integer define */
    
    text *getsal = (text *) "SELECT salary FROM salaries
                            WHERE job_title='RADIOLOGY_CHIEF'
    sword    new_sal;
    OCINumber   orl_new_sal;
    ...
    OCIDefineByPos(...,1,...,new_sal,...,INT_TYPE,...);
                            /* define int output */
    OCIStmtExecute(...,getsal,...);
                            /* get new salary as int */
    OCINumberFromInt(...,new_sal,...,&orl_new_sal);
                            /* convert salary to OCINumber */
    OCINumberAssign(...,&orl_new_sal, &my_person->salary);
                            /* assign new salary */
    

Method 2 - fetch, assign

This method eliminates one of the steps in Method 1:

  1. Define an output variable of type OCINumber, so that no conversion is necessary after the value is retrieved.

  2. Assign the new salary to Bruce

    #define VARNUM_TYPE 6         /* datatype code for defining VARNUM */
    
    text *getsal = (text *) "SELECT salary FROM salaries
                                  WHERE job_title='RADIOLOGY_CHIEF'
    OCINumber   orl_new_sal;
    ...
    OCIDefineByPos(...,1,...,orl_new_sal,...,VARNUM_TYPE,...);
                                         /* define OCINumber output */
    OCIStmtExecute(...,getsal,...);      /* get new salary as OCINumber */
    OCINumberAssign(...,&orl_new_sal, &my_person->salary); 
                                         /* assign new salary */
    

Method 3 - direct fetch

This method accomplishes the entire operation with a single define and fetch. No intervening output variable is used, and the value retrieved from the database is fetched directly into the salary attribute of the object stored in the cache.

  1. Since Bruce is pinned in the object cache, use the location of his salary attribute as the define variable, and execute/fetch directly into it.

    #define VARNUM_TYPE 6         /* datatype code for defining VARNUM */
    
    text *getsal = (text *) "SELECT salary FROM salaries
                                WHERE job_title='RADIOLOGY_CHIEF'
    ...
    OCIDefineByPos(...,1,...,&my_person->salary,...,VARNUM_TYPE,...);
                /* define bruce's salary in cache as output variable */
    OCIStmtExecute(...,getsal,...);
                 /* execute and fetch directly */
    

Summary and Notes

As the previous three examples show, the C datatypes provide flexibility for binding and defining. In these examples an integer can be fetched, and then converted to an OCINumber for manipulation. An OCINumber can be used as an intermediate variable to store the results of a query. Or, data can be fetched directly into a desired OCINumber attribute of an object.


Note:

In all of these examples it is important to keep in mind that in OCI, if an output variable is defined before the execution of a query, the resulting data will be prefetched directly into the output buffer. 


In the above examples, extra steps would be necessary to insure that changes are written to the database permanently. This may involve SQL UPDATE calls and OCI transaction commit calls.

These examples all dealt with define operations, but a similar situation applies for binding.

Similarly, although these examples dealt exclusively with the OCINumber type, a similar variety of operations are possible for the other Oracle C types described in the remainder of this chapter.

SQLT_NTY Bind/Define Example

The following code fragments demonstrate the use of SQLT_NTY bind and define calls, including OCIBindObject() and OCIDefineObject(). In each example, a previously defined SQL statement is being processed.

Bind Example

/*
** This example performs a SQL insert statement
*/
void insert(envhp, svchp, stmthp, errhp, insstmt, nrows)
OCIEnv *envhp;
OCISvcCtx *svchp;
OCIStmt *stmthp;
OCIError *errhp;
text *insstmt;
ub2   nrows;
{
  orttdo *addr_tdo = NULLP(orttdo);
  address  addrs;
  null_address naddrs;
  address *addr = &addrs;
  null_address *naddr = &naddrs;
  sword custno =300;
  OCIBind *bnd1p, *bnd2p;
  ub2 i;

  /* define the application request  */
  checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) insstmt,
           (ub4) strlen((char *)insstmt),
           (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));

  /* bind the input variable */
 checkerr(errhp, OCIBindByName(stmthp, &bnd1p, errhp, (text *) ":custno", 
          (sb4) -1, (dvoid *) &custno, 
          (sb4) sizeof(sword), SQLT_INT,
          (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, 
          (ub4) OCI_DEFAULT));
  
  checkerr(errhp, OCIBindByName(stmthp, &bnd2p, errhp, (text *) ":addr", 
          (sb4) -1, (dvoid *) 0, 
          (sb4) 0, SQLT_NTY, (dvoid *) 0, (ub2 *)0, (ub2 *)0,
          (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT));
  
  checkerr(errhp, OCITypeByName(envhpx, errhp, svchpx, (const text *)
      SCHEMA, (ub4) strlen((char *)SCHEMA), (const text *)   
       "ADDRESS_VALUE", (ub4) strlen((char *)"ADDRESS_VALUE"), 
       OCI_DURATION_SESSION, &addr_tdo));

  if(!addr_tdo)
  {
    printf("Null tdo returned\n");
    goto done_insert;
  }

  checkerr(errhp, OCIBindObject(bnd2p, errhp, addr_tdo, (dvoid **) &addr,
        (ub4 *) 0, (dvoid **) &naddr, (ub4 *) 0));

Define Example

/* 
** This example executes a SELECT statement from a table which includes 
** an object.
*/

void selectval(envhp, svchp, stmthp, errhp)
OCIEnv *envhp;
OCISvcCtx *svchp;
OCIStmt *stmthp;
OCIError *errhp;
{
  orttdo *addr_tdo = NULLP(orttdo);
  OCIDefine *defn1p, *defn2p;
  address *addr = (address *)NULL;
  sword custno =0;
  sb4 status;

  /* define the application request  */
  checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) selvalstmt,
                        (ub4) strlen((char *)selvalstmt),
                        (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));

  /* define the output variable */
checkerr(errhp, OCIDefineByPos(stmthp, &defn1p, errhp, (ub4) 1, (dvoid *)
       &custno, (sb4) sizeof(sword), SQLT_INT, (dvoid *) 0, (ub2 *)0, 
       (ub2 *)0, (ub4) OCI_DEFAULT));

checkerr(errhp, OCIDefineByPos(stmthp, &defn2p, errhp, (ub4) 2, (dvoid *)
        0, (sb4) 0, SQLT_NTY, (dvoid *) 0, (ub2 *)0,
        (ub2 *)0, (ub4) OCI_DEFAULT));

  checkerr(errhp, OCITypeByName(envhpx, errhp, svchpx, (const text *) 
       SCHEMA, (ub4) strlen((char *)SCHEMA), (const text *) 
       "ADDRESS_VALUE", (ub4) strlen((char *)"ADDRESS_VALUE"),OROODTSES, 
       &addr_tdo));

  if(!addr_tdo)
  {
    printf("NULL tdo returned\n");
    goto done_selectval;
  } 


  checkerr(errhp, OCIDefineObject(defn2p, errhp, addr_tdo, (dvoid **) 
       &addr, (ub4 *) 0, (dvoid **) 0, (ub4 *) 0));

  checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, 
       (OCISnapshot *) NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT));


Go to previous page Go to next page
Oracle
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback