Category: Uncategorized

1. Read the manual. Set up the grants and stuff as directed. Note that the following assumes that you are connected to the database as finagle.

2. Create an old-style database link with the username and password in it. If you don’t AQ will try to log on to the remote machine as sys and give you an ORA-04052: error occurred when looking up remote object … error if you use the one where you just give the connect string. Some kind of security thing, the manual for the error message says you need some views installing. Ignore it and create one of these:

create public database link yada_remote
connect to finagle identified by frippery
using ‘yada.world’

(this cost me 2 days)

3. create your payload type on both instances say my_payload

create or replace type my_payload is object
( id   integer
, XML                CLOB
);

4. create queue table and queue on local instance and start it (I put this in a package to save typing and make it portable)

 dbms_aqadm.create_queue_table
  ( queue_table        => ‘marvy_queue_tab’
  , comment            => ‘My marvy queue’
  , multiple_consumers => TRUE
  , queue_payload_type => ‘my_payload’
  );
 dbms_aqadm.create_queue
  ( queue_name   => ‘finagle.marvy_queue’
  , queue_table  => ‘marvy_queue_tab’
  );
  dbms_aqadm.start_queue ( ‘finagle.marvy_queue’ ) ;

5. create queue on remote instance and start it

<same commands as before>

6. add remote subscriber on the local instance. The @link tells AQ that there is a subscriber at the remote instance. Do this on the local instance.

DECLARE
  subscriber         sys.aq$agent;
BEGIN
  subscriber := sys.aq$agent(null,‘finagle.marvy_queue@yada_remote’,null);
  dbms_aqadm.add_subscriber(
   queuename         => ’finagle.marvyqueue’,
   subscriber         => subscriber,
   rule           => null,
   transformation => null );
END;

7. start propagation for local and remote destinations on source instance

EXECUTE DBMS_AQADM.SCHEDULE_PROPAGATION(-
   Queue_name    =>    ‘finagle.marvy_queue’ )

EXECUTE DBMS_AQADM.SCHEDULE_PROPAGATION(-
   Queue_name    =>    ‘finagle.marvy_queue’, –
   Destinatio   =>    ‘yada_remote’);

This will start pushing data to the remote queue. Note that <schema>.<queue> have to be the same.

8. Test it!

Send a test message:

— send an empty object
declare
thing my_payload ;
dummy raw(16) ;
begin
thing := new my_payload( 1, null ) ;
  DBMS_AQ.enqueue
  ( queue_name             => ’finagle.marvy_queue
  , enqueue_options        => <read the manual>
  , message_properties     => <read the manual>
  , payload                => thing
  , msgid                  => dummy
  );
end ;

— remote dequeue
declare
pi_dequeue_options        DBMS_AQ.dequeue_options_t  := <some clever package>.DEFAULT_DEQUEUE_OPTIONS ;
po_message_properties     DBMS_AQ.message_properties_t ;
po_msgid                  RAW ;
thing               my_payload ;
BEGIN
  pi_dequeue_options.consumer_name := ‘REMOTE_SUB’;

  DBMS_AQ.dequeue( queue_name             => ‘finagle.marvy_queue’ 
                 , dequeue_options        => pi_dequeue_options
                 , message_properties     => po_message_properties
                 , payload                => thing
                 , msgid                  => po_msgid
                 );
dbms_output.put_line( thing.id ) ;
END ;

If aq just isn’t working there’s a useful (but a little out of date) fault finder on metalink: http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=233099.1.

I’m pretty curious about queues being used bi-directionally. I can’t see how it could stop you if you set propagation up on both instances. I may need this, but would have to then send messages to a specific subscriber or you’d be sending messages back to the same instance, maybe you don’t care.

I went for a walk this evening. It was a beautiful autumn evening with a two-thirds moon and the stars just peeking out from behind some cloud cover. The clouds were catching the distant neon glow, and just being pierced by the sharp points of light. I walked some way under the bright moon, talking to Rosie on the old earpiece and getting the crap out of my head with the excellent clear air.

