Storing XML Data in Oracle: Binary XML, Object-Relational, CLOB

Storing XML Data in Oracle

Oracle provides an abstract SQL data type called XMLType for storing XML data in the database. You can create an XMLType table,  or an XMLType column in a relational table, to persist XML data.

Different storage models are available in Oracle to best fit the nature of the XML data and its expected use. These are:

  • Binary XML storage
  • Structured storage (object-relational)
  • Unstructured XML storage (CLOB)

Here’s an overview of each of these XML storage models with the use cases the model is appropriate for.

Read more

SQL: Compare Two Tables for Differences in Data

SQL Find Differences between Two Tables

An approach for comparing two tables for differences in data, using a pure SQL solution:

In a recent project, a shadow table had to be compared periodically with its main source table to identify the differences between the two tables.

The nature of differences fell into one of these buckets:

  1. Insert Required [INS]: If the source had rows not present in the shadow table
  2. Update Required [UPD]: If one or more columns in a row (identified by a primary key) had changed values
  3. Delete Required [DEL]: If the shadow table had rows not present in the source table

Comparing two tables for differences (INS/UPD/DEL) needed a different solution from a simple MERGE SQL statement. Here an upsert was not to be executed, only the nature of differences to be identified.

Read more

DATE Format SQL for Dates Stored as Strings

Convert-VARCHAR2-to-DATE.jpg

It is well-acknowledged that attributes in the database should use the correct datatypes (numbers should go into NUMBER columns, dates into DATE columns, etc). Storing date values in VARCHAR2 columns is an open invitation for bugs and issues due to date format such as the one I’m about to describe. Besides, using string-type columns to represent dates rules out your ability to build upon features like range interval partitioning. Yes – ideally you should never have to convert VARCHAR2 to date.

It is equally well-acknowledged that we do not live in an ideal world. In some situations, we do not control the database design; we have to live with what exists and provide a solution to the problem.

This article addresses one such problem — how to compare two dates when they are stored as strings with different DATE formats.

Read more

INNER JOIN and OUTER JOIN Explained

Joins

For those new to SQL, terms like INNER JOIN and OUTER JOIN can seem like fearsome foes. As the wise say, understanding conquers fear. Behind those geeky terms lie concepts rooted in simple real-world knowledge.

Here’s a quickstart guide to these two basic joins in SQL: INNER JOIN and OUTER JOIN.

Read more

Grant SELECT on V$ Views

Grant Select on V$ Views

When querying v$instance or v$session[1] as a non-admin user, you might come across this error:

SQL> select version
  2  from v$instance;
from v$instance
     *
ERROR at line 2:
ORA-00942: table or view does not exist

The error suggests that the non-admin user does not have the SELECT privilege on the sys-owned v$ view. On the face of it, the fix appears as simple as: log in as sys and grant select on v$ views to the user.

Appearances are deceptive, they say. Try that and you will get an ORA-02030 error.

Read more

Access Control List (ACL) in Oracle 11G

Access Control Lists in Oracle

I recently upgraded Oracle XE from 10G to 11G, and found that none of the PL/SQL code using UTL_HTTP was working after upgrade.

The code failed with the error:

declare
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 47

Oracle 10G used to be happy as long as the user running network packages like UTL_HTTP had execute permission on the package. Oracle 11G and above are not so easy to please (and rightly so!) — they enforce extra security, which means you need more access control configuration to get this working.

Read more