Ora-20103: Null input is not allowed

Another fine problem to grapple with:

I got this error when I was trying to process a string that was over the limit for a varchar2, which is 32767. What Oracle 9i does is quietly demote CLOB data to varchar2 if it can and then throw one of these exceptions if it can’t.

so you can have, say, small clob is 3200 characters and big clob is 3300:

htp.p(small_clob) ; – – No exception
htp.p(big_clob); – – 20103 not a useful error message really, and the call stack looks wrong too

I was trying to output the CLOB from the HTP package and it choked on it. I give to the world this noddy procedure I wrote that solved it (8i people beware, substr does not work on CLOBs before 9i, you’ll have to use dbms_lob). 

  — Local procedure that will put a CLOB using the HTP package
  procedure put_clob( p_cl in out nocopy clob ) is
    l_buffer varchar2(32767) ;
    k_max_size constant integer := 32767 ;
    l_start integer := 1 ;
    l_cloblen integer ; 
  begin
    l_cloblen := dbms_lob.getlength( p_cl ) ; — actually, length() might work with this in 9i, try it
    loop
       l_buffer := substr( p_cl, l_start, k_max_size ) ;
       htp.prn( l_buffer ) ;
       l_start := l_start + k_max_size ;
       exit when l_start > l_cloblen ;
    end loop ;
    htp.p;
  end put_clob 

The in out nocopy thing is more efficent, allegedly.

Don’t forget this can get even stickier with database columns, varchar2 is still only 2000 chars. I wish Oracle would just have one character stream type of indeterminate length (well LOB types are limited to 4GB, but you know what I mean). Allowing you to interchange varchars and clobs is OK, until you get weird errors like these, which only became apparent when a piece of dynamic html got to 33000 characters. Of course in 8i interoperability of varchars and clobs probably wouldn’t be allowed; maybe it was better?

I’ve also had this exception when calling a function over a database link. If there is an exception in the function it throws one of these errors. The real exception was in fact No data found, but I need to look at the code in more detail to verify this, perhaps the execption was being caught and what we were getting back was a null.