Simple Insertion of LOBs returns error: ORA-01461: can bind a LONG value only for insert into a LONG column

[bpajk]

Hello!

I am having difficulty using a CLOB variable as an input in a PL/SQL function. I am binding a string variable in javaScript to a CLOB variable in PL/SQL function based on the oracledb documentation simple-insertion-of-lobs but I get an error: ORA-01461: can bind a LONG value only for insert into a LONG column. I have tested this behaviour for different string lengths and the error persists if the input variable is 4000 characters long (or more), but if I change the string length to 3000 (or less) characters everything works fine.

PL/SQL function definition:

FUNCTION MSG_insert_ATT(p_MSG_TYPE NUMBER, p_ACK_REQUIRED NUMBER, p_RECIPIENTS VARCHAR2, p_MSG_SUBJECT VARCHAR2,
  		p_MESSAGE_TEXT CLOB, p_ATT VARCHAR2, p_ACCOUNT_TYPE  NUMBER DEFAULT NULL) RETURN NUMBER;

NodeJs code:

var command='BEGIN :msg_insert := MSG.MSG_insert_ATT(:p_msg_type, :p_ack_required, :p_recipients, :p_msg_subject, :p_message_text, :p_ATT, :p_account_type); END;';
var bindVars={
    msg_insert:         { type: oracledb.NUMBER, dir: oracledb.BIND_OUT },
    p_msg_type:         1,
    p_ack_required:     1,
    p_recipients:       "test@mail.com",
    p_msg_subject:      "a".repeat(4000), //problematic bind to CLOB variable works for 3000 characters
    p_message_text:     "test subject",
    p_ATT:              1,
    p_account_type:     1
};

var options={
    outFormat: oracledb.OBJECT
};
conn.execute(command, bindVars, options, function(error, result){
    if(error == null){
        if(result.outBinds.msg_insert != -1){
            conn.commit(function(error){
                if(error){
                    winston.log("error","msgInsertAtt commit",error);
                }
            });
        }else{
            winston.log("error","msgInsertAtt","Message insert unsuccessful.");
        }
    }else{
        winston.log("error","msgInsertAtt",error);
    }
});

I am using a connection pool to obtain a connection.

System information:
architecture: x64
NodeJs 6.10.3
oracledb 1.13.0
instant client 11.2
Oracle database 12.1.0.2.0

Am I missing something obvious?

Any help is appreciated.

[cjbj]

Is the error coming from the binding from node-oracledb to the PL/SQL function, or something the function is doing? I can bind > 4K strings and insert them. Simplify MSG_insert_ATT until the problem is narrowed down.

I'd also recommend you get the 12.2 Instant Client. With node-oracledb 1.x and 11.2 client there's a ~64K limit for some LOB-as-string operations. (This 11.2 limitation will go away with node-oracledb 2.x)

[bpajk]

Thank you for the prompt response. I have narrowed down the problem to the PL/SQL function so it looks like oracledb works fine.

Thank you!