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:

 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 ;
 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.


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