Top 60 Oracle Blogs

Recent comments


HOWTO: Mount your Database as File System via WebDAV under Linux

No, no this isn’t another DBFS post but a more simple and direct way of achieving the same


Just had a funny discussion with Roel Hartman regarding how to trick the Tomcat APEX 4 setup in believing that the virtual XFILES directory in the database was actually available on disk of the local server. This is probably not the way to solve this but should be realized via Tomcat / APEX 4. The OTN Development virtualbox environment with APEX 4 gets his “/i/” images via Tomcat from the directory.

#66cc66;">[oracle@localhost i#66cc66;">]$ pwd

The easiest solution would have been to copy the XFILES images and files in a directory called XFILES under the ROOT directory.

HOWTO: Merge files under Windows

Small “howto”, because I always forget.

Today I needed to merge to Linux tar files on Windows. I downloaded the two Oracle VM Templates for Oracle Business Intelligence Enterprise Edition (OBIEE). After unzipping them you get two tar files called

  • OVM_EL4U5_X86_PVM_10gR3BIEE.tgz.file1of2
  • OVM_EL4U5_X86_PVM_10gR3BIEE.tgz.file1of2

To be able to merge them and then “untar” those files, under Linux or Unix you would do something like:

cat #66cc66;">{file01#66cc66;">} #66cc66;">{file02#66cc66;">}  | tar -xvz
cat OVM_EL4U5_X86_PVM_10gR3BIEE.tgz.file1of2 OVM_EL4U5_X86_PVM_10gR3BIEE.tgz.file2of2| tar -xvz

…with other words, you would “cat” the first and second file into a file and then “untar”-ed it on the fly via the “tar -xvz”. In Windows you would do this via the following command, that is the first “cat” part, merging the files, via:

#b1b100; font-weight: bold;">copy /B #66cc66;">{file01#66cc66;">} #66cc66;">{file02#66cc66;">} #66cc66;">{newfile#66cc66;">}
#b1b100; font-weight: bold;">copy /B OVM_EL4U5_X86_PVM_10gR3BIEE.tgz.file1of2 + OVM_EL4U5_X86_PVM_10gR3BIEE.tgz.file2of2 OVM_EL4U5_X86_PVM_10gR3BIEE.tgz

the Windows equivalent of the Linux/Unix

Common XQuery mistakes…applied in XML DB

Do you read FAQ…?

Somehow I keep people reminding there is a FAQ URL on the XMLDB forum and even then people refuse to read those good examples… Anyway found two great posts I want to share and remember on this, my, web “notepad”. Besides the treewalker example, I tested the examples of those mentioned in the XQuery post on a Oracle 11.2 database.

As far as I could find the treewalker example is part of DOM V2 and not mandatory to implement but I wonder how I can get around the local() stuff, anyway, I will have to investigate a bit further if its just me being a novice in XQuery or that I am missing out on details/info. The XQuery post only demonstrates to me how powerful this extra query language is in an Oracle database and that it is time for me to learn this properly…

The posts that I was referring to:

…be aware of the use of the (double quote instead single quote), namespaces (indeed apparently always an issue) and using (::) in SQL*Plus… The (::) is needed in SQL*Plus to mark that the “;”  is not seen as direct processing instruction for SQL*Plus, but in this case, is for the XQuery engine.

The headlines follow the ones in the XQuery post…

External Views (XML based)

Something new? Eh? Should you do this? Eh?

In all, probably not, but for me this was a good exercise towards some more updated demo scripting for my “Boost your environment with XMLDB” presentation or hopefully more clearer relabeled Oracle Open World name for the almost same presentation called “Interfacing with Your Database via Oracle XML DB” (S319105). Just up front, there are some issues with the following:

  • Why should you do it at all. You should have a good reason doing so…
  • It can cause a lot of Physical I/O, at least initially when not cached in the SGA
  • Until current versions, AFAIK, it will do a lot of “Pickler Fetching”, serializing in memory, which is very resource intensive (CPU/PGA)
  • …and its probably not supported…?

…but it is good fun for a small exercise based on the following OTN Thread: “Error with basic XMLTable“…

Let me show you what I mean.

Via “bfilename” you are able, since a long time, I guess Oracle 9.2 and onwards, to read a file as a BLOB and because an “XMLTYPE” can swallow almost any datatype, you could do the following…

XMLDB Whitepapers and Tooling about Design, Performance and Selectivity

From time to time the main Oracle XML DB page gets updated with new whitepapers, tooling or Oracle By Example/ Hands-on Lab examples. “Lately” some cool and interesting new whitepapers and updated tooling content were created on this main Oracle XML DB page. The following items and content are really worth reading. Small issue, though, is that you need a bit more than basic understanding to put all this “lessons learned from the last one, two years” into context, but its worth it and otherwise a small reprise on the Oracle XML DB Developers Guide is always useful. A bit like re-reading the Oracle Concepts Manual.

The “Ease of Use Tools” ( tool set) for handling XMLType Object Relational storage has been updated and is now applicable on Oracle 10.x and 11.x. No specific to be installed versioned tool set needed anymore. This prepacked tool set on PL/SQL packages is installable on both versions. The zip file also contains a whitepaper that describes some of the (performance) lessons learned while using XMLType Object Relational storage.

