ORA-01483: invalid length for DATE or NUMBER bind variable

[mihaitodor]

Hey @sijms, I bumped into a weird issue in https://github.com/benthosdev/benthos/issues/2574 where, given this table:

CREATE TABLE TEST (FOO NUMBER(1), BAR BLOB)

the following INSERT statement is successful:

INSERT INTO TEST(FOO, BAR) VALUES(:1, :2)

but this one fails with ORA-01483: invalid length for DATE or NUMBER bind variable:

INSERT INTO TEST(BAR, FOO) VALUES(:1, :2)

I'm guessing the binary blob is too big, which causes some serialisation issue.

Here's a self-contained example which reproduces the issue using github.com/sijms/go-ora/v2@v2.8.18 and the gvenzl/oracle-free Docker container:

> docker run --rm -it -e ORACLE_PASSWORD=testpass -p1521:1521 gvenzl/oracle-free:slim-faststart

package main

import (
    "database/sql"
    "encoding/hex"
    "log"

    _ "github.com/sijms/go-ora/v2"
)

func main() {
    db, err := sql.Open("oracle", "oracle://system:testpass@localhost:1521/FREEPDB1")
    if err != nil {
        log.Fatalf("Failed to connect to DB: %s", err)
    }
    defer db.Close()

    _, err = db.Exec("CREATE TABLE TEST (FOO NUMBER(1), BAR BLOB)")
    if err != nil {
        log.Fatalf("Failed to create table: %s", err)
    }

    data, err := hex.DecodeString("TODO")
    if err != nil {
        log.Fatalf("Failed to decode image: %s", err)
    }

    _, err = db.Exec("INSERT INTO TEST(FOO, BAR) VALUES(:1, :2)", 1, data)
    if err != nil {
        log.Fatalf("Failed foo + bar: %s", err)
    }

    _, err = db.Exec("INSERT INTO TEST(BAR, FOO) VALUES(:1, :2)", data, 1)
    if err != nil {
        log.Fatalf("Failed bar + foo: %s", err)
    }
}

Since GitHub doesn't want to let me dump a long hex string as plain text, please download the following image, then run xxd -plain image.png | tr -d '\n' to encode it to hex and replace the TODO in the code above with the output.

If you run the code above after the Docker container starts up, you should see the following message: Failed bar + foo: ORA-01483: invalid length for DATE or NUMBER bind variable.

PS: Thank you again for this library!

[sijms]

to insert BLOB into database: 1- if your data < 32Kb pass it as []byte which is equal to type RAW and database will convert RAW into BLOB 2- for larger data you should use go_ora.Blob{} data type the correct code for insert:

// data contain large data object
_, err = db.Exec(`INSERT INTO TTB_557 (BAR, FOO) VALUES (:1 , :2)`, go_ora.Blob{Data: data}, 1)
if err != nil {
    return err
}

[mihaitodor]

Thank you for looking into it @sijms, your suggestion does make the error go away. In order to keep the SQL components generic in Benthos, preventing users from inserting payloads larger than 32Kb into blobs might be the best thing to do, so thanks for clarifying this limitation!

As a future enhancement to go-ora, I think it would be better for the library to reject the query if someone tries to pass in a []byte which exceeds 32Kb instead of succeeding in some cases such as db.Exec("INSERT INTO TEST(FOO, BAR) VALUES(:1, :2)", 1, data).

[sijms]

thanks @mihaitodor in next release I will return errors when input parameter for string and []byte exceed the max length

[sijms]

I will also test changing data type to LongRaw and LongVarchar to increase size from 32KB to 1GB

[sijms]

in next release I add support for long input up to 1GB. now the driver detect input parameters (string, []byte) more than 32kb and send them as type LONGVARCHAR and LONGRAW and it will fit into LONG, LOB data types

[sijms]

you can test last commit also I add example/long_input and testing file long_Input_test

[sijms]

fixed in v2.8.19

[mihaitodor]

That did the trick, thank you @sijms! ❤️