Here’s a prototype for using the SQL/XML function XMLTABLE to map XML data into relational rows and columns.
This solution uses the standard EMP table — the same can be extended to work with any XMLTYPE-relational mapping.
{ 0 comments }
XML
After the post on models for storing XML data in Oracle and examples of binary XML storage, here’s a detailed look at the rigorous and performant structured XML storage or object-relational storage.
{ 1 comment }
The previous post on storing XML data in Oracle gave an overview of binary XML storage: compact, flexible, compatible with XML data with or without associated XML schema.
This post shows working examples of binary XML storage
1.1. without XML Schema
1.2 with XML Schema
{ 0 comments }
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.
{ 0 comments }
Oracle has built-in functions to convert relational data into XML format easily. These functions comes under the umbrella of SQL/XML, a specification that supports the mapping and manipulation of XML from SQL.
This article shows you how to generate XML from relational data using Oracle SQL/XML functions as building blocks.
{ 0 comments }
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.
{ 1 comment }
You can call a web service from PL/SQL using the Oracle-supplied package UTL_HTTP. Here’s a demo of calling a public web service that returns latest city weather by ZIP.
{ 5 comments }
Consider a PL/SQL call to a function which returns a user-defined type (UDT) – say, a SQL Type object. This works well as long as the called function resides in the same database as the calling PL/SQL, but if the called function is in a remote database, the PL/SQL call via DBLink end ups with the following error:
Error: PLS-00453: remote operations not permitted on object tables or user-defined type columns
This has often led to issues discovered too late for developers, who successfully tested function calls over multiple schemas simulating remote calls, but when the function databases were separated in later stages of testing, the PL/SQL call no longer worked.
{ 1 comment }
A frequent requirement in SQL is to "pivot" a result set — that is, display rows as columns. Before 11G, the developer had to do the equivalent of jumping through hoops to pivot the data. In 11G, with the introduction of the new PIVOT syntax, the task of transposing rows to columns has become a lot more intuitive.
This post shows the use of PIVOT with an example and sample scripts.
{ 7 comments }
