Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dVygJ-00071Y-Lh for pgsql-interfaces@arkaria.postgresql.org; Fri, 14 Jul 2017 11:22:47 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dVygJ-0002LE-8N for pgsql-interfaces@arkaria.postgresql.org; Fri, 14 Jul 2017 11:22:47 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dVyfx-0001gs-32 for pgsql-interfaces@postgresql.org; Fri, 14 Jul 2017 11:22:25 +0000 Received: from mail-pg0-x233.google.com ([2607:f8b0:400e:c05::233]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dVyft-0005P1-K7 for pgsql-interfaces@postgresql.org; Fri, 14 Jul 2017 11:22:23 +0000 Received: by mail-pg0-x233.google.com with SMTP id t186so44587705pgb.1 for ; Fri, 14 Jul 2017 04:22:21 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:from:date:message-id:subject:to; bh=mHTedVoYWGwlgJFVt/GRV+25fKCZuhRzzThgB+nxXs4=; b=Y5t7nbaM1J6zg4wkJQb4pNpLkF7HtYgr50yomv3sKOtZizdfmV9Oj7iTbadna2Od38 gvw/9x0PDPqRWy89B8QlFNtZuWGxGjfuLrCPMglvlArXG7M+tgKJSHNibaDRN6xcTvoh /oaTl5dEWK3ENDctip8L1WvgfbitWn7dqGDebcEWXTrKWfiqlYu/vO4L175hG6xwGFyd kAKHZH6PM9V1p4vRVDnPpDAur5utAKhqpBr5gxKa7ez32RhW+9w+zlQYlo9EmyDefWcZ Rzj8N+d2v5rX3DEE89TntbSeZEzYa8pyhA685CNJ5OFiPssS7Tzap8VzfDSAal44lJHL LbJg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:from:date:message-id:subject:to; bh=mHTedVoYWGwlgJFVt/GRV+25fKCZuhRzzThgB+nxXs4=; b=IHioavj0zY+SKDZpJ3FYAaeQpuhXVyGDKmTtqWeaK0qUK8ATQhpcTojkhpgzYRQYv1 MiucyKRsCYTfiQH5kVzbeGaEqEp0EBoN3EB45Fz0zwhxEFFwT+kqP70F4EWZyZGW+DOo Aa0w170ExjExKugu3QL4GgqE/N1w+t/HnQrNwiUNp5lxSbydHlwlZhX7809H7fjqzRob BqBUcPppAmiuo8ByYuKX3jXlii5w8nsp1rsHogNggmoJD2cyz1tBN96hq0mqTMWizqsV 8FDEF+4oNUCF6hnoigchMBDIvSO/c1NqQl5ZsTpzeAllbU5OJ7aRROZyUMm3fLE7I+8/ BGHQ== X-Gm-Message-State: AIVw111h+4LIGbio5pCgX8IwoyaFEsBwuDk29jNKZ6iQr1CCilyzEBZv NtuO604U1xdecKgXmYuHbj9aHgUrjA== X-Received: by 10.99.54.9 with SMTP id d9mr2448365pga.195.1500031340094; Fri, 14 Jul 2017 04:22:20 -0700 (PDT) MIME-Version: 1.0 From: Peter Koukoulis Date: Fri, 14 Jul 2017 11:22:09 +0000 Message-ID: Subject: ref cursor in C++ using SQLAPI++ To: pgsql-interfaces@postgresql.org Content-Type: multipart/alternative; boundary="94eb2c19c3382da8a7055445431b" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-interfaces Precedence: bulk Sender: pgsql-interfaces-owner@postgresql.org --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 #include =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
Hi=C2=A0

I am able to pass a ref cursor= out of an Oracle stored procedure, using the C++ SQLAPI++ library, as show= n below:

Is it possible to create a similar stored= proc, that takes a string as a parameter, and outputs a ref cursor?
<= div>The ref cursor, should be a weak ref cursor, since the result set is ba= sed on a string.

Thanks

<= b>C++ code
#include <iostream>
#include </home/mw/SQLAPI/include/S= QLAPI.h>
= =E2=80=A6
int main(int argc, char* argv[]) {
=C2=A0 SAConnection con; // connection ob= ject

=C2=A0 try {
= =C2=A0 =C2=A0 con.Connect("//10.11.12.18:1521/ftnode", "ordb", &q= uot;ordb", SA_Oracle_Client);
=C2=A0 =C2=A0 SACommand cmd(&con);
=C2=A0=C2=A0
<= div class=3D"inbox-inbox-F3hlO">
=C2=A0 =C2=A0 cmd.setCommandText("pkg_ref_cursor.get_dref&quo= t;);
=C2=A0 =C2=A0=C2=A0
=C2=A0 =C2=A0 cmd.Param("v_sql"= ).setAsString() =3D"select x,y from test1"; // input parameter
=E2=80=A6
<= div class=3D"inbox-inbox-vI" style=3D"border-left:1px solid rgb(224,224,224= );color:rgb(117,117,117);padding:10px">
=C2=A0 =C2=A0 cmd.Execute();
=C2=A0 =C2=A0 std::cout << "Stored procedure execute= d OK!" << "\n";
=C2=A0=C2=A0
=C2=A0 =C2=A0 SACommand *pRefCursor =3D cmd.Param("REFCURSOR")= ; //output parameter
=C2=A0=C2=A0=
=C2=A0 =C2=A0 // fetch results r= ow by row and print results
=C2= =A0 =C2=A0 while(pRefCursor->FetchNext())
=C2=A0 =C2=A0 =C2=A0 std::cout << (const char*)pRefCursor= ->Field(1).Name() << " =3D " << pRefCursor->Fie= ld(1).asLong() << ", "
=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
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 << "\n";
=C2= =A0 }

=C2=A0 catch(SAException &x) {
=C2=A0 =C2=A0 try { con.Rollback(); }
=E2=80=A6
=C2=A0 =C2=A0 catch(SAException &) { }
=C2=A0 =C2=A0 // print error message
=C2=A0 =C2=A0 std::cout << (con= st char*)x.ErrText() << "\n";
=C2=A0 }
=C2=A0 re= turn 0;
}


=E2= =80=A6Stored proc PL/SQL code:
=
create or replace package pkg_ref_cursor as=C2= =A0
=C2=A0 procedure get_dref(v_s= ql in varchar2, refcursor out sys_refcursor);
end pkg_ref_cursor;
/

create or replace package body pkg_ref_cursor as
=C2=A0 procedure get_dref(v_sql in varcha= r2, refcursor out sys_refcursor) as
=C2=A0 =C2=A0 v_Cursor =C2=A0binary_integer :=3D dbms_sql.open_cursor;
=C2=A0 =C2=A0 v_Ref =C2=A0 =C2=A0 = sys_refcursor;
=C2=A0 =C2=A0 v_Ex= ec =C2=A0 =C2=A0binary_integer;
= =C2=A0 =C2=A0 begin

=C2=A0 =C2=A0 =C2=A0 dbms_sql.parse(v_Cur= sor, v_sql, dbms_sql.native);
=C2= =A0 =C2=A0 =C2=A0 v_Exec :=3D dbms_sql.execute(v_Cursor);
= =C2=A0 =C2=A0 =C2=A0 v_Ref :=3D dbms_sql.to_refcur= sor(v_Cursor);
=C2=A0 =C2=A0 =C2= =A0 refcursor =C2=A0:=3D =C2=A0v_Ref;
=C2=A0 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 gr= eet
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--