Oracle sql cannot insert string with length more than 200 characters "Error: ORA-06502: PL/SQL:-CodePudding

I am trying to INSERT to a column with datatype VARCHAR2(4000 bytes) in Oracle sql. I wrote an API using node js as lambda function, but when I insert a string with more than 200 characters, I am getting error "Error: ORA-06502: PL/SQL: numeric or value error: DML Returning: Error writing to host variable". But when I tried to insert directly through Oracle sql developer windows, I am able to insert string of length upto 4000 character.

Here's my query:

const sql = `BEGIN INSERT INTO job_log(
        job_log_entry_id,
          job_id,
          entry_text,
          entry_date)VALUES (
          job_log_entry_seq.nextval,
          :jobID,
          :entryText,
          TO_DATE('${params.entryDate}', 'DD/MM/YY')
          )
        RETURNING job_log_entry_id, entry_text
        INTO :logEntryId, :note;
        COMMIT; END;`

const opt = {
                jobId:{val:params.jobId,dir: oracledb.BIND_IN,type: oracledb.STRING},
                entryText:{val: params.entryText,dir: oracledb.BIND_IN,type: oracledb.STRING},
                entryDate:{dir: oracledb.BIND_OUT,type: oracledb.DATE}
            };
            try {
                result = await conn.execute(sql, opt);
            } catch (error) {
                sqlError = true
            }

CodePudding user response：

You are passing the parameter as a DATE data type. However, the PL/SQL block is passing it to the TO_DATE function and that expects string arguments so there will be an implicit cast from the DATE being passed to a string so that it can then be converted back to a DATE; this implicit cast will cause issues when the NLS_DATE_FORMAT does not match the DD/MM/YY format in the explicit cast. Remove the TO_DATE function and just pass a DATE:

const sql = `BEGIN
  INSERT INTO job_log(
    job_log_entry_id,
    job_id,
    entry_text,
    entry_date
  )VALUES (
    job_log_entry_seq.nextval,
    :jobID,
    :entryText,
    :entryDate
  )
  RETURNING job_log_entry_id, entry_text INTO :logEntryId, :note;
  COMMIT;
  END;`

You also pass the entryDate as an OUT parameter when it should be an IN parameter and you do not have OUT parameters for logEntryId or note (although I am unsure why you need note as it should be the value you put in).

Something like:

const opt = {
  jobId:     {val: params.jobId,     dir: oracledb.BIND_IN, type: oracledb.STRING},
  entryText: {val: params.entryText, dir: oracledb.BIND_IN, type: oracledb.STRING},
  entryDate: {val: params.entryDate, dir: oracledb.BIND_IN, type: oracledb.DATE},
  logEntryId:{dir: oracledb.BIND_OUT, type: oracledb.NUMBER},
  note:      {dir: oracledb.BIND_OUT, type: oracledb.STRING, maxSize: 4000}
};