Customer Relationship Management (CRM)
Data Farm
Inc.®
|
The Technical "Know-How"
|
Data CLOB and BLOB - Oracle Has The Answer
BLOBs (Binary Large Objects) and CLOBs (Character Large Objects):
Caché SQL supports the ability to store BLOBs (Binary Large Objects) and
CLOBs (Character Large Objects) within the database. BLOBs are used to store binary
information, such as images, while CLOBs are used to store character information. BLOBs
and CLOBs can store up to 4 Gigabytes of data (the limit imposed by the JDBC and ODBC specifications).
In a nutshell, converting DAO to XML and XML to DOA need both XML database field and
database vendors support. Luckily, Oracle has the following to say.
(http://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb03usg.htm)
Storing XML as XMLType - according to Oracle Support (Oracle9i release 1 (9.0.1)):
There were two ways to store XML content in Oracle Database:
|
•
|
Use Oracle XML Developer's Kit (XDK) to parse the XML document outside Oracle
Database, and store the extracted XML data as rows in one or more tables in the database.
|
|
•
|
Store the XML document in Oracle Database using a Character Large Object
(CLOB), Binary Large Object (BLOB), Binary File (BFILE), or VARCHAR column.
|
What is XMLType?
New datatype, XMLType, to facilitate native handling of XML data in the database:
|
•
|
XMLType can represent an XML document in the database, so it is accessible in SQL.
|
|
•
|
XMLType has built-in methods that operate on XML content. For example, you can use XMLType methods to create, extract, and index XML data stored in Oracle Database.
|
|
•
|
XMLType functionality is also available through a set of Application Program Interfaces (APIs) provided in PL/SQL and Java.
|
|
•
|
XMLType can be used in PL/SQL stored procedures for parameters, return values, and variables
|
Benefits of the XMLType Datatype and API:
The XMLType datatype and application programming interface (API) enable SQL operations on XML content and XML operations on SQL content:
|
•
|
Versatile API. XMLType has a versatile API for application development that includes built-in functions, indexing, and navigation support.
|
|
•
|
XMLType and SQL. You can use XMLType in SQL statements, combined with other datatypes. For example, you can query XMLType columns and join the result of the extraction with a relational column. Oracle Database determines an optimal way to run such queries.
|
Indexing:
|
•
|
Oracle XML DB lets you create B*Tree indexes on the object-relational tables that provide structured storage of XMLType tables and columns.
|
|
•
|
Oracle Text indexing supports text indexing of the content of structured and unstructured XMLType tables and columns.
|
|
•
|
The CTXXPATH domain index type of Oracle Text provides an XML-specific text index with transactional semantics. This index type can speed up certain XPath-based searches on both structured and unstructured content.
|
|
•
|
Function-based indexes can be used to create indexes on explicit XPath expressions for both structured and unstructured XMLType storage.
|
When to Use XMLType
Use XMLType any time you want to use the database as a persistent storage of XML. XMLType functionality includes the following:
|
•
|
SQL queries on part of or the whole XML document – SQL functions existsNode and extract provide the necessary SQL query functions over XML documents.
|
|
•
|
XPath access using SQL functions existsNode and extract – XMLType uses the built-in C XML parser and processor and hence provides better performance and scalability when used inside the server.
|
|
•
|
Strong typing inside SQL statements and PL/SQL functions – The strong typing offered by XMLType ensures that the values passed in are XML values and not any arbitrary text string.
|
|
•
|
Indexing on XPath document queries – XMLType has methods that you can use to create function-based indexes that optimize searches.
|
|
•
|
Separation of applications from storage models – Using XMLType instead of CLOB values or relational storage allows applications to gracefully move to various storage alternatives later without affecting any of the query or DML statements in the application.
|
|
•
|
Support for future optimizations – New XML functionality will support XMLType. Because Oracle Database is natively aware that XMLType can store XML data, better optimizations and indexing techniques can be done. By writing applications to use XMLType, these optimizations and enhancements can be easily achieved and preserved in future releases without your needing to rewrite applications.
|
When to Use CLOB Storage for XMLType
Use CLOB storage for XMLType in the following cases:
|
•
|
When you are interested in storing and retrieving the whole document.
|
|
•
|
When you do not need to perform piece-wise updates on XML documents.
|
|
|
