orclib-users — Discussions/Questions about using OCILIB

Re: [Orclib-users] Simple insert with in variables to procedure

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

I'm currently finishing creating release packages for v4.0.1.
They will be online wihtin the next hour.

You cannot send a C++ class to an oracle procedure.

Regards,

Vincent

On Tue, Jan 13, 2015 at 10:34 PM, Miguel Vaz <pag...@gm...> wrote:

> Hi Vincent,
>
> Thats perfectly ok. Just glad you know what the problem is. I did spend
> the whole afternoon cursing over my code and lack of skills, hehe. :-)
>
> On a different note, how can i send a simple class over to an oracle
> procedure?
>
> Something like:
>
> class person{
>    string nome;
>    int id;
> }
>
> on the oracle side somethign like:
>
> create ...
> procedure_name(indata IN cursor){
>
>
> }
>
> Any pointer on this is great help.
>
> Again, no worries about the bug. :-)
>
> best regards,
> MV
>
>
>
>
>
>
>
>
>
>
>
>
> On Tue, Jan 13, 2015 at 6:40 PM, vincent rogier <vin...@gm...>
> wrote:
>
>> Hi Miguel,
>>
>> I apologize as there is an issue with binding string in the released
>> header files.
>>
>> I'm going to release an version 4.0.1 with some fixes for both C and C++
>> APIs this week.
>>
>> Regarding your issue, while waiting for the new packages, you can fix it
>> quickly by making the following simple modification :
>>
>> * in the file ocilib_impl.hpp, line 3860  (BindAdaptor template class
>> constructor)
>> * replace _size by size
>>
>> and it should work again :)
>>
>> Sorry for the inconvenience !
>>
>> Regards,
>>
>> Vincent
>>
>>
>> On Tue, Jan 13, 2015 at 6:15 PM, Miguel Vaz <pag...@gm...> wrote:
>>
>>> Hi,
>>> I cant a simple insert to work using a procedure and ocilib.
>>>
>>> The procedure:
>>>
>>> CREATE OR REPLACE
>>> PROCEDURE criasistema(idout OUT NUMBER, nm IN VARCHAR2) AS
>>> BEGIN
>>> insert into sistemas(NOME) values(nm) returning id_sistema into idout;
>>>
>>> END;
>>>
>>>
>>> Heres the c++ code:
>>>
>>> Environment::Initialize(Environment::Default | Environment::Threaded);
>>> string sql = "declare ids NUMBER begin criasistema(:ids,:nm); end;";
>>>
>>> try{
>>> con.Open(connectString, user, passw);
>>> Statement st(con);
>>> Statement st2(con);
>>> st.Prepare(sql);
>>> st.Bind(":ids", st2, BindInfo::Out);
>>> st.Bind(":nm", nome, 255, BindInfo::In);
>>> st.Execute();
>>>
>>> con.Commit();
>>> con.Close();
>>>
>>> }
>>> catch (Exception &ex){
>>> cout << ex.what() << endl;
>>> }
>>>
>>> Environment::Cleanup();
>>>
>>> It compiles but breaks at st.Bind(":nm", nome, 255, BindInfo::In);
>>> What am i doing wrong?
>>>
>>> I assumed it would be simply a matter of binding in and out variables
>>> and it shuld work, but not so easy.. :-P
>>>
>>> I am really not at easy with the binds. Next step will be to send a full
>>> object (int,string,...) array, but if i cant solve this simple string
>>> issue, i am not going anywahere.
>>>
>>> Thanks.
>>> MV
>>>
>>>
>>> ------------------------------------------------------------------------------
>>> New Year. New Location. New Benefits. New Data Center in Ashburn, VA.
>>> GigeNET is offering a free month of service with a new server in Ashburn.
>>> Choose from 2 high performing configs, both with 100TB of bandwidth.
>>> Higher redundancy.Lower latency.Increased capacity.Completely compliant.
>>> http://p.sf.net/sfu/gigenet
>>> _______________________________________________
>>> Orclib-users mailing list
>>> Orc...@li...
>>> https://lists.sourceforge.net/lists/listinfo/orclib-users
>>>
>>>
>>
>>
>> --
>> Vincent Rogier
>>
>
>


-- 
Vincent Rogier

Re: [Orclib-users] Simple insert with in variables to procedure

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

Hi Miguel,

I apologize as there is an issue with binding string in the released header
files.

I'm going to release an version 4.0.1 with some fixes for both C and C++
APIs this week.

Regarding your issue, while waiting for the new packages, you can fix it
quickly by making the following simple modification :

* in the file ocilib_impl.hpp, line 3860  (BindAdaptor template class
constructor)
* replace _size by size

and it should work again :)

Sorry for the inconvenience !

Regards,

Vincent


On Tue, Jan 13, 2015 at 6:15 PM, Miguel Vaz <pag...@gm...> wrote:

> Hi,
> I cant a simple insert to work using a procedure and ocilib.
>
> The procedure:
>
> CREATE OR REPLACE
> PROCEDURE criasistema(idout OUT NUMBER, nm IN VARCHAR2) AS
> BEGIN
> insert into sistemas(NOME) values(nm) returning id_sistema into idout;
>
> END;
>
>
> Heres the c++ code:
>
> Environment::Initialize(Environment::Default | Environment::Threaded);
> string sql = "declare ids NUMBER begin criasistema(:ids,:nm); end;";
>
> try{
> con.Open(connectString, user, passw);
> Statement st(con);
> Statement st2(con);
> st.Prepare(sql);
> st.Bind(":ids", st2, BindInfo::Out);
> st.Bind(":nm", nome, 255, BindInfo::In);
> st.Execute();
>
> con.Commit();
> con.Close();
>
> }
> catch (Exception &ex){
> cout << ex.what() << endl;
> }
>
> Environment::Cleanup();
>
> It compiles but breaks at st.Bind(":nm", nome, 255, BindInfo::In);
> What am i doing wrong?
>
> I assumed it would be simply a matter of binding in and out variables and
> it shuld work, but not so easy.. :-P
>
> I am really not at easy with the binds. Next step will be to send a full
> object (int,string,...) array, but if i cant solve this simple string
> issue, i am not going anywahere.
>
> Thanks.
> MV
>
>
> ------------------------------------------------------------------------------
> New Year. New Location. New Benefits. New Data Center in Ashburn, VA.
> GigeNET is offering a free month of service with a new server in Ashburn.
> Choose from 2 high performing configs, both with 100TB of bandwidth.
> Higher redundancy.Lower latency.Increased capacity.Completely compliant.
> http://p.sf.net/sfu/gigenet
> _______________________________________________
> Orclib-users mailing list
> Orc...@li...
> https://lists.sourceforge.net/lists/listinfo/orclib-users
>
>


