Month: February 2005

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.

This was a weird one that took me hours

Something stopped working. It was now giving me a 404 (page not found for the unititiated) error.

An HTML form was calling a procedure that was constructing a magic URL that was being used to redirect things back again and pass some arguments back to the portlet.

I used the excellent facility in the Firefox web developer add-on that allows you to turn posts into gets so I could see what it was trying to do. This also shows the hidden fields as well, fantastic.

I changed the procedure to just dump out the arguments it had received into the browser and then return.

I then trimmed and messed with the URL for a while until it did something. Then I added things back in until it broke.

At first I thought it was something to do with using PL/SQL table types (which has always worked as far back as I can remember).

I finally tracked it down to the procedure referencing types defined in a package that is in another database, on the other side of a DB link. I changed the procedure to only use locally defined types and it started working. It seemed to sometimes work if I just took out the tables but then it stopped completely.

Now, I couldn’t do this for the production version because we’ve literally just shipped it. Also, it was working when I tested it a couple of weeks ago, unless I’ve gone completely mad.

I have 2 client databases with different DB links, one is 9.1.x, the other 9.2.×. The portal infrastructure db is 9.2.×. I discovered that if I rebuild (not recompiled but reloaded the code) the package in the remote 9.2 db it works. It won’t with the 9.1 one. Probably something to do with caching PL/SQL but there’s nothing in the log files, even if you try and turn the logging on, which didn’t work for me.

Of course, the long term solution is to change it to use local types but at least I can get things running if there’s a problem out there in the wild.

Oddly the Portal server has not fallen over on me for ages since my last post. Weird.

Added 12 April 2005 we discovered that the constant falling over was due to a lack of disk space.

I got my Java 1.4 Certification at the end of January. Now I can move on and look at the more interesting certifications. I think the next one will be the Business Components one. I need to write off to Sun to get the logo.

I thought maybe I could do some J2EE stuff through Oracle because we get a discount but all I could find was the usual Oracle tools stuff which I’ve been doing for nearly 20 years.

Paddled the river Crake on Saturday. Not bad when it has some water in it. My forward paddling is rubbish. Because I shepherd groups down rivers I’ve got into the habit of sitting on a low brace and watching other people. If I want to improve I’m going to have to do a lot more me paddling on grade 3+ water. To that end I went to the Tryweryn on Wednesday (we were going to do the Conwy but no water). Colin helped me a lot and I’m now doing much better. I need to keep it up if I want to get anywhere though. Was paddling my Dagger Redline which is quite a bit longer than the playboat and will actually get up waves and things. I enjoyed paddling it a lot more than I thought, which is interesting.

Oracle Portal/9iAs is a pain in the proveribial

I’m really struggling with 904. It keeps locking up. I think it’s something to do with using session variables in PL/SQL. It’s very hard to keep up a sustained development effort when you have to reboot the server every 20 minutes.

Rosie’s Birthday Party

Festivities started on Saturday when Rosie’s mum took us to an excellent meal at Oriental Delight where several beers were drunk. On Sunday the party was muppet themed and we got into the swing of things quite well, with me as the swedish chef (hat and rubber chicken), Rosie was Animal, Katherine was Beaker, Deb Miss Piggy. We now have enought chocs to stock a small shop and we’re both trying to lose weight.

Old Friends

Met an old school friend with whom I hadn’t parted company on the best of terms a couple of weeks ago. It was nice to see him and talk through old times and people. It’s interesting how we both hate a lot of the same things and at least some of my enemies (who he still keeps in contact with) have mellowed into reasonable folk. Nice to catch up, it’s a shame he didn’t make Rosie’s party but I suppose he might have felt a little out of it.

Buddhism

Going back to this again. Unfortunately Lama Jampa has moved to London so it’ll be difficult to get to him to ask for advice. I will write him a letter. One of my relatives was quite sarcastic about my beliefs which made me quite angry. Maybe if they had ever seen something worthwhile through to the end I could take it seriously. I have come to the belief that any connection with this tiny foolish life and an enlightened being somewhere downstream of me is very tenuous. This doesn’t mean one should give up, more that what you have now is even more precious.

I came up with a personal mission statement the other day:

Spend the rest of my days helping other people to succeed in between having lots of fun!

Blessings all