Oracle Collections in C++

[gharabed]

I have some user defined types that are collections containing nested tables within Oracle (11g). For example:

CREATE OR REPLACE TYPE a_list_type AS OBJECT (
  a_id INTEGER,
  start_date DATE,
  stop_date DATE,
  desc VARCHAR2(64)
);

CREATE OR REPLACE TYPE a_collection AS TABLE of a_list_type;

CREATE OR REPLACE TYPE main_type AS OBJECT (
   main_id INTEGER,
   main_type VARCHAR2(10),
   a_list_collection a_collection
);

CREATE OR REPLACE TYPE main_collection AS TABLE OF main_type;

Assuming I have a stored function that populates and returns an object of "main_collection" type. How would I reference the returned object from inside a C++ program? What sort of C++ object would an Oracle object be returned into?

[cmartin]

Hello gharabed,
As a start I would recommend you had a look at the sample 'ora8obj.cpp' to be found in $ILDHOME/examples/src.
This sample manipulates objects and varrays, pushing the values using parameters and retrieving values through straight selection.

Another sample that may be of help is named 'oraproc.cpp'. It exhibits the use of array parameters in a procedure call. IF the colleciton value is returned by a function, not as an out argument, then you need to use a parameter to bind the return value.
This parameter should be of the class IldADTValue.

Do not forget to use the 'abstract data type' description to bind the parameter (see in ora8obj.cpp, part Varray, item 2/).

HTH,
cmartin

[gharabed]

Can I find the example files online someplace? I don't see any examples in our directories here. Where can I get the example files?

[cmartin]

Hello gharabed,
No I am sorry but the code samples are not available online.
Yet in a standard installation of DB link you should have an 'src' directory in the 'examples' directory. May be for some reason they were deleted from your installation.

This forum is not the right place for me to send the code because it is too big (over 10K) but here is the part I mentioned:
// Varray
// ------
// 1/ parse the insertion request
const char* parseArr = "insert into pversions values (name , vers)";
cout << "Parsing insertion request : " << parseArr << endl;
if (!request->parse(parseArr))
Ending(dbms, request, "Parse failed: ", 1);
cout << endl;

// 2/ declare the variables
cout << "Binding input variable :name of type string" << endl;
if (!request->bindParam(":PNAME", IldStringType, 21))
Ending(dbms, request, "Variable binding failed:", 1);
cout << "Binding input variable vers of type collection" << endl;
adt = dbms->getAbstractType("INTTAB");
if (dbms->isErrorRaised())
Ending(dbms, request, "Cannot get type description", 1);
if (!request->bindParam(":PVERS", IldCollectionType, -1, 0, 0,
IlFalse, 0, adt))
Ending(dbms, request, "Variable binding failed:", 1);
cout << endl;

// 3/ iterate while giving them some values
const char* products[] = {"Views", "Solver", "Dblink"};
IldADTValue* vers = new IldADTValue(adt);
for (i = 0 ; i < 3 ; ++i) {
if (!request->setParamValue(products[i], 0))
Ending(dbms, request, "Set parameter failed:", 1);
vers->setValue((IlInt)i, 0);
vers->setValue((IlInt)(i + 1), 1);
vers->setValue((IlInt)(i + 2), 2);
if (!request->setParamValue(vers, 1))
Ending(dbms, request, "Set parameter failed:", 1);

// execute the insertion
cout << "Inserting row" << endl;
if (!request->execute())
Ending(dbms, request, "Execution failed: ", 1);
}