CODEWITHSUNDEEP
javasqloracle-database
San
San

Reputation: 31

Data not being saved in Database due to limited size of the field

There is a column message in the database table fsr_system_log for the schema fsr_appl. This table is supposed to store the system log message. The size of the column is 255 and the datatype is varchar2. The logic implemented for saving message whose size is greater than 255 characters is :

public void saveSystemLog(SystemLogRequest systemLog){
    User user = systemLog.getUser();
    String system = systemLog.getSystem();
    Log log = systemLog.getSystemLog();

    try {
        initializeDelegate();
        delegate.beginTransaction();

        LogEntry[] logEntries = log.getItemArray();
        for (LogEntry logEntry : logEntries) {
            // Save each entry
            ParamVector<Object> params = new ParamVector<Object>();

            //Check if message is greater than 255 characters
            String message = logEntry.getMsg();
            notifier.debug("Log Message is : " + message);
            if(message.length()>255){
                message = message.substring(0,255);
                notifier().debug("Message string greater than 255 characters : " + message);
            }
            params.add(message, 255, false);
}

But despite implementing the code, I face the following error :

Failed to save system log due to SQL error: ORA-12899: value too large for column "FSR_APPL". "FSR_SYSTEM_LOG". "MESSAGE" (actual: 257, maximum: 255)

A sample log being used is :

<xbe:systemLogRequest xmlns:xbe="http://tdc.dk/fsr/common/xbean">
<user>
    <userNumber>a62267</userNumber>
</user>
<system>Client</system>
<systemLog>
    <item>
        <timestamp>2011-10-27T17:03:08.404+02:00</timestamp>
        <type>Info</type>
        <msg><![CDATA[<html><center>Din registrering er nu sendt<br><br>Tak for indmeldingen</center></html>]]></msg>
    </item>
    <item>
        <timestamp>2011-10-27T17:03:13.701+02:00</timestamp>
        <type>Info</type>
        <msg><![CDATA[<html><center>Din registrering er nu sendt<br><br>Tak for indmeldingen</center></html>]]></msg>
    </item>
    <item>
        <timestamp>2011-10-28T12:45:47.801+02:00</timestamp>
        <type>Info</type>
        <msg><![CDATA[<html><center>Din registrering er nu sendt<br><br>Tak for indmeldingen</center></html>]]></msg>
    </item>
    <item>
        <timestamp>2011-10-28T12:45:57.926+02:00</timestamp>
        <type>Info</type>
        <msg>Afsluttet uden at gemme fejlregistering</msg>
    </item>
</systemLog>
</xbe:systemLogRequest>

Please help!

Note : The error thrown is for certain system log messages only. And the actual value is always a constant 257 when this error is being thrown.

Upvotes: 0

Views: 567

Answers (3)

Marlin Pierce
Marlin Pierce

Reputation: 10089

You can change your field type to CLOB, BLOB, or XMLType. Looking up Oracle data types, VARCHAR2 seems to also work on bytes.

You might, understandably, not want to change your field type, in which case, replace

if(message.length()>255){
  message = message.substring(0,255);
  notifier().debug("Message string greater than 255 characters : " + message);
}

with

pos = 255
while (message.getBytes().size() > 255) {
  message = message.substring(0,pos--);
}

Upvotes: 0

user330315
user330315

Reputation:

Apparently you have some characters that are represented with more than one byte.

In Java you get the length of the String in characters whereas Oracle apparently checks the byte limit due to the way the column was defined.

Check out the definition of your table, most probably the column is defined with default character semantics - which is byte (but depends on your Oracle installation), so it defaults to VARCHAR2(255 Byte)

If you redefine the column as VARCHAR2(255 Char) things should be fine.

Something like:

CREATE TABLE FSR_SYSTEM_LOG
(
   ...
   MESSAGE  VARCHAR2(255 Char),
   ...
);

To me it seems rather strange to store an XML in such a (length) limited column. Can you make sure your XML is never longer than 255 characters? Why don't you store the message as a CLOB?

Upvotes: 1

undx
undx

Reputation: 191

At first sight, I suspect an encoding issue...

Java uses unicode : a character in unicode may cost one to many byte(s)... A java string's length may be 1 but when marshalled on storage, it can take 2 bytes, 4bytes...

To check bytes length, maybe you should try: 

if(message.getBytes("US-ASCII").length()>255){

What is the encoding of your Oracle DB ?

Another possible issue is the class that writes to the DB adds a CR/LF(0D0A) to the msg parameter or something else...

Upvotes: 0

Related Questions