-- 
Vincent Rogier

[Orclib-users] Simple insert with in variables to procedure

[Miguel V. <pag...@gm...>]

Hi,
I cant a simple insert to work using a procedure and ocilib.

The procedure:

CREATE OR REPLACE
PROCEDURE criasistema(idout OUT NUMBER, nm IN VARCHAR2) AS
BEGIN
insert into sistemas(NOME) values(nm) returning id_sistema into idout;

END;


Heres the c++ code:

Environment::Initialize(Environment::Default | Environment::Threaded);
string sql = "declare ids NUMBER begin criasistema(:ids,:nm); end;";

try{
con.Open(connectString, user, passw);
Statement st(con);
Statement st2(con);
st.Prepare(sql);
st.Bind(":ids", st2, BindInfo::Out);
st.Bind(":nm", nome, 255, BindInfo::In);
st.Execute();

con.Commit();
con.Close();

}
catch (Exception &ex){
cout << ex.what() << endl;
}

Environment::Cleanup();

It compiles but breaks at st.Bind(":nm", nome, 255, BindInfo::In);
What am i doing wrong?

I assumed it would be simply a matter of binding in and out variables and
it shuld work, but not so easy.. :-P

I am really not at easy with the binds. Next step will be to send a full
object (int,string,...) array, but if i cant solve this simple string
issue, i am not going anywahere.

Thanks.
MV

[Orclib-users] Calling oracle procedure and send object array

[Miguel V. <pag...@gm...>]

Hi again,

Last time i asked about receiving a resultset from a procedure, but now i
need the opposite: send to a procedure a c++ structure (specifically a list
of objects ou multidimensional array) to be inserted/updated in the
database.

I got so far as this, for the procedure:

CREATE OR REPLACE
PROCEDURE gravaSistema(lcorpos IN ARRAY) AS
BEGIN
total := lcorpos.count;
   FOR i in 1 .. total LOOP
      insert into CORPOS (nome) values(lcorpos(i));
   END LOOP;
END;

but its far from working. Even so since i dont have a clue on how to send
the array/list from c++ using ocilib. :-P

How can i send a structure to a procedure using ocilib?

Please forgive my probably amateur emails, but i cant find proper ocilib
c++ api documentation on procedures anywhere. I read the documentation but
all i find are descriptions of the methods, which doesnt help a newbie that
much.

Thanks in advance.

best regards,
MV

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

[Miguel V. <pag...@gm...>]

Absolutely awesome, vincent. :-)

Thank you also, Luiz.

And again, my apologies for the lack of knowledge to solve the problem.
Thank you for everything.

Heres the full code for future reference:

PL/SQL:

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;


c++ code:

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);
st.Execute();
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();

best regards,
miguel



On Sat, Jan 3, 2015 at 6:04 PM, vincent rogier <vin...@gm...>
wrote:

> use st.Execute() instead of st.Execute(sql)
>
> Vincent
>
> On Sat, Jan 3, 2015 at 7:01 PM, Miguel Vaz <pag...@gm...> wrote:
>
>> (sorry for the duplicate, i keep clicking reply, instead of reply all)
>>
>> If i change that i get:
>>
>> "The statement must be described to perform this operation"
>>
>> full code:
>>
>> string sql = "declare rc SYS_REFCURSOR; begin listatipos(:rc); end;";
>> //string sql = "begin listatipos(:1); end;";
>> con.Open(connectString, user, passw);
>> Statement st(con);
>> Statement st2(con);
>> st.Prepare(sql);
>> st.Bind(":rc", st2, BindInfo::Out);
>> //st.Bind(":1", st2, BindInfo::Out);
>> 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();
>>
>>
>> best regards,
>> Miguel
>>
>> On Sat, Jan 3, 2015 at 5:56 PM, vincent rogier <vin...@gm...>
>> wrote:
>>
>>> 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
>>>
>>
>>
>
>
> --
> Vincent Rogier
>

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

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

use st.Execute() instead of st.Execute(sql)

Vincent

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

> (sorry for the duplicate, i keep clicking reply, instead of reply all)
>
> If i change that i get:
>
> "The statement must be described to perform this operation"
>
> full code:
>
> string sql = "declare rc SYS_REFCURSOR; begin listatipos(:rc); end;";
> //string sql = "begin listatipos(:1); end;";
> con.Open(connectString, user, passw);
> Statement st(con);
> Statement st2(con);
> st.Prepare(sql);
> st.Bind(":rc", st2, BindInfo::Out);
> //st.Bind(":1", st2, BindInfo::Out);
> 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();
>
>
> best regards,
> Miguel
>
> On Sat, Jan 3, 2015 at 5:56 PM, vincent rogier <vin...@gm...>
> wrote:
>
>> 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
>>
>
>


-- 
Vincent Rogier

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

[Miguel V. <pag...@gm...>]

(sorry for the duplicate, i keep clicking reply, instead of reply all)

If i change that i get:

"The statement must be described to perform this operation"

full code:

string sql = "declare rc SYS_REFCURSOR; begin listatipos(:rc); end;";
//string sql = "begin listatipos(:1); end;";
con.Open(connectString, user, passw);
Statement st(con);
Statement st2(con);
st.Prepare(sql);
st.Bind(":rc", st2, BindInfo::Out);
//st.Bind(":1", st2, BindInfo::Out);
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();


best regards,
Miguel

On Sat, Jan 3, 2015 at 5:56 PM, vincent rogier <vin...@gm...>
wrote:

> 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
>

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

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

[Miguel V. <pag...@gm...>]

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
>>>
>>>
>>
>

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

[Miguel V. <pag...@gm...>]

​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
>>
>>
>

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

[Luiz R. C. <lu...@xh...>]

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
>
>

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

[Miguel V. <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
>>
>>

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

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

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
>
>

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

[Miguel V. <pag...@gm...>]

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.

[Orclib-users] array of OCI_Statement

[Rafael C. <cu...@gm...>]

Hi

is possible to build an array os dynamic OCI_Statement *?

using
OCI_Statement ** p = (OCI_Statement **) malloc(5*sizeof(OCI_Statment*)); is
not possible

or only is valid an single statement per time and i cannot cache more then
one in an array?

Regards
Luiz

Re: [Orclib-users] Result Fetch Status

[Rafael C. <cu...@gm...>]

Hi Vincent

Thanks , it worked

Regards
Luiz

2014-11-13 19:20 GMT-02:00 vincent rogier <vin...@gm...>:

> Hi,
>
> the equivalent of SQL_NO_DATA_FOUND is OCI_FetchNext()  returning FALSE
> with no error encountered errors.
> Thus, if you pass the flag OCI_ENV_CONTEXT to OCI_Initialize(), you can
> retrieve the last error after calling OCI_FetchNext().
> If OCI_FetchNext() returns FALSE and OCI_GetLastError() returns NULL its
> means SQL_NO_DATA_FOUND.
>
> Regards,
>
> Vincent
>
> On Wed, Nov 12, 2014 at 5:37 PM, Rafael Culik <cu...@gm...> wrote:
>
>> Hi. Is their an simple way to get the fetch_status from OCI_Resultset
>> structure?
>>
>> In ony my aplication o need to mimic ODBC SQLFetch( hStmt ); function and
>> check for the SQL_NO_DATA_FOUND returned. if their an way to mimic this
>> with orclib?
>>
>> Regards
>> Luiz
>>
>>
>>
>>
>> ------------------------------------------------------------------------------
>> Comprehensive Server Monitoring with Site24x7.
>> Monitor 10 servers for $9/Month.
>> Get alerted through email, SMS, voice calls or mobile push notifications.
>> Take corrective actions from your mobile device.
>>
>> http://pubads.g.doubleclick.net/gampad/clk?id=154624111&iu=/4140/ostg.clktrk
>> _______________________________________________
>> Orclib-users mailing list
>> Orc...@li...
>> https://lists.sourceforge.net/lists/listinfo/orclib-users
>>
>>
>
>
> --
> Vincent Rogier
>

Re: [Orclib-users] Result Fetch Status

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

Hi,

the equivalent of SQL_NO_DATA_FOUND is OCI_FetchNext()  returning FALSE
with no error encountered errors.
Thus, if you pass the flag OCI_ENV_CONTEXT to OCI_Initialize(), you can
retrieve the last error after calling OCI_FetchNext().
If OCI_FetchNext() returns FALSE and OCI_GetLastError() returns NULL its
means SQL_NO_DATA_FOUND.

Regards,

Vincent

On Wed, Nov 12, 2014 at 5:37 PM, Rafael Culik <cu...@gm...> wrote:

> Hi. Is their an simple way to get the fetch_status from OCI_Resultset
> structure?
>
> In ony my aplication o need to mimic ODBC SQLFetch( hStmt ); function and
> check for the SQL_NO_DATA_FOUND returned. if their an way to mimic this
> with orclib?
>
> Regards
> Luiz
>
>
>
>
> ------------------------------------------------------------------------------
> Comprehensive Server Monitoring with Site24x7.
> Monitor 10 servers for $9/Month.
> Get alerted through email, SMS, voice calls or mobile push notifications.
> Take corrective actions from your mobile device.
>
> http://pubads.g.doubleclick.net/gampad/clk?id=154624111&iu=/4140/ostg.clktrk
> _______________________________________________
> Orclib-users mailing list
> Orc...@li...
> https://lists.sourceforge.net/lists/listinfo/orclib-users
>
>


-- 
Vincent Rogier

[Orclib-users] Result Fetch Status

[Rafael C. <cu...@gm...>]

Hi. Is their an simple way to get the fetch_status from OCI_Resultset
structure?

In ony my aplication o need to mimic ODBC SQLFetch( hStmt ); function and
check for the SQL_NO_DATA_FOUND returned. if their an way to mimic this
with orclib?

Regards
Luiz

Re: [Orclib-users] UTF8 strings

[Gulácsi T. <gt...@gt...>]

Thanks, it is working :)

2014-05-20 20:49 GMT+02:00 vincent rogier <vin...@gm...>:
> Hello,
>
> Sorry for this really late response !
>
> Yes it seems correct.
>
> Regards,
>
> Vincent
>
>
> On Mon, Mar 24, 2014 at 12:12 PM, Gulácsi Tamás <gt...@gt...> wrote:
>>
>> Hi,
>>
>> I'm trying OCILIB with Go, which requires every string to be UTF-8.
>> I've compiled OCILIB with the "ansi" charset option, and I'm setting
>> the NLS_LANG environment variable to "american_america.AL32UTF8"
>> before calling OCI_Initializ().
>> Is this correct?
>>
>> Thanks, Tamás Gulácsi
>>
>>
>> ------------------------------------------------------------------------------
>> Learn Graph Databases - Download FREE O'Reilly Book
>> "Graph Databases" is the definitive new guide to graph databases and their
>> applications. Written by three acclaimed leaders in the field,
>> this first edition is now available. Download your free book today!
>> http://p.sf.net/sfu/13534_NeoTech
>> _______________________________________________
>> Orclib-users mailing list
>> Orc...@li...
>> https://lists.sourceforge.net/lists/listinfo/orclib-users
>
>
>
>
> --
> Vincent Rogier

