BLOB does not work in Oracle

[avpalienko]

In the last version we have a problem:

    sql << "insert into soci_test (id, b) values(5, '0')";
    sql.commit ();

    soci::blob      output_blob ( sql );
    soci::indicator ind;

    sql << "select b from soci_test where id = 5", soci::into ( output_blob, ind );
    CHECK ( ind == soci::i_ok );
    CHECK ( output_blob.get_len () == 1 );

ORA-22292: Cannot open a LOB in read-write mode without a transaction for the parameter number 1 while executing "select b from soci_test where id = 5".

[vadz]

AFAIU (@Krzmbrzl please correct me if I'm wrong), this never worked before, as you need a transaction in order to use LOBs with Oracle, but now this is more explicit.

IOW the fix is to have a transaction around the code working with LOBs.

[avpalienko]

It worked at 047b74915bb9e8a259536f6d3596bf4bfefe36d2

[vadz]

Could you please use git-bisect which exact commit broke it? I see 8b5b312d (Fix Oracle bug that prevented selecting into initialized BLOB, 2023-11-01) which might be related...

[Krzmbrzl]

Tbh I don't quite remember the exact semantics of Oracle. Iirc there were/are situations in which you didn't need an explicit transaction... it was a bit of an odd situation, that much I know 👀

It worked at 047b749

@avpalienko "at" or "before"? Aka: is this the commit that broke it or is it the last commit for which this works as expected?

[avpalienko]

I don't know - it works in my fork. Fork is based on 8ddddcac3073a7f33dbfdea318a6c9ad45f639db I try rebase to the last commit of master and found the problem

[Krzmbrzl]

Could you do a bisect to find the commit that causes the issue?

[avpalienko]

Could you do a bisect to find the commit that causes the issue?

I'll try, but I have no experience with bisect. So it will take a while

[avpalienko]

I found one more problem. If rowset contains more than one row move_as() does access violation ( use after move )

diff --git a/tests/common-tests.h b/tests/common-tests.h
index d2b8fb42..ffc80fc6 100644
--- a/tests/common-tests.h
+++ b/tests/common-tests.h
@@ -6779,7 +6779,7 @@ TEST_CASE_METHOD(common_tests, "BLOB", "[core][blob]")
         }
         SECTION("move_as")
         {
-            soci::rowset< soci::row > rowSet = (sql.prepare << "select b from soci_test where id=:id", soci::use(id));
+            soci::rowset< soci::row > rowSet = (sql.prepare << "select b from soci_test where id=:id union all select b from soci_test where id=:id", soci::use(id, "id"));
             bool containedData = false;
             for (auto it = rowSet.begin(); it != rowSet.end(); ++it)
             {

[Krzmbrzl]

If rowset contains more than one row move_as() does access violation ( use after move )

What do you mean by that? If you use move_as on the same element that is an obvious error on the user side but I have the feeling that's not what you're getting at 👀 (I don't understand the relation between what you've written and the parch you have provided)

[avpalienko]

This patch demonstrates the problem. If it apply the test fails with access violation error

            soci::rowset< soci::row > rowSet = (sql.prepare << "select b from soci_test where id=:id union all select b from soci_test where id=:id", soci::use(id, "id"));
            bool containedData = false;
            for (auto it = rowSet.begin(); it != rowSet.end(); ++it)
            {
                containedData = true;
                const soci::row &currentRow = *it;

                soci::blob intoBlob = currentRow.move_as<soci::blob>(0);

at second iteration will error

Sorry for my bad English

[Krzmbrzl]

Thanks for the clarification. I have created a separate issue for this: https://github.com/SOCI/soci/issues/1144