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