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.