Re: [Orclib-users] Problem calling and receiving an oracle stored procedure resultset

[vincent r. <vin...@gm...>]

because you must add the semicolon in your sql statement :

"declare rc SYS_REFCURSOR; begin listatipos(*:rc*); end;"

Vincent

On Sat, Jan 3, 2015 at 6:54 PM, Miguel Vaz <pag...@gm...> wrote:

> Follow up:
>
> if i add a Prepare:
>
> string sql = "declare rc SYS_REFCURSOR; begin listatipos(rc); end;";
> con.Open(connectString, user, passw);
> Statement st(con);
> Statement st2(con);
> st.Prepare(sql);
> st.Bind(":rc", st2, BindInfo::Out);
> etc..
>
> I get an output of "ORA-01036: illegal variable name/number"
>
>
>
> best regards,
> Miguel
>
> On Sat, Jan 3, 2015 at 5:54 PM, Miguel Vaz <pag...@gm...> wrote:
>
>> ​Hi,
>> Thank you so much for all the replies.
>>
>> Luiz: with your code, i get an error at this line:
>>
>> st.Bind(":1", &st2, 1);
>>
>> specifically at the dot(.) after "st". The error is:
>>
>>  error C2664: 'void ocilib::Statement::Bind<ocilib::Statement*>(const
>> ocilib::ostring &,TDataType &,ocilib::BindInfo::BindDirection)' : cannot
>> convert argument 2 from 'ocilib::Statement *' to 'ocilib::Statement *&'
>>
>> Vincent: with your code, using st.Bind(":rc", st2, BindInfo::Out);
>>
>> no compiling error, but i get an output (while running) error:
>>
>> "The statement must be prepared to perform this operation"
>>
>> i have the call to a method that has my code inside a try catch:
>> try{
>> Environment::Initialize(Environment::Default | Environment::Threaded);
>> Environment::EnableWarnings(true);
>> ioc.getListaTiposCorpo(); // the c++ code on my first post
>>
>>
>> }
>> catch (Exception &ex){
>> cout << ex.what() << endl;
>>
>> }
>>
>>
>>
>> So sorry for all these questions.
>>
>>
>> best regards,
>> Miguel
>>
>> On Sat, Jan 3, 2015 at 5:35 PM, Luiz Rafael Culik <lu...@xh...>
>> wrote:
>>
>>> Hi Miguel
>>>
>>> the problem is the C++ Code
>>>
>>> try this
>>>
>>> string sql = "begin listatipos(:1); end;";
>>> con.Open(connectString, user, passw);
>>> Statement st(con);
>>> Statement st2(con);
>>> St.Bind(":1",&st2,1);
>>> st.Execute(sql);
>>> // something missing to bind the oracle variable "rc"?
>>> Resultset rs = st2.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();
>>>
>>> Regards
>>> Luiz
>>>
>>> 2015-01-03 15:09 GMT-02:00 Miguel Vaz <pag...@gm...>:
>>>
>>> Forgive my ignorance, but can you point me to an example or show me a
>>>> bit of code to get me started? I am a beginner when it comes to oracle.
>>>> From what i gathered from your reply, the PL/SQL procedure is the problem,
>>>> not the c++ code, right?
>>>>
>>>> Thank you for the swift reply, vincent.
>>>>
>>>> best regards,
>>>> Miguel
>>>>
>>>> On Sat, Jan 3, 2015 at 3:44 PM, vincent rogier <vin...@gm...>
>>>> wrote:
>>>>
>>>>> 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
>>>>>
>>>>> Regards
>>>>>
>>>>> Vincent
>>>>> Le 3 janv. 2015 14:56, "Miguel Vaz" <pag...@gm...> a écrit :
>>>>>
>>>>>> 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 display it on my c++
>>>>>> application (using visual studio 2013).
>>>>>>
>>>>>> 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);
>>>>>> // something missing to bind the oracle variable "rc"?
>>>>>> 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();
>>>>>>
>>>>>> 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
>>>>>>
>>>>>> NOTE: i also posted this question on the forums, please forgive the
>>>>>> duplication.
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> ------------------------------------------------------------------------------
>>>>>> Dive into the World of Parallel Programming! The Go Parallel Website,
>>>>>> sponsored by Intel and developed in partnership with Slashdot Media,
>>>>>> is your
>>>>>> hub for all things parallel software development, from weekly thought
>>>>>> leadership blogs to news, videos, case studies, tutorials and more.
>>>>>> Take a
>>>>>> look and join the conversation now. http://goparallel.sourceforge.net
>>>>>> _______________________________________________
>>>>>> Orclib-users mailing list
>>>>>> Orc...@li...
>>>>>> https://lists.sourceforge.net/lists/listinfo/orclib-users
>>>>>>
>>>>>>
>>>>
>>>>
>>>> ------------------------------------------------------------------------------
>>>> Dive into the World of Parallel Programming! The Go Parallel Website,
>>>> sponsored by Intel and developed in partnership with Slashdot Media, is
>>>> your
>>>> hub for all things parallel software development, from weekly thought
>>>> leadership blogs to news, videos, case studies, tutorials and more.
>>>> Take a
>>>> look and join the conversation now. http://goparallel.sourceforge.net
>>>> _______________________________________________
>>>> Orclib-users mailing list
>>>> Orc...@li...
>>>> https://lists.sourceforge.net/lists/listinfo/orclib-users
>>>>
>>>>
>>>
>>
>


-- 
Vincent Rogier