Creating Custom Aggregate Functions in C/ C++
The aim of this article is to describe the process of creating a user-defined aggregate function. Oracle 9i was used to prepare and test the function; some parts of the code may not work with Oracle versions older than 9i. This article gathers information that is needed to write the aggregate function in one place, and presents a clear step-by-step descripion of the process.
Literature
This work is based mainly on Oracle documentation. If you need further details on some of described topics please consult one of these documents:
- Oracle 9i Application Developer Guide Fundamentals. Chapter 10 - Calling External Procedures.
- Oracle Call Interface Programmer's Guide. Chapter 17 - OCI Datatype Mapping and Manipulation Functions
- Oracle 9i Data Cartridge Developer's Guide
External procedures enviroment
The first step is to configure the environment for calling external procedures. The external procedure is a function written in a 3GL (third generation language) like Java or C. The function must be called from an external library. For Java it would be a *.jar file, whereas for C we are going to create *.dll or *.so. For this article I considered creating a *.dll file in MS Visual Studio .NET 2003. The Oracle documentation states that only procedures written in C will work; however, I cound that C++ code did just as well.
How do we configure the environment? It is necessary only to edit two files: listener.ora and tnsnames.ora. You will find these files somewhere in your Oracle instalation directory. In my case it is called $ORACLE_HOME/network/admin/, but this might be different in your case.
tnsnames.ora configuration:
A structure similar to the one below should be added to the tnsnames.ora file:
extproc_connection_data =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = IPC)
(KEY = extproc))
(CONNECT_DATA =
(SID = extproc)
)
)
To be honest, it does not matter much what we enter in the KEY and SID fields. But, it is important to remember that these fields need to be identical with the appropriate fields in listener.ora (see below).
listener.ora configuration:
It is sufficient to extend the configuration of your default listener in such a way that it enables external procedures. This can be achieved by adding the lines indicated below to the listener.ora file:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = someserv)(PORT = someport))
(ADDRESS= (PROTOCOL= IPC)(KEY= extproc) ) -- ADD THIS LINE
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\DEVBASE) -- may be different in your installation
(ENVS = "EXTPROC_DLLS=ANY") -- ADD THIS LINE
)
(SID_DESC =
(GLOBAL_DBNAME = MYDB)
(ORACLE_HOME = D:\oracle\DEVBASE) -- may be different in your installation
(SID_NAME = MYDB)
(ENVS = "EXTPROC_DLLS=ANY") -- ADD THIS LINE
)
(SID_DESC = -- ADD THIS LINE
(SID_NAME = extproc) -- ADD THIS LINE, SID_NAME = SID from tnsnames.ora
(PROGRAM = extproc) -- ADD THIS LINE, DO NOT CHANGE THIS
(ENVS = "EXTPROC_DLLS=ANY") -- ADD THIS LINE
)
)
Remember that the field SID_NAME in the new SID_DESC entry must match the SID value from tnsnames.ora. Similarly, the KEY entry from LISTENER->ADDRESS must match the KEY value you entered in tnsnames.ora.
It is also possible to configure a separate listener process for servicing external procedures calls. Please consult the documentation for more details.
Creating a Dynamic Library
In this paragraph I will describe how to create a simple DLL library using MS Visual Studio .NET 2003.
First, you need to create a DLL project. To do this, choose
File->New->Project.
From the tree view in the upper left part of the screen, choose Visual C++ Projects. From the icon list on the right, choose Win32 Project. In the name field write a name for your DLL. Click OK.
The next window will appear. Click Application Settings on the left. Then from Application Type choose DLL. Click Finish. VS will generate for you a file with content like this:
// sampleDll.cpp : Defines the entry point for the DLL application.
//
#include "stdafx.h"
BOOL APIENTRY DllMain( HANDLE hModule,
DWORD ul_reason_for_call,
LPVOID lpReserved
)
{
return TRUE;
}
According to Microsoft documentation DllMain is an optional method of entry into a DLL. We will not use this function in our example.
Once you have created your project, you may define your functions in the following way:
extern "C" __declspec(dllexport) int doNothing()
{
return 0;
}
Note that the extern "C" __declspec(dllexport) prefix is required to access your function from outside the DLL. The prefix is not needed when you want to define a function that is not explicitly run from outside the environment.
You may build your DLL using Build->Build solution. After building it you should move it to the directory where you want to keep your external libraries - say, C:\OracleExtLib\first.dll
Defining the DLL in the Oracle environment
To access the functions of the DLL, we need to register the library in Oracle. To do this, execute following statement:
CREATE OR REPLACE LIBRARY firstLib IS 'C:\OracleExtLib\first.dll';
In our case firstLib is a logical name that defines the library. The string after IS should be the valid path of the dynamic library.
Defining Aggregate function
We have finally reached the part where serious coding begins. I implement a function that finds the smallest distance between a set of points. The points are represented by the following structure:
type Coordinates as object (
x number,
y number
);
To define the aggregate function you need to execute a statement similar to this:
create or replace function MinDistance(val in Coordinates) return number aggregate using MinDistanceImpl;
MinDistance is the name of our aggregate function; Coordinates is the type of input value. MinDistanceImpl is the most interesting of all. It is an object that implements the aggregate function. It must contain four methods: ODCIAggregateInitialize, ODCIAggregateIterate, ODCIAggregateTerminate and ODCIAggregateMerge. Here is a brief description of these methods:
- ODCIAggregateInitialize is called at the beginning of every aggregate function. Its goal is to initialize variables, allocate memory, etc.
- ODCIAggregateIterate is called once for every element processed by the aggregate function. In our case it is called for every Coordinates object in the aggregated set.
- ODCIAggregateTerminate is called at the end of the execution of every aggregate function. It should deallocate the memory and return the result of the aggregate function.
- ODCIAggregateMerge is called when we allow our aggregate function to be run on many processors. Then the aggregated set is divided into several subsets. Every processor executes the aggregate function on its subset, and then the results are merged. ODCIaggregateMerge takes two objects implementing our aggregate function and returns the result of merging them. It should de-allocate memory allocated by the second object. To enable parallel computation of our aggregate function it is enough to add PARALLEL_ENABLE to the function declaration:
create or replace function MinDistance(val in Coordinates) return number PARALLEL_ENABLE aggregate using MinDistanceImpl;
In the example that finds minimal distance between points, I will not use parallel computation because it may produce the wrong results. You are strongly advised to you use parallel computation carefully, because in some problems (as in this one) it can be dangerous.
The declaration of aggregate function implementation should look like this:
TYPE MINDISTANCEIMPL as object (
key RAW(4),
static function ODCIAggregateInitialize(sctx IN OUT MinDistanceImpl)
return number as language C
library distance name "ODCIAggregateInitialize"
with context
parameters (
context,
sctx,
sctx INDICATOR STRUCT,
RETURN
),
member function ODCIAggregateIterate(
self IN OUT MinDistanceImpl,
value IN Coordinates)
return number as language C
library distance name "ODCIAggregateIterate"
with context
parameters (
context,
self,
self INDICATOR STRUCT,
value ,
value INDICATOR STRUCT,
value TDO,
RETURN
),
member function ODCIAggregateTerminate(
self IN OUT MinDistanceImpl, returnValue OUT number,
flags IN number)
return number
as language C
library distance name "ODCIAggregateTerminate"
with context
parameters (
context,
self,
self INDICATOR STRUCT,
returnValue ,
returnValue INDICATOR,
flags,
flags INDICATOR ,
RETURN ),
member function ODCIAggregateMerge(
self IN OUT MinDistanceImpl,
ctx2 IN MinDistanceImpl)
return number
);
As you can see, it includes all four ODCI functions necessary for the aggregate function. It is extremely important to remember that the parameters of these functions should always be similar to the parameters given in my example. Of course, for different aggregate functions their types will differ (because of the different data processed and different names of the implementation object) but their IN/OUT properties and names should remain the same.
The implementation object includes one additional member, key of type RAW(4) (I will explain its meaning below).
As I mentioned, ODCIAggregateMerge is not necessary for this implementation so it is not included in the DLL. It is defined in the body of MinDistanceImpl. The function only returns success, and its body is defined in the following way:
member function ODCIAggregateMerge(
self IN OUT MinDistanceImpl,
ctx2 IN MinDistanceImpl)
return number
IS
BEGIN
RETURN ODCIConst.Success;
END;
The remaining functions, i.e. ODCIAggregateInitialize, ODCIAggregateIterate, and ODCIAggregateTerminate, have no body defined in Oracle. They are all included in the DLL. As shown above, to link such DLL-defined functions it is necessary to add the following phrase to the object declaration:
as language C
library firstLib name "ODCIAggregateIterate"
with context
parameters (
--
--
--
)
In the place of firstLib it is necessary to provide the name of the DLL registered in Oracle (see the section above "Defining the DLL in the Oracle environment"). Name must be followed by the valid name of function included in the DLL. Remember that this name is case sensitive. The with context parameters phrase should be followed by the list of parameters taken by the DLL function. These list should be consistent with actual parameters, otherwise Oracle will not be able to call the function.
with context
parameters (
context,
self,
self INDICATOR STRUCT,
value ,
value INDICATOR STRUCT,
value TDO,
RETURN
)
Context is the aggregation context passed to our function. It will be described in the section devoted to the C implementation of ODCI functions.
Self is the reference to the actual object, similar to this pointer from C or Java.
Self INDICATOR STRUCT is a structure of indicators for what is pointed to by self. Indicators are variables that inform us whether the variable is null or not. When a simple type variable is passed, INDICATOR should be used instead of INDICATOR STRUCT.
value is an object of the Coordinates type;
value TDO is a Type Definition Object of the value structure. It will not be needed in this implementation, but it is often needed for some OCI (Oracle Call Interface) functions, so it is worth remembering how to pass it to an external function.
It is possible to use any identifier defined in a function's parameters in the with context parameters field. Moreover, these identifiers may be the modifiers INDICATOR, and INDICATOR STRUCT; and TDO may be used in these identifiers. Again, remember that the with context parameters list must be consistent with the DLL function's parameters in respect of the number of parameters and their types.
C Implementation
Every function linked in the aggregate implementation object MinDistanceImpl must be defined in the DLL. Moreover, each of these functions should be accessible from outside of the DLL, so their declarations should be preceded by extern "C" __declspec(dllexport). The most important thing is to choose proper parameters for the functions. Take the ODCIAggregateIterate function. As mentioned, its Oracle header is defined in the following way:
member function ODCIAggregateIterate(
self IN OUT MinDistanceImpl,
value IN Coordinates)
return number as language C
library distance name "ODCIAggregateIterate"
with context
parameters (
context,
self,
self INDICATOR STRUCT,
value ,
value INDICATOR STRUCT,
value TDO,
RETURN
)
A proper C header for this function is:
extern "C" __declspec(dllexport) OCINumber * ODCIAggregateIterate( OCIExtProcContext *context, MinDistanceImpl * self, MinDistanceImpl_ind * self_ind, Coordinates* value, Coordinates_ind* value_ind, OCIType* value_tdo)
As can be seen, a lot of new structures is used in this declaration.
OCIExtProcContext * should always be used when dealing with the context parameter. It is defined in oci.h. This file should be included in our project. It can be found somewhere in $ORACLE_HOME - in my case, in $ORACLE_HOME\oci\include.
Numbers are represented differently in Oracle and C: for example Oracle introduces nullity, C cannot distinguish between null and 0. So, the OCINumber data type was introduced to deal with Oracle numbers. Like OCIExtProcContext, it is defined in the file oci.h. It is worth remembering that standard arithmetical operations like + or * are not defined on OCINumber elements. To perform calculations on OCINumbers and conversions between OCINumbers and standard C types, a wide range of functions was introduced. A list of these functions can be found in Oracle Call Interface Programmer's Guide, chapter 18: OCI Datatype Mapping and Manipulation Functions.
The remaining parameter types are project specific and should be declared by the programmer. To perform this task automatically, the Object Type Translator (OTT) should be used.
OTT comes with the wide range of applications delivered when Oracle is installed. Basing on existing Oracle types, OTT generates C types with which those Oracle type are matched. To use OTT one needs to create an input file. Here's a sample input file of OTT:
CASE=LOWER TYPE Coordinates
The input file contains information about the Oracle data type that needs to be converted. In my case, OTT can be found in $ORACLE_HOME\bin. One can execute OTT using this console command:
ott userid=orauser/orapass intype=infile.txt outtype=outfile.txt code=c hfile=output.h
In userid the Oracle username and password should be specified. infile.txt stands for the name of our input file. Outtype describes the name of an output file. That outtype file can be empty. The result of the ott computation will be saved in the file specified in the hfile field.
Below I include C data types corresponding to the Coordinates and Coordinates Indicator. As mentioned, these types can be obtained using OTT.
struct Coordinates
{
OCINumber x;
OCINumber y;
};
struct Coordinates_ind
{
OCIInd _atomic;
OCIInd x;
OCIInd y;
};
With this introductory information, it is possible to start coding elements of the aggregate function. The first one is ODCIAggregateInitialize.
As mentioned earlier, the initial steps of aggregation should be performed here. If our function is meant to be usable, it should somehow remember the data processed in ODCIAggregateIterate. It is not possible to store that information in ordinary variables, because these variables would be redeclared every time a function from DLL is called. That is why the proper solution is to allocate some memory and pass a pointer to that memory to Oracle. Then every time we need data written to the memory, we just access the block pointed to by the remembered address. The solution to this problem can be found in Oracle9i Data Cartridge Developer's Guide: Appendix A - Pipelined Table Functions: Interface Approach. It is convenient to use three functions from the solution presented in that document. These functions are:
static int checkerr(Handles_t* handles, sword status)
{
text errbuf[512]; /* error message buffer */
sb4 errcode; /* OCI error code */
switch (status)
{
case OCI_SUCCESS:
case OCI_SUCCESS_WITH_INFO:
return 0;
case OCI_ERROR:
OCIErrorGet ((dvoid*) handles->errhp, (ub4) 1, (text *) NULL, &errcode,
errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR);
sprintf((char*)errbuf, "OCI ERROR code %d",errcode);
break;
default:
sprintf((char*)errbuf, "Warning - error status %d",status);
break;
}
OCIExtProcRaiseExcpWithMsg(handles->extProcCtx, 29400, errbuf,
strlen((char*)errbuf));
return -1;
}
static int GetHandles(OCIExtProcContext* extProcCtx, Handles_t* handles)
{
/* store the ext-proc context in the handles struct */
handles->extProcCtx=extProcCtx;
/* Get OCI handles */
if (checkerr(handles, OCIExtProcGetEnv(extProcCtx, &handles->envhp,
&handles->svchp, &handles->errhp)))
return -1;
/* get the user handle */
if (checkerr(handles, OCIAttrGet((dvoid*)handles->svchp,
(ub4)OCI_HTYPE_SVCCTX,
(dvoid*)&handles->usrhp,
(ub4*) 0, (ub4)OCI_ATTR_SESSION,
handles->errhp)))
return -1;
return 0;
}
static StoredCtx* GetStoredCtx(Handles_t* handles, MinDistanceImpl* self,
MinDistanceImpl_ind* self_ind)
{
StoredCtx *storedCtx; /* Stored context pointer */
ub1 *key; /* key to retrieve context */
ub4 keylen; /* length of key */
/* return NULL if the PL/SQL context is NULL */
if (self_ind->_atomic == OCI_IND_NULL) return NULL;
/* Get the key */
key = OCIRawPtr(handles->envhp, self->key);
keylen = OCIRawSize(handles->envhp, self->key);
/* Retrieve stored context using the key */
if (checkerr(handles, OCIContextGetValue((dvoid*) handles->usrhp,
handles->errhp,
key, (ub1) keylen,
(dvoid**) &storedCtx)))
return NULL;
return storedCtx;
}
Moreover, these functions require struct Handles_t, which is defined by:
struct Handles_t
{
OCIExtProcContext* extProcCtx;
OCIEnv* envhp;
OCISvcCtx* svchp;
OCIError* errhp;
OCISession* usrhp;
};
Let me briefly explain the actions performed by these functions:
checkerr is used for error detection. This function is really useful for debugging your program. It should be called every time there is doubt about the success of OCI functions. checkerr is called in the remaining two functions.
GetHandles uses the external procedure context to obtain most the important OCI handles. These handles are necessary to execute OCI functions. As mentioned, we must use OCI functions to perform calculations on OCINumber variables, so these handles are compulsory.
GetStoredCtx is used to retrieve the stored context which is just a convenient way to store data processed in ODCIAggregateIterate. You will remember that self is the pointer to instances of the currently used Oracle object (in our case this object is of the type MINDISTANCEIMPL). One of the member variables of this object is the key RAW(4). That key is nothing more than a pointer to the stored context. As noted earlier, it is possible to remember just about any data in the storedCtx.
A question possibly arises: Why is it necessary to use that stored context? We never know how many items will be processed by the aggregate function. That is why we can never assume the number of variables stored in the Oracle object. It is convenient to hold data processed in ODCIAggregateIterate in a data structure - for example, a linked list. We may store the head (or many heads) of such a list in the stored context. Of course, we can try to store that head in our Oracle object, but in that case we would be forced to encode the C pointer into the UB4 key every time we wanted to replace the head with anything else. Stored context is allocated only once and the pointer is encoded once as well. This is done in ODCIAggregateInitialize. In this case StoredCtx is defined in the following way:
struct StoredCtx
{
List * head;
};
Memory for StoreCtx is allocated with the function OCIMemoryAlloc. Here is a sample of code for allocating memory for StoredCtx.
StoredCtx* storedCtx; /* Stored context pointer */
ub4 key; /* key to retrieve stored context */
Handles_t handles;
GetHandles(context,&handles);
OCIMemoryAlloc((dvoid*) handles.usrhp, handles.errhp,
(dvoid**) &storedCtx,
OCI_DURATION_STATEMENT,
(ub4) sizeof(StoredCtx),
OCI_MEMORY_CLEARED);
storedCtx->head=NULL;
OCIContextGenerateKey((dvoid*) handles.usrhp, handles.errhp, &key);
OCIContextSetValue( (dvoid*)handles.usrhp,
handles.errhp,
OCI_DURATION_STATEMENT,
(ub1*) &key,
(ub1) sizeof(key),
(dvoid*) storedCtx);
OCIRawAssignBytes( handles.envhp,
handles.errhp,
(ub1*) &key,
(ub4) sizeof(key),
&(sctx->key));
sctx_ind->_atomic=OCI_IND_NOTNULL;
sctx_ind->key=OCI_IND_NOTNULL;
One important thing about memory allocation is the duration time. Oracle automatically de-allocates memory allocated with OCIMemoryAlloc at the end of each duration period. In this example, duration is set to OCI_DURATION_STATEMENT. This means that, when the current database statement has been executed, the whole memory allocated with that flag will be deallocated. This is the main reason for using OCIMemoryAlloc instead of malloc or new. It is possible that our function might crash, meaning that the part of the code that should deallocate the memory would never be executed. OCIMemoryAlloc is safer because we always have certainty that the memory will be deallocated after the end of the duration. Other possible values of the duration flag are OCI_DURATION_CALLOUT, OCI_DURATION_STATEMENT, OCI_DURATION_SESSION, OCI_DURATION_PROCESS or a duration created by the user. However, in aggregate functions OCI_DURATION_STATEMENT is most commonly used.
OCIContextGenerateKey generates unique 4-byte identifier.
OCIContextSetValue creates a one-to-one relationship between key (which keeps a unique identifier) and the memory block pointed by storedCtx. From now on, key may be threated as a kind of pointer to storedCtx. To be precise, it is the variable that helps Oracle to find the place in memory where storedCtx is written; which is why this information is lost every time the call of one of our functions finishes.
OCIRawAssignBytes writes the value of key into the Oracle object (represented by self or sctx).
Remember that the ODCIAggregate functions must return ODCI_SUCCESS (defined in oci.h) if they are successfull. This can be achieved with the following code:
OCINumber * returned;
int success = ODCI_SUCCESS;
OCIMemoryAlloc((dvoid*) handles.usrhp,
handles.errhp,
(dvoid**) &returned,
OCI_DURATION_STATEMENT,
(ub4) sizeof(OCINumber),
OCI_MEMORY_CLEARED);
OCINumberFromInt( handles.errhp,
&success,
sizeof(int),
OCI_NUMBER_SIGNED,
returned );
return returned;
The function OCINumberFromInt creates the OCINumber variable based on the int value.
As far as returning computed value is concerned, this must be done by overwriting the returnValue parameter of the ODCIAggregateTerminate function. ReturnValue should be of the type OCINumber *.
The aggregate function code should be as efficient as possible. That is why one must always remember to allocate memory only through the OCIMemoryAlloc function. Remember that STL objects such as vectors, trees or lists allocate memory by themselves. That is why the special Oracle version of such dynamic data structures should be written.
Summary
These comments should be sufficient to write any kind of aggregate function. C or C++ should be used to implement every aggregate that requires good performance.
Working code of aggregate function computing minimal distance between points on a plane:
// distancedll.cpp : Defines the entry point for the DLL application.
//
#include "stdafx.h"
#include <odci.h>
#include <oci.h>
#include <stdio.h>
#include <ociextp.h>
#include <math.h>
#include <fstream>
#include <vector>
#include <algorithm>
using namespace std;
struct MinDistanceImpl
{
OCIRaw* key;
};
struct MinDistanceImpl_ind
{
OCIInd _atomic;
OCIInd key;
};
struct DoubleCoordinates{
public:
double x;
double y;
};
struct Handles_t
{
OCIExtProcContext* extProcCtx;
OCIEnv* envhp;
OCISvcCtx* svchp;
OCIError* errhp;
OCISession* usrhp;
};
typedef OCIRef Coordinates_ref;
struct Coordinates
{
OCINumber x;
OCINumber y;
};
typedef struct Coordinates Coordinates;
struct Coordinates_ind
{
OCIInd _atomic;
OCIInd x;
OCIInd y;
};
typedef struct Coordinates_ind Coordinates_ind;
struct List{
List* next;
DoubleCoordinates* dc;
};
struct StoredCtx
{
List * head;
};
static int checkerr(Handles_t* handles, sword status)
{
text errbuf[512]; /* error message buffer */
sb4 errcode; /* OCI error code */
switch (status)
{
case OCI_SUCCESS:
case OCI_SUCCESS_WITH_INFO:
return 0;
case OCI_ERROR:
OCIErrorGet ((dvoid*) handles->errhp, (ub4) 1, (text *) NULL, &errcode,
errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR);
sprintf((char*)errbuf, "OCI ERROR code %d",errcode);
break;
default:
sprintf((char*)errbuf, "Warning - error status %d",status);
break;
}
OCIExtProcRaiseExcpWithMsg(handles->extProcCtx, 29400, errbuf,
strlen((char*)errbuf));
return -1;
}
static int printerr(Handles_t* handles, sword status, text *errbuf)
{
sb4 errcode; /* OCI error code */
switch (status)
{
case OCI_SUCCESS:
case OCI_SUCCESS_WITH_INFO:
return 0;
case OCI_ERROR:
OCIErrorGet ((dvoid*) handles->errhp, (ub4) 1, (text *) NULL, &errcode,
errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR);
sprintf((char*)errbuf, "OCI ERROR code %d",errcode);
break;
default:
sprintf((char*)errbuf, "Warning - error status %d",status);
break;
}
return -1;
}
static int GetHandles(OCIExtProcContext* extProcCtx, Handles_t* handles)
{
/* store the ext-proc context in the handles struct */
handles->extProcCtx=extProcCtx;
/* Get OCI handles */
if (checkerr(handles, OCIExtProcGetEnv(extProcCtx, &handles->envhp,
&handles->svchp, &handles->errhp)))
return -1;
/* get the user handle */
if (checkerr(handles, OCIAttrGet((dvoid*)handles->svchp,
(ub4)OCI_HTYPE_SVCCTX,
(dvoid*)&handles->usrhp,
(ub4*) 0, (ub4)OCI_ATTR_SESSION,
handles->errhp)))
return -1;
return 0;
}
static StoredCtx* GetStoredCtx(Handles_t* handles, MinDistanceImpl* self,
MinDistanceImpl_ind* self_ind)
{
StoredCtx *storedCtx; /* Stored context pointer */
ub1 *key; /* key to retrieve context */
ub4 keylen; /* length of key */
/* return NULL if the PL/SQL context is NULL */
if (self_ind->_atomic == OCI_IND_NULL) return NULL;
/* Get the key */
key = OCIRawPtr(handles->envhp, self->key);
keylen = OCIRawSize(handles->envhp, self->key);
/* Retrieve stored context using the key */
if (checkerr(handles, OCIContextGetValue((dvoid*) handles->usrhp,
handles->errhp,
key, (ub1) keylen,
(dvoid**) &storedCtx)))
return NULL;
return storedCtx;
}
double dist(DoubleCoordinates & c1, DoubleCoordinates & c2);
double sq(double x);
void convert(DoubleCoordinates * dc, Coordinates c,Handles_t handles);
int med(int x);
double minimum(double x, double y);
bool DClessX(DoubleCoordinates c1, DoubleCoordinates c2);
bool DClessY(DoubleCoordinates c1, DoubleCoordinates c2);
bool DCLessX(DoubleCoordinates *c1, DoubleCoordinates *c2)
{
if(c1->x<c2->x)
return true;
else return false;
}
bool DCLessY(DoubleCoordinates *c1, DoubleCoordinates *c2)
{
if(c1->y<c2->y)
return true;
else return false;
}
double dist(DoubleCoordinates & c1, DoubleCoordinates & c2)
{
return sqrt(sq(c1.x-c2.x) + sq(c1.y-c2.y));
}
double sq(double x)
{
return x*x;
}
double minimum(double x, double y)
{
if(x<=y)
return x;
else
return y;
}
int med(int x)
{
if(x%2==0)
{
return x/2;
}
else
{
return (x-1)/2;
}
}
double OCINumberSubToReal(Handles_t* handles,OCINumber * n1, OCINumber * n2)
{
OCINumber * res=NULL;
OCINumberSub(handles->errhp,n1, n1, res);
double ret;
OCINumberToReal (handles->errhp,res,sizeof(double),&ret);
return ret;
}
double divConq(vector<DoubleCoordinates*> v,int start, int end)
{
double minValue;
int m =med(end-start);
if((end-start)==1)
{
minValue=dist(*v[start],*v[end]);
}
else
{
if(start+m+1!=end)
minValue =minimum(divConq(v, start, start + m), divConq(v,start+m+1,end));
else
minValue =minimum(divConq(v, start, start + m), divConq(v,start+m,end));
}
vector<DoubleCoordinates*> withinD;
withinD.clear();
for(int i=start;i<end;i++)
{
if(dist(*v[i], *v[m]) <= minValue)
{
withinD.push_back(v[i]);
}
}
sort(withinD.begin(), withinD.end(), DCLessY);
double d;
for(int i=0;i<(int)withinD.size();i++)
{
int c = 0;
for(int j=i-1;j>=0;j--)
{
if(abs(withinD[i]->y-withinD[j]->y)>minValue)
break;
d = dist(*withinD[i],*withinD[j]);
if(d<minValue)
{
minValue=d;
}
c++;
}
for(int j=i+1;j<(int)withinD.size();j++)
{
if(abs(withinD[i]->y-withinD[j]->y)>minValue)
break;
d = dist(*withinD[i],*withinD[j]);
if(d<minValue)
{
minValue=d;
}
c++;
}
}
withinD.clear();
return minValue;
}
BOOL APIENTRY DllMain( HANDLE hModule,
DWORD ul_reason_for_call,
LPVOID lpReserved)
{
return TRUE;
}
extern "C" __declspec(dllexport) OCINumber * ODCIAggregateInitialize(
OCIExtProcContext *context,
MinDistanceImpl * sctx,
MinDistanceImpl_ind * sctx_ind,
short * ret_ind)
{
StoredCtx* storedCtx; /* Stored context pointer */
ub4 key; /* key to retrieve stored context */
Handles_t handles;
GetHandles(context,&handles);
OCIMemoryAlloc((dvoid*) handles.usrhp, handles.errhp,
(dvoid**) &storedCtx,
OCI_DURATION_STATEMENT,
(ub4) sizeof(StoredCtx),
OCI_MEMORY_CLEARED);
storedCtx->head=NULL;
OCIContextGenerateKey((dvoid*) handles.usrhp, handles.errhp, &key);
OCIContextSetValue((dvoid*)handles.usrhp,
handles.errhp,
OCI_DURATION_STATEMENT,
(ub1*) &key,
(ub1) sizeof(key),
(dvoid*) storedCtx);
OCIRawAssignBytes( handles.envhp,
handles.errhp,
(ub1*) &key,
(ub4) sizeof(key),
&(sctx->key));
sctx_ind->_atomic=OCI_IND_NOTNULL;
sctx_ind->key=OCI_IND_NOTNULL;
OCINumber * returned;
int success = ODCI_SUCCESS;
OCIMemoryAlloc((dvoid*) handles.usrhp,
handles.errhp,
(dvoid**) &returned,
OCI_DURATION_STATEMENT,
(ub4) sizeof(OCINumber),
OCI_MEMORY_CLEARED);
OCINumberFromInt( handles.errhp,
&success,
sizeof(int),
OCI_NUMBER_SIGNED,
returned );
return returned;
}
extern "C" __declspec(dllexport) OCINumber * ODCIAggregateIterate(
OCIExtProcContext *context,
MinDistanceImpl * self,
MinDistanceImpl_ind * self_ind,
Coordinates* value,
Coordinates_ind* value_ind,
OCIType* value_tdo)
{
StoredCtx * storedCtx;
Handles_t handles;
OCINumber * returned;
GetHandles(context,&handles);
if(value_ind->x==OCI_IND_NOTNULL && value_ind->y==OCI_IND_NOTNULL )
storedCtx=GetStoredCtx(&handles,self,self_ind);
OCIMemoryAlloc((dvoid*) handles.usrhp,
handles.errhp,
(dvoid**) &returned,
OCI_DURATION_STATEMENT,
(ub4) sizeof(OCINumber),
OCI_MEMORY_CLEARED);
DoubleCoordinates * dc;
OCIMemoryAlloc((dvoid*) handles.usrhp,
handles.errhp,
(dvoid**) &dc,
OCI_DURATION_STATEMENT,
(ub4) sizeof(DoubleCoordinates),
OCI_MEMORY_CLEARED);
OCINumberToReal(handles.errhp,&(value->x),sizeof(double),&(dc->x));
OCINumberToReal (handles.errhp,&(value->y),sizeof(double),&(dc->y));
List * lst;
OCIMemoryAlloc((dvoid*) handles.usrhp,
handles.errhp,
(dvoid**) &lst,
OCI_DURATION_STATEMENT,
(ub4) sizeof(List),
OCI_MEMORY_CLEARED);
lst->next=storedCtx->head;
lst->dc=dc;
storedCtx->head=lst;
int success = ODCI_SUCCESS;
OCINumberFromInt( handles.errhp,
&success,
sizeof(int),
OCI_NUMBER_SIGNED,
returned );
return returned;
}
extern "C" __declspec(dllexport) OCINumber * ODCIAggregateTerminate(
OCIExtProcContext *context,
MinDistanceImpl * self,
MinDistanceImpl_ind * self_ind,
OCINumber * returnValue,
short * returnValue_ind,
OCINumber * flags,
short flags_ind)
{
StoredCtx * storedCtx;
Handles_t handles;
OCINumber * returned = NULL;
GetHandles(context,&handles);
storedCtx=GetStoredCtx(&handles,self,self_ind);
vector<DoubleCoordinates*> v ;
List * head = storedCtx->head;
while(head)
{
v.push_back(head->dc);
head=head->next;
}
double minValue;
OCIMemoryAlloc((dvoid*) handles.usrhp,
handles.errhp,
(dvoid**) &returned,
OCI_DURATION_STATEMENT,
(ub4) sizeof(OCINumber),
OCI_MEMORY_CLEARED);
if(v.size()<2)
{
int error = ODCI_SUCCESS;
OCINumberFromInt( handles.errhp,
&error,
sizeof(int),
OCI_NUMBER_SIGNED,
returned );
return returned;
}
sort(v.begin(),v.end() , DCLessX);
minValue=divConq(v,0,(int)v.size()-1);
v.clear();
OCINumberFromReal( handles.errhp,
&minValue,
sizeof(minValue),
returnValue);
int success = ODCI_SUCCESS;
OCINumberFromInt( handles.errhp,
&success,
sizeof(int),
OCI_NUMBER_SIGNED,
returned );
*returnValue_ind = OCI_IND_NOTNULL;
return returned;
}- Marcin Moczyróg's blog
- Log in to post comments
Comments
Queries on Custom aggregate function implementations.
Hi,
I was trying to use your example as start point to build my own aggregate function using OCI.
But i was getting some problems while calling from SQLPLUS
QL> select MinDistance(TT) from egg1;
select MinDistance(TT) from egg1
*
ERROR at line 1:
ORA-06520: PL/SQL: Error loading external library
ORA-06522: /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib/custagg.so:
cannot dynamically load executable
Also i am running this from Linux machine(32 bit). Please let me know if you could help me out by looking at my code...
I am trying for this the past 5 days...
Thanks,
-NN