ref cursor in C++ using SQLAPI++
Peter Koukoulis <[email protected]> Fri, 14 Jul 2017 11:22:09 +0000
| Newsgroups | gmane.comp.db.postgresql.interfaces |
|---|---|
| Message-ID | <CABpxA9i1fLfp7qatRjXVn7=fn4xGD8isVvT7KEqrfFAz68NgMw@mail.gmail.com> |
--94eb2c19c3382da8a7055445431b
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Hi
I am able to pass a ref cursor out of an Oracle stored procedure, using the
C++ SQLAPI++ library, as shown below:
Is it possible to create a similar stored proc, that takes a string as a
parameter, and outputs a ref cursor?
The ref cursor, should be a weak ref cursor, since the result set is based
on a string.
Thanks
*C++ code*
#include <iostream>
#include </home/mw/SQLAPI/include/SQLAPI.h>
=E2=80=A6
int main(int argc, char* argv[]) {
SAConnection con; // connection object
try {
con.Connect("//10.11.12.18:1521/ftnode", "ordb", "ordb",
SA_Oracle_Client);
SACommand cmd(&con);
cmd.setCommandText("pkg_ref_cursor.get_dref");
cmd.Param("v_sql").setAsString() =3D"select x,y from test1"; // input
parameter
=E2=80=A6
cmd.Execute();
std::cout << "Stored procedure executed OK!" << "\n";
SACommand *pRefCursor =3D cmd.Param("REFCURSOR"); //output parameter
// fetch results row by row and print results
while(pRefCursor->FetchNext())
std::cout << (const char*)pRefCursor->Field(1).Name() << " =3D " <<
pRefCursor->Field(1).asLong() << ", "
<< (const char*)pRefCursor->Field(2).Name() << " =3D " <<
(const char*)pRefCursor->Field(2).asString()
<< "\n";
}
catch(SAException &x) {
try { con.Rollback(); }
=E2=80=A6
catch(SAException &) { }
// print error message
std::cout << (const char*)x.ErrText() << "\n";
}
return 0;
}
*=E2=80=A6Stored proc PL/SQL code:*
create or replace package pkg_ref_cursor as
procedure get_dref(v_sql in varchar2, refcursor out sys_refcursor);
end pkg_ref_cursor;
/
create or replace package body pkg_ref_cursor as
procedure get_dref(v_sql in varchar2, refcursor out sys_refcursor) as
v_Cursor binary_integer :=3D dbms_sql.open_cursor;
v_Ref sys_refcursor;
v_Exec binary_integer;
begin
dbms_sql.parse(v_Cursor, v_sql, dbms_sql.native);
v_Exec :=3D dbms_sql.execute(v_Cursor);
v_Ref :=3D dbms_sql.to_refcursor(v_Cursor);
refcursor :=3D v_Ref;
end get_dref;
end pkg_ref_cursor;
/
$ ./ora_ref_cursor
Stored procedure executed OK!
X =3D 1, Y =3D Hello
X =3D 2, Y =3D goodbye
X =3D 3, Y =3D greet
X =3D 4, Y =3D welcome
X =3D 5, Y =3D lag
X =3D 6, Y =3D fill
X =3D 7, Y =3D fill
X =3D 8, Y =3D FFF
--94eb2c19c3382da8a7055445431b
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
<div dir=3D"ltr">Hi=C2=A0<div><br></div><div>I am able to pass a ref cursor=
out of an Oracle stored procedure, using the C++ SQLAPI++ library, as show=
n below:</div><div><br></div><div>Is it possible to create a similar stored=
proc, that takes a string as a parameter, and outputs a ref cursor?</div><=
div>The ref cursor, should be a weak ref cursor, since the result set is ba=
sed on a string.</div><div><br></div><div>Thanks</div><div><br></div><div><=
b>C++ code</b></div><div><div class=3D"inbox-inbox-uyb8Gf" style=3D"color:r=
gb(33,33,33);font-size:13px"><div><div class=3D"inbox-inbox-F3hlO"><div dir=
=3D"ltr"><div><div><font face=3D"monospace">#include <iostream></font=
></div><div><font face=3D"monospace">#include </home/mw/SQLAPI/include/S=
QLAPI.h></font></div></div></div></div></div></div><div class=3D"inbox-i=
nbox-uyb8Gf" style=3D"color:rgb(33,33,33);font-size:13px"><div><div title=
=3D"Hide quoted text" tabindex=3D"0" class=3D"inbox-inbox-mg" style=3D"bord=
er-radius:2px;color:rgb(117,117,117);display:inline-block;padding:0px 2px">=
=E2=80=A6</div><div class=3D"inbox-inbox-vI" style=3D"border-left:1px solid=
rgb(224,224,224);color:rgb(117,117,117);padding:10px"><div dir=3D"ltr"><di=
v><div><font face=3D"monospace">int main(int argc, char* argv[]) {</font></=
div><div><font face=3D"monospace">=C2=A0 SAConnection con; // connection ob=
ject</font></div><div><font face=3D"monospace"><br></font></div><div><font =
face=3D"monospace">=C2=A0 try {</font></div><div><font face=3D"monospace">=
=C2=A0 =C2=A0 con.Connect("//<a href=3D"http://10.11.12.18:1521/ftnode=
" target=3D"_blank">10.11.12.18:1521/ftnode</a>", "ordb", &q=
uot;ordb", SA_Oracle_Client);</font></div><div><font face=3D"monospace=
">=C2=A0 =C2=A0 SACommand cmd(&con);</font></div><div><font face=3D"mon=
ospace">=C2=A0=C2=A0</font></div></div></div></div></div></div><div class=
=3D"inbox-inbox-uyb8Gf" style=3D"color:rgb(33,33,33);font-size:13px"><div><=
div class=3D"inbox-inbox-F3hlO"><div dir=3D"ltr"><div><div><font face=3D"mo=
nospace">=C2=A0 =C2=A0 cmd.setCommandText("pkg_ref_cursor.get_dref&quo=
t;);</font></div><div><font face=3D"monospace">=C2=A0 =C2=A0=C2=A0</font></=
div><div><font face=3D"monospace">=C2=A0 =C2=A0 cmd.Param("v_sql"=
).setAsString() =3D"select x,y from test1"; // input parameter</f=
ont></div></div></div></div></div></div><div class=3D"inbox-inbox-uyb8Gf" s=
tyle=3D"color:rgb(33,33,33);font-size:13px"><div><div title=3D"Hide quoted =
text" tabindex=3D"0" class=3D"inbox-inbox-mg" style=3D"border-radius:2px;co=
lor:rgb(117,117,117);display:inline-block;padding:0px 2px">=E2=80=A6</div><=
div class=3D"inbox-inbox-vI" style=3D"border-left:1px solid rgb(224,224,224=
);color:rgb(117,117,117);padding:10px"><div dir=3D"ltr"><div><div><font fac=
e=3D"monospace">=C2=A0 =C2=A0 cmd.Execute();</font></div><div><font face=3D=
"monospace">=C2=A0 =C2=A0 std::cout << "Stored procedure execute=
d OK!" << "\n";</font></div><div><font face=3D"monospa=
ce">=C2=A0=C2=A0</font></div></div></div></div></div></div><div class=3D"in=
box-inbox-uyb8Gf" style=3D"color:rgb(33,33,33);font-size:13px"><div><div cl=
ass=3D"inbox-inbox-F3hlO"><div dir=3D"ltr"><div><div><font face=3D"monospac=
e">=C2=A0 =C2=A0 SACommand *pRefCursor =3D cmd.Param("REFCURSOR")=
; //output parameter</font></div><div><font face=3D"monospace">=C2=A0=C2=A0=
</font></div><div><font face=3D"monospace">=C2=A0 =C2=A0 // fetch results r=
ow by row and print results</font></div><div><font face=3D"monospace">=C2=
=A0 =C2=A0 while(pRefCursor->FetchNext())</font></div><div><font face=3D=
"monospace">=C2=A0 =C2=A0 =C2=A0 std::cout << (const char*)pRefCursor=
->Field(1).Name() << " =3D " << pRefCursor->Fie=
ld(1).asLong() << ", "</font></div><div><font face=3D"monos=
pace">=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 << (con=
st char*)pRefCursor->Field(2).Name() << " =3D " <<=
(const char*)pRefCursor->Field(2).asString()=C2=A0</font></div><div><fo=
nt face=3D"monospace">=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 << "\n";</font></div><div><font face=3D"monospace">=C2=
=A0 }</font></div><div><font face=3D"monospace"><br></font></div><div><font=
face=3D"monospace">=C2=A0 catch(SAException &x) {</font></div><div><fo=
nt face=3D"monospace">=C2=A0 =C2=A0 try { con.Rollback(); }</font></div></d=
iv></div></div></div></div><div class=3D"inbox-inbox-uyb8Gf" style=3D"color=
:rgb(33,33,33);font-size:13px"><div><div title=3D"Hide quoted text" tabinde=
x=3D"0" class=3D"inbox-inbox-mg" style=3D"border-radius:2px;color:rgb(117,1=
17,117);display:inline-block;padding:0px 2px">=E2=80=A6</div><div class=3D"=
inbox-inbox-vI" style=3D"border-left:1px solid rgb(224,224,224);color:rgb(1=
17,117,117);padding:10px"><div dir=3D"ltr"><div><div><font face=3D"monospac=
e">=C2=A0 =C2=A0 catch(SAException &) { }</font></div><div><font face=
=3D"monospace">=C2=A0 =C2=A0 // print error message</font></div></div></div=
></div></div></div><div class=3D"inbox-inbox-uyb8Gf" style=3D"color:rgb(33,=
33,33);font-size:13px"><div><div class=3D"inbox-inbox-F3hlO"><div dir=3D"lt=
r"><div><div><font face=3D"monospace">=C2=A0 =C2=A0 std::cout << (con=
st char*)x.ErrText() << "\n";</font></div><div><font face=
=3D"monospace">=C2=A0 }</font></div><div><font face=3D"monospace">=C2=A0 re=
turn 0;</font></div><div><font face=3D"monospace">}</font></div><div><font =
face=3D"monospace"><br></font></div><div><font face=3D"monospace"><br></fon=
t></div></div></div></div></div></div><div class=3D"inbox-inbox-uyb8Gf" sty=
le=3D"font-size:13px"><div style=3D"color:rgb(33,33,33)"><div title=3D"Hide=
quoted text" tabindex=3D"0" class=3D"inbox-inbox-mg" style=3D"border-radiu=
s:2px;color:rgb(117,117,117);display:inline-block;padding:0px 2px"><b>=E2=
=80=A6Stored proc PL/SQL code:</b></div></div><div><font color=3D"#757575">=
<br></font><div class=3D"inbox-inbox-vI" style=3D"color:rgb(117,117,117);bo=
rder-left:1px solid rgb(224,224,224);padding:10px"><div dir=3D"ltr"><div><d=
iv><font face=3D"monospace">create or replace package pkg_ref_cursor as=C2=
=A0</font></div><div><font face=3D"monospace">=C2=A0 procedure get_dref(v_s=
ql in varchar2, refcursor out sys_refcursor);</font></div><div><font face=
=3D"monospace">end pkg_ref_cursor;</font></div><div><font face=3D"monospace=
">/</font></div><div><font face=3D"monospace"><br></font></div><div><font f=
ace=3D"monospace">create or replace package body pkg_ref_cursor as</font></=
div><div><font face=3D"monospace">=C2=A0 procedure get_dref(v_sql in varcha=
r2, refcursor out sys_refcursor) as</font></div><div><font face=3D"monospac=
e">=C2=A0 =C2=A0 v_Cursor =C2=A0binary_integer :=3D dbms_sql.open_cursor;</=
font></div><div><font face=3D"monospace">=C2=A0 =C2=A0 v_Ref =C2=A0 =C2=A0 =
sys_refcursor;</font></div><div><font face=3D"monospace">=C2=A0 =C2=A0 v_Ex=
ec =C2=A0 =C2=A0binary_integer;</font></div><div><font face=3D"monospace">=
=C2=A0 =C2=A0 begin</font></div><div><font face=3D"monospace"><br></font></=
div><div><font face=3D"monospace">=C2=A0 =C2=A0 =C2=A0 dbms_sql.parse(v_Cur=
sor, v_sql, dbms_sql.native);</font></div><div><font face=3D"monospace">=C2=
=A0 =C2=A0 =C2=A0 v_Exec :=3D dbms_sql.execute(v_Cursor);</font></div><div>=
<font face=3D"monospace">=C2=A0 =C2=A0 =C2=A0 v_Ref :=3D dbms_sql.to_refcur=
sor(v_Cursor);</font></div><div><font face=3D"monospace">=C2=A0 =C2=A0 =C2=
=A0 refcursor =C2=A0:=3D =C2=A0v_Ref;</font></div><div><font face=3D"monosp=
ace">=C2=A0 end get_dref;</font></div><div><font face=3D"monospace"><br></f=
ont></div><div><font face=3D"monospace">end pkg_ref_cursor;</font></div><di=
v><font face=3D"monospace"><span style=3D"font-size:10.5625px">/</span></fo=
nt></div></div><div><font face=3D"monospace"><span style=3D"font-size:10.56=
25px"><br></span></font></div></div></div></div></div><div class=3D"inbox-i=
nbox-uyb8Gf" style=3D"color:rgb(33,33,33);font-size:13px"><div><div class=
=3D"inbox-inbox-F3hlO"><div dir=3D"ltr"><div><font face=3D"monospace"><div>=
$ ./ora_ref_cursor</div><div>Stored procedure executed OK!</div><div>X =3D =
1, Y =3D Hello</div><div>X =3D 2, Y =3D goodbye</div><div>X =3D 3, Y =3D gr=
eet</div><div>X =3D 4, Y =3D welcome</div><div>X =3D 5, Y =3D lag</div><div=
>X =3D 6, Y =3D fill</div><div>X =3D 7, Y =3D fill</div><div>X =3D 8, Y =3D=
FFF</div></font></div></div></div></div></div></div></div>
--94eb2c19c3382da8a7055445431b--