[FSF Associate Member] View Annang Sowah's profile on LinkedIn

Friday 19 July 2013

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.


Wednesday 17 July 2013

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.




Thursday 11 July 2013

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.