[odb-users] Using odb with oracle: stored procedures and cursors

[Boris Kolpackov]

Hi Marco,

Marco Craveiro <marco.craveiro at gmail.com> writes:

> Many thanks for a fantastic product.

Thanks, I am glad you like it.


> However, I could not find any references to stored procedures or
> cursors in the Oracle section of the manual - although I can see
> it for SQL Server and others.

Right, we test/add support for stored procedures based on demand.


> 1. Is it possible at present to call stored procedures for Oracle?

We haven't got a test for it yet but it could already be working if
Oracle returns the result in a way similar to a SELECT query (and
all indications point to this being the case). If so, then calling
a procedure would require a view along these lines:

#pragma db view query("CALL my_stored_procedure(?)")
struct my_stored_procedure
{
  ...
};

And then:

using query = odb::query<my_stored_procedure>;

db.query<my_stored_procedure> (query::_val (123) + "," +
                               query::_val ("abc"));

I could whip up a test if you are interested.


> 2. Is it possible for stored procedures from Oracle to return multiple
>    cursors?

You mean multiple result sets (e.g., sets of rows from multiple,
potentially different, SELECT statements)? If so, that would most
likely require extra, potentially non-trivial, support.


> 3. If one of both of these features are not currently available, would
>    this require a lot of changes on the ODB side?

It's hard to say without further investigation, but achieving anything
with OCI (which is what libodb-oracle uses) is never straightforward.

Boris