Re: [Orclib-users] UTF8 strings

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

Hello,

Sorry for this really late response !

Yes it seems correct.

Regards,

Vincent


On Mon, Mar 24, 2014 at 12:12 PM, Gulácsi Tamás <gt...@gt...> wrote:

> Hi,
>
> I'm trying OCILIB with Go, which requires every string to be UTF-8.
> I've compiled OCILIB with the "ansi" charset option, and I'm setting
> the NLS_LANG environment variable to "american_america.AL32UTF8"
> before calling OCI_Initializ().
> Is this correct?
>
> Thanks, Tamás Gulácsi
>
>
> ------------------------------------------------------------------------------
> Learn Graph Databases - Download FREE O'Reilly Book
> "Graph Databases" is the definitive new guide to graph databases and their
> applications. Written by three acclaimed leaders in the field,
> this first edition is now available. Download your free book today!
> http://p.sf.net/sfu/13534_NeoTech
> _______________________________________________
> Orclib-users mailing list
> Orc...@li...
> https://lists.sourceforge.net/lists/listinfo/orclib-users
>



-- 
Vincent Rogier

[Orclib-users] UTF8 strings

[Gulácsi T. <gt...@gt...>]

Hi,

I'm trying OCILIB with Go, which requires every string to be UTF-8.
I've compiled OCILIB with the "ansi" charset option, and I'm setting
the NLS_LANG environment variable to "american_america.AL32UTF8"
before calling OCI_Initializ().
Is this correct?

Thanks, Tamás Gulácsi

[Orclib-users] Database Change Notification question

[Gulácsi T. <gt...@gt...>]

Hi,

I'd like to use DCN, but get "ORA-24912: Listener thread failed.
Failed to get IP address of host.".
I'm using a user/passw@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=p520)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=cigf.unosoft.local)))
connection string.

Where/how should this IP address be set?

During the registration, netstat -ntlp shows my process listens on
some random IPv6 ::1:port, then I get this error. Even if I set the
port to a fix (60999) number!

Is this DCN thing works reliable, or at all? Or shall I try the HTTP
notification method?

Thanks, Tamás Gulácsi

Re: [Orclib-users] Question about calling procedures in packages with custom types

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

Hi,

I've realized that for nested table, OCILIB does not provide support for
the DELETE method (that delete an item at a given index).
I've just added (OCI_CollDeleteAt() ) it and it will be part of 4.0.
release.
Will be committed into SVN within few days.


On Wed, Nov 13, 2013 at 9:48 PM, vincent rogier <vin...@gm...>wrote:

