Frederick Tang Weblog

Today, one of the developers encountered the following error when trying to insert more than 4000 bytes into a CLOB datatype column. The environment is Java 1.4 and Oracle9i (9.2.0.8) database:

ORA-01704: string literal too long

That can happen if the literal (the 4000+ characters String) is embedded into an INSERT statement like a normal String/VARCHAR2. Suppose I create a table like so:

SQL> create table clob_test (id number(10), str clob);

Table created.

This INSERT method won’t work, and will result in the Oracle error, this is because VARCHAR2 has a 4000 bytes limit:

// Create a String of 5000 characters
char[] charArray = new char[5000];
String clobstr = new String(charArray);
     
Statement stmt = conn.createStatement();
try {
   String sql = “INSERT INTO clob_test (str) values (‘”+clobstr+”‘)”;
   stmt.executeUpdate(sql);
} catch (SQLException t1) {
   t1.printStackTrace();
} finally {
   stmt.close();
   conn.commit();
}

Thanks to Holly, she’s worked out a few methods to do this, but they require the use of oracle.sql.CLOB. I will show one of the suggested ways here:

try {
   String sql = “INSERT INTO clob_test (id, str) values (1, empty_clob())”;
   Statement stmt = conn.createStatement();
   stmt.executeUpdate(sql);
   sql = “select str from clob_test where id=1 for update”;
   ResultSet rss=stmt.executeQuery(sql);

   if(rss.next()){
      oracle.sql.CLOB clob= (oracle.sql.CLOB)rss.getClob(“str”);
      clob.putString(1,clobstr);
      sql = “update clob_test set str=? where id=1″;
      PreparedStatement pstmt=conn.prepareStatement(sql);
      pstmt.setClob(1,clob);
      pstmt.executeUpdate();
      pstmt.close();
   }
} catch (SQLException t2) {
   t2.printStackTrace();
} finally {
   stmt.close();
   conn.commit();
}

The Official Oracle9i suggested to use OutputStream, which you can read about it here. It looks a bit more complicated, but a Java developer has to evaluate which of these methods is a better alternative.

There are some good news for Oracle10g databases, which has a much easier way of working with CLOBs. The full Java demo is available here.

I found a need to track the total CPU usage by the oracle user:

$ id
uid=101(oracle) gid=101(dba)
$ prstat -t -u 101 > prstat_out
$ more prstat_out
 NPROC USERNAME  SIZE   RSS MEMORY      TIME  CPU
   332 oracle   1737G  529G   100% 283:44:40 7.0%
Total: 333 processes, 720 lwps, load averages: 1.74, 1.73, 1.98
 NPROC USERNAME  SIZE   RSS MEMORY      TIME  CPU
   332 oracle   1737G  529G   100% 283:44:53  10%
Total: 333 processes, 720 lwps, load averages: 1.88, 1.75, 1.98
 NPROC USERNAME  SIZE   RSS MEMORY      TIME  CPU
   332 oracle   1737G  529G   100% 283:45:05  13%
Total: 333 processes, 720 lwps, load averages: 1.97, 1.77, 1.99
 NPROC USERNAME  SIZE   RSS MEMORY      TIME  CPU
   332 oracle   1737G  529G   100% 283:45:17  14%
Total: 333 processes, 720 lwps, load averages: 2.12, 1.81, 2.00
 NPROC USERNAME  SIZE   RSS MEMORY      TIME  CPU
   332 oracle   1737G  529G   100% 283:45:30  16%
Total: 333 processes, 720 lwps, load averages: 2.25, 1.84, 2.01
 NPROC USERNAME  SIZE   RSS MEMORY      TIME  CPU
   332 oracle   1737G  529G   100% 283:45:41  16%

It is not so pretty… so perhaps I will use awk:

$ grep oracle prstat_out | awk {'print $7'}
7.0%
10%
13%
14%
16%
16%

Sometimes curiosity gets me into researching something that may not be of use at times, or maybe I just don’t have the wisdom to realise its value.

