Call stored procedure and return resultset

[Anonymous]

Hi,
I searched everywhere for a solution to this apparently simple question, but i couldnt find it anywhere. All the solutions i find are for C using OCI_* way.

I have a stored procedure on an oracle db (12) and i simply want to call it, retrieve the resulting resultset and displayit on my c++ application.

My stored procedure:

CREATE OR REPLACE
procedure listatipos(p_recordset OUT SYS_REFCURSOR) as
BEGIN
open p_recordset FOR
select id_ctipo,nome from corpostipos cpt order by id_ctipo asc;
END listatipos;

My c++ code:

string sql = "declare rc SYS_REFCURSOR; begin listatipos(rc); end;";
con.Open(connectString, user, passw);
Statement st(con);
st.Execute(sql);
Resultset rs = st.GetResultset();
int total_colunas = int(rs.GetColumnCount());
cout << "Tabela com " << total_colunas << " colunas." << endl;
while (rs.Next())
{
for (int i = 1; i <= total_colunas; i++){
cout << rs.Get<string>(i) << "\t";
}
cout << std::endl;
}
con.Close();</string>

This results in "A null Resultset handle has been provided". If i use a simple query like "select * from table1", the records are listed perfectly.

From comparing my code to the previous version ones i find online, i can see its probably something with a bind(?)? Is it something with my procedure?

Please help, i find the ocilib a very straightforward library to work with but this simple problem is driving me crazy. there are no references online as to how to call and retrieve procedure results using it.

Should i use the other code? I mean, use OCI_ etc? Shouldnt the c++ api be complete?

Thanks!

best regards,
MV

[Vincent Rogier]

Hi,

In your procedure the ref cursor is not a return value but an out parameter. You must bind another local statement object indicating to the statement executing the sql and add a " :"in the sql string. When you call st.Bind() passing the other stmt object that will receive the ref cursir you must specify the out direction

Here is the proper way to do it :

string sql = "declare rc SYS_REFCURSOR; begin listatipos(:rc); end;";
con.Open(connectString, user, passw);
Statement st(con);
Statement rc(con);
st.Bind(":rc", rc, BindInfo::Out);
st.Execute();
Resultset rs = rc.GetResultset();

You can also check the test_ref_cursor() method in the demo/ocilib_demo.cpp sample

Regards,

Vincent