Oracle XML: When an Oracle Database houses an XML guest

...Oracle hooks-up with XML

XML documents apart from being a datasource also serves as a structured document with a definable schema. XML, as a standard, contains textual data which can be stored in an oracle database. XML data is usually described as a Character Large Object(CLOB) since it hosts several bytes of string data in a single document or data structure.

The fact that XML is a specialised data and datasource makes it’s storage in an Oracle database demand a specific approach to  its data storage and retrieval.

An Oracle database stores XML data in either a table or a column with a specific datatype – XMLTYPE.  Such data when stored are accessed using PL/SQL functions (which are an extension of custom XMLTYPE functions and operations) and XPATH constructs.

XPATH, the XML Path Language, is a query language for selecting nodes from XML data.

Below are sampled steps used to store and retrieve XML data in an Oracle database.

1. We create a table to store all ORDER xml content within a column of a database table.

CREATE TABLE ORDER_XML_DOC_TBL

(

FILEID varchar2 (12),

FILENAME varchar2 (64),

XML_DOCUMENT XMLTYPE

);

2. Creation of a CLOB variable to temporary host xml data

DECLARE XML_TEXT CLOB := '

<?xml version="1.0"?>

<Order>

 <Date>2003/07/04</Date>

 <CustomerId>123</CustomerId>

 <CustomerName>Joyce Appiah Ofori</CustomerName>

 <Item>

         <ItemId>356</ItemId>

         <ItemName>Sephora Beauty Range</ItemName>

         <Quantity>2</Quantity>

 </Item>

<Item>

         <ItemId>030</ItemId>

         <ItemName>Mary Kay Cosmetics</ItemName>

         <Quantity unit="12">3</Quantity>

</Item>

<Item> 

         <ItemId>579</ItemId>

         <ItemName>L’eggs Stockings</ItemName>

         <Quantity>1</Quantity>

 </Item>

</Order>';

3. Storage of XML in database table within a transaction to guarantee a full export or none

BEGIN

INSERT INTO ORDER_XML_DOC_TBL VALUES ('ORDER_DOC_1','ORDER_101.xml', XMLTYPE (XML_TEXT));

END;

 4. Fetching data from the XML table using XMLTYPE functions of PL/SQL

Example 1. Using SQL function ‘extract’  and ‘extractValue’ to display all ItemId child elements of the Order parent as a varchar2 string type.

SELECT extract (XML_DOCUMENT, '/Order/Item/ItemId /text()').getStringVal()  "ITEM_IDs"  FROM  ORDER_XML_DOC_TBL

or simply

SELECT extract (XML_DOCUMENT, '/Order//ItemId /text()').getStringVal()  "ITEM_IDs"  FROM ORDER_XML_DOC_TBL

 or using extractValue function (which needs no data conversion) with an ORDER BY clause.

SELECT extractValue(XML_DOCUMENT, '/Order/Item/ItemId ')  "ITEM_IDs" FROM  ORDER_XML_DOC_TBL ORDER BY extractValue(XML_DOCUMENT, '/Order/Item/Quantity ')

Example 2. Using SQL function ‘extractValue’ to display a particular Item with Id= 579. Also introduced is the existsNode boolean function that returns 0 or 1 for either true or false.

SELECT extractValue(XML_DOCUMENT, '/Order/Item/ItemId ')  "ITEM_ID"  FROM ORDER_XML_DOC_TBL WHERE existsNode (XML_DOCUMENT, '/ Order //emp [ItemId ="579"]') =  1;

5. Reference

 Sample Order XML from Sybase Infocenter site.

 Oracle XML syntax from the Oracle Documentation site.

Oracle Database Diagnostics: About Trace and Log Files

The Oracle DBA’s swiss-knife for fixing an issue is first knowing where and how to find diagnostic information needed to trouble-shoot defects and warnings on a database and its instance.

Oracle has rightly provided a centralised means of tracking down log and trace info about all its vendor products (rdbms, app_servers etc) in a single location called the Automatic Diagnostic Repository(ADR).

a.    It is found in the diag folder of the base-location of oracle - <ORACLE_BASE>/diag.

e.g.  /home/install/Orabase/diag/rdbms/annang_db/sample_sid/trace/alert_log

b.   At the database level, the diagnostic info can be stated by displaying info from running a select statement on the dynamic performance view v$diag_info.

c.    The location of diagnostic files can be customised for an oracle database by setting the DIAGNOSTIC_DEST parameter in the parameter file (PFILE/SPFILE) usually when the database is not mounted.

d.    alert logs usually contains DDL statements, system level activities and non-standard parameters configured or modified.

Performing a Cyclic Redundancy Check Checksum on a file using Java

I want to delve on how to generate a CRC checksum (acronym for SUMmation CHECK) for  textual/binary data using the Java programming language.

This is achieved using the Checksum class and its derivative/child class CRC32. As a programming best practice, a subroutine/function (called method in Java) is created and invoked at a point real data is passed unto it as a parameter. 

Purpose: This enables one to use a string token to verify if a file has been compromised or not. This is done by a sending point generating a token (programmatically or other) on the file in transit and the receiving point regenerating a token for comparison to ascertain the authenticity of a file.

a. Generating CRC checksum programmatically 
snippet 1.0: Java code snippet to generate checksum on a file labelled data.avi.

public class TestChecksum {

                

                 //catch exceptions if neccessary

public static long performChecksum(Object data) {

Checksum checksum = new CRC32();

//build checksum

checksum.update(data.getBytes(), 0,

data.getBytes().length);

//produce and return checksum token.

return checksum.getValue();

}

             //catch exceptions if neccessary

public static void main(String[] args){

//input data here: txt, avi, doc etc

Object data = new File (‘data.avi);

//Invoke instance method performChecksum here

new TestChecksum().performChecksum(data);

}

}

b. Generating CRC checksum from Linux to ascertain the authenticity of a file 

This is achieved by using the cksum command on Linux to generate a checksum to compare to a previous token.

fig. 1.0: command line code to generate the checksum and data byte size.

Joseph Annang Sowah : An Introductory Blog Post

Hi peeps,

My Ghanaian parents gave me the moniker Joseph Annang Sowah.

With this first posting on this blog I welcome you- my cherished guests-to peruse through my technical insights and also stop by to pickup some few crumbs of my thoughts(quotes) concerning life and more.

Technical: Focus would be on Open-Source, IBM & Oracle technologies interspersed with an accidental :)  exposure to the Microsoft stack of technologies.

Life: A few quotes and life experiences.
Also have a quick look at some of my quotes on Quotesdaddy.com

I am a proud Associate Member of the Free Software Foundation(#12420) in pursuance of my passion for Free and Open Source Software and Technologies.