The last few weeks, whilst I was researching into a problem at work, I found myself sidetracked into learning the size of NUMBERs stored in an Oracle database, and more specifically, how Oracle stores NUMBERs internally. The size of NUMBERs in Oracle database is not a fixed number like Java’s int – 4 bytes, it is variable depending on the value.

Before the story continues, this information is documented in the Concept Guide: Oracle10g and Oracle9i. Oracle may change its implementation with every release, therefore, it is best to check the documentation. On top of that, some Metalink Notes also goes into demonstrating how the internal representation works: 1007641.6 – Understanding Oracle NUMBER Datatype; 1031902.6 – How Does Oracle Store Internal Numeric Data. For more reading, Chapter 12 of Thomas Kyte’s Expert Oracle Database Architecture book provides more explanation.

A numeric column can be defined as NUMBER or NUMBER (p, s), where p = precision (total number of digits) and scale (number of digits to the right of the decimal point). “Each value is stored in scientific notation, with one byte used to store the exponent and up to 20 bytes to store the mantissa. Oracle does not store leading and trailing zeroes.” – Note1031902.6.

For example, if I declare a NUMBER(5,2) column, this means I can store values up to precision (p) = 5 and scale (s) =2. Since values are represented in scientific notation, the number 123.45 has p=5 and s=2. It is interesting to note the number 10000 will not fit in a NUMBER(5,2) column because 10000.00 has 7 digits in total when converted to scale of 2 – a similar example is explained in Tom’s book.

Interesting thing is, how the column is defined in the table does not have a direct relation with the size (bytes) of the NUMBER it stores. To demonstrate, I will create a table with a NUMBER(5,2) column and insert a few values, then use the dump() and vsize() to show the internal representation:

SQL> create table number_test (i number(5,2));

Table created.

SQL> insert into number_test (i) values (123.45);

1 row created.

SQL> insert into number_test (i) values (12.23);

1 row created.

SQL> insert into number_test (i) values (1);

1 row created.

SQL> insert into number_test (i) values (0.99);

1 row created.

SQL> insert into number_test (i) values (1234.12);
insert into number_test (i) values (1234.12)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column

SQL> insert into number_test (i) values (100);

1 row created.

SQL> commit;

Commit complete.

SQL> col dump format a40

SQL> select i, dump(i) as dump, vsize(i) as vsize from number_test;

         I DUMP                             VSIZE(I)
---------- ------------------------------ ----------
    123.45 Typ=2 Len=4: 194,2,24,46                4
     12.23 Typ=2 Len=3: 193,13,24                  3
         1 Typ=2 Len=2: 193,2                      2
       .99 Typ=2 Len=2: 192,100                    2
       100 Typ=2 Len=2: 194,2                      2

Interesting results to observe from the above output, the dump() function showed the length of NUMBER values, as well as how the NUMBER is internally represented. For example, 123.45 is represented in 4 bytes and the value of each byte is 194, 2, 24, 46 respectively. How these four bytes represent the actual value is explained in Note 1007641.6.

Perhaps what us mere mortals are really after, is a simple formula that can calculate the size of the NUMBER values. This is given in the Oracle Concept Guide and Metalink Note in a slightly different form:

Oracle Concept Guide: ROUND((length(p)+s)/2))+1, where p = precision of the value, s = 0 if value is positive; s = 1 if value is negative.

Metalink Note 1031902.6: FLOOR[(p+1)/2] + 1 and add +1 byte (only for negative numbers where the number of significant digits is less than 38), where p = prevision of the value and scale has no effect.

The mind-boggling thing is these two formulas are actually the same, with a little interpretation required: length(p) actually means the number of significant digits without leading or trailing zeroes, and the p in the Metalink Note formula actually meant length(p). So following my examples above:

It is not hard to prove mathematically that ROUND(x/2)+1 = FLOOR((x+1)/2)+1, where x >= 0.