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 )
       retList( tagOrder ) := ’’;
      elsif xmldom.getNodeType(currChild) = xmldom.TEXT_NODE
       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 a while ago and forgot to mention it. Seems to be better than technet for finding stuff.

Hogs and quiches compadres.