1

I have created a trigger, inside the trigger I am appending lot of varchar and creating a new varchar. if I append too many varchars then I am getting below Sql Exception. (if I append 4 or 5 varchar then I am not getting this error)

I have found out that varchar maximum size is based on page size of the table space. I have checked my page size, its 8K bytes.

But The content I am appending is not 8K bytes. Please help me to resolve this error.

An error occurred in a triggered SQL statement in trigger "ORDDBA.ORD_IR_IN". Information returned for the error includes SQLCODE "-433", SQLSTATE "22001" and message tokens "ACTION|AREA_UNIT|BAL_CHG_FLAG|CANCEL_DATE|".. SQLCODE=-723, SQLSTATE=09000, DRIVER=3.66.46

declare columnNames varchar(5000);
SET columnNames='';
SET columnNames = 'value1' ;
SET columnNames = CONCAT(columnNames, '|') ;
SET columnNames = CONCAT(columnNames, 'value2') ;
SET columnNames = CONCAT(columnNames, '|') ;
SET columnNames = CONCAT(columnNames, 'value3') ;
SET columnNames = CONCAT(columnNames, '|') ;
SET columnNames = CONCAT(columnNames, 'value14') ;
SET columnNames = CONCAT(columnNames, '|') ;
SET columnNames = CONCAT(columnNames, 'value5') ;
SET columnNames = CONCAT(columnNames, '|') ;
SET columnNames = CONCAT(columnNames, 'value6') ;
SET columnNames = CONCAT(columnNames, '|') ;
SET columnNames = CONCAT(columnNames, 'value6') ;
SET columnNames = CONCAT(columnNames, '|') ;
SET columnNames = CONCAT(columnNames, 'value8') ;
SET columnNames = CONCAT(columnNames, '|') ;
SET columnNames = CONCAT(columnNames, 'value9') ;
SET columnNames = CONCAT(columnNames, '|') ;
SET columnNames = CONCAT(columnNames, 'value10') ;
SET columnNames = CONCAT(columnNames, '|') ;
SET columnNames = CONCAT(columnNames, 'value11') ;
SET columnNames = CONCAT(columnNames, '|') ;
SET columnNames = CONCAT(columnNames, 'value12') ;
SET columnNames = CONCAT(columnNames, '|') ;
SET columnNames = CONCAT(columnNames, 'value13') ;
SET columnNames = CONCAT(columnNames, '|') ;
SET columnNames = CONCAT(columnNames, 'value14') ;
SET columnNames = CONCAT(columnNames, '|') ;
SET columnNames = CONCAT(columnNames, 'value15') ;
SET columnNames = CONCAT(columnNames, '|') ;
SET columnNames = CONCAT(columnNames, 'value16') ;
Vadim K.
  • 2,370
  • 18
  • 26
raghav132
  • 23
  • 1
  • 1
  • 6
  • Is this for DB2 or oracle? If its DB2 your tag should read SQL PL not plsql – mmmmmpie May 08 '15 at 11:57
  • this is for DB2 database. – raghav132 May 08 '15 at 13:03
  • Alrighty I don't know anything about DB2 but [the doc](https://www-304.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.codes/src/tpc/n433.dita) says your end string is too long. Now your error message hints that you are passing values that are different than the generic `value1`, etc. Are you passing more than 5000 characters into columnNames? – mmmmmpie May 08 '15 at 17:04
  • Yes mmmmmpie, you are correct, if I count the number of character in the varchar its less than 5000, seems DB2 counting in different way so its failing. Then I changed the datatype to CLOB, then its working fine :) – raghav132 May 12 '15 at 06:28

2 Answers2

2

From your comment:

if I count the number of character in the varchar its less than 5000, seems DB2 counting in different way so its failing. Then I changed the datatype to CLOB, then its working fine

This is because the length of a varchar is given in bytes, while the length of a clob is given in characters (documentation).

Based on character encoding, a single character may be longer than one byte. Therefore it is not always possible to store 5000 characters in a varchar(5000).

Uooo
  • 6,204
  • 8
  • 36
  • 63
0

Varchar columns defined in Octets are a match to characters. Codeunits32 are where I have fallen into this trap.

Gillian
  • 1