ref cursor in C++ using SQLAPI++

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

ref cursor in C++ using SQLAPI++

bluefrog
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