Enabling and Disabling Database Options

One of those small items that is easy overlooked (at least I overlooked it...) and I think, arrived with Oracle database version 11.2, at least on Windows/Linux...

The Oracle 11gR2 Database Installation Guide for Windows described a new tool, at least for me, that enables or disables database features on Windows. In the manual it is described as follows...

When you install Oracle Database, certain options are enabled and others are disabled. If you must enable or disable a particular database feature for an Oracle home, then shut down the database and use the chopt tool. See Example 5-1.

The chopt tool is a command-line utility that is located in the ORACLE_HOME\bin directory. The syntax for chopt is as follows:

chopt [ enable | disable] db_option

The possible values for db_option described in the following table.

Oracle OTN Developer Day VirtualBox Appliance

I have forgotten and searched for this cool VirtualBox Appliance, now so often that I found it time to make a small notepad/placeholder for this.

For those who haven't found it yet, there is a OTN Developer VirtualBox appliance out there that is a great start-up environment to use as a test, presentation, try-out or development environment. Probably officially used for Oracle demo's and or Hands on Exercises (HOL) but also very usable on a laptop.

Its a VirtualBox environment with Hands on Lab (HOL) exercises and the following software environment/software:

  • Oracle Enterprise Linux 5
  • Oracle Database 11g Release 2 Enterprise Edition
  • Oracle TimesTen In-Memory Database Cache
  • Oracle XML DB
  • Oracle SQL Developer
  • Oracle SQL Developer Data Modeler
  • Oracle Application Express
  • Oracle JDeveloper
  • Hands-On-Labs (accessed via the Toolbar Menu in Firefox)

Be aware that, as it is described as well on the download instruction page, that this appliance is for testing purposes only, as such it is unsupported and should not to be used in production environment. I really hope they make more of those cool VirtualBox example appliances.

Click here for the Pre-Workshop Setup Instructions - Hands-On Labs


Oracle OTN Developer Day VirtualBox Appliance

ORA-31098: Internal event to turn on XDB tracing

“Do not document”…

…if not only due to that comment, it is a very interesting event, if not only that it seems that it is used for multiple items and not only tracing. I tried to figure out what I could do with this event regarding the XDB Protocol Server trying to figure out how it works and to trace a partially documented/undocumented feature in the manuals that makes use of the XDB Protocol Server.

The XDB Procotol Server architecture is used for more than the (APEX) PL/SQL Gateway. It also supports HTTP, FTP and WebDAV (so called) “servlets” and a hook-in into C kernel library that enables the XMLDB Native Database Web Service (NDWS).

The following should only be done asked by Oracle Support and/or are at your own risk. Always test on a test environment (so if when the database is corrupt is not a big deal)

Anyway, “events” can be set on session and system level and/or via the oradebug facility. Julian Dyke has a good post on the basics. As Julian describes, there are four types of numeric events: Immediate dumps, Conditional dumps, Trace dumps, Events that change database behavior. The “fun” with the ORA-31098 seems that is used for multiple things. It at least creates DDL scripts in trace file during dbms_xmlschema registration and it also traces XDB Protocol Server issues. During X-Mas and new years eve I had some time, so I tried to figure out some of them…

Described in a small overview XDB event post, some of these settings can be found somewhere in the Oracle manuals…

Debugging XML Schema Registration

ORA-31098: Internal event to turn on XDB tracing

Structured XMLIndex (Part 3) – Building Multiple XMLIndex Structures

You will probably never build only one structured XMLIndex. A practical use case would be an unstructured XMLIndex, indexing the semi-structured parts of your XML, multiple structured XMLIndexes, indexing the highly structured XML islands of data and maybe even a Oracle Text Context index indexing unstructured XML data.

So the next example’s will show how to build an unstructured XMLIndex and build multiple structured XMLIndexes on top of the first one. Also it will give some examples on what to do if you have made mistakes and/or how to apply some maintenance on the XMLIndex structures. You start of by determining which sections should be addressed by the Unstructured XMLIndex and via path subsetting restrict the index to that part (also see “Oracle 11g – XMLIndex (Part 2) – XMLIndex Path Subsetting” for more info on path subsetting). There should be, I think, a good reason for indexing the same node path via multiple structured or unstructured XMLIndexes. One I can think of is to support different kind of XML Queries, but be aware that it, multiple XMLIndex structures on the same nodes, will come with an extra index maintenance overhead.

Anyway, lets say you want most part (haven’t used path subsetting here for the unstructured XMLIndex, but as said I should have done) of the XML document indexed via a unstructured XMLIndex and an extra of two structured XMLIndexes on top of the domain XMLIndex…

Structured XMLIndex (Part 2) – Howto build a structured XMLIndex

As said in the “rule of numb” post, test your statement before you build an XMLIndex (structured or unstructured) on you column or table XML store. The database will check on the syntax you will use but NOT on the outcome. So if you statement doesn’t have the proper result set or is even empty, than the content table(s) or path table will be indexing the wrong element values or even a null data set. Be aware that XML in Oracle is case-sensitive and critical on calling a namespace reference if one if demanded by the W3C rules.

The following example will build a single structured XMLIndex on a binary xml column.