It reminded me of another time when I used to walk alone in the sharp mornings and evenings, when I walked to school across the golf course. Now I know that most of us are fairly fixed in what we believe that we are seeing around us. For example, you expect to get home and see your home; to be known by the people there and have some measure of safety (well when you are a kid…) but I used to have this recurring semi-fear that when I got home reality would have shifted around me and I just wasn’t going to see what I expected: I was going back to a home that wasn’t mine any more, and in fact in this reality it had never been so because I had never existed. I had a mantra: Right time, right place, in time and space. It must have worked.

Looking back now I think that it is something to do with my father dying suddenly. I remember being taken to school by someone else and then my mother telling us that he was dead when we came back in the afternoon. I didn’t believe it until my aunt smiled a weird smile at me when I ran away to the back garden. Fucking hell, this was my first experience of impermanence, and it must have hung over me for ever after that. My eight year old mind realised that all of everything is a dream where we agree on the vocabulary but not what really hides behind the labels.

Been having a lot of trouble sleeping properly. Suspect I’m a bit stressed, IBS flared up.

Got my generated J2EE code to compile with no problem, now I need to devise some kind of test harness and then move on to (finally) generating the JSPs for the data entry. JDev won’t display the file directories correctly. Need to av a fink abaht why.

Then it’ll only be a case of rolling it out onto the main site, and sorting out the hosting. Trivial. (I wish)

This week I’ve been playing with Oracle Advanced Queueing, which seemed easy enough to set up if you only want to work within the same database. I decided to change my implementation so that it sends a structured object with some surrounding data as well as the XML, decided to send the XML in a CLOB as the xmldom package has a parseclob method and I didn’t need to send a ready parsed doc so that was OK. I didn’t use sys.xmltype in the end. I like the way that the xmltype queues can have filters based on xpath expressions but don’t really need it at the moment.

That said I need a worked example that shows how to get queues to replicate across databases. I will put one up here if I ever find one that goes beyond vague hints with no examples in the manual.

Diary

Rosie and the kids came up to Nottingham Wed pm and stayed at the B&B with me. They had fun playing in Sherwood forest and the Robin Hood centre (which is on Maid Marion Way – nuff said). They had a good time and we celebrated Jon’s birthday. We had a bit of a mare finding somewhere to eat ‘cos the local pub were having a 45 minute wait so we drove to Mansfield and went to Pizza Hut, which was OK. It looks like Thursday is going out day in Nottingham, the pubs near Blidworth where we were staying were packed to the rafters. Got him a pretty cool Scooby Doo cake. He used his money to get one of those Tyco stunt bikes at TRU, wouldn’t buy the heavy parental thing about inventors really needing to get into Meccano.

Discovered that I will be paid for some of last month but still suffering from agency and umbrella company timeline mismatch. Never mind, bank co-operative and payday at the end of November should sort it aht.

My incompetent former employer still haven’t managed to send me my P45, it’s only been what, 5 weeks? If I didn’t know they were totally useless and lazy I’d think that there was something dodgy going on. On the other hand one of the guys who was made redundant in the first wave never got his P45 so maybe there is something there? It just doesn’t feel likely, it would require imagination and wit and they ain’t got none. When I emailed I got the usual bullshit. Of course, having been paid now, the P45 is a bit irrelevant except it stops me going on an emergency tax code and why should they worry that their idle bollocks costs me money? I’m only a human being and they are a suit-infested buncha nobodies who don’t deserve the intelligence and goodwill of their very able underpaid staff. Apparently a certain person is coming in clean shaven. Not sure what he can do about the ineffectual geek bit, though. I mean, it was like being made redundant by Wurzel Gummidge, (ah the scent of burning bridges: FYTP). Buddhists should be feeling compassion rather than contempt and I think I’ve crossed that bridge, but it was funny, shoulda slipped him a quid for a cup of tea. I make no pretense at my own sartorial splendour, but then again I do wear a suit for interviews and appraisals.

