How to deal with Oracle pl/sql functions that return a CLOB.

[PaulvdLinden]

I'm having some difficulties processing the CLOB result of a package function when it's larger than 32767 bytes.

FUNCTION SomeFunction RETURN CLOB;

My C++ program links to Soci 4.0.3. This is the code to get the data:

std::string result;
soci::session sql(_database.pool());
sql << "BEGIN :result := MyPkg.SomeFunction; END;", soci::use(result, "result");

In this example result contains the requested data until it's more than 32767 bytes. In that case I'll get an Oracle error: ORA-06502: PL/SQL: numeric or value error.

When a change the type of result into soci::long_string it never contains any data, there are no Oracle errors.

soci::long_string result;
soci::session sql(_database.pool());
sql << "BEGIN :result := MyPkg.SomeFunction; END;", soci::use(result, "result");

Only with a select statement I can retrieve the data regardless of the size.

soci::long_string result;
soci::session sql(_database.pool());
sql << "SELECT MyPkg.SomeFunction INTO :result FROM DUAL;", soci::into(result, "result");

The difference is that I now use soci::into instead of soci::use. I was wondering if the functionality from my second example is simply missing or is it intentional?

[vadz]

There have been many changes to blobs support recently, see #992, so I'd strongly recommend trying to use the latest version from master.