String Bind Variables are limited to 64k in size

[Andreas741]

Hello,

I think I found a bug in ocilib 4.7.4. The problem also occurred in ocilib 4.5.1. The String Bind variables are always bound by specifying a length. Since the length specification type only has 16 bits, the length is limited to 64k. If you bind variables without specifying the length, you can use strings any number of characters up to the final '\0' byte. I fixed this temporarily, see the code below. It would be nice if this feature could be included in the next release.

Kind regards

File bind.c line 540:

/ ---------------------------------------------------------------------------------------------

• OcilibBindPerformBinding
• --------------------------------------------------------------------------------------------- */

static boolean OcilibBindPerformBinding ( OCI_Bind bnd, unsigned int mode, unsigned int index, unsigned int exec_mode, boolean plsql_table ) { ENTER_FUNC ( / returns / boolean, FALSE, / context */ OCI_IPC_BIND, bnd )

dbtext* dbstr = NULL;
int dbsize = -1;

CHECK_PTR(OCI_IPC_BIND, bnd)

// 28.03.2019 akh
// Don't use the length array on scalar strings with a maximum length > 32k
// because the length array are of type ub2 wich limits the length to 32K.
int use_length_array = OcilibBindGetUseLengthArray(bnd);

if (OCI_BIND_BY_POS == bnd->stmt->bind_mode)
{
    CHECK_OCI
    (
        bnd->stmt->con->err,
        OCIBindByPos,
        bnd->stmt->stmt,
        (OCIBind **)&bnd->buffer.handle,
        bnd->stmt->con->err,
        (ub4)index,
        (void *)bnd->buffer.data,
        bnd->size,
        bnd->code,
        (void *)bnd->buffer.inds,
        (ub2 *)(use_length_array ? bnd->buffer.lens : NULL),
        bnd->plrcds,
        (ub4)(plsql_table ? bnd->nbelem : 0),
        (ub4*)(plsql_table ? &bnd->nbelem : NULL),
        (ub4) exec_mode
    )
}
else
{
    dbstr = OcilibStringGetDBString(bnd->name, &dbsize);

    CHECK_OCI
    (
        bnd->stmt->con->err,
        OCIBindByName,
        bnd->stmt->stmt,
        (OCIBind **)&bnd->buffer.handle,
        bnd->stmt->con->err,
        (OraText *)dbstr,
        (sb4)dbsize,
        (void *)bnd->buffer.data,
        bnd->size,
        bnd->code,
        (void *)bnd->buffer.inds,
        (ub2 *)(use_length_array ? bnd->buffer.lens : NULL),
        bnd->plrcds,
        (ub4)(plsql_table ? bnd->nbelem : 0),
        (ub4*)(plsql_table ? &bnd->nbelem : NULL),
        (ub4) exec_mode
    )
}

File bind.h line 106:

// akh 02.05.2022 OCI_SYM_LOCAL int OcilibBindGetUseLengthArray ( OCI_Bind* bnd );

File lcilib.c line 216:

// akh 02.05.2022 int OCI_API OCI_BindGetUseLengthArray ( OCI_Bind* bnd ) { CALL_IMPL(OcilibBindGetUseLengthArray, bnd) }

File api.h line 4054:

// akh 02.05.2022 OCI_SYM_PUBLIC int OCI_API OCI_BindGetUseLengthArray ( OCI_Bind* bnd );

File BindObjectAdaptor.hpp line 62:

    // 02.05.2022 akh
    template<>
    inline void BindObjectAdaptor<ostring>::SetOutData()
    {
        if (GetMode() & OCI_BDM_OUT)
        {
            OCI_Bind* bnd = core::Check(OCI_GetBind2(_statement, _name.c_str()));
            // 28.03.2019 akh
            // Don't use the length array on scalar strings with a maximum length > 32k
            // because the length array are of type ub2 wich limits the length to 32K.
            int use_length_array = OCI_BindGetUseLengthArray(bnd);

            if (use_length_array)
            {
                size_t size = core::Check(OCI_BindGetDataSize(bnd));
                _object.assign(_data, _data + size);
            }
            else
            {
                // Assign string value until "\0"
                _object.assign(_data);
            }
        }
    }

[vrogier]

Hi, This is not a bug but rather an oracle limitation. Oracle varchar/varchar2 types are limited in length in both PL/SQL and SQL engines. It cannot be greater than 4k or 32k depending on oracle versions and database init settings. OCI API uses an unsigned short for specifying bind host variable size for strings. Thus, you cannot do what was exposed in your message.

Regards,

Vincent

[Andreas741]

Hello,

many thanks for the quick response.

Specifying the length is optional with the bind function; without this length specification, a string of any length up to the final 0 byte is transmitted. We use this feature to insert and modify long and clob columns via bind variables. As far as I know there aren't many ways to handle long and clob columns with data longer than 64k. Using the bind variable is a very powerful option. This feature would make it extremely easy to replace other Oracle interfaces with ocilib.

Greetings from Germany

[vrogier]

Hi,

binding host variables for clob columns shall not use strings but ocilib::clob (despite it works for clob content that is less than varchar max size)

Best regards,

Vincent