Sofa

Apparently I’ve been replaced with a second hand sofa. Good to know your true value, I say. (Missed out the MD’s brand new Jag as well for ironic purposes).

Music

Enjoying the new Starsailor in the car. I like the Stereophonics new one too, except that a couple of tracks are king irritating (Madame Helga springs to mind). I want the REM best of but not yet.

Spiritual stuff

Trying to get up at 6 am so’s I can get half an hour’s meditation in. Very hard when sleep is eluding you.

Routes

Went north and then across country today. Avoided the M6 but found the windy road from Buxton down to Macclesfield a bit of a trial. 2.5 hours, which is about .5 quicker than the M6 route. I think the A50 route is probably still the best for Mondays ‘cos I’ll be hitting Macc and so on around 7 going the other way and it will be getting busy.

Blessings all.</p

My new short story is The End of All Meeting. It’s a bit preachy but it helped me sort some thngs out. Have a read if you want.

Null is not Null

If you select length(’’) from dual in Oracle what you get in return is a null. I was trying to prevent string buffer overflow using some code like:

if length(buffer)+length(message) < 32767
then
 buffer := buffer || message ;
end if ;

Ah yes but buffer was initialised to ‘’, with me thinking that its length would be 0. Nope, its length is null. Then remember the golden rule any operation involving null returns a null, any boolean operation is always false. Yes, you guessed it, the buffer remained empty. This took a bit of finding because I thought I’d dealt with it by setting the buffer to ‘’. Interestingly this means that you can’t have a varchar2 string that has a length of 0, you have to nvl the length to 0. Of course, if they fixed this a mountain of code would break, I have used it myself occasionally.

This gives us the interesting paradox

null is false

not null is false

null <> not null is false

My head hurts.

Fun with inheritance

I got to the bottom of the bug with my old code. I had refactored a class that I was doing a lot of inheriting from into an abstract class so we had something like:

public abstract class XMLActions
extends org.xml.sax.helpers.DefaultHandler
{
 String currentTable = “” ;
 static String sourceFile = “public_html\data-model.xml” ;
 boolean firstColumn = false ;
 boolean firstViewColumn = false ;
 boolean firstTable = false ;
 boolean doWhere = false ;
 PrintStream os = System.out ;
 Map tagTables = new HashMap() ;
 /* … */
 final public void characters
  ( final char[] ch
  , final int start
  , final int len )
 {
  if ( doWhere ) // we are inside a view
  {
   final String text = new String( ch, start, len ).trim();
   if( text.length() > 0 ) os.println(text);
  }
 }
}

The inheriting class (which was the original) implements an abstract method called handleViewWhereClause (trips off the tongue) that sets the doWhere boolean so that the text will be echoed out to the output stream. But silly me, I had left the instance variables in it when I factored it out. This class was setting its local attribute of the same name and the parent class was blithely ignoring it. Here endeth the lesson.

Making laptop screen fonts clearer in XP

Right click on the desktop. Select appearance, check effects, check cleartype on font smoothing. Got it from an O’Rielly book XP Pro : The missing manual. I might even buy it rather than looking at it in PC World, but probably not from them. I haven’t paid the retail price for a computer book in ages and I’m not about to start now. There’s also one on setting up WiFi which looked OK, but I haven’t had a problem with WiFi: just read the manual.

Blessings and good health all.

Got me keys sorted. In the end I split the monsta query into two passes. Been having a hassle where some of the tables don’t have primary keys. I was trying to use primary keys as a first cut for the relationships in my deepcopy tables. Not a problem, will just have to be done by hand. As I’m prototyping now I decided to simply ignore the problem until it matters. Gone back to the high-level design and looking at the business rules.

Got postres going last night on the laptop. No problem.

