October 24, 2007

Updating Long Character Data

I've heard of this issue recently at two different PeopleSoft sites. When a program is trying to insert or update a long character data value into a table, the value is cut off at or around 256 characters or an error reports that the value is too long for the column.

Unfortunately, that's all I know about it. Have you run into this? It's probably controlled by a setting somewhere, but what I have to offer is a rather weak workaround. Take the value and slice it up into 250-character chunks and then have the database reassemble them as part of the SQL statement. (SQR syntax is used in this example but the same could apply to PeopleCode.)

   let $X1 = substr($Long, 1, 250)
   let $X2 = substr($Long, 251, 250)
   let $X3 = substr($Long, 501, 250)
   
   begin-sql
     INSERT INTO PS_MY_TABLE
     (MY_KEY, MY_DATA) 
     VALUES ($Key, $X1 || $X2 || $X3)
   end-sql

Yes, admittedly it's an ugly kludge. I'd appreciate any information you may have on this or a more elegant solution.

Until next time...