How do I bind a clob variable to the return value of a plsql function

[PaulvdLinden]

I have this code snippet:

        ocilib::Environment::Initialize();
        ocilib::Connection con("foo", "usr", "pwd");
        ocilib::Statement st(con);

        st.Prepare("BEGIN :result := DemoPkg.CreateArticle(vcArtNr => :artnr, vcDescr => :descr); END;");
        
        string artNr = "art0001";
        st.Bind(":artnr", artNr, static_cast<unsigned int>(artNr.length()), ocilib::BindInfo::In);

        string descr = "descr_art0001" ;
        st.Bind(":descr", descr, static_cast<unsigned int>(descr.length()), ocilib::BindInfo::In);

        //string result;
        //st.Bind(":result", result, static_cast<unsigned int>(result.length()), ocilib::BindInfo::In);
        ocilib::Clob result;
        st.Bind(":result", result, ocilib::BindInfo::Out);
        st.ExecutePrepared();

This results in the following error: Error occurred at OcilibStatementBindLob: A null Lob handle has been provided

When I change the type from ocilib::clob to std::string and have the PL/SQL function return a VARCHAR2 instead of a CLOB it works. But I need to use a CLOB.

I don't understand why I'm getting the null lob handle error, am I doing something wrong?

[vrogier]

Hi,

you need to provide a valid clob object by using the clob constructor that takes a connection object.

replace
ocilib::Clob result;
with
ocilib::Clob result(con);

the default constructor initialize the clob as a null clob object.
the constructor with the connection initialize the clob with an empty valid CLOB handle (with connection details) that can be assigned fetched lob handle.

Regards,

Vincent

[PaulvdLinden]

Ah, thanks! Stupid that I missed that, the error message speaks for itself. It works like a charm now.
Nice library by the way. I have also tested with occi and soci, but will probably use ocilib.

[vrogier]

Good to hear :)

So, what are the pros/cons, from your standpoint of each library ?

Regards,

Vincent

[PaulvdLinden]

I see a lot of interface similarities between occi and ocilib. The biggest con of ocilib is the use of deleters. In addition, I ran into linker problems with occi on Windows, which has to do with the runtime library settings in my project. On Linux I kept having tns problems with occi, which prevented me from connecting to Oracle.

I liked the interface that soci provides, but ultimately this library is less complete than ocilib. Also I had trouble using CLOBs as the return value from a package function.

[vrogier]

I agree with you.

The biggest issues with occi, from my standpoint, are the C++ runtime dependencies and dynamic memory usage.
That's what motivated me to have an C++ layer for ocilib.
SOCI is nice, more C++ spirit but rather limited compared to ocilib and occi.

C++ ocilib API has been created at a time when C++11 was not yet established and was built on top of the ocilib C API.
Few years ago, I wanted to reverse it : have the native implementation in pure C++11 and propose an C interface on top of it.
It would have allowed to have way more simple and concise codebase.
But I got a lot of push back for it and I kept it this way.
I still have in mind to do it but it is quite an important time investment...