Reviewed my project and discovered – horror! – a bug in one of the early phases that I need to think about. The generated SQL wouldn’t work in postgres ‘cos the view handler routine is knackered. Ah well; can’t be perfect all of the time.

Got a subscription copy of .Net developer. Now I just need to think of what I need to develop on it! Probably loads a tutorials to start with.

Watched DVD of Dog Soldiers – it was ok but if I see another pound of sausages covered with Kensington Gore (stage blood), I’ll have to hit someone. Then they superglued the sarge’s wound back together and he didn’t die. I dunno, I thought guts should twitch a bit, particularly when still attached, and he didn’t just die of some galloping infection before the werewolf magic took over and healed him.

Going to ring round the wifi network providers in Nottingham tonight with a view to taking the lappy and getting some connectivity. If you’re looking for Wifi stuff try http://www.wifinder.com/, I also had a go with http://www.wi-fizone.org/zoneLocator.asp but they didn’t seem to have as much info. Note for UK peeps, on the first site select United Kingdom, not UK, cos it wont’ find anything. Thanks to Roger for the URLs.

Discovered that the new google toolbar will selectively suppress popups. Very useful.

Envoi

Hmmm … deepcopy utility was going back to parent’s primary key so we were getting duplicates on compound keys. Needed to redesign so that you know what your primary key is and the parent values are passed down. Sigh. It all seemed finished. Took me less than a day to get the receiving procedure written that takes the XML and generates the insert statements. Of course need to do the deep delete as well. Maybe just call the package deep? Forgot that getting a value doesn’t work with XML, you have to get the child node and get it’s value:

      currChild := xmldom.getFirstChild( currChild ) ;
      if xmldom.isNull( currChild )
      then
       retList( tagOrder ) := ’’;
      elsif xmldom.getNodeType(currChild) = xmldom.TEXT_NODE
      then
       retList( tagOrder ) := xmldom.getNodeValue( currChild ) ;
      end if ;

Note having to check for nullity – it doesn’t just give you a null back, oh no, that would mean it couldn’t throw a spurious exception and make you think it wasn’t working. Oracle site mentioned below very helpful, there is a FAQ section on the xmldom package.

Having fun with inline views to generate out the key references:

select  dct_child.table_id
,       dct_child.parent_table_id
,     (
        select distinct parent_cols.column_name
        from all_constraints parent
        ,  ALL_CONS_COLUMNS parent_cols
        ,    all_constraints child2
        ,    all_cons_columns child_cols2
        where  parent_cols.position = child_cols.position
        and     parent_cols.TABLE_NAME  = parent.table_name
        and     parent_cols.constraint_name = parent.constraint_name
        and     parent_cols.owner = parent.owner
        and     parent.table_name = dct_parent.table_name
        and     parent.constraint_type = ‘P’
        and     child2.r_owner = parent.owner
        and     child2.r_constraint_name = parent.constraint_name
        and     child_cols2.table_name = child2.table_name
        and     child_cols2.column_name = child_cols.column_name
        and     child_cols2.position = parent_cols.position
        and     child.table_name = child2.table_name
        )
,       child_cols.COLUMN_NAME
,       child_cols.position
,  child.constraint_name
from    ALL_CONS_COLUMNS child_cols
,      ALL_CONSTRAINTS child
,       deepcopy_tables dct_child
,       deepcopy_tables dct_parent
where   child.TABLE_NAME  = child_cols.table_name
and     child.constraint_name = child_cols.constraint_name
and     child.owner = child_cols.owner
and     child.table_name = dct_child.table_name
and     child.constraint_type = ‘P’
and     dct_parent.table_name = ‘CUSTOMER’
and     dct_child.parent_table_id = dct_parent.table_id
;

Horrible isn’t it? If I’d joined directly I’d have had to outer join the parent key stuff (if it even would have worked).

Had a stomach bug yesterday. Not a lot of fun to be honest. Interesting drive to work today but fortunately it didn’t give me any gyp.

Having fun with new laptop (see savastore here – I upgraded to a 2.4 GHz processor and XP Pro). It has the ability to play CD’s without being switched on. Very handy. Will watch some DVDs tonight.

I found http://www.oracle-base.com/ a while ago and forgot to mention it. Seems to be better than technet for finding stuff.

Hogs and quiches compadres.

Was trying to do something like:

type long_string is varchar2(32767) ;

The compiler didn’t like it at all. In fact you have to declare this :

subtype long_string is varchar2(32767) ;

It looks like the language designer was being a little pedantic to me but hey, what do I care, only had to RTFM. This does have the advantage in packages etc. that means you can now just say:

fred long_string ;

when you are declaring things, and if you want to change the size of things it’s easy and in one place. To go the whole hog you could have a package header with your standard types in (don’t need a body btw):

create or replace package project_types is
 subtype long_string is varchar2(32767) ;
 subtype short_string is varchar2(200) ;
 subtype flag is char(1) ;
 subtype delimiter is varchar2(10) ;
— And so on
end project types ;

Then just declare stuff as

fred project_types.long_string ;

Then you can insulate yourself against having to make global search/replace across your whole project. Subtypes transparently work within the base type. I would also recommend declaring yourself a ref cursor type (why there isn’t a standard one and you have to declare the king stupid thing every time is one of those little mysteries), if you haven’t got the SYS one available (sys.rc).

Paddling

Went to Holme Pierpoint and had a pretty good time except when I screwed up and came down the concrete, fell in and my roll wouldn’t work ‘cos I was being pressed back under by the force of the water. Need to work on rolling on both sides and using the reverse screw roll more. Next week’s practice items I think. Need to buy more thermals!

Laptop

My laptop has arrived so I can go on the road and work on my pet projects. My old lappy would just about run Mess Werd. Hey – I can even watch DVD’s! Short story about half done. Need to buy one of those security devices.

Looking forward to seeing the family this weekend.

Blogging works

Used my blog to locate some code I have used before. Very useful. Recommend blogging to everyone who needs to keep track of things. I had a look at my utilities package and it’s a bit out of date now

Blessings upon you all, dear readers (all 2 of you).

Been struggling the last few days with tiredness. Seem to keep waking up at 5 am, not a lot of fun to be honest. I’ve been wondering if it’s my IBS flaring up again. Suspect the tiredness comes from the weekend originally, where I paddled for 2 days and even had a drink in the evening. Probably paying for it now, methinks.

Deep copy

I’ve been working on a PL/SQL procedure to do a deep copy of an object from one schema to another. Sounds fairly trivial but the usual nonsense with security has screwed things up; you can’t select from all_tab_columns in a pl/sql procedure and look at someone else’s schema. I had to move to the main schema out of my own one, surprise! Currently creating an XML message (yet to work out mechanics of sending it). Alternate foreign keys to the same parent are a pain. There is a data structure here which allows you to browse across to other accounts the customer has, but only one has a direct relationship with the main account record. I need to work out how to make this go.

Having fun with oracle types (needed to create a complex structure that allows me to drill down rather than up). Look ma, multidimensional arrays:

declare
 type col_values is table of varchar2(2000) index by binary_integer ;
 type value_list is table of col_values index by binary_integer ;
 cv col_values ;
 vl value_list ;
begin
 cv(1) := ‘asdf’ ;
 vl(1) := cv ;
 dbms_output.put_line( vl(1)(1) ) ;
end ;

The syntax is fairly obvious in the end. I now have a complex type that holds the IDs of its children so it can go down to them recursively. Watch this space for a debugged version being posted some time soon.

I’m using a much underused trick with oracle tables. They are in fact sparse arrays and you can index them on the table ID (for example) and easily navigate around parent/child relationships without a lot of hassle. I like sparse arrays a lot. Java has them but of course they’re in a utility class, not the language syntax so it’s not that wonderful. It shows the usual thing: you can do anything in any language, just that some have syntax that makes it easier. I’d love a SQL-enabled AWK, for example. The main thing is thinking in abstract structures and then bending the language to fit. If you only have say, VB, then the world (well your brain’s perception of it) is only VB-enabled. You need to learn more to progress; if you have a hammer everything is a nail.

Thinking of going to see a film tonight, was going to go paddling but tiredness pissing me off.

Stories

Started a short story last night; don’t know if it’ll go anywhere but what the hell.

Birthdays

I was 44 yesterday. Crap. Not that good a birthday either, as these things go. Bought myself a personal CD player with a car kit so I can listen to my CD collection in the car. Really miss the multichanger in the old car. Ho fkn hum. Spent the day working and driving and trying to sleep. I’m convinced Burger King put caffeine in their Fanta. It’s the only explanation I can find for not being able to sleep. Couldn’t get meditating together today either.

Got a call from an old friend yesterday whom I haven’t seen for at least 12 years. She’s coming to the UK on the 25th so hopefully we can link up.

Catching up

I’m working for a utility company based in Nottingham which is about 100 miles from home. I’ve got a six month contract here and the work is really interesting. The people are good as well. The downside is being away from home 5 nights a week. I’d trade the excellent contract rate for being able to see Rosie and the kids like a shot. I have a blog entry on my laptop but no way of putting it here just yet.

Holme Pierpoint

For those who don’t know this is the National White Water centre and it’s based in Nottingham. I had a paddle there on Tuesday and copped a bad swim. Backrest loose and being cocky. It’s funny how nature humbles you back to the insignificant nothing you really are.

The mind

Was watching the BBC programme about the mind yesterday. Very interesting, all these things starting scientists have discovered that … you can change your personality, you can choose your behaviour, you can learn to be gentle, things are fixed in your childhood but can be altered. Buddhists have known this stuff for two and a half thousand years, without the benefits of MRI scans. Even the bit about the mind being like an orchestra.

Anyway, blessings all, even the dyed geeks.

So what’s been happening for these last few weeks?

First off, I found a contract job in Nottingham working for a utility company, this is 2 hours from where I live. So I’m back on the road, staying in hotels and not getting enough exercise.

It’s quite odd; I went for a walk today and it all looked very unreal. I’m staying on the edge of town in the Travelodge and there’re all of these buildings lit up from below; proxy country houses built in 1997. It’s like a movie set, underneath, behind it there’s nothing substantial. I wonder if my studying and meditiation practice are having an effect, allowing me to see that there’s nothing there really. It’s not a bad feeling at all, I’m almost getting a sense of completion.

Nottingham has the national white water centre, they’re open until 9 during the week so I’m thinking that I will probably spend a lot of time there.

I went to buy a laptop from my friends at savastore using Rosie’s credit card (it’s new and got a long interest free period). Cocked up the delivery address and couldn’t change it on the web so cancelled. It took 24 hrs to cancel and the card has almost run out of cash so I can’t order again until tomorrow when the credit goes back onto the card.

Unlike the days when I was contracting before there are now umbrella companies that allow you to reclaim your expenses and mitigate (but not avoid) some of the effects of the nasty IR35. I am using a company called prosperity4. The only downside is that I won’t get paid until November and my expenses are pretty extreme, like about £400 a week. Think I’ll have to have a word with my bank manager.

The work at here is very interestng but obviously for reasons of confidentiality I can’t discuss it here. Aggressive deadlines and a large IT infrastructure, oh, how I have missed them in the backwater I was travelling in. My one worry is that I sometimes go a bit passive under pressure and just do what’s fun, so I’m going to have to watch myself.

I met one of my old Oracle colleagues here, Michel, whom I knew from the Halifax project.

I’m having fun and will be able to pay my bills so fuck it, just have to get on with life and make sure it’s worth living. I just wish I could get home at night but that will have to wait until the market picks up.