Howto

People who have attended the UKOUG presentation this year where Mark Drake, Sr. Product Manager XML Technologies / XMLDB, Oracle HQ, and I demonstrated the first principles of the XDB Repository, might have been impressed with its (GEO/KML Spatial, Image EXIF info) capabilities combined with Google Earth. This post will zoom in on how to …

Continue reading »

Oracle XMLDB Repository Events, IMHO, was one of the coolest functionalities introduced in Oracle 11.1. In principal they are a kind of event “triggers” that get fired during actions / methods on objects in the XDB Repository. One of the disadvantages of this functionality is that they are very “sparsely” documented in the Oracle XMLDB …

Continue reading »

Since a long time, the database has had some versioning capabilities, long before features like “Edition Based Redefinition” in Oracle 11gR2 appeared. This versioning, via XMLDB functionality, is based on its XDB Repository access to the database. The XDB Repository is a file/folder metaphor that acts as a file server. You can enable this functionality …

Continue reading »

This was a small mind exercise on the OakTable website (OakTable Challenge)for a person regarding how to go from a relational table to anydata datatype table and back, which I, of course, approached via an “XMLType” of way thinking. Probably the whole thing is not that practical and/or can be optimized in various ways, but …

Continue reading »

This week Mark Drake, Senior Product Manager Oracle XMLDB, put up a new page on Oracle OTN with some great code examples and utilities demonstrating Oracle XMLDB functionality. Among others, the latest code for Mark’s XFILES XMLDB demo application version 5, Introduction to Oracle XML DB Repository Events, Basic Introduction to Oracle XML DB 11.2.0.3.0, …

Continue reading »

I just made use of the very cool OTN Virtual Developer Day Database site. In this environment you can follow OTN Developer Day sessions, for example, at home, while making use of all the material available on that site plus the downloadable Virtualbox OTN Developer Day Appliance. Despite you can choose for tracks like Java, .Net, APEX, there is also a database section which handles (as you might expect it from me regarding interest) Oracle XMLDB functionality.

Someone beat me to it in this good post: Oracle Tip: Counting ROWS for all tables in a Schema . So here a reminder for me where to find it.

Although not a “pure” XML partitioning example, that is partitioning data on criteria within the XML document, and before I forget to mention this exercise, I would like to point out the following URL:

http://forums.oracle.com/forums/thread.jspa?threadID=2234618

This small exercise was setup based on questions / comments from a reader on this blog regarding the ”
Structured XMLIndex (Part 3) – Building Multiple XMLIndex Structures” content after heaving trouble to setup structured and unstructured local XMLIndexes.

The forum link demonstrates howto:

• Register a XML Schema for use with Binary XML storage
• Create a RANGE partitioned table with a XMLType column (Binary XML Securefile storage)
• Create a Unstructured LOCAL Partitioned XMLIndex (UXI)
• Create multiple Structured local partitioned XMLIndexes (SXI)
• Create secondary indexes on the Content Tables created by the SXI structures
• The effects of different queries and their explain plan output making use of the UXI, SXI and partitioning

HTH

Most XML datetime information is represented like the following:

#66cc66;"><date#66cc66;">>#cc66cc;">2011#66cc66;">-05#66cc66;">-23T12:01:#cc66cc;">51.217#66cc66;">+02:00#66cc66;">date#66cc66;">>

So said that, how do you get from that format to a Oracle datetype… The following will help converting you from the W3C datetime towards Oracle datetypes…

#993333; font-weight: bold;">WITH datestuff #993333; font-weight: bold;">AS
 #66cc66;">(#993333; font-weight: bold;">SELECT xmltype#66cc66;">(#ff0000;">'2011-05-23T12:01:51.217+02:00'#66cc66;">) xmlcol 
    #993333; font-weight: bold;">FROM dual
 #66cc66;">)
#993333; font-weight: bold;">SELECT to_timestamp_tz#66cc66;">(xt#66cc66;">.datum#66cc66;">,#ff0000;">'YYYY-MM-DD"T"HH24:MI:SS.FF9tzh:tzm'#66cc66;">) 
       #993333; font-weight: bold;">AS #ff0000;">"TO_TIMESTAMP_TZ"
#993333; font-weight: bold;">FROM   datestuff 
#66cc66;">,      xmltable#66cc66;">(#ff0000;">'*'
                passing xmlcol
                #993333; font-weight: bold;">COLUMNS
                  datum varchar2#66cc66;">(#cc66cc;">35#66cc66;">) PATH #ff0000;">'/date'
                #66cc66;">) xt;
 
TO_TIMESTAMP_TZ
#808080; font-style: italic;">-----------------------------------
#cc66cc;">23#66cc66;">-05#66cc66;">-#cc66cc;">11 #cc66cc;">12:01:#cc66cc;">51#66cc66;">,#cc66cc;">217000000 #66cc66;">+02:00

So use the timestamp with timezone function and make sure the format fits while using a varchar2(35).

Sometimes the answer can be so simple…

I wanted to reset the OTN Developer Days Virtualbox APEX Listener admin password without redeploying the apex.war file and destroying the current setup, so I searched all config and properties APEX Listener files I could find. Searched via Google. No useful hits. I found a credential file with the passwords in them on the virtualbox environment, probably MD5 hashed ones, but no clue about how to reset them. At final I got in direct contact with Kris Rice which had put so much effort in setting up this training environment…and was a bit startled about the simple solution…

Marco,
There’s a file named credentials under ~/.apex/[port numner]/. If you remove that, it will reprompt for the passwords. Let me know if you need anything else.

-kris

Life can be so simple.