> Hi,
>
> Here is an example on how creating a collection, filling it with entries,
> iterating over entries and cleaning the collection :
>
> int main (int argc, char **argv)
>> {
>>     OCI_Connection *cn;
>>     OCI_Statement *st;
>>     OCI_Resultset *rs;
>>     OCI_TypeInfo *col_type;
>>     OCI_TypeInfo *obj_type;
>>     OCI_Coll * coll;
>>     OCI_Object *obj;
>>     OCI_Elem * elem;
>>     OCI_Iter *iter;
>>     int i;
>>
>>     OCI_Initialize(err_handler, NULL, OCI_ENV_DEFAULT );
>>
>>     cn = OCI_ConnectionCreate("db12c", "usr", "pwd", OCI_SESSION_DEFAULT);
>>     col_type = OCI_TypeInfoGet ( cn, "test_obj_coll", OCI_TIF_TYPE ) ;
>>     obj_type = OCI_TypeInfoGet ( cn, "test_obj", OCI_TIF_TYPE ) ;
>>
>>     coll = OCI_CollCreate( col_type ) ;
>>     obj  = OCI_ObjectCreate( cn, obj_type ) ;
>>     elem = OCI_ElemCreate ( col_type ) ;
>>
>>     for (i = 1; i <= 5000; i++)
>>     {
>>         dtext buf[50];
>>
>>         mtsprintf(buf, 50, "val1 - %04d", i);
>>         OCI_ObjectSetString ( obj, "val1", buf ) ;
>>
>>         mtsprintf(buf, 50, "val1 - %04d", i);
>>         OCI_ObjectSetString ( obj, "val2", buf ) ;
>>
>>
>>         OCI_ElemSetObject ( elem, obj ) ;
>>         OCI_CollAppend ( coll, elem ) ;
>>     }
>>
>>     OCI_ElemFree( elem );
>>     OCI_ObjectFree (obj ) ;
>>
>>
>>     iter = OCI_IterCreate ( coll ) ;
>>     elem = OCI_IterGetNext ( iter ) ;
>>
>>     while (elem)
>>     {
>>         obj = OCI_ElemGetObject( elem ) ;
>>
>>         printf("val1 = %s, val2 = %s\n", OCI_ObjectGetString(obj,
>> "val1"), OCI_ObjectGetString(obj, "val1"));
>>
>>
>>         elem = OCI_IterGetNext ( iter ) ;
>>     }
>>
>>     OCI_CollClear(coll);
>>
>>
>>     OCI_IterFree ( iter ) ;
>>     OCI_CollFree ( coll ) ;
>>     OCI_ConnectionFree ( cn ) ;
>>
>>     OCI_Cleanup () ;
>> }
>>
>
>
>
> On Tue, Nov 12, 2013 at 10:54 PM, vincent rogier <vin...@gm...>wrote:
>
>> I'll have a look tomorrow and let you know.
>> Le 12 nov. 2013 18:49, "Florian Grißmer" <spi...@gm...> a écrit :
>>
>> Hi Vincent
>>>
>>> Using this destroy-loop doesn't make any diffence
>>>
>>>   While ( elem != Null ) {
>>>
>>>     OCI_ObjectFree ( OCI_ElemGetObject( elem ) ) ;
>>>
>>>     elem = OCI_IterGetNext ( iter ) ;
>>>   }
>>>
>>> I still get the same error
>>>
>>>
>>> Only call OCI_xxxFree() çon object returned by OCI_xxxCreate()
>>>>
>>>
>>> Yes Thats why I thought i coul call the Function OCI_ElemFree on my
>>> elem, which I created with OCI_ElemCreate. Is it impossible to destroy
>>> the objects using only the collection-Object?
>>>
>>> Thx for your help
>>>
>>>
>>>
>>>>
>>>> Regards,
>>>>
>>>> Vincent
>>>>
>>>>
>>>> On Tue, Nov 12, 2013 at 4:12 PM, Florian Grißmer <spi...@gm...>wrote:
>>>>
>>>>> Hi Vincent
>>>>>
>>>>> Thx for you answer, I was afraid of that, I liked the package-scheme,
>>>>> It's a pity I can't use it this way.
>>>>>
>>>>> I have another question. I created and destroyed a list (collection of
>>>>> an object-type with two string-fields) with 5000 Elements as Objects and it
>>>>> still gives me this error at the end: *OCI Error 0, Found 5000
>>>>> unfreed OCI Object handles *after the OCI_Cleanup call. What am I
>>>>> doing wrong, I create and destroy all Elements
>>>>>
>>>>> I call OCI_ElemFree ( elem ), but because of this check:
>>>>>
>>>>>     if ((obj)->hstate == OCI_OBJECT_FETCHED_CLEAN)
>>>>>         return (ret);
>>>>>
>>>>> This Elements object ist not destroyed. Is this an effect of my test,
>>>>> that Elements in Lists, if they are not used in a request can't be
>>>>> destroyed and in a productive environment, where I would always call a
>>>>> PL/SQL procedure with this list, this would never happen? Or sth different?
>>>>>
>>>>> Thx for your help
>>>>>
>>>>>
>>>>>   OCI_Initialize ( err_handler, Null, OCI_ENV_DEFAULT) ) ;
>>>>>
>>>>>   con = OCI_ConnectionCreate ( db, user, pwd ) ;
>>>>>
>>>>>   list_typinf = OCI_TypeInfoGet ( con, name, OCI_TIF_TYPE ) ;
>>>>>
>>>>>   column = OCI_TypeInfoGetColumn ( list_typinf, 1 ) ;
>>>>>
>>>>>   col_typinf = OCI_ColumnGetTypeInfo ( column ) ;
>>>>>
>>>>>   coll = OCI_CollCreate ( list_typinf ) ;
>>>>>
>>>>>   obj = OCI_ObjectCreate ( con, col_typinf ) ;
>>>>>
>>>>>   OCI_ObjectSetString ( obj, field_1, field_1_value ) ;
>>>>>
>>>>>   OCI_ObjectSetString ( obj, field_2, field_2_value ) ;
>>>>>
>>>>>   For (int i = 1; i <= 5000; i++) {
>>>>>
>>>>>     elem = OCI_ElemCreate ( list_typinf ) ;
>>>>>
>>>>>     OCI_ElemSetObject ( elem, obj ) ;
>>>>>
>>>>>     OCI_CollAppend ( coll, elem ) ;
>>>>>   }
>>>>>
>>>>>   OCI_ObjectFree ( obj ) ;
>>>>>
>>>>>   iter = OCI_IterCreate ( coll ) ;
>>>>>
>>>>>   elem = OCI_IterGetNext ( iter ) ;
>>>>>
>>>>>   While ( elem != Null ) {
>>>>>
>>>>>     OCI_ElemFree ( elem ) ;
>>>>>
>>>>>     elem = OCI_IterGetNext ( iter ) ;
>>>>>   }
>>>>>
>>>>>   OCI_IterFree ( iter ) ;
>>>>>
>>>>>   OCI_CollFree ( coll ) ;
>>>>>
>>>>>   OCI_ConnectionFree ( con ) ;
>>>>>
>>>>>   OCI_Cleanup () ;
>>>>>
>>>>>
>>>>>
>>>>> On Mon, Nov 11, 2013 at 3:53 PM, vincent rogier <
>>>>> vin...@gm...> wrote:
>>>>>
>>>>>> Hello,
>>>>>>
>>>>>> PL/SQL records and tables of records cannot be used outside PL/SQL.
>>>>>> In order to use collections in PL/SQL,SQL and host programs, you must use
>>>>>> SQL collections (varrays or nested tables).
>>>>>> Thus you need to create an object type and a nested table of objects.
>>>>>> Change your record definition to an object type definition.
>>>>>> You can have a look at the ocilib demo source files for definition
>>>>>> and usage :)
>>>>>>
>>>>>> Regards,
>>>>>>
>>>>>> Vincent
>>>>>> Le 11 nov. 2013 14:13, "Florian Grißmer" <spi...@gm...> a
>>>>>> écrit :
>>>>>>
>>>>>>>  Hi
>>>>>>>
>>>>>>> I have a question about an OCI_Collection Argument of a procedure.
>>>>>>> Suppose I have a procedure defined in a package like this:
>>>>>>>
>>>>>>> CREATE OR REPLACE
>>>>>>> PACKAGE TEST_PACK AS
>>>>>>>
>>>>>>>   TYPE test_rcd IS RECORD ( testnum Number(4,0)
>>>>>>>                           , test_varchar Varchar(14) );
>>>>>>>
>>>>>>>   TYPE list_of_rcds IS TABLE OF test_rcd;
>>>>>>>
>>>>>>>   Procedure test_prc1 ( rcd IN Out list_of_rcds ) ;
>>>>>>>
>>>>>>>
>>>>>>> END TEST_PACK;
>>>>>>>
>>>>>>> Ho do I call this procedure with a binded argument? To build the
>>>>>>> "rcd"-OCI_Collection Variable i have to have an OCI_TypeInf, which I cant
>>>>>>> get, because the type is inside a package. OCI_TypeInfoGet() uses
>>>>>>> "Describe" and this can't access types inside packages.
>>>>>>>
>>>>>>> Thx in advance.
>>>>>>>
>>>>>>>
>>>>>>> ------------------------------------------------------------------------------
>>>>>>> November Webinars for C, C++, Fortran Developers
>>>>>>> Accelerate application performance with scalable programming models.
>>>>>>> Explore
>>>>>>> techniques for threading, error checking, porting, and tuning. Get
>>>>>>> the most
>>>>>>> from the latest Intel processors and coprocessors. See abstracts and
>>>>>>> register
>>>>>>>
>>>>>>> http://pubads.g.doubleclick.net/gampad/clk?id=60136231&iu=/4140/ostg.clktrk
>>>>>>> _______________________________________________
>>>>>>> Orclib-users mailing list
>>>>>>> Orc...@li...
>>>>>>> https://lists.sourceforge.net/lists/listinfo/orclib-users
>>>>>>>
>>>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> Vincent Rogier
>>>>
>>>
>>>
>
>
> --
> Vincent Rogier
>



-- 
Vincent Rogier

Re: [Orclib-users] Question about calling procedures in packages with custom types

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

Hi,

Here is an example on how creating a collection, filling it with entries,
iterating over entries and cleaning the collection :

int main (int argc, char **argv)
> {
>     OCI_Connection *cn;
>     OCI_Statement *st;
>     OCI_Resultset *rs;
>     OCI_TypeInfo *col_type;
>     OCI_TypeInfo *obj_type;
>     OCI_Coll * coll;
>     OCI_Object *obj;
>     OCI_Elem * elem;
>     OCI_Iter *iter;
>     int i;
>
>     OCI_Initialize(err_handler, NULL, OCI_ENV_DEFAULT );
>
>     cn = OCI_ConnectionCreate("db12c", "usr", "pwd", OCI_SESSION_DEFAULT);
>     col_type = OCI_TypeInfoGet ( cn, "test_obj_coll", OCI_TIF_TYPE ) ;
>     obj_type = OCI_TypeInfoGet ( cn, "test_obj", OCI_TIF_TYPE ) ;
>
>     coll = OCI_CollCreate( col_type ) ;
>     obj  = OCI_ObjectCreate( cn, obj_type ) ;
>     elem = OCI_ElemCreate ( col_type ) ;
>
>     for (i = 1; i <= 5000; i++)
>     {
>         dtext buf[50];
>
>         mtsprintf(buf, 50, "val1 - %04d", i);
>         OCI_ObjectSetString ( obj, "val1", buf ) ;
>
>         mtsprintf(buf, 50, "val1 - %04d", i);
>         OCI_ObjectSetString ( obj, "val2", buf ) ;
>
>         OCI_ElemSetObject ( elem, obj ) ;
>         OCI_CollAppend ( coll, elem ) ;
>     }
>
>     OCI_ElemFree( elem );
>     OCI_ObjectFree (obj ) ;
>
>     iter = OCI_IterCreate ( coll ) ;
>     elem = OCI_IterGetNext ( iter ) ;
>
>     while (elem)
>     {
>         obj = OCI_ElemGetObject( elem ) ;
>
>         printf("val1 = %s, val2 = %s\n", OCI_ObjectGetString(obj, "val1"),
> OCI_ObjectGetString(obj, "val1"));
>
>         elem = OCI_IterGetNext ( iter ) ;
>     }
>
>     OCI_CollClear(coll);
>
>     OCI_IterFree ( iter ) ;
>     OCI_CollFree ( coll ) ;
>     OCI_ConnectionFree ( cn ) ;
>
>     OCI_Cleanup () ;
> }
>



On Tue, Nov 12, 2013 at 10:54 PM, vincent rogier <vin...@gm...>wrote:

> I'll have a look tomorrow and let you know.
> Le 12 nov. 2013 18:49, "Florian Grißmer" <spi...@gm...> a écrit :
>
> Hi Vincent
>>
>> Using this destroy-loop doesn't make any diffence
>>
>>   While ( elem != Null ) {
>>
>>     OCI_ObjectFree ( OCI_ElemGetObject( elem ) ) ;
>>
>>     elem = OCI_IterGetNext ( iter ) ;
>>   }
>>
>> I still get the same error
>>
>>
>> Only call OCI_xxxFree() çon object returned by OCI_xxxCreate()
>>>
>>
>> Yes Thats why I thought i coul call the Function OCI_ElemFree on my
>> elem, which I created with OCI_ElemCreate. Is it impossible to destroy
>> the objects using only the collection-Object?
>>
>> Thx for your help
>>
>>
>>
>>>
>>> Regards,
>>>
>>> Vincent
>>>
>>>
>>> On Tue, Nov 12, 2013 at 4:12 PM, Florian Grißmer <spi...@gm...>wrote:
>>>
>>>> Hi Vincent
>>>>
>>>> Thx for you answer, I was afraid of that, I liked the package-scheme,
>>>> It's a pity I can't use it this way.
>>>>
>>>> I have another question. I created and destroyed a list (collection of
>>>> an object-type with two string-fields) with 5000 Elements as Objects and it
>>>> still gives me this error at the end: *OCI Error 0, Found 5000 unfreed
>>>> OCI Object handles *after the OCI_Cleanup call. What am I doing wrong,
>>>> I create and destroy all Elements
>>>>
>>>> I call OCI_ElemFree ( elem ), but because of this check:
>>>>
>>>>     if ((obj)->hstate == OCI_OBJECT_FETCHED_CLEAN)
>>>>         return (ret);
>>>>
>>>> This Elements object ist not destroyed. Is this an effect of my test,
>>>> that Elements in Lists, if they are not used in a request can't be
>>>> destroyed and in a productive environment, where I would always call a
>>>> PL/SQL procedure with this list, this would never happen? Or sth different?
>>>>
>>>> Thx for your help
>>>>
>>>>
>>>>   OCI_Initialize ( err_handler, Null, OCI_ENV_DEFAULT) ) ;
>>>>
>>>>   con = OCI_ConnectionCreate ( db, user, pwd ) ;
>>>>
>>>>   list_typinf = OCI_TypeInfoGet ( con, name, OCI_TIF_TYPE ) ;
>>>>
>>>>   column = OCI_TypeInfoGetColumn ( list_typinf, 1 ) ;
>>>>
>>>>   col_typinf = OCI_ColumnGetTypeInfo ( column ) ;
>>>>
>>>>   coll = OCI_CollCreate ( list_typinf ) ;
>>>>
>>>>   obj = OCI_ObjectCreate ( con, col_typinf ) ;
>>>>
>>>>   OCI_ObjectSetString ( obj, field_1, field_1_value ) ;
>>>>
>>>>   OCI_ObjectSetString ( obj, field_2, field_2_value ) ;
>>>>
>>>>   For (int i = 1; i <= 5000; i++) {
>>>>
>>>>     elem = OCI_ElemCreate ( list_typinf ) ;
>>>>
>>>>     OCI_ElemSetObject ( elem, obj ) ;
>>>>
>>>>     OCI_CollAppend ( coll, elem ) ;
>>>>   }
>>>>
>>>>   OCI_ObjectFree ( obj ) ;
>>>>
>>>>   iter = OCI_IterCreate ( coll ) ;
>>>>
>>>>   elem = OCI_IterGetNext ( iter ) ;
>>>>
>>>>   While ( elem != Null ) {
>>>>
>>>>     OCI_ElemFree ( elem ) ;
>>>>
>>>>     elem = OCI_IterGetNext ( iter ) ;
>>>>   }
>>>>
>>>>   OCI_IterFree ( iter ) ;
>>>>
>>>>   OCI_CollFree ( coll ) ;
>>>>
>>>>   OCI_ConnectionFree ( con ) ;
>>>>
>>>>   OCI_Cleanup () ;
>>>>
>>>>
>>>>
>>>> On Mon, Nov 11, 2013 at 3:53 PM, vincent rogier <vin...@gm...
>>>> > wrote:
>>>>
>>>>> Hello,
>>>>>
>>>>> PL/SQL records and tables of records cannot be used outside PL/SQL. In
>>>>> order to use collections in PL/SQL,SQL and host programs, you must use SQL
>>>>> collections (varrays or nested tables).
>>>>> Thus you need to create an object type and a nested table of objects.
>>>>> Change your record definition to an object type definition.
>>>>> You can have a look at the ocilib demo source files for definition and
>>>>> usage :)
>>>>>
>>>>> Regards,
>>>>>
>>>>> Vincent
>>>>> Le 11 nov. 2013 14:13, "Florian Grißmer" <spi...@gm...> a
>>>>> écrit :
>>>>>
>>>>>>  Hi
>>>>>>
>>>>>> I have a question about an OCI_Collection Argument of a procedure.
>>>>>> Suppose I have a procedure defined in a package like this:
>>>>>>
>>>>>> CREATE OR REPLACE
>>>>>> PACKAGE TEST_PACK AS
>>>>>>
>>>>>>   TYPE test_rcd IS RECORD ( testnum Number(4,0)
>>>>>>                           , test_varchar Varchar(14) );
>>>>>>
>>>>>>   TYPE list_of_rcds IS TABLE OF test_rcd;
>>>>>>
>>>>>>   Procedure test_prc1 ( rcd IN Out list_of_rcds ) ;
>>>>>>
>>>>>>
>>>>>> END TEST_PACK;
>>>>>>
>>>>>> Ho do I call this procedure with a binded argument? To build the
>>>>>> "rcd"-OCI_Collection Variable i have to have an OCI_TypeInf, which I cant
>>>>>> get, because the type is inside a package. OCI_TypeInfoGet() uses
>>>>>> "Describe" and this can't access types inside packages.
>>>>>>
>>>>>> Thx in advance.
>>>>>>
>>>>>>
>>>>>> ------------------------------------------------------------------------------
>>>>>> November Webinars for C, C++, Fortran Developers
>>>>>> Accelerate application performance with scalable programming models.
>>>>>> Explore
>>>>>> techniques for threading, error checking, porting, and tuning. Get
>>>>>> the most
>>>>>> from the latest Intel processors and coprocessors. See abstracts and
>>>>>> register
>>>>>>
>>>>>> http://pubads.g.doubleclick.net/gampad/clk?id=60136231&iu=/4140/ostg.clktrk
>>>>>> _______________________________________________
>>>>>> Orclib-users mailing list
>>>>>> Orc...@li...
>>>>>> https://lists.sourceforge.net/lists/listinfo/orclib-users
>>>>>>
>>>>>>
>>>>
>>>
>>>
>>> --
>>> Vincent Rogier
>>>
>>
>>


-- 
Vincent Rogier

Re: [Orclib-users] Question about calling procedures in packages with custom types

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

I'll have a look tomorrow and let you know.
Le 12 nov. 2013 18:49, "Florian Grißmer" <spi...@gm...> a écrit :

> Hi Vincent
>
> Using this destroy-loop doesn't make any diffence
>
>   While ( elem != Null ) {
>
>     OCI_ObjectFree ( OCI_ElemGetObject( elem ) ) ;
>
>     elem = OCI_IterGetNext ( iter ) ;
>   }
>
> I still get the same error
>
>
> Only call OCI_xxxFree() çon object returned by OCI_xxxCreate()
>>
>
> Yes Thats why I thought i coul call the Function OCI_ElemFree on my elem,
> which I created with OCI_ElemCreate. Is it impossible to destroy the
> objects using only the collection-Object?
>
> Thx for your help
>
>
>
>>
>> Regards,
>>
>> Vincent
>>
>>
>> On Tue, Nov 12, 2013 at 4:12 PM, Florian Grißmer <spi...@gm...>wrote:
>>
>>> Hi Vincent
>>>
>>> Thx for you answer, I was afraid of that, I liked the package-scheme,
>>> It's a pity I can't use it this way.
>>>
>>> I have another question. I created and destroyed a list (collection of
>>> an object-type with two string-fields) with 5000 Elements as Objects and it
>>> still gives me this error at the end: *OCI Error 0, Found 5000 unfreed
>>> OCI Object handles *after the OCI_Cleanup call. What am I doing wrong,
>>> I create and destroy all Elements
>>>
>>> I call OCI_ElemFree ( elem ), but because of this check:
>>>
>>>     if ((obj)->hstate == OCI_OBJECT_FETCHED_CLEAN)
>>>         return (ret);
>>>
>>> This Elements object ist not destroyed. Is this an effect of my test,
>>> that Elements in Lists, if they are not used in a request can't be
>>> destroyed and in a productive environment, where I would always call a
>>> PL/SQL procedure with this list, this would never happen? Or sth different?
>>>
>>> Thx for your help
>>>
>>>
>>>   OCI_Initialize ( err_handler, Null, OCI_ENV_DEFAULT) ) ;
>>>
>>>   con = OCI_ConnectionCreate ( db, user, pwd ) ;
>>>
>>>   list_typinf = OCI_TypeInfoGet ( con, name, OCI_TIF_TYPE ) ;
>>>
>>>   column = OCI_TypeInfoGetColumn ( list_typinf, 1 ) ;
>>>
>>>   col_typinf = OCI_ColumnGetTypeInfo ( column ) ;
>>>
>>>   coll = OCI_CollCreate ( list_typinf ) ;
>>>
>>>   obj = OCI_ObjectCreate ( con, col_typinf ) ;
>>>
>>>   OCI_ObjectSetString ( obj, field_1, field_1_value ) ;
>>>
>>>   OCI_ObjectSetString ( obj, field_2, field_2_value ) ;
>>>
>>>   For (int i = 1; i <= 5000; i++) {
>>>
>>>     elem = OCI_ElemCreate ( list_typinf ) ;
>>>
>>>     OCI_ElemSetObject ( elem, obj ) ;
>>>
>>>     OCI_CollAppend ( coll, elem ) ;
>>>   }
>>>
>>>   OCI_ObjectFree ( obj ) ;
>>>
>>>   iter = OCI_IterCreate ( coll ) ;
>>>
>>>   elem = OCI_IterGetNext ( iter ) ;
>>>
>>>   While ( elem != Null ) {
>>>
>>>     OCI_ElemFree ( elem ) ;
>>>
>>>     elem = OCI_IterGetNext ( iter ) ;
>>>   }
>>>
>>>   OCI_IterFree ( iter ) ;
>>>
>>>   OCI_CollFree ( coll ) ;
>>>
>>>   OCI_ConnectionFree ( con ) ;
>>>
>>>   OCI_Cleanup () ;
>>>
>>>
>>>
>>> On Mon, Nov 11, 2013 at 3:53 PM, vincent rogier <vin...@gm...>wrote:
>>>
>>>> Hello,
>>>>
>>>> PL/SQL records and tables of records cannot be used outside PL/SQL. In
>>>> order to use collections in PL/SQL,SQL and host programs, you must use SQL
>>>> collections (varrays or nested tables).
>>>> Thus you need to create an object type and a nested table of objects.
>>>> Change your record definition to an object type definition.
>>>> You can have a look at the ocilib demo source files for definition and
>>>> usage :)
>>>>
>>>> Regards,
>>>>
>>>> Vincent
>>>> Le 11 nov. 2013 14:13, "Florian Grißmer" <spi...@gm...> a écrit
>>>> :
>>>>
>>>>>  Hi
>>>>>
>>>>> I have a question about an OCI_Collection Argument of a procedure.
>>>>> Suppose I have a procedure defined in a package like this:
>>>>>
>>>>> CREATE OR REPLACE
>>>>> PACKAGE TEST_PACK AS
>>>>>
>>>>>   TYPE test_rcd IS RECORD ( testnum Number(4,0)
>>>>>                           , test_varchar Varchar(14) );
>>>>>
>>>>>   TYPE list_of_rcds IS TABLE OF test_rcd;
>>>>>
>>>>>   Procedure test_prc1 ( rcd IN Out list_of_rcds ) ;
>>>>>
>>>>>
>>>>> END TEST_PACK;
>>>>>
>>>>> Ho do I call this procedure with a binded argument? To build the
>>>>> "rcd"-OCI_Collection Variable i have to have an OCI_TypeInf, which I cant
>>>>> get, because the type is inside a package. OCI_TypeInfoGet() uses
>>>>> "Describe" and this can't access types inside packages.
>>>>>
>>>>> Thx in advance.
>>>>>
>>>>>
>>>>> ------------------------------------------------------------------------------
>>>>> November Webinars for C, C++, Fortran Developers
>>>>> Accelerate application performance with scalable programming models.
>>>>> Explore
>>>>> techniques for threading, error checking, porting, and tuning. Get the
>>>>> most
>>>>> from the latest Intel processors and coprocessors. See abstracts and
>>>>> register
>>>>>
>>>>> http://pubads.g.doubleclick.net/gampad/clk?id=60136231&iu=/4140/ostg.clktrk
>>>>> _______________________________________________
>>>>> Orclib-users mailing list
>>>>> Orc...@li...
>>>>> https://lists.sourceforge.net/lists/listinfo/orclib-users
>>>>>
>>>>>
>>>
>>
>>
>> --
>> Vincent Rogier
>>
>
>