public inbox for [email protected]  

help / color / mirror / Atom feed

From: Peter Koukoulis <[email protected]>
To: [email protected]
Subject: ref cursor in C++ using SQLAPI++
Date: Fri, 14 Jul 2017 11:22:09 +0000
Message-ID: <CABpxA9i1fLfp7qatRjXVn7=fn4xGD8isVvT7KEqrfFAz68NgMw@mail.gmail.com> (raw)
List-Unsubscribe:  <mailto:[email protected]?body=unsub%20pgsql-interfaces>

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>
…
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() ="select x,y from test1"; // input
parameter
…
    cmd.Execute();
    std::cout << "Stored procedure executed OK!" << "\n";

    SACommand *pRefCursor = cmd.Param("REFCURSOR"); //output parameter

    // fetch results row by row and print results
    while(pRefCursor->FetchNext())
      std::cout << (const char*)pRefCursor->Field(1).Name() << " = " <<
pRefCursor->Field(1).asLong() << ", "
                << (const char*)pRefCursor->Field(2).Name() << " = " <<
(const char*)pRefCursor->Field(2).asString()
                << "\n";
  }

  catch(SAException &x) {
    try { con.Rollback(); }
…
    catch(SAException &) { }
    // print error message
    std::cout << (const char*)x.ErrText() << "\n";
  }
  return 0;
}


*…Stored 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 := 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 := dbms_sql.execute(v_Cursor);
      v_Ref := dbms_sql.to_refcursor(v_Cursor);
      refcursor  :=  v_Ref;
  end get_dref;

end pkg_ref_cursor;
/

$ ./ora_ref_cursor
Stored procedure executed OK!
X = 1, Y = Hello
X = 2, Y = goodbye
X = 3, Y = greet
X = 4, Y = welcome
X = 5, Y = lag
X = 6, Y = fill
X = 7, Y = fill
X = 8, Y = FFF

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: ref cursor in C++ using SQLAPI++
  In-Reply-To: <CABpxA9i1fLfp7qatRjXVn7=fn4xGD8isVvT7KEqrfFAz68NgMw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox