How to call a stored procedure with a parameter of type table

Question

I'd like to call a stored procedure, that has a parameter of type TABLE.
How can I do this using OCCI (11g1) in a windows C++ application?

Here's the definition of the stored procedure:

  FUNCTION am_send(
    p_caFnr                   IN       VARCHAR2,
    p_TabBgr                  IN       DSKS_BGR_TAB,
    p_caTextout               OUT      VARCHAR2)
  RETURN NUMBER;

and the used types:

create or replace
TYPE      DSKS_BGR_TAB,
AS TABLE OF DSKS_BGR

create or replace
TYPE      DSKS_BGR
(BgrNr    VARCHAR2(3),
 TrId     VARCHAR2(8))

What I have done so far:

I created a object representation of type DSKS_BGR using the OTT Utility.

My code so far:

Environment* env = Environment::createEnvironment(Environment::OBJECT); 

try
{
    Connection *con = env->createConnection("xxxxx", "xxxxx", "xxxxx");

    Statement* statement = con->createStatement("BEGIN :1 := am_send(:2, :3, :4); END;");

    statement->registerOutParam(1, OCCINUMBER);
    statement->setString(2, "Test");    
    // ?? DSKS_BGR_TAB
    statement->registerOutParam(4, OCCISTRING, 1000);

    statement->execute();

    int result = statement->getNumber(1);
    string textOut = statement->getString(4);

    env->terminateConnection(con);  
}
catch(const SQLException &exc)
{
    cout << exc.getErrorCode() << exc.getMessage();
}

Environment::terminateEnvironment(env);

I have no idea how to set the TABLE parameter.

Ronald McBean · Accepted Answer · 2014-04-05T08:24:02.323

You are almost there!

Create object representations of your oracle types using the Object Type Translator utility OTT
Create a vector of pointers to the OTT created type and use the OCCI call setVector() on the statement
Execute!

Here is a small code example:

#include <occi.h>
#include <iostream>
#include "RegisterMappings.h"

using namespace oracle::occi;
using namespace std;

void callproc(Connection *con)
{  
    vector<my_obj_t *> vect;  
    int i;  
    for (i=0; i<10; i++)  
    {    
        my_obj_t *obj = new my_obj_t();    
        obj->setid(i);
        obj->setname("TEST");
        vect.push_back(obj);
    }  
    cout << "\ncallproc - invoking a PL/SQL procedure with parameters"  << endl;  
    Statement *stmt = con->createStatement("BEGIN my_proc(:1); END;");  
    cout << "\nExecuting the block :" << stmt->getSQL() << endl;  
    setVector(stmt, 1, vect, "MY_OBJ_TAB_T");  
    stmt->execute();  
    con->terminateStatement (stmt);  

    cout << "\nocciproc - done" << endl; 

    // delete allocated memory
    for (i=0; i<10; i++)  
    {    
        delete vect[i];
    }  
}
// end of callproc ()
int main()
{
try {
    Environment* env = Environment::createEnvironment(Environment::OBJECT);  
    RegisterMappings(env);  
    Connection* conn = env->createConnection("scott","tiger");  
    callproc(conn);   conn->commit();  
    env->terminateConnection(conn);  
    Environment::terminateEnvironment(env);
    }
    catch(SQLException &ex)
    {  
        cout << ex.getMessage() << endl;
    }
}

I think there will be a memory leak - you need to free the memory manually using delete for each item in the vector. I know it is a good example, just that i've wasted so much time looking for memory leaks lately, sorry for being pesky :/ — user1645975, Apr 04 '14 at 09:48
@user1645975: You are absolutely right: there was a memory leak in the above example. I'll fix the (official oracle) example code above. — Ronald McBean, Apr 05 '14 at 08:21
Yeah, i remember those examples, a few years ago when i had no idea about memory leaks and how i was thinking - "surely there can not be an error here, it's an official example..." — user1645975, Apr 07 '14 at 16:19

How to call a stored procedure with a parameter of type table

1 Answers1