From: | Peter Koukoulis <pkoukoulis(at)gmail(dot)com> |
---|---|
To: | pgsql-interfaces(at)postgresql(dot)org |
Subject: | ref cursor in C++ using SQLAPI++ |
Date: | 2017-07-14 11:22:09 |
Message-ID: | CABpxA9i1fLfp7qatRjXVn7=fn4xGD8isVvT7KEqrfFAz68NgMw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-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
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Cramer | 2017-08-01 19:01:59 | JDBC 42.1.4 released |
Previous Message | Tom Lane | 2017-07-12 15:53:15 | Re: Sporadic "relation does not exist" errors with psqlODBC |