1. June 26

    Hi Marco,

    I used the extract method too and tried to rewrite to what you suggest, but get an error: SQL command not properly ended. I’m trying on 10.1. Is it supposed to work on there?


  2. June 26

    XMLTABLE is a new xml operator you can use from Oracle 10g Release 2 and upwards. My advise would be to use it from and upwards, because the xquery functionality has been significantly improved in this patch level.

  3. January 27

    Hi Marco!
    It seems that lately the Oracle Forums RSS software has something inside that is causing the HTTPURITYPE.getXML method to fail, allegedly reporting HTTP 500 error. The curious bit is in that accessing the same RSS source with a normal RSS reader, the operation succeeds without any problems, so i started wondering what could be the possible cause of this. This started happening about around mid January and is database version independent as i’ve tried with different Oracle versions and it was confirmed by another guy who is apparently attempting to do the same elsewhere.
    I wonder if someone introduced some filtering at the level of the user agent in the forum software since that date.

    You can easily check this out by attempting to execute the following query:

    select httpuritype(‘http://forums.oracle.com/forums/rss/rssmessages.jspa?forumID=137’).getCLOB() x
    from dual;

    Any ideas?
    I hate to think that i shall need to rewrite this built-in functionality just to spoof the user agent.
    Note that also the utl_http.request_pieces is affected by the same problem.


  4. February 4

    Quick update: i replaced Oracle built-in getClob method with mine, that i rewrote from the ground up, and guess what? it works great!


  5. February 4

    Maybe worth a small blog post? So I also can reference it here?

  6. June 29

    Had similar problem.

    By adding a RSS news entry of 56 KB, select performance dropped from 2 seconds to more than one minute. Total RSS feed was then 240 KB.

    Obviously Oracle switches somewhere between varchar2 and clob implementation. And the CLOB implementation is not as efficient with some operations such as concatenation (‘||’).

    Changing ‘/item’ to:

    ‘//item[string-length(./content:encoded/text()) < 8000]'

    Fixed performance.

  7. July 3

    You should never use a // though, if you like performance that is, plus I wonder if content less than 8000 characters would always suffice…

Comments are closed.