Publication of NCE Legacy Information using the Oracle

Agenda • • • Introduction Design Hindsight. . . Conclusions Questions? UCB Innovation for Specialists™ A Global Biopharma Leader OLSUG April 3, 2006 - 2

UCB Innovation for Specialists™ A Global Biopharma Leader

UCB Today “Focusing on severe diseases treated by specialists” Slough & Cambridge (UK) Braine l’Alleud(B) • Leader in fields of epilepsy, allergy, and monoclonal antibody research • Revenues of € 2. 1 billion • A Top 30 Pharma and a top 5 Biopharma company • Global presence in over 40 countries Brussels (B) (Corporate HQ) ® ® Tokyo (J) Atlanta (US) Bulle (CH) ® UCB Innovation for Specialists™ A Global Biopharma Leader OLSUG April 3, 2006 - 4

What is Biopharma ? Keppra ( small molecule ) Ig. G ( antibody, a large molecule ) UCB Innovation for Specialists™ A Global Biopharma Leader OLSUG April 3, 2006 - 5

Technical Context: Legacy systems Oracle 8. 1. 7 Commercial design Day. Light UD 3 (Cell. Tech) Oracle 9. 2 In-house design MDL Sethi (UCB) UCB Innovation for Specialists™ A Global Biopharma Leader OLSUG April 3, 2006 - 6

Technical Context: Target system • Object model • Persisted into Oracle • XSD to validate the XML files transmitting new batches information UCB Innovation for Specialists™ A Global Biopharma Leader OLSUG April 3, 2006 - 7

Design: Chimie and Chemistry. . . • A batch is a batch – but what’s a compound implement? • From base structure to adducts-adorned structure • Stereochemistry, mixtures, alternates, unknowns. . . => Scientific decisions ! UCB Innovation for Specialists™ A Global Biopharma Leader OLSUG April 3, 2006 - 8

Design: Choice of the XML-generating tool • All Unix servers supporting Oracle databases • First a bulk migration, but then also an ongoing migration phase (keep capturing changes on source databases until new registration application is in use) • Skill set of the available resources • Oracle known for its internet initiatives • XSD-based pre-send validation preferred => Believe it or not: Oracle ! UCB Innovation for Specialists™ A Global Biopharma Leader OLSUG April 3, 2006 - 9

Design: Which Oracle XML Tool? • Sethi: Oracle 9. 2. 0. 4. 0 • UD 3: Oracle 8. 0. 4 => common least denominator is the XDK (including the XML Query utility XSU) • XSU: -- set up the query context. . . ! query. Ctx : = DBMS_XMLQuery. new. Context('select * from emp'); -- get the result. . ! result : = DBMS_XMLQuery. get. XML(query. Ctx); Þ is best used when document-building logic is simple • <dbms_>xmldom: • full PL/SQL programming flexibility • XSD validation-enabled. . . from 9 i on (XML DB) UCB Innovation for Specialists™ A Global Biopharma Leader OLSUG April 3, 2006 - 10

Design: Version Issues No XSD validation Not upgradeable Oracle 8. 1. 7 Commercial design Day. Light UD 3 (Cell. Tech) XSD validation OK Upgrades are costly Oracle 9. 2 In-house design MDL Sethi (UCB) UCB Innovation for Specialists™ A Global Biopharma Leader OLSUG April 3, 2006 - 11

Design: There Must Be Only One Select. . . From mytab<@UD 3> Oracle 8. 1. 7 Commercial design DB Link Oracle 9. 2 XML Day. Light UD 3 (Cell. Tech) Oracle 9. 2 In-house design XML MDL Select. . . From mytab Sethi (UCB) UCB Innovation for Specialists™ A Global Biopharma Leader OLSUG April 3, 2006 - 12

Design: A Long Story of Chemistry. . . Select LONG From mytab<@UD 3> Oracle 8. 1. 7 Commercial design DB Link Oracle 9. 2 XML Day. Light UD 3 (Cell. Tech) Oracle 9. 2 In-house design XML MDL Select. . . From mytab Sethi (UCB) UCB Innovation for Specialists™ A Global Biopharma Leader OLSUG April 3, 2006 - 13

Design: Packaging the Molecules in 8 i Mol. Table Long. Mol LONG Temp. LOBTable SQL level only: Insert into Temp. LOBTable select to_clob(Long. Mol) Temp. LOB CLOB Package Molecule. Sender Char. Table. Type is table of of varchar 2 PL/SQL: parse into table of varchar 2 UCB Innovation for Specialists™ A Global Biopharma Leader OLSUG April 3, 2006 - 14

Design: Packaging the Molecules in 8 i CREATE global temporary table templobtable (templob CLOB); CREATE OR REPLACE package MOLECULE_SENDER as type chartabletype is table of varchar 2(2000) index by binary_integer; function package_molecule (ye. Mol. Nbr) return chartabletype; D B L i n k function package_molecule. . . is. . . execute immediate 'insert into templobtable select to_lob(Long. Mol) from Mol. Table where Mol. Nbr=: 1‘ using ye. Mol. Nbr; select templob into zeclob from templobtable; for i in 1. . zenbrchunks loop zetab(i) : = dbms_lob. substr(zeclob, zechunklength, (i-1)*zechunklength+1); end loop; return zetab; zechartable molecule_sender@UD 3. chartabletype; zechartable : = molecule_sender@UD 3. package_molecule(ye. Mol. Nbr); UCB Innovation for Specialists™ A Global Biopharma Leader OLSUG April 3, 2006 - 15

Design: dbms_xmldom on Elements and Nodes <An. Element> Creating an Element and related (? ) Sub. Element: <ASub. Element/> <An. Element/> ze. Doc dbms_xmldom. DOMDocument: ze. Elem : = Dbms_xmldom. create_element(ze. XMLDoc, ’An. Element’); ze. Sub. Elem : = Dbms_xmldom. create_element(ze. XMLDoc, ’ASub. Element’); => you need to use the DOM Element // Node equivalence to make the Sub. Element a child of the Element: ze. Node : = Dbms_xmldom. make. Node(ze. Elem); ze. Sub. Node : = Dbms_xmldom. make. Node(ze. Sub. Elem); ze. Dummy : = dbms_xmldom. append. Child(ze. Node, ze. Sub. Node); UCB Innovation for Specialists™ A Global Biopharma Leader OLSUG April 3, 2006 - 16

Design: dbms_xmldom on Locality A DOM variable cannot be reused locally <An. Element> <ASub. Element/> <An. Element/> Loop ze. Sub. Element : = Dbms_xmldom. create_element(ze. XMLDoc, ’ASub. Element’); ze. Sub. Node : = Dbms_xmldom. make. Node(ze. Sub. Elem); ze. Dummy : = dbms_xmldom. append. Child(ze. Node, ze. Sub. Node); <fill the contents of your ASub. Element> end loop; Þ Fails ! Workaround: From the loop, call a subprocedure where ze. Sub. Element and ze. Sub. Node are local variables. . . UCB Innovation for Specialists™ A Global Biopharma Leader OLSUG April 3, 2006 - 17

Design: dbms_xmldom on long CDATA dbms_xmldom cannot add more than a varchar 2 -full into a CDATA section Workaround: • use a place holder whilst creating the XML document dbms_xmldom. create. CDATASection (ze. XMLDoc, ’<<<PLACEHOLDER>>>’); . . . • Then before publishing copy the XML document to a CLOB: dbms_xmldom. write. To. Clob(ze. XMLDoc, ze. XMLCLOB); and use dbms_lob to replace the <<<PLACEHOLDER>>> by the actually expected CDATA section inside the CLOB variable UCB Innovation for Specialists™ A Global Biopharma Leader OLSUG April 3, 2006 - 18

Hindsight • Even 9 i. R 2 required a patch for dbms_xmldom to work properly ! • Otherwise: too many XMLDOM handles open (error ORA-31182) • Oracle 9 i+ anyway, so could have used XQuery instead of dbms_xmldom ? • Indeed, dbms_xmldom is memory-intensive (the whole XML document is put in memory) • Hence: build up the XML document from atomic XQuery results into a CLOB? . . . • But: need to manage yourself the closing of tags that enclose the results of atomic XQueries? • XML DB ? • Create the document structure, then use Update. XML to fill in the attributes? UCB Innovation for Specialists™ A Global Biopharma Leader OLSUG April 3, 2006 - 19

Conclusions • Oracle and XML: it works, and fast enough on the existing server too • Many ways to skin a cat ! • There is legacy and older legacy. . . • XML/XSD: a nice bridge from the relational world to the object-oriented one UCB Innovation for Specialists™ A Global Biopharma Leader OLSUG April 3, 2006 - 20