Results 1 to 4 of 4

Thread: Oracle Collections in C++

  1. #1
    Junior Member
    Join Date
    Nov 2013
    Posts
    2

    Oracle Collections in C++

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

    Code:
    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?

  2. #2
    Junior Member
    Join Date
    Oct 2012
    Posts
    29
    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

  3. #3
    Junior Member
    Join Date
    Nov 2013
    Posts
    2
    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?

  4. #4
    Junior Member
    Join Date
    Oct 2012
    Posts
    29
    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);
    }

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •