I'm searching for a way to call an anonymous PLsql block through SOCI. The data transfer takes place through a refcursor that was previously created as a variable in the script:
variable rc refcursor
declare
v_obj_id number(4,0) := 1;
v_obj_def varchar(30);
v_obj_type number := 1;
begin
open :rc for
select v_obj_id, OBJ_DEF_ID
from MY_OBJECT_DEFS
where OBJECT_TYPE = v_obj_type;
end;
I need to read the refcursor from my application to retrieve the data. I tried to execute the above through a soci::statement
but it gives me the error: ORA-24333: zero iteration count
. The PLsql script works fine when executed in SqlPlus.
- How can I make the connection between the statement and the refcursor rc? Should I use some other SOCI construct (other than statement) for this purpose?
- I understand there are two instructions in the above script; (i. the refcursor creation, ii. the anonymous PLsql block itself). I'm not sure whether its possible to call multiple instructions in a single SOCI statement. Can this be confirmed?
Following is the what I tried. The sSQL
contains the above PLsql script:
dbConn.open("...");
int iObjId;
std::string iObjDefId;
soci::indicator ind_iObjId = soci::i_ok,
ind_iObjDefId = soci::i_ok;
soci::statement stmt(dbConn);
stmt.alloc();
stmt.prepare(sSQL);
stmt.exchange(soci::into(iObjId, ind_iObjId));
stmt.exchange(soci::into(iObjDefId, ind_iObjDefId));
stmt.define_and_bind();
stmt.execute(false);
while (stmt.fetch())
{
if (soci::i_ok == ind_iObjId)
std::cout << "Obj ID: " << iObjId << std::endl;
if (soci::i_ok == ind_iObjDefId)
std::cout << "Obj Def ID: " << iObjDefId << std::endl;
}
EDIT: I'm using Oracle 11g