Development and Runtime Experiences with a Canonical Data Model Part I: Standards & Guidelines

Introduction

In my previous blog I’ve explained what a Canonical Data Model (CDM) is and why you should use it. This blog is about how to do this. I will share my experiences on how to create and use a CDM. I gained these experiences at several projects, small ones, and large ones. All of these experiences were related to an XML based CDM. This blog consists of three parts. This blogpost contains part I: (more...)

Development and Runtime Experiences with a Canonical Data Model Part II: XML Namespace Standards

This blog is about XML namespace standards. Primary for using them in a Canonical Data Model (CDM), but also interesting for anyone who has to define XML data by creating XML Schema files (XSD). This blogpost is the second part of a trilogy about my experiences in using and developing a CDM. The first blogpost is about naming & structure standards and the third blogpost is about dependency management & interface tailoring.

XML Namespace Standards

(more...)

Development and Runtime Experiences with a Canonical Data Model Part III: Dependency Management & Interface Tailoring

Introduction

This blogpost is part III, the last part of a trilogy on how to create and use a Canonical Data Model (CDM). The first blogpost contains part I in which I share my experiences in developing a CDM and provide you with lots of standards and guidelines for creating a CDM. The second part is all about XML Namespace Standards. This part is about usage of a CDM in the integration layer, thus how (more...)

ORA-22926 when using getClobVal to convert XMLType to CLOB

I ran into a problem the other day when moving some code from one database to another (both XE 11g). The code in question needs to convert an XMLType to a CLOB to do some (hacky) string manipulation on it, and then turn it back to an XMLType.

Here's the original code:

  l_xml := apex_web_service.make_request(...);
 
  -- little hack to remove bad empty namespace from result
  l_clob := l_xml.getClobVal();
(more...)

checking suspicious bind variables in v$sql_monitor

In my current company we try to stabilize the performance of our Peoplesoft application. So on a more or less regular base I get a call to investigate as "it" is "slow" now.
During my research I found one error-pattern I'd like to show here: SQLs which where parsed for very selective BINDs (like customer-id) are executed with a single space (" ") as bind.
Unfortunately in Peoplesoft this character is used similar to NULL - (more...)

Oracle Dynamic SQL: generic search – crazy case

For the Friday evening I decided to dig out my example from the most recent of my published books (Expert PL/SQL Practices, APress, 2011) - it was a very fun project to work with: first, to come with with the idea of such multi-author book, second, to write a chapter focused on Dynamic SQL.

As one of the examples I decided to illustrate how XMLType can be used as both a collection of (more...)

SQL Solution for the Third International NoCOUG SQL & NoSQL Challenge

SQL vs NoSQL: Third International NoCOUG SQL & NoSQL Challenge sponsored by Pythian
As published in the 102nd issue of the NoCOUG Journal

THE WICKED WITCH OF THE WEST NEEDS HELP

BE IT KNOWN BY THESE PRESENTS that the Wicked Witch of the West needs your help to create
a (more...)

How to simulate DML Operations on XML data using XU (XQuery Update)

The following SQL pattern can be used to simulate DML operations on XML data by utilizing XU (XQuery Update) functionalities.

This query will do the following XML data transformation by using XU insert/delete/rename/replace statements.

1) Convert all the XML Attributes to Elements.                ( XU Delete & Insert)

2) Change the  (more...)

How to validate XML data using XSD in Oracle XML DB

The following XML Schema can be used to validate the XML input data in the XML DB.

It enforces the following validation rules:

1) Parent_Tab/Parent_Row/dep_id is the (primary) key within Parent_Tab.
2) Parent_Tab/Parent_Row/dep_name should be one of the xs:enumeration list in DeptnameType and it is also unique within Parent_Tab.
3) (more...)

How to do XML based data manipulation in SQL and XSLT

The following SQL pattern can be used to do XML based data manipulation and transformation by utilizing XSLT functionality.

This query will do the following data manipulation and transformation:

1) Rename the xml tag <FirstName> to <FN> when the position of the element node is
either the last one or (more...)

How to do table column based data manipulation in SQL and XQuery

The following SQL pattern can be used to stragg all the table column data together
The STRAGG(column data) is grouped together based on the table column names.
This SQL also manipulates comma-delimited strings using XQuery functionalities.


CREATE TABLE PERSON
(ID            NUMBER,
 FIRSTNAME     VARCHAR2(20 BYTE),
 MIDDLENAME    VARCHAR2(20 BYTE),
 LASTNAME      VARCHAR2(20 BYTE),
  (more...)

How to do character based data manipulation in SQL and XQuery

The following SQL pattern can be used to do character based data manipulation
by utilizing the XQuery functionalities.


COLUMN old_str  FORMAT  A28
COLUMN ordered_no_dup_chars FORMAT A23
COLUMN Reversed_chars FORMAT  A23



--------------------SQL Query-------------------


WITH data AS
(SELECT '3332221118888'   AS  old_str  FROM dual
  UNION ALL
  SELECT 'CCCBBBAAA'          FROM dual 
   UNION ALL
  SELECT  (more...)

How to convert an Internet HTML Table to CSV format in SQL

The following SQL pattern can be used to transform an HTML Table on the web to CSV format.


set long 10000;
variable url_input varchar2(500)
exec :url_input:='http://oraqa.com/2012/02/27/how-to-transform-a-csv-file-into-html-table-in-sql/'


-------------------------------------------------------------------SQL QUERY---------------------------------------------------------------------


WITH HTML_TAB AS
(SELECT CAST(REGEXP_REPLACE(SUBSTR(STR, 0, REGEXP_INSTR(str,'</table>', 1,1,1, 'i') ), '( ){2,}', ' ') 
         AS VARCHAR2(4000)) AS html_str
 FROM 
 (SELECT SUBSTR(str,  (more...)

How to transform a RefCursor query resultset into HTML table in SQL

The following SQL pattern can be used to tranform a RefCursor query resultset into data in HTML table format by utilizing the XSLT functionality. The query can handle NULL in the data columns. The SQL input string should be based on the following format:

SELECT Col AS name_x, Function() AS (more...)

How to convert a RefCursor query resultset to CSV format in SQL

The following SQL pattern can be used to convert a RefCursor query result set into a CSV format. This query can handle NULL in the data columns. The SQL input string should be based on the following format:

SELECT Col AS name_x, Function() AS name_y, Analytical_function( ) AS name_z FROM (more...)

How to transform a CSV file into HTML table in SQL

The following SQL pattern can be used to tranform the CSV file into a HTML table by utilizing the XQUERY functionality.


create or replace directory tmp as '/tmp';

create a word.csv file.

C:\tmp>more word.csv

ID,FIRSTNAME,MIDDLENAME,LASTNAME
1,Frank,,Zhou
2,Peter,B,Lee
3,John,C,Adam
4,Dave,,Washington


CREATE TABLE CSV_FILE (csv_str VARCHAR2(3000))
     ORGANIZATION EXTERNAL
     (
       TYPE  (more...)

JDeveloper Overview and (book) Review

Dear visitors, I will pretend I forgot that I'm about a year without posting anything and I will go straight to the subject that I owe to you: The review of the book "Processing XML documents with...

This is a summary only. Please, visit the blog for full content and (more...)

The X (Path) File

by Eduardo Rodrigues This week I came across one of those mysterious problems where I had some test cases that needed to verify the content of some DOM trees to guarantee that the test went fine....

This is a summary only. Please, visit the blog for full content and more.

Exposing Web Services as Database Views

In recent years, Web Services have become the defacto standard in passing  data between applications.   They make communication between disparate computer languages and architectures relatively simple.  For the most part, we tend to think of web services in terms of traditional application development (Java, .NET, Ruby, PHP, etc …) and inter-process communication.  What if we looked at them from another perspective?  What if we could easily integrate web services into the database?  Make them consumable by Business Intelligence tools, and Data Mining engines WITHOUT having to change the traditional manner in which these tools access (more...)

PL/SQL Web Service Utility: Accessing Web Services with PL/SQL is Simple!

This entry showcases a PL/SQL package called WebServiceUtils that makes accessing a web service from with the Oracle database very simple.  The package makes use of Tim Hall’s SOAP_API PL/SQL package (updated by me to get the SOAP response as a CLOB instead of a VARCHAR2) to do the SOAP encoding and processing.  The WebServiceUtils package puts a user friendly layer over the SOAP_API.    The WebServiceUtils package (here after known as the “utils” package) provides three structures (2 record types and 1 collection).  The two record types are:

  • service_definition_type – Contains attributes related to the service (more...)