Not able to insert 240 characters to Oracle DB using stored procedure

Question

I am new to Oracle and is facing an issue while inserting 240 characters using Stored procedure. Below is the stored procedure:

procedure add_user_note(p_user_seq    in varchar2,
                          p_author      in varchar2,
                          p_note_text   in varchar2,
                          o_return_code out integer) IS
  BEGIN
    o_return_code := RC_SUCCESS;

    INSERT INTO user_notes
      (sss_user_object_id, note_date, note_text, note_author)
    VALUES
      (p_user_seq, SysDate, p_note_text, p_author);
    commit;
  exception
    when others then
      o_return_code := SQLCODE;
  END;

Using this procedure i can insert 238 characters, but my requirement is to insert 240 characters to parameter p_note_text. Any help is appreciated.

[edit] your question and add the `create table` statement for `user_notes`. — , Jul 27 '16 at 06:56

score 0 · Accepted Answer · edited May 23 '17 at 12:32

Your code does not restrict the number of characters in p_note_text. Your code can handle up to 32767 bytes, which is PLSQL max varchar2 length.

The number of characters you can insert is probably restricted by the column definition of user_notes.note_text.

See here a discussion about the difference about BYTE and CHAR in column definitions.

Difference between BYTE and CHAR in column datatypes

score 0 · Answer 2 · answered Jul 27 '16 at 07:00

0

you should play with length() and lengthB() functions

Length - for char count LengthB - for size in terms of byte

answered Jul 27 '16 at 07:00

Prashant Mishra

619
9
25

Not able to insert 240 characters to Oracle DB using stored procedure

2 Answers2