[odb-users] Inserting CLOB greater having size greater then 12288

[roberto minarelli]

Hi Boris,
as you suggested I created a simple table with only two fields as follows

create table TEST_LOB_TABLE
(
  id      NUMBER not null,
  request CLOB
)

then I mapped the preceding table on the following class

#pragma db object table("MPVR_DBA.TEST_LOB_TABLE")
class TestLobTable
{
#pragma db id column("ID")
    unsigned long Id;
#pragma db column("REQUEST") type("CLOB")
    string strRequest;

    friend class odb::access;

public:
    TestLobTable(){}
    virtual ~TestLobTable(){}

    const unsigned long getId() const
    {
        return Id;
    }

    void setId(const unsigned long value)
    {
        Id = value;
    }

    const string &getRequest() const
    {
        return strRequest;
    }

    void setRequest(const string &value)
    {
        strRequest = value;
    }
};

and following there is a simple code I wrote to test the insertion of the
CLOB

int main(int argc, char* argv[])
{
// some initialization

    string strLob    string strLob(20000, 'A');

    try
    {
        unique_ptr<oracle::connection_factory> f (new
oracle::connection_pool_factory (20, 1));
        dbPtr db (new oracle::database (strUser, strPwd, strDBname,0, 0,
nullptr, getConnection(f) ));

        unsigned long uId(2);

        spdlog::info("trying with TestLobTable");
        oracle::transaction t1(db->begin());
        TestLobTable obj;
        obj.setId(uId);
        bool bRes(false);
        size_t len(strLob.length());
        while(!bRes)
        {
                try
                {
                    spdlog::info("len is {}", len);
                    obj.setRequest(strLob.substr(0,len));

                    db->persist(obj);
                    spdlog::info("ok strLob len is {0}, strLob is {1}",
len, strLob.substr(0,len));
                    t1.commit();
                    bRes=true;
                }
                catch(odb::object_already_persistent& e)
                {
                    t1.rollback();
                    spdlog::error("error is {}", e.what());
                    break;
                }
                catch (std::exception &e)
                {
                    len--;
                    spdlog::error("error is {}", e.what());
                }
            }
        }
    }
    catch(std::exception& e)
    {
        spdlog::error("error is {}", e.what());
        return EXIT_FAILURE;
    }
    return EXIT_SUCCESS;
}

this result in the following situation

[2024-01-09 19:36:15.409] [info] trying with TestLobTable
[2024-01-09 19:48:17.324] [info] len is 20000
[2024-01-09 19:48:17.347] [error] error is 1461: ORA-01461: can bind a LONG
value only for insert into a LONG column
[2024-01-09 19:48:17.347] [info] len is 19999
[2024-01-09 19:48:17.348] [error] error is 1461: ORA-01461: can bind a LONG
value only for insert into a LONG column

.....
[2024-01-09 19:36:15.740] [info] len is 12288
[2024-01-09 19:36:15.756] [info] ok strLob len is 12288, strLob is
"AAA....."

the datatype are correct and there is no possible ambiguity
So the question rises again: How can I possibly insert a CLOB greater than
12288 char?

Thanks a lot in advance for your support
Roberto Minarelli Della Valle



Il giorno ven 22 dic 2023 alle ore 15:37 Boris Kolpackov <
boris at codesynthesis.com> ha scritto:

> roberto minarelli <roberto.minarelli.de at gmail.com> writes:
>
> > I am trying to inserting a  in a column specified as CLOB more than 12288
> > chars, that appears to be the actual limit
> >
> > ORA-01461: can bind a LONG value only for insert into a LONG column
>
> I don't believe you should be getting this error when trying to insert
> this amount of data into CLOB. I suspect you are trying to insert it
> (perhaps by mistake) into, for example, a VARCHAR2 column. I would
> suggest that you create a separate test with a single object that
> contains a single column of the CLOB type (plus the id column) and
> try this. If you are able to insert more than 12288 characters in
> this test, then there is something wrong with your application.
>
> Also see:
>
>
> https://stackoverflow.com/questions/9156019/ora-01461-can-bind-a-long-value-only-for-insert-into-a-long-column-occurs